mysql 半同步的作用_MYSQL半同步

MYSQL默认情况是异步复制的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不会验证从库是否接收完毕。这意味着当主服务器或从服务器发生故障时，有可能从服务器没有接收到的主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失。

半同步复制即主库完成数据库更新后不是先给修改数据的客户端做响应，而是先与所以从库同步binlog日志，一旦有从库反馈给主库同步完成则主库立即响应客户端，为了让客户端有更好的体验，这种等待从库反馈的时长不会可以设置的短一些，根据具体业务来设置主库等待从库的时长。半同步属于一种降低性能选择保证数据的折中方法。

配置思路：基于一主两从实现(主从配置已经配置完成)

1.查看主配置前的状态

2.主节点加载半同步主模块，开启半同步功能，设置超时时长为3000ms

3.从节点加载半同步从模块，开启半同步功能

4.验证配置结果

1.查看主配置前的状态

MariaDB [(none)]> show global variables like '%semi%';+---------------------------------------+--------------+

| Variable_name | Value |

+---------------------------------------+--------------+

| rpl_semi_sync_master_enabled | OFF |

| rpl_semi_sync_master_timeout | 10000 |

| rpl_semi_sync_master_trace_level | 32 |

| rpl_semi_sync_master_wait_no_slave | ON |

| rpl_semi_sync_master_wait_point | AFTER_COMMIT |

| rpl_semi_sync_slave_delay_master | OFF |

| rpl_semi_sync_slave_enabled | OFF |

| rpl_semi_sync_slave_kill_conn_timeout | 5 |

| rpl_semi_sync_slave_trace_level | 32 |

+---------------------------------------+--------------+

9 rows in set (0.001sec)

MariaDB [(none)]> show global status like '%semi%';+--------------------------------------------+-------+

| Variable_name | Value |

+--------------------------------------------+-------+

| Rpl_semi_sync_master_clients | 0 |

| Rpl_semi_sync_master_get_ack | 0 |

| Rpl_semi_sync_master_net_avg_wait_time | 0 |

| Rpl_semi_sync_master_net_wait_time | 0 |

| Rpl_semi_sync_master_net_waits | 0 |

| Rpl_semi_sync_master_no_times | 0 |

| Rpl_semi_sync_master_no_tx | 0 |

| Rpl_semi_sync_master_request_ack | 0 |

| Rpl_semi_sync_master_status | OFF |

| Rpl_semi_sync_master_timefunc_failures | 0 |

| Rpl_semi_sync_master_tx_avg_wait_time | 0 |

| Rpl_semi_sync_master_tx_wait_time | 0 |

| Rpl_semi_sync_master_tx_waits | 0 |

| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |

| Rpl_semi_sync_master_wait_sessions | 0 |

| Rpl_semi_sync_master_yes_tx | 0 |

| Rpl_semi_sync_slave_send_ack | 0 |

| Rpl_semi_sync_slave_status | OFF |

+--------------------------------------------+-------+

18 rows in set (0.000 sec)

2.主节点加载半同步主模块，开启半同步功能，设置超时时长为3000ms

[mysqld]

server-id=211log-bin

plugin-load-add=semisync_master

rpl_semi_sync_master_enabled=ON

rpl_semi_sync_master_timeout=3000

3.从节点加载半同步从模块，开启半同步功能

[mysqld]

server-id=212log-bin

plugin_load_add=semisync_slave

rpl_semi_sync_slave_enable=on

[mysqld]

server-id=213log-bin

plugin_load_add=semisync_slave

rpl_semi_sync_slave_enable=on

4.验证配置结果

主节点：

MariaDB [(none)]> show global status like '%semi%';+--------------------------------------------+-------+

| Variable_name | Value |

+--------------------------------------------+-------+

| Rpl_semi_sync_master_clients | 2 |

| Rpl_semi_sync_master_get_ack | 3 |

| Rpl_semi_sync_master_net_avg_wait_time | 0 |

| Rpl_semi_sync_master_net_wait_time | 0 |

| Rpl_semi_sync_master_net_waits | 3 |

| Rpl_semi_sync_master_no_times | 2 |

| Rpl_semi_sync_master_no_tx | 2 |

| Rpl_semi_sync_master_request_ack | 3 |

| Rpl_semi_sync_master_status | ON |

| Rpl_semi_sync_master_timefunc_failures | 0 |

| Rpl_semi_sync_master_tx_avg_wait_time | 613 |

| Rpl_semi_sync_master_tx_wait_time | 613 |

| Rpl_semi_sync_master_tx_waits | 1 |

| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |

| Rpl_semi_sync_master_wait_sessions | 0 |

| Rpl_semi_sync_master_yes_tx | 1 |

| Rpl_semi_sync_slave_send_ack | 0 |

| Rpl_semi_sync_slave_status | OFF |

+--------------------------------------------+-------+

18 rows in set (0.000sec)

MariaDB [(none)]> show global variables like '%semi%';+---------------------------------------+--------------+

| Variable_name | Value |

+---------------------------------------+--------------+

| rpl_semi_sync_master_enabled | ON |

| rpl_semi_sync_master_timeout | 3000 |

| rpl_semi_sync_master_trace_level | 32 |

| rpl_semi_sync_master_wait_no_slave | ON |

| rpl_semi_sync_master_wait_point | AFTER_COMMIT |

| rpl_semi_sync_slave_delay_master | OFF |

| rpl_semi_sync_slave_enabled | OFF |

| rpl_semi_sync_slave_kill_conn_timeout | 5 |

| rpl_semi_sync_slave_trace_level | 32 |

+---------------------------------------+--------------+

9 rows in set (0.001 sec)

将从节点mysql服务停掉一个

MariaDB [(none)]> show global status like '%semi%';+--------------------------------------------+-------+

| Variable_name | Value |

+--------------------------------------------+-------+

| Rpl_semi_sync_master_clients | 1 |#半同步客户端数量为0| Rpl_semi_sync_master_get_ack | 3 |

| Rpl_semi_sync_master_net_avg_wait_time | 0 |

| Rpl_semi_sync_master_net_wait_time | 0 |

| Rpl_semi_sync_master_net_waits | 3 |

| Rpl_semi_sync_master_no_times | 2 |

| Rpl_semi_sync_master_no_tx | 2 |

| Rpl_semi_sync_master_request_ack | 3 |

| Rpl_semi_sync_master_status | ON |

| Rpl_semi_sync_master_timefunc_failures | 0 |

| Rpl_semi_sync_master_tx_avg_wait_time | 613 |

| Rpl_semi_sync_master_tx_wait_time | 613 |

| Rpl_semi_sync_master_tx_waits | 1 |

| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |

| Rpl_semi_sync_master_wait_sessions | 0 |

| Rpl_semi_sync_master_yes_tx | 1 |

| Rpl_semi_sync_slave_send_ack | 0 |

| Rpl_semi_sync_slave_status | OFF |

+--------------------------------------------+-------+

18 rows in set (0.000 sec)

创建一个数据库还是能够迅速同步完成

MariaDB [(none)]>create database db5;

Query OK,1 row affected (0.001 sec)

将两个从节点mysql服务都停掉

MariaDB [(none)]> show global status like '%semi%';+--------------------------------------------+-------+

| Variable_name | Value |

+--------------------------------------------+-------+

| Rpl_semi_sync_master_clients | 0 |#半同步客户端数量为0| Rpl_semi_sync_master_get_ack | 4 |

| Rpl_semi_sync_master_net_avg_wait_time | 0 |

| Rpl_semi_sync_master_net_wait_time | 0 |

| Rpl_semi_sync_master_net_waits | 4 |

| Rpl_semi_sync_master_no_times | 3 |

| Rpl_semi_sync_master_no_tx | 3 |

| Rpl_semi_sync_master_request_ack | 5 |

| Rpl_semi_sync_master_status | OFF |

| Rpl_semi_sync_master_timefunc_failures | 0 |

| Rpl_semi_sync_master_tx_avg_wait_time | 586 |

| Rpl_semi_sync_master_tx_wait_time | 1172 |

| Rpl_semi_sync_master_tx_waits | 2 |

| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |

| Rpl_semi_sync_master_wait_sessions | 0 |

| Rpl_semi_sync_master_yes_tx | 2 |

| Rpl_semi_sync_slave_send_ack | 0 |

| Rpl_semi_sync_slave_status | OFF |

+--------------------------------------------+-------+

18 rows in set (0.000 sec)

创建数据库需要等待超时时间3秒后才能结束，且未数据库同步完成

MariaDB [(none)]>create database db6;

Query OK,1 row affected (3.001 sec)