mysql的半同步模式

1.半同步模式原理

mysql的主备库通过binlog日志保持一致，主库本地执行完事务，binlog日志落盘后即返回给用户；备库通过拉取主库binlog日志来同步主库的操作。默认情况下，主库与备库并没有严格的同步，因此存在一定的概率备库与主库的数据是不对等的。半同步特性的出现，就是为了保证在任何时刻主备数据一致的问题。相对于异步复制，半同步复制要求执行的每一个事务，都要求至少有一个备库成功接收后，才返回给用户。实现原理也很简单，主库本地执行完毕后，等待备库的响应消息(包含最新备库接收到的binlog(file,pos))，接收到备库响应消息后，再返回给用户，这样一个事务才算真正完成。在主库实例上，有一个专门的线程(ack_receiver)接收备库的响应消息，并以通知机制告知主库备库已经接收的日志，可以继续执行。

2.启用半同步模式

[root@mysql1 ~]# vim /etc/my.cnf配置完后要重新启动服务
[root@mysql1 ~]#/etc/init.d/mysqld restart

进入mysql：

#安装半同步插件
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';#打开半同步功能
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;#查看半同步功能状态
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+-------------------------------------------+------------+
| 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_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |  
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 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_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)

在slave端开启半同步功能：

[root@mysql1 ~]# vim /etc/my.cnf配置完后要重新启动服务
[root@mysql1 ~]#/etc/init.d/mysqld restart

进入mysql：

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;
Query OK, 0 rows affected (0.00 sec)mysql> STOP SLAVE IO_THREAD; #重启io线程，半同步才能生效
Query OK, 0 rows affected (0.00 sec)mysql> START SLAVE IO_THREAD; ##重启io线程，半同步才能生效
Query OK, 0 rows affected (0.00 sec)mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.01 sec)mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

3.测试

在master端写入数据

mysql> insert into lee.userlist values ('xixi','123123');
Query OK, 1 row affected (0.01 sec)mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 2     |      #链接客户端两个
| Rpl_semi_sync_master_net_avg_wait_time     | 0     | 
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 2     |
| Rpl_semi_sync_master_no_times              | 0     |  
| Rpl_semi_sync_master_no_tx                 | 0     |      #未同步数据0笔
| Rpl_semi_sync_master_status                | ON    |      #表示半同步复制启用中
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 981   |
| Rpl_semi_sync_master_tx_wait_time          | 981   |
| 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     |        #已同步数据1笔
+--------------------------------------------+-------+

模拟故障：

#在slave端10、20端都断开slave服务连接mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)#在master端插入数据mysql> insert into lee.userlist values ('xixi','123123');
Query OK, 1 row affected (10.00 sec)                       #10秒超时，会卡住十秒钟然后从同步 转为异步，再插入数据mysql> SHOW STATUS LIKE 'Rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |      连接人数变为0
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 2     |
| Rpl_semi_sync_master_no_times              | 1     |
| Rpl_semi_sync_master_no_tx                 | 1     |      #一笔数据为同步
| Rpl_semi_sync_master_status                | OFF   |      #自动转为异步模式，当slave恢复
| Rpl_semi_sync_master_timefunc_failures     | 0     |      #会自动恢复
| Rpl_semi_sync_master_tx_avg_wait_time      | 981   |
| Rpl_semi_sync_master_tx_wait_time          | 981   |
| 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     |    #主服务器成功提交事务并得到从服务器 确认的次数
+--------------------------------------------+-------+
14 rows in set (0.00 sec)