MySQL双节点部署主从半同步复制并测试
| 编号 | 主机名 | IP | 数据库版本 | 数据库功能 |
|---|---|---|---|---|
| 1 | mysqlnode1 | 192.168.1.121 | mysql5.6.21源码包安装 | MySQL主库 |
| 2 | mysqlnode2 | 192.168.1.122 | mysql5.6.21源码包安装 | MySQL从库 |
以下操作主从都要执行
[root@mysqlnode1 ~]# vim /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=mysqlnode1
NTPSERVERARGS=iburst
NETWORKING_IPV6=off
[root@mysqlnode2 mysql]# vim /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=mysqlnode2
NTPSERVERARGS=iburst
NETWORKING_IPV6=off
~以下操作主从都要执行
[root@mysqlnode1 ~]# vim /etc/hosts
192.168.1.121 mysqlnode1
192.168.1.122 mysqlnode2
[root@mysqlnode2 ~]# vim /etc/hosts
192.168.1.121 mysqlnode1
192.168.1.122 mysqlnode2
~这里我采用的是基于GTID的主从复制。具体操作请转到https://www.cnblogs.com/plutozzl/p/13217838.html
安装前首先要加载插件，由于半同步复制是一个功能模块，需要进行加载。半同步模块路径（源码包安装位置：/usr/local/mysql/lib/plugin）
以下操作在主库执行
mysql> install plugin rpl_semi_sync_master soname ‘ semisync_master.so‘;
Query OK, 0 rows affected (0.01 sec)
## 进行加载，如果目录下没有，执行不会成功
mysql> select plugin_name,plugin_status from inform ation_schema.plugins where plugin_name like ‘%semi%‘;
+----------------------+---------------+
| plugin_name | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
+----------------------+---------------+
1 row in set (0.00 sec)
## 查看插件是否加载成功以下操作在备库执行
mysql> install plugin rpl_semi_sync_slave soname ‘s emisync_slave.so‘;
Query OK, 0 rows affected (0.00 sec)
## 进行加载
mysql> select plugin_name,plugin_status from inform ation_schema.plugins where plugin_name like ‘%semi% ‘;
+---------------------+---------------+
| plugin_name | plugin_status |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE |
+---------------------+---------------+
1 row in set (0.00 sec)
## 查看插件是否加载成功安装完插件后半同步复制默认是关闭的，需要手动设置参数进行打开。
启动的两种方式：
1）登录MySQL数据库后进行命令行操作，分别在主库和备库执行。
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)
##执行后查看半同步是否在运行
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 | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
##这里显示OFF状态是正常的，需要重启IOThread线程后才会变为ON状态2）修改配置文件，推荐使用这种办法。
主库:
[root@mysqlnode1 mysql]# vim my.cnf
...
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1从库:
[root@mysqlnode2 mysql]# vim my.cnf
...
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1注意是从数据库
mysql> stop slave IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
##重启IO线程后，slave会在master上注册为半同步复制的slave角色。
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)
##查看状态变为ON
至此，半同步复制环境部署完成
当半同步复制发生超时的时候，会暂时关闭半同步复制，转而使用异步复制。当master dump线程发送完一个事务的所有事件之后，如果在rpl_semi_sync_master_timeout（这个参数是设置超时时间的，单位毫秒默认10000）内，收到从库响应则主从重新恢复为半同步复制。
#查看当前数据库半同步复制超时时间（主库操作）
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.00 sec)
#主库进行数据插入操作，半同步复制会发生超时，而后半同步复制关闭启用异步复制。（主库操作）
mysql> insert into mstest values (2);
Query OK, 1 row affected (**10.01** sec)
#可以看到执行时间超过10s此时已经转为异步复制
主库：
mysql> show status like ‘Rpl_semi_sync_master_status‘;
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | OFF |
+-----------------------------+-------+
1 row in set (0.01 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.00 sec)
mysql> select * from mstest;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 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> show status like ‘Rpl_semi_sync_slave_status‘;
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)验证完成
上述操作证明了，半同步超时后整个集群会退出半同步复制状态，转而进入异步复制状态。而当恢复正常后，半同步复制会自动恢复。
先在从库进行操作，再回到主库进行操作
从库
# 停止slave
mysql> stop slave;
Query OK, 0 rows affected (0.00 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)
#删除my.cnf文件相关内容
[root@mysqlnode2 ~]# vim /usr/local/mysql/my.cnf
...
#plugin-load=rpl_semi_sync_slave=semisync_slave.so
#rpl_semi_sync_slave_enabled=1
#重启数据库服务
[root@mysqlnode2 ~]# /etc/init.d/mysql restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!主库
# 查看当前模块状态
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 | 10008 |
| Rpl_semi_sync_master_net_wait_time | 20017 |
| Rpl_semi_sync_master_net_waits | 2 |
| 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 | 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.01 sec)
# 卸载模块
mysql> UNINSTALL PLUGIN rpl_semi_sync_master;
Query OK, 0 rows affected (0.01 sec)
# 再次查看状态为空
mysql> show status like ‘Rpl_semi_sync_master_status‘;
Empty set (0.01 sec)
# 删除my.cnf里面的相关配置
[root@mysqlnode1 ~]# vim /usr/local/mysql/my.cnf
...
#plugin-load=rpl_semi_sync_master=semisync_master.so
#rpl_semi_sync_master_enabled=1
# 务必重启MySQL数据库
[root@mysqlnode1 ~]# /etc/init.d/mysql restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
# 从库重启IO线程
mysql> stop slave IO_THREAD;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
# 从库启动slave
mysql> start slave IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.121
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000021
Read_Master_Log_Pos: 271
Relay_Log_File: mysqlnode2-relay-bin.000013
Relay_Log_Pos: 441
Relay_Master_Log_File: mysql-bin.000021
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
删除完毕。
参考：https://www.cnblogs.com/kevingrace/p/6256603.html
原文：https://www.cnblogs.com/plutozzl/p/13220365.html