mysql5.6 semi replication 半同步复制配置

--###MYSQL 半同步配置 用于主备切换所以主备配置相同

--1、插件位置
mysql> show variables like 'plugin_dir'
-> ;
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| plugin_dir | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+


[mysql@sql2 data]$ ll /usr/local/mysql/lib/plugin/semisync_*
-rwxr-xr-x 1 root root 415020 Jul 5 19:50 /usr/local/mysql/lib/plugin/semisync_master.so
-rwxr-xr-x 1 root root 248711 Jul 5 19:50 /usr/local/mysql/lib/plugin/semisync_slave.so

 

--2、master/slave 加载插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

show plugins; #--验证加载是否正常

--3、master/slave 开启半同步功能
vi /etc/mysql/my.cnf
rpl_semi_sync_master_enabled = 1 #--master 开启半同步功能
rpl_semi_sync_master_timeout = 3000 #--超出3秒改为异步复制

rpl_semi_sync_slave_enabled = 1 #--slave 开启半同步功能

--4、重启mysql

 

--###监控半同步复制 监控同步模式/监控同步状态
--master
mysql> show status like '%Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 476 |
| Rpl_semi_sync_master_net_wait_time | 476 |
| Rpl_semi_sync_master_net_waits | 1 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 610 |
| Rpl_semi_sync_master_tx_wait_time | 610 |
| 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_master_status = on #--master 开启半同步功能

Rpl_semi_sync_master_clients = 1 #--有多少个client 开启半同步功能

 

--slave
mysql> show status like '%Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+

 

--模拟超时后 半同步状态
--slave
stop slave io_thread;

mysql> show status like '%Rpl_semi_sync_slave_status%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+

 

--master 超过 rpl_semi_sync_master_timeout 该值，master不再等待slave接受确认，而是自动提交并变为异步复制
mysql> show variables like '%rpl_semi_sync_master_timeout%';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| rpl_semi_sync_master_timeout | 3000 |
+------------------------------+-------+