MySQL5.6半同步复制部署
文章目录
1.部署目标
MySQL双节点部署主从半同步复制并测试
2.系统环境(系统版本centos6.8)
2.1 IP规划
编号 | 主机名 | IP | 数据库版本 | 数据库功能 |
---|---|---|---|---|
1 | mysqlnode1 | 192.168.1.121 | mysql5.6.21源码包安装 | MySQL主库 |
2 | mysqlnode2 | 192.168.1.122 | mysql5.6.21源码包安装 | MySQL从库 |
2.2 拓扑图
3.系统配置(系统常规初始化完成)
3.1 修改主机名
以下操作主从都要执行
[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
~
3.2 修改hosts文件
以下操作主从都要执行
[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
~
4.数据库配置
4.1 数据库配置主从复制
这里我采用的是基于GTID的主从复制。具体操作请转到https://www.cnblogs.com/plutozzl/p/13217838.html
4.2 安装半同步复制模块
安装前首先要加载插件,由于半同步复制是一个功能模块,需要进行加载。半同步模块路径(源码包安装位置:/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)
## 查看插件是否加载成功
4.3 启用半同步复制
安装完插件后半同步复制默认是关闭的,需要手动设置参数进行打开。
启动的两种方式:
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
4.4 重启从数据库的IO线程
注意是从数据库
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
至此,半同步复制环境部署完成
5.半同步环境测试
5.1 测试目标
当半同步复制发生超时的时候,会暂时关闭半同步复制,转而使用异步复制。当master dump线程发送完一个事务的所有事件之后,如果在rpl_semi_sync_master_timeout(这个参数是设置超时时间的,单位毫秒默认10000)内,收到从库响应则主从重新恢复为半同步复制。
5.2 测试过程
#查看当前数据库半同步复制超时时间(主库操作)
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)
验证完成
上述操作证明了,半同步超时后整个集群会退出半同步复制状态,转而进入异步复制状态。而当恢复正常后,半同步复制会自动恢复。
6.删除半同步环境
先在从库进行操作,再回到主库进行操作
从库
# 停止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