半同步复制原理:
半同步复制保证了数据库之间数据的一致性。
MySQL5.7以前:
rpl_semi_sync_master_wait_point = AFTER_COMMIT
client-->MySQL SQL Parse-->Storage Involve-->write binary log-->storage commit-->wait ACK-->client(OK)
主库把每一个事务写到二进制日志并保存到磁盘上,且发送给从库,并把事务写到存储引擎里,主库在等待从库写到自己的relay-log里确认信息。在接收到确认信息后,主库把相应结果返回给客户端。
问题:主库把事务写到存储后,如果此时从库因网络等问题没有收到,但此时主库又挂了,在HA架构中,因故障转移进行主从切换时,就丢失了该事务。
MySQL5.7:
rpl_semi_sync_master_wait_point = AFTER_SYNC
client-->MySQL SQL Parse-->Storage Involve-->write binary log-->wait ACK-->storage commit-->client(OK)
主库把每一个事务写道二进制日志并保存到磁盘上,并发给从库,主库等待从库写到自己的relay-log日志里的确认信息。主库收到确认后再将事务写到存储引擎里,然后将结果返回给客户端。
rpl_semi_sync_master_wait_slave_count 指定有几台slave收到binlog才成功返客户端请求,默认任意一台。
实验实现:
master:172.25.67.1
slave1:172.25.67.3
slave2:172.25.67.4
firewalld selinux disabled
安装mysql5.7
半同步复制模式必须在主服务器和从服务器同时启用,否则主服务器默认使用异步复制模式。
master和slave都做:
半同步复制需要安装相应插件,如果用rpm包装的mysql5.7,则插件在该目录下:
/usr/lib64/mysql/plugin/
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql> set global rpl_semi_sync_master_enabled=ON;
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_slave_enabled=ON;
初次加载插件后,MySQL会将该插件记录到系统表mysql.plugin中,下次启动会自动加载该插件,但如果要让插件自动生效,需要修改配置文件:
vim /etc/my.cnf
log-bin=mysql-bin
binlog-do-db=test
server-id=1 #slave的id一定不同
binlog-ignore-db=mysql
# 自动开启半同步复制
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_slave_enabled=ON
***********
测试:
master:
mysql> show status like "%semi%";
+--------------------------------------------+-------+
| 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 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 252 |
| Rpl_semi_sync_master_tx_wait_time | 252 |
| 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 |
| Rpl_semi_sync_slave_status | OFF |
+--------------------------------------------+-------+
Rpl_semi_sync_master_clients 用于显示有多少个从服务器配置成了半同步模式
Rpl_semi_sync_master_status 用于指示主服务器是使用异步还是半同步模式,ON->半同步
Rpl_semi_sync_master_yes_tx 用于显示从服务器确认的成功提交数量
在slave:
mysql> show status like "%semi%";
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_slave_status | ON |
+--------------------------------------------+-------+
Rpl_semi_sync_slave_status 表示从服务器的半同步复制模式已经开始工作。
压力测试:
半同步复制:
[root@server1 ~]# mysqlslap -uroot -pxxxxxxxx --concurrency=100 --number-of-queries=1000 --auto-generate-sql
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 2.094 seconds
Minimum number of seconds to run all queries: 2.094 seconds
Maximum number of seconds to run all queries: 2.094 seconds
Number of clients running queries: 100
Average number of queries per client: 10
异步复制:
[root@server1 ~]# mysqlslap -uroot -pxxxxxxxx --concurrency=100 --number-of-queries=1000 --auto-generate-sql
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 1.840 seconds
Minimum number of seconds to run all queries: 1.840 seconds
Maximum number of seconds to run all queries: 1.840 seconds
Number of clients running queries: 100
Average number of queries per client: 10