MySQL5.6 关闭半同步

环境

参数

Master

mysql> show global status like '%semi%';
+--------------------------------------------+-----------+
| Variable_name                              | Value     |
+--------------------------------------------+-----------+
| Rpl_semi_sync_master_clients               | 2         |
| Rpl_semi_sync_master_net_avg_wait_time     | 508       |
| Rpl_semi_sync_master_net_wait_time         | 480620193 |
| Rpl_semi_sync_master_net_waits             | 945539    |
| Rpl_semi_sync_master_no_times              | 2         |
| 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      | 910       |
| Rpl_semi_sync_master_tx_wait_time          | 576324352 |
| Rpl_semi_sync_master_tx_waits              | 633076    |
| Rpl_semi_sync_master_wait_pos_backtraverse | 1         |
| Rpl_semi_sync_master_wait_sessions         | 0         |
| Rpl_semi_sync_master_yes_tx                | 1900240   |
| Rpl_semi_sync_slave_status                 | OFF       |
+--------------------------------------------+-----------+
15 rows in set (0.00 sec)
mysql> show global variables like '%semi%';
+------------------------------------+---------+
| Variable_name                      | Value   |
+------------------------------------+---------+
| rpl_semi_sync_master_enabled       | ON      |
| rpl_semi_sync_master_timeout       | 1000000 |
| rpl_semi_sync_master_trace_level   | 32      |
| rpl_semi_sync_master_wait_no_slave | ON      |
| rpl_semi_sync_slave_enabled        | OFF     |
| rpl_semi_sync_slave_trace_level    | 32      |
+------------------------------------+---------+
6 rows in set (0.01 sec)

slave

mysql> 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_slave_enabled        | ON    |
| rpl_semi_sync_slave_trace_level    | 32    |
+------------------------------------+-------+
6 rows in set (0.00 sec)

注释:
本次使用的MySQL是5.6
5.7 中的show global variables like ‘%semi%’ 会多两个参数
rpl_semi_sync_master_wait_for_slave_count: master获得ACK的数量
rpl_semi_sync_master_wait_point : master 等待ACK的位置

场景模拟

一个脚本不断往master插入数据

#!/bin/bash
for i in {1..300000}
do
#sleep 1
/mysql/bin/mysql -uroot -p'mbV' -e "insert into test_li.t1 values($i)"
/mysql/bin/mysql -uroot -p'mbV' -e "select i from test_li.t1 order by i desc limit 1;"
done

在脚本运行的同时进行关闭半同步

关闭半同步

总述:

1.调整master 参数
2.关闭slave半同步
3.关闭master半同步

操作

1.调整master参数

    set global rpl_semi_sync_master_timeout=100;
    set global rpl_semi_sync_master_wait_no_slave='off';

目的: rpl_semi_sync_master_timeout 在slave关闭半同步的时候缩短等待ACK锁的时间
rpl_semi_sync_master_wait_no_slave :设置为off,是当一个发生半同步降级后,如果slave 追上同步,MySQL不会自动升级为半同步模式

2.关闭slave 半同步,并重启复制

重点注意:
注意 MySQL 5.6 中的master 获取ack 数量和有多少个slave无关(N>=1) 即只有当所有的半同步的slave都停了的时候才会发生ack 等待.关闭一个是不会发生等待的
因此只需要关闭1个slave 是不会发生ACK等待,不需要也不能关闭master的半同步模式

set global rpl_semi_sync_slave_enabled='off';
stop slave;
start slave;

3.关闭master 半同步

set  global rpl_semi_sync_master_enabled='off';

错误操作

如果发生ACK等待

mysql> show processlist;
+--------+----------+-------------------+---------+-------------+------+-----------------------------------------------------------------------+-------------------------------------+
| Id     | User     | Host              | db      | Command     | Time | State                                                                 | Info                                |
+--------+----------+-------------------+---------+-------------+------+-----------------------------------------------------------------------+-------------------------------------+
|      1 | root     | localhost         | test_li | Sleep       |   66 |                                                                       | NULL                                |
|  45964 | root     | localhost         | NULL    | Query       |    0 | init                                                                  | show processlist                    |
| 755735 | rpl_user | 172.30.2.68:57061 | NULL    | Binlog Dump |  441 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL                                |
| 764915 | root     | localhost         | NULL    | Query       |  142 | Waiting for semi-sync ACK from slave                                  | insert into test_li.t1 values(1715) |
+--------+----------+-------------------+---------+-------------+------+-----------------------------------------------------------------------+-------------------------------------+
4 rows in set (0.00 sec)

解决办法:

恢复一个slave 的半同步即可恢复

错误解决操作

1.调整 rpl_semi_sync_master_timeout 和rpl_semi_sync_master_wait_no_slave 参数在master操作不会影响现有回话的
(可以证明关闭半同步 先调整master
set global rpl_semi_sync_master_timeout=100;
set global rpl_semi_sync_master_wait_no_slave=’off’;
参数的重要性)
2关闭master 的半同步
set global rpl_semi_sync_master_enabled=’off’; 该操作会造成数据宕机!!!!!!!!!!!!!!!!!!!!!!!!!

关闭错误步骤

1.不修改 master 参数直接将slave关闭半同步
这样的主会在每次等待超时变成异步之后,slave追上后重新变成半同步如此循环
2.先关闭slave 半同步,在主没有修改rpl_semi_sync_master_timeout,rpl_semi_sync_master_wait_no_slave 参数下直接关闭master 半同步,如果发生了等待关闭主的半同步则会直接宕机