Mysql半同步复制模式说明及配置示例 - 运维小结

mysql> CHANGE MASTER TO
    -> MASTER_HOST='master_host_name',
    -> MASTER_USER='replication_user_name',
    -> MASTER_PASSWORD='replication_password',
    -> MASTER_LOG_FILE='recorded_log_file_name',
    -> MASTER_LOG_POS=recorded_log_position;
    -> MASTER_CONNECT_RETRY=10;                           #连接主库失败时，每隔10s钟就重新连一下! 一般省略这一行配置，不用配置这一行。

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

[要保证/usr/local/mysql/lib/plugin/目录下有semisync_master.so文件 (默认编译安装后就有)]
---------------------------------------------------------------------------------------
如果要卸载(前提是要关闭半同步复制功能)，就执行
mysql> UNINSTALL PLUGIN rpl_semi_sync_master;

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

[要保证/usr/local/mysql/lib/plugin/目录下有semisync_slave.so文件 (默认编译安装后就有)]
---------------------------------------------------------------------------------------
如果要卸载(前提是要关闭半同步复制功能)，就执行
mysql> UNINSTALL PLUGIN rpl_semi_sync_slave;

mysql> show plugins;
........
| rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;

mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1

plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1

plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1

mysql> STOP SLAVE IO_THREAD;
mysql> START SLAVE IO_THREAD;

2019-01-05T10:03:40.104327Z 5 [Note] While initializing dump thread for slave with UUID <ce9aaf22-5af6-11e6-850b-000c2988bad2>, found a zombie dump thread with the same UUID. Master is killing the zombie dump thread(4).
2019-01-05T10:03:40.111175Z 4 [Note] Stop asynchronous binlog_dump to slave (server_id: 2)
2019-01-05T10:03:40.119037Z 5 [Note] Start binlog_dump to master_thread_id(5) slave_server(2), pos(mysql-bin.000003, 621)
2019-01-05T10:03:40.119099Z 5 [Note] Start semi-sync binlog_dump to slave (server_id: 2), pos(mysql-bin.000003, 621)

mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.20 sec)

mysql> show variables like "rpl_semi_sync_master_timeout";
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| rpl_semi_sync_master_timeout | 10000 |
+------------------------------+-------+
1 row in set (0.01 sec)

mysql> create database bobo;
Query OK, 1 row affected (0.05 sec)

mysql> create table bobo.ceshi(id int);
Query OK, 0 row affected (0.28 sec)

mysql> insert into bobo.ceshi values(1);
Query OK, 1 row affected (0.09 sec)

mysql> stop slave;

mysql> insert into bobo.ceshi values(2);
Query OK, 1 row affected (10.01 sec)

mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | OFF   |
+-----------------------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'Rpl_semi_sync_slave_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_slave_status  | OFF   |
+-----------------------------+-------+
1 row in set (0.01 sec)

mysql> start slave;

mysql> insert into bobo.ceshi values(3);
Query OK, 1 row affected (0.00 sec)

mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'Rpl_semi_sync_slave_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_slave_status  | ON   |
+-----------------------------+-------+
1 row in set (0.00 sec)

2019-01-05T11:51:49.855452Z 6 [Warning] Timeout waiting for reply of binlog (file: mysql-bin.000003, pos: 1447), semi-sync up to file mysql-bin.000003, position 1196.
2019-01-05T11:51:49.855742Z 6 [Note] Semi-sync replication switched OFF.

2019-01-05T11:52:40.477098Z 7 [Note] Start binlog_dump to master_thread_id(7) slave_server(2), pos(mysql-bin.000003, 1196)
2019-01-05T11:52:40.477168Z 7 [Note] Start semi-sync binlog_dump to slave (server_id: 2), pos(mysql-bin.000003, 1196)
2019-01-05T11:52:40.523475Z 0 [Note] Semi-sync replication switched ON at (mysql-bin.000003, 1447)

mysql> show variables like '%Rpl%';
+-------------------------------------------+------------+
| 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 |
| rpl_stop_slave_timeout                    | 31536000   |
+-------------------------------------------+------------+
7 rows in set (0.30 sec)

mysql> show status like '%Rpl_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             | 6     |
| 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      | 1120  |
| Rpl_semi_sync_master_tx_wait_time          | 4483  |
| Rpl_semi_sync_master_tx_waits              | 4     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 4     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

5.6版本的半同步复制，dump thread 承担了两份不同且又十分频繁的任务：传送binlog 给slave ，还需要等待slave反馈信息，而且这两个任务是串行的，dump thread 必须等待 slave 返回之后才会传送下一个 events 事务。dump thread 已然成为整个半同步提高性能的瓶颈。在高并发业务场景下，这样的机制会影响数据库整体的TPS 。

5.7版本的半同步复制中，独立出一个 ack collector thread ，专门用于接收slave 的反馈信息。这样master 上有两个线程独立工作，可以同时发送binlog 到slave ，和接收slave的反馈。

mysql主数据库： 172.16.60.205
mysql从数据库： 172.16.60.206
mysql5.6.39 安装部署，参考：https://www.cnblogs.com/kevingrace/p/6109679.html

主数据库172.16.60.205配置：
[root@mysql-master ~]# cat /usr/local/mysql/my.cnf
.......
server-id=1
log-bin=mysql-bin
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed

从数据库172.16.60.206配置：
[root@mysql-slave ~]# cat /usr/local/mysql/my.cnf
.........
server-id=2
log-bin=mysql-bin
slave-skip-errors = all

然后从库同步操作，不需要跟master_log_file 和 master_log_pos=120
mysql> change master to master_host = '172.16.60.205', master_port = 3306, master_user ='slave', master_password ='slave@123';

其他的配置，参考https://www.cnblogs.com/kevingrace/p/6256603.html
即主从同步配置不过滤库，是基于整个数据库的同步。其他的操作和这个文档中记录的差不多

========================================================
主数据库
[root@mysql-master ~]# mysql -p123456
......
mysql> use kevin;
mysql> show tables;
+-----------------+
| Tables_in_kevin |
+-----------------+
| haha            |
+-----------------+
1 row in set (0.00 sec)
  
mysql> select * from haha;
+----+--------+
| id | name   |
+----+--------+
|  2 | anxun  |
|  3 | huoqiu |
|  4 | xihuju |
+----+--------+
3 rows in set (0.00 sec)
  
从数据库：
[root@mysql-slave ~]# mysql -p123456
..........
..........
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.60.205
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
.........
.........
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
  
mysql> select * from kevin.haha;
+----+--------+
| id | name   |
+----+--------+
|  2 | anxun  |
|  3 | huoqiu |
|  4 | xihuju |
+----+--------+
3 rows in set (0.00 sec)
  
主数据库插入数据
mysql> insert into haha values(1,"linux");
Query OK, 1 row affected (0.03 sec)
mysql> insert into haha values(11,"linux-ops");
Query OK, 1 row affected (0.04 sec)
  
从数据库查看
mysql> select * from kevin.haha;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | linux     |
|  2 | anxun     |
|  3 | huoqiu    |
|  4 | xihuju    |
| 11 | linux-ops |
+----+-----------+
5 rows in set (0.00 sec)
  
mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.39-log |
+------------+
1 row in set (0.00 sec)
  
mysql> show variables like "have_dynamic_loading";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| have_dynamic_loading | YES   |
+----------------------+-------+
1 row in set (0.00 sec)
  
从上面可知，已满足mysql半同步复制功能部署的条件：
1）MySQL 5.5及以上版本！
2）变量have_dynamic_loading为YES
3）主从复制已经存在！
  
===================================================
接下来进行mysql半同步复制环境部署：
  
1）加载mysql半同步复制的插件
主数据库执行:
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 row affected (0.04 sec)
  
从数据库执行：
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 row affected (0.04 sec)
  
2）查看插件是否加载成功的两种方式：
主数据库执行：
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)
  
从数据库执行：
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_slave  | ACTIVE        |
+----------------------+---------------+
2 rows in set (0.01 sec)
  
3） 启动半同步复制
主数据库执行：
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
  
从数据库执行：
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
  
----------------------------------------------------------------------------------------------------------------------------------------------
温馨提示：
除了上面的设置方法以外， 还可以以下面方式启动半同步复制，即在my.cnf文件中添加启动配置（推荐这种方式）：
主数据库
[root@mysql-master ~]# vim /usr/local/mysql/my.cnf      #在[mysqld]区域添加下面内容
........
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=ON         #或者设置为"1"，即开启半同步复制功能
rpl-semi-sync-master-timeout=1000       #超时时间为1000ms，即1s
  
[root@mysql-master ~]# /etc/init.d/mysqld restart
  
主数据库
[root@mysql-slave ~]# vim /usr/local/mysql/my.cnf
........
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=ON
  
[root@mysql-slave ~]# /etc/init.d/mysqld restart
----------------------------------------------------------------------------------------------------------------------------------------------
  
4）查看半同步是否在运行
主数据库执行：
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+
1 row in set (0.00 sec)
  
从数据库执行(此时可能还是OFF状态，需要在下一步重启IO线程后，从库半同步状态才会为ON)：
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)
 
5）重启从数据库上的IO线程
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.04 sec)
  
mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
  
重启从数据的IO线程之后，  slave会在master上注册为半同步复制的slave角色
查看主数据库上的error日志，就会发现下面信息：
[root@mysql-master ~]# tail -f /data/mysql/data/mysql-error.log
2019-01-06 23:23:34 10436 [Note] Semi-sync replication initialized for transactions.
2019-01-06 23:23:34 10436 [Note] Semi-sync replication enabled on the master.
2019-01-06 23:27:28 10436 [Note] Stop asynchronous binlog_dump to slave (server_id: 2)
2019-01-06 23:27:28 10436 [Note] Start semi-sync binlog_dump to slave (server_id: 2), pos(mysql-bin.000004, 2944)
2019-01-06 23:32:03 10436 [Note] Stop semi-sync binlog_dump to slave (server_id: 2)
2019-01-06 23:32:03 10436 [Note] Start semi-sync binlog_dump to slave (server_id: 2), pos(mysql-bin.000004, 3357)
  
如上操作，就已经部署了mysql的半同步复制环境
  
现在往主数据库插入新数据
mysql> insert into kevin.haha values(5,"grace");
Query OK, 1 row affected (0.13 sec)
  
mysql> insert into kevin.haha values(6,"huihui");
Query OK, 1 row affected (0.11 sec)
  
到从数据库上查看，正常复制过来了
  
mysql> show slave status \G;
.........
.........
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
  
mysql> select * from kevin.haha;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | linux     |
|  2 | anxun     |
|  3 | huoqiu    |
|  4 | xihuju    |
|  5 | grace     |
|  6 | huihui    |
| 11 | linux-ops |
+----+-----------+
7 rows in set (0.00 sec)
  
查看主数据库
mysql> show status like '%Rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 475   |               #网络等待的平均时间
| Rpl_semi_sync_master_net_wait_time         | 1427  |               #网络等待时间    
| Rpl_semi_sync_master_net_waits             | 3     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |               #大于0就是异步。半同步是应为0  
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 622   |               # 平均等待时间
| Rpl_semi_sync_master_tx_wait_time          | 1868  |               #总的等待时间 
| Rpl_semi_sync_master_tx_waits              | 3     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 3     |               #大于0就是半同步模式
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
 
==========================================================
现在做下测试：
当半同步复制发生超时（由rpl_semi_sync_master_timeout参数控制，单位是毫秒，默认为10000，即10s），会暂时关闭半同步复制，转而使用异步复制。
当master dump线程发送完一个事务的所有事件之后，如果在rpl_semi_sync_master_timeout内，收到了从库的响应，则主从又重新恢复为半同步复制。
 
mysql> show variables like "rpl_semi_sync_master_timeout";
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| rpl_semi_sync_master_timeout | 10000 |
+------------------------------+-------+
1 row in set (0.00 sec)
 
关闭从数据库的slave
mysql> stop slave;
Query OK, 0 rows affected (0.14 sec)
 
然后在主数据库插入一条数据，发现半同步复制会发生超时
发生超时后，暂时关闭半同步复制，转而使用异步复制
mysql> insert into kevin.haha values(55,"laolao");
Query OK, 1 row affected (10.13 sec)
 
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | OFF   |
+-----------------------------+-------+
1 row in set (0.00 sec)
 
从数据库也会关闭半同步
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF   |
+----------------------------+-------+
1 row in set (0.00 sec)
 
接着再打开从数据库的slave
mysql> start slave; 
Query OK, 0 rows affected (0.04 sec)
 
然后主数据再插入一条数据，就会发现半同步复制不会超时，半同步复制功能打开也打开了
mysql> insert into kevin.haha values(555,"laolaolao");
Query OK, 1 row affected (0.04 sec)
 
mysql> select * from haha;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | linux     |
|   2 | anhui     |
|   3 | huoqiu    |
|   4 | xihuju    |
|   5 | grace     |
|   6 | huihui    |
|  11 | linux-ops |
|  55 | laolao    |
| 555 | laolaolao |
+-----+-----------+
9 rows in set (0.00 sec)
 
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+
1 row in set (0.00 sec)
 
查看从数据库
mysql> select * from haha;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | linux     |
|   2 | anhui     |
|   3 | huoqiu    |
|   4 | xihuju    |
|   5 | grace     |
|   6 | huihui    |
|  11 | linux-ops |
|  55 | laolao    |
| 555 | laolaolao |
+-----+-----------+
9 rows in set (0.00 sec)
 
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

先在从数据库上关闭半同步复制功能，然后卸载半同步插件
mysql> stop slave;
Query OK, 0 rows affected (0.07 sec)

mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF   |
+----------------------------+-------+
1 row in set (0.00 sec)

mysql> UNINSTALL PLUGIN rpl_semi_sync_slave; 
Query OK, 0 rows affected (0.00 sec)

mysql> show status like 'Rpl_semi_sync_slave_status';
Empty set (0.00 sec)

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';
Empty set (0.01 sec)

删除my.cnf里面的半同步配置
[root@mysql-slave mysql]# vim /usr/local/mysql/my.cnf
#plugin-load=rpl_semi_sync_slave=semisync_slave.so
#rpl_semi_sync_slave_enabled=ON

一定要重启mysql服务
[root@mysql-slave mysql]# /etc/init.d/mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL..                                           [  OK  ]

=========================================================
接着再主数据库关闭半同步复制功能，卸载半同步插件
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | OFF   |
+-----------------------------+-------+
1 row in set (0.00 sec)

mysql> show status like '%Rpl_semi%';
+--------------------------------------------+--------+
| Variable_name                              | Value  |
+--------------------------------------------+--------+
| Rpl_semi_sync_master_clients               | 0      |                               #确保这一行的数值为0，即没有半同步复制的客户端
| Rpl_semi_sync_master_net_avg_wait_time     | 40186  |
| Rpl_semi_sync_master_net_wait_time         | 401860 |
| Rpl_semi_sync_master_net_waits             | 10     |
| Rpl_semi_sync_master_no_times              | 2      |
| 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      | 592    |
| Rpl_semi_sync_master_tx_wait_time          | 4737   |
| Rpl_semi_sync_master_tx_waits              | 8      |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0      |
| Rpl_semi_sync_master_wait_sessions         | 0      |
| Rpl_semi_sync_master_yes_tx                | 8      |
+--------------------------------------------+--------+
14 rows in set (0.00 sec)

mysql> UNINSTALL PLUGIN rpl_semi_sync_master;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%'; 
Empty set (0.00 sec)

mysql> show status like 'Rpl_semi_sync_master_status';
Empty set (0.00 sec)

删除my.cnf里面的半同步配置
[root@mysql-master ~]# vim /usr/local/mysql/my.cnf 
#plugin-load=rpl_semi_sync_master=semisync_master.so
#rpl_semi_sync_master_enabled=ON
#rpl-semi-sync-master-timeout=1000

一定要重启mysql服务
[root@mysql-master ~]# /etc/init.d/mysql restart
Shutting down MySQL...                                     [  OK  ]
Starting MySQL..                                           [  OK  ]

=========================================================
再接着重启从数据库上的IO线程
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.04 sec)

mysql> START SLAVE IO_THREAD;  
Query OK, 0 rows affected (0.00 sec)

=========================================================
通过上面操作，就完全删掉了mysql半同步复制模式，恢复异步复制模式。
在关闭半同步复制模式的过程中，可以查看/data/mysql/data/mysql-error.log日志信息，从中观察到半同步关闭的信息。

在从机重新start slave
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status \G;
.........
.........
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

接着在主数据插入新数据
mysql> select * from kevin.haha;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | linux     |
|   2 | anhui     |
|   3 | huoqiu    |
|   4 | xihuju    |
|   5 | grace     |
|   6 | huihui    |
|  11 | linux-ops |
|  55 | laolao    |
| 555 | laolaolao |
+-----+-----------+
9 rows in set (0.00 sec)

mysql> insert into kevin.haha values(12,"wangjuan");
Query OK, 1 row affected (0.04 sec)

mysql> insert into kevin.haha values(13,"congcong");
Query OK, 1 row affected (0.04 sec)

去从库上查看，发现已经同步过来了
mysql> select * from kevin.haha;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | linux     |
|   2 | anhui     |
|   3 | huoqiu    |
|   4 | xihuju    |
|   5 | grace     |
|   6 | huihui    |
|  11 | linux-ops |
|  12 | wangjuan |
|  13 | congcong |
|  55 | laolao    |
| 555 | laolaolao |
+-----+-----------+
9 rows in set (0.00 sec)

======================================================
温馨提示：
经过几次实验，发现了一个坑，就是在做mysql半同步复制模式下，主从同步配置要是基于整个数据库的同步，而不要使用"binlog_do_db"
和"binlog_ingore_db"进行过滤库的同步配置，否则会造成半同步复制模式下的数据同步失败。

之前做过一次实验，mysql主从关系是针对某个具体库进行配置同步的，即：
主数据库的my.cnf配置：
#主从同步配置
server-id=1        
log-bin=mysql-bin   
binlog-do-db=kevin
binlog-ignore-db=mysql  
sync_binlog = 1    
binlog_checksum = none
binlog_format = mixed

# 半同步复制功能开启
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=ON    
rpl-semi-sync-master-timeout=1000  

从数据库的my.cnf配置：
#主从同步配置
server-id=2   
log-bin=mysql-bin   
replicate-do-db=kevin
replicate-ignore-db=mysql  
slave-skip-errors = all

# 半同步复制功能开启
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=ON

然后从库通过下面方式跟主数据库同步
mysql> change  master to master_host='172.16.60.205,master_user='slave',master_password='slave@123',master_log_file='mysql-bin.000007',master_log_pos=120;

以上配置的mysql主从同步是针对kevin这个具体的库的，在后续半同步复制模式下，主从数据同步失败。
而且在这种情况下，删除半同步复制模式配置，恢复到异步同步模式，主从数据同步还是失败。

-----------------------------------------------------------
最后修改主从数据库的my.cnf文件，按照下面方式进行主从同步，则半同步复制模式下，主从数据同步正常。
并且删除半同步复制模式配置，恢复到异步同步模式，主从数据同步一样正常。

主数据库的my.cnf文件调整后的配置：
#主从同步配置
server-id=1
log-bin=mysql-bin
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed

# 半同步复制功能开启
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1
rpl-semi-sync-master-timeout=1000

从数据库的my.cnf文件调整后的配置：
#主从同步配置
server-id=2
log-bin=mysql-bin
slave-skip-errors = all

# 半同步复制功能开启
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1
 
然后从库通过下面方式跟主数据库同步
mysql> change master to master_host = '172.16.60.205', master_port = 3306, master_user ='slave', master_password ='slave@123';

============================================================
特别注意下：
在做mysq主从同步时最好别过滤库了，即最好进行基于整个数据库的同步配置，同步命令为：
"change master to master_host = '主数据库ip', master_port = 主数据库mysql端口, master_user ='同步的用户', master_password ='同步的密码';"

使用过滤库或过滤表的方式进行主从同步配置，后续会带来一些比较麻烦的坑。
如果业务数比较多的情况下，就使用mysql多实例方式进行同步，一个业务一个mysql实例，主从同步配置中不要进行过滤库或表的配置，即基于整个数据库同步。

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)

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_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)