mysql 半同步复制和异步复制
-- 在主库中安装半同步插件,开启半同步复制功能
install plugin rpl_semi_sync_master soname 'semisync_master.so';
set global rpl_semi_sync_master_enabled=on;
show plugins;
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> set global rpl_semi_sync_master_enabled=on;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show variables like '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
mysql>
-- 在从库安装半同步复制插件,开启半同步复制功能
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
set global rpl_semi_sync_slave_enabled=on;
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.04 sec)
mysql> set global rpl_semi_sync_slave_enabled=on;
Query OK, 0 rows affected (0.00 sec)
mysql>
-- 重启从库的IO线程,激活半同步复制
stop slave io_thread;
start slave io_thread;
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.03 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql>
-- 在主库上查看半同步复制是否正常运行
mysql> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| 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_status | ON |
| 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 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
mysql>
-- 在从库上查看半同步状态
mysql> show global status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
mysql>
-- 半同步复制和异步模式的切换
-- 查看半同步方式的默认的timeout的值,默认是10秒
mysql> show variables like '%semi_sync_master_timeout%';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| rpl_semi_sync_master_timeout | 10000 |
+------------------------------+-------+
1 row in set (0.00 sec)
mysql>
-- 把从库的IO thread关闭,并查看从库的半同步状态
stop slave io_thread;
show global status like '%semi%';
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.09 sec)
mysql> show global status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
mysql>
-- 查看主库的半同步状态,是开启的,并且有从库连接
show global status like '%semi%';
mysql> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 2953 |
| Rpl_semi_sync_master_net_wait_time | 64966 |
| Rpl_semi_sync_master_net_waits | 22 |
| 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 | 4409 |
| Rpl_semi_sync_master_tx_wait_time | 44093 |
| Rpl_semi_sync_master_tx_waits | 10 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 22 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
mysql>
-- 然后在主库上插入语句,发现执行这条语句很慢,主库一直在等待从库的回复,知道超过了默认的10秒
insert into t1 values('b');
mysql> insert into t1 values('b');
Query OK, 1 row affected (10.11 sec)
mysql>
-- 这个时候,再次查看主库的半同步复制状态,发现主库的半同步模式是OFF。说明已经从半同步复制自动切换到了异步复制模式了
mysql> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 2953 |
| Rpl_semi_sync_master_net_wait_time | 64966 |
| Rpl_semi_sync_master_net_waits | 22 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 4409 |
| Rpl_semi_sync_master_tx_wait_time | 44093 |
| Rpl_semi_sync_master_tx_waits | 10 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 22 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
mysql>
-- 再次从异步复制模式切换为半同步模式,则需要重启从库的IO thread .
start slave io_therad;
mysql> show global status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
mysql>
mysql> show global status like '%semi%';
+--------------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------------+--------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 7618 |
| Rpl_semi_sync_master_net_wait_time | 175232 |
| Rpl_semi_sync_master_net_waits | 23 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 4409 |
| Rpl_semi_sync_master_tx_wait_time | 44093 |
| Rpl_semi_sync_master_tx_waits | 10 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 22 |
+--------------------------------------------+--------+
14 rows in set (0.00 sec)
mysql>
END