MySQL复制技术及半同步实践

文章来源: 陶老师运维笔记-微信公众号

1. MySQL复制基础

默认情况下，MySQL的复制就是异步的，即在Master上将所有的更新操作都写入Binlog之后并不能确保所有的更新是否都复制到了Slave服务器的中继日志中，以及是否应用到了Slave数据库里。

基本原理:

1. Master记录二进制日志， 每次提交事务完成数据更新前，Master将数据更新的时间记录到二进制日志中。MySql会按事务提交的顺序而非每条语句的执行顺序来记录二进制日志。再记录二进制日志后，主库会告诉存储引擎可以提交事务了。

   
   



2. Slave将Master的二进制日志复制到本地的中继日志中。 首先，Slave会启动一个工作线程，成为I/O线程， I/O线程跟Master建立一个普通的客户端链接，然后再Master上启动一个特殊的二进制转储（binlog dump）线程（该线程没有对应的SQL命令），这个二进制转储线程会读取主库上的二进制日志中的事件。从库I/O线程将接受到时间记录到中继日志中。

   
   



3. 从库的SQL线程执行relay。 该线程的从中继日志中读取事件并在从库执行，从而实现从库数据更新。

   
   

2. 常见复制技术

2.1 MySQL异步复制

MySQL master事务的提交不需要经过slave的确认，slave是否接收到master的binlog，master并不care。slave接收到master binlog后先写relay log，最后异步地去执行relay log中的sql应用到自身。由于master的提交不需要确保slave relay log是否被正确接受，当slave接受master binlog失败或者relay log应用失败，master无法感知。

假设master发生宕机并且binlog还没来得及被slave接收，而切换程序将slave提升为新的master，就会出现数据不一致的情况！ 另外，在高并发的情况下，传统的主从复制，从节点可能会与主产生较大的延迟（当然mysql后续版本陆续做了优化，推出了并行复制，以此降低异步复制的延迟。

2.2 MySQL半同步复制

2.2.1 MySQL半同步介绍及特点

基于传统异步存在的缺陷，mysql在5.5版本推出半同步复制。可以说半同步复制是传统异步复制的改进，在master事务的commit之前，必须确保一个slave收到relay log并且响应给master以后，才能进行事务的commit。 但是slave对于relay log的应用仍然是异步进行的，原理如下图所示:

半同步复制，它在异步复制基础上添加了一个同步操作。每一个事务,master都需要等待至少一个slave的 ack回复，然后才能提交事务。

如果在更新期间，主从库发生了网络故障或者从库宕机，那么此时主库在事务提交后会等待10秒（默认值），如果10秒内还是不能联系到该从库，就会放弃更新该从库，并向用户返回数据。这时主从库就会恢复到默认的异步复制状态。 如果主机间的通信延迟较小，而且更新的事务较小，则半同步复制可以在主库更新性能损失很小的情况下，最大限度地实现某一台从库的零数据丢失。

半同步复制优缺点:

• 优点: 对于常规的互联网应用，主从复制就够用了，对于主从一致性要求较高的应用可以使用半同步复制方案。

  
  



• 缺点: 当从库网络不稳定时会对主库更新带来严重的性能下降。
  
  1）将主库半同步超时调短（例如，1~2秒）
  
  2）半同步复制的从库硬件与主库之间的网络配置要更好一些
  
  3）半同步从库不用提供任何业务服务（包括读服务）

  
  

2.2.2 MySQL半同步及增强半同步

• 参考笨笨王的Blog: https://www.cnblogs.com/beebon/archive/2018/12/06/10044051.html


• https://dev.mysql.com/doc/refman/5.7/en/replication-options-master.html

半同步复制：

早期的MySQL5.5/5.6半同步当主库上的事务在存储引擎层提交之后，只需要等待从库返回ACK信号，并且在接收到从库返回ACK信号或者等待超时才会返回给客户端一个提交结果。

存在的问题：

• 缺点1： 幻读
  
  当用户发起一个事务，该事务已经写入redo日志和binlog日志，但该事务还没写入从库，此时处在waiting slave dump处，此时另一个用户可以读取到这条数据，而他自己却不能；


• 缺点2：数据丢失
  
  一个事务在waiting slave dump处crash后，主库将比从库多一条数据

增强版半同步:

为解决MySQL5.6半同步问题，MySQL5.7.2后，推出了增强版半同步,调整了Storage Commit的次序，增加了rpl_semi_sync_master_wait_point=alter_sync设置。即日志没有传输到备库，主库这时候也不会commit。若这时候服务挂掉了，主库其他会话看到的是未提交的数据，并且也没有传输到备库，所以数据不存在丢失一说。

较MySQL5.6半同步改进点：

• 改善1：解决幻读
  
  当用户发起一个事务，该事务写入二进制后，便向从库进行同步，此时其他用户无法读取到该数据，解决了幻读。


• 改善2：解决数据丢失
  
  一个事务在waiting slave dump处crash掉后，可以通过观察从库上是否存在主库的last gtid值，如果存在，这条数据正常恢复，如果不存在则删除主库的那条多余的GTID值，然后恢复，保证了数据的完整性；

2.3 全同步

全同步复制（full sync replication）是指当主库执行完一个事务后，需要确保所有的从库都执行了该事务才返回给客户端。因为需要等待所有的从库都执行完该事务才能返回，所以全同步复制的性能较差。

MySQL自身不支持同步复制，需要用到第三方工具如DRBD（sync模式）等实现同步复制，严格来说，把半同步复制技术默认（或人为）全部应用到所有从库上也算是全同步复制。

2.4 MRG组复制

• https://dev.mysql.com/doc/refman/5.7/en/group-replication.html


• 陶老师运维笔记-MySQL5.7 MGR介绍及安装

基于传统异步复制和半同步复制的缺陷——数据的一致性问题无法保证，MySQL官方在5.7.17版本正式推出组复制（MySQL Group Replication，简称MGR）。由若干个节点共同组成一个复制组，一个事务的提交，必须经过组内大多数节点（N / 2 + 1）决议并通过，才能得以提交。

引入组复制，主要是为了解决传统异步复制和半同步复制可能产生数据不一致的问题。组复制依靠分布式一致性协议(Paxos协议的变体)，实现了分布式下数据的最终一致性，提供了真正的数据高可用方案。

3. 测试环境规划

先搭建好异步复制的MySQL环境,步骤略。

4.MySQL 半同步配置

4.1 半同步配置及参数说明

• https://dev.mysql.com/doc/refman/5.7/en/replication-options-master.html

1)半同步复制配置参数说明

主库半同步复制可配置的参数较少，可通过“show global variables like ‘%semi%’;”查看。

#MySQL5.6
192.110.103.41 : (none) > show global variables like '%semi%';
+------------------------------------+-------+
| 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_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
##MySQL5.7
show global variables like '%semi%';
+-------------------------------------------+------------+
| 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 |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)


注意MySQL5.7的增强半同步较MySQL5.6多了如下两参数：

• rpl_semi_sync_master_wait_for_slave_count: 至少有N个slave接收到日志,Default 1.


• rpl_semi_sync_master_wait_point: AFTER_SYNC（5.7的默认值，写入relaylog后，master commit）, AFTER_COMMIT 等同于5.6的半同步。

2）主库半同步复制状态说明

主库半同步复制状态的参数较多，可通过“show global status like ‘%semi%’;”查看。

192.110.103.41 : (none) > show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 360 |
| Rpl_semi_sync_master_net_wait_time | 2164 |
| Rpl_semi_sync_master_net_waits | 6 |
| Rpl_semi_sync_master_no_times | 2 |
| Rpl_semi_sync_master_no_tx | 3 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 354 |
| Rpl_semi_sync_master_tx_wait_time | 1063 |
| 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 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)


4.2 搭建主/从复制

本例使用GTID搭建，也可以用传统方法建主从复制。

master执行:

show global variables like '%gtid%';
# master上执行，测试环境权限放的比较大。
GRANT SUPER,REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO repl@'192.%.%.%' identified by 'repl';
flush privileges;


slave执行:

#查看gtid
show global variables like '%gtid%';
# MySQL5.6/MySQL5.7 低版本slave上执行
#reset slave all;
reset master;
CHANGE MASTER TO MASTER_HOST='192.110.103.41', MASTER_PORT=3105, MASTER_USER='repl', MASTER_PASSWORD='repl',MASTER_AUTO_POSITION = 1;
start slave;
show slave status\G
## MySQL5.7 高版本slave命令
#MySQL 5.7，高版本没有在change master to语句中加入user和password项，而是在start slave语句中使用，否则会警告。
change master to master_host='192.110.103.41',master_port=3105,master_auto_position=1;
start slave user='repl' password='repl';
show slave status\G


4.3 配置主服务器

登录MySQL主库,安装半同步插件。

主要步骤:

1. 链接Mysql数据库，安装插件rpl_remi_sync_master
   
   install plugin rpl_semi_sync_master soname ‘semisynv_mster.so’;


2. 查看插件是否安装show plugins;


3. 启用插件
   
   set global rpl_semi_sync_master_enabled = ON; # 注意= 与字符和数字之间有空格，否则会报错


4. 安装完成后，查看插件的状态。

4.3.1 配置主服务器步骤

半同步配置文件:

#my.cnf中
[mysqld]
rpl_semi_sync_master_enabled = ON
rpl_semi_sync_master_timeout = 1000


安装半同步插件

#master
install plugin rpl_semi_sync_master soname 'semisync_master.so';
set global rpl_semi_sync_master_enabled = ON;


检查是否生效:

4.4 配置从服务器

登录MySQL主库,安装半同步插件。

主要步骤:

1. 进入数据库，安装并启用插件rpl_semi_sync_slave,相关的操作可以参考配置主服务器的1&2

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = ON;


需要注意，在Master中使用的是master 模块，在从服务器中使用的是slave模块.

2. 查看semi插件的状态

select * from mysql.plugin;
+---------------------+-------------------+
| name | dl |
+---------------------+-------------------+
| rpl_semi_sync_slave | semisync_slave.so |
+---------------------+-------------------+
#
show global status like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
show global variables like '%semi%';


半同步配置文件:

#my.cnf中
[mysqld]
rpl_semi_sync_slave_enabled = ON


4.5 检查半同步状态

如果在一个正在运行的Slave上开启半同步复制的功能，那么在配置半同步以后，需要重启停止Slave的I/O线程：

从库上执行:

#从库上执行
mysql> stop slave io_thread; ---停止IO线程
mysql> start slave io_thread; ---启动IO线程
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
---省略若干---
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
---省略若干---
1 row in set (0.00 sec)


主库上查看半同步状态

查看主库上面的半同步插件是否开启，输出如下，注意第一个参数clients的值变为1了（之前是0），表示主从半同步复制连接成功。

#从库start slave前
show global status like '%Rpl_semi_sync_master_clients%';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
+------------------------------+-------+
1 row in set (0.00 sec)
#从库start slave 后。
show global status like '%Rpl_semi_sync_master_clients%';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
+------------------------------+-------+
1 row in set (0.00 sec)


5.MySQL 半同步测试

5.1 测试半同步复制

1)检查主从库半同步设置

#主库
192.110.103.41 : test > show global status like '%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 | 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)
#从库
192.110.103.43 : (none) > show global status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)


在Master上如下是半同步复制是正常的。

2)Master写入数据

#master 192.110.103.41
create table if not exists test.tt (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
insert into test.tt values(1,'zhang3');


3)主库半同步复制状态

#master上查看
192.110.103.41 : test > show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 281 | ---平均网络等待时间为281毫秒
| Rpl_semi_sync_master_net_wait_time | 563 | ---总网络等待时间为563毫秒
| Rpl_semi_sync_master_net_waits | 2 | ---总网络等待次数为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 | 349 | ---平均事务等待时间为434毫秒
| Rpl_semi_sync_master_tx_wait_time | 698 | ---总事务等待时间为868毫秒
| Rpl_semi_sync_master_tx_waits | 2 | ---总事务等待次数为2
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 | ---收到了2次接受的确认
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
#slave:查看
192.110.103.43 : test > select * from test.tt;
+----+--------+
| id | name |
+----+--------+
| 1 | zhang3 |
+----+--------+


5.2 测试半同步复制超时

1)停止从库的IO线程复制

---测试从库IO线程是否停止，半同步复制更新
192.110.103.43 : test > stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)


2）在主库中更新数据

#---此时执行该命令等待了很久（设置了10秒），因为从库已经停止了
192.110.103.41 : test > insert into test.tt values(2,'li4');
Query OK, 1 row affected (10.00 sec)
#半同步失败退化了异步
192.110.103.41 : test > show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 193 |
| Rpl_semi_sync_master_net_wait_time | 579 |
| Rpl_semi_sync_master_net_waits | 3 |
| Rpl_semi_sync_master_no_times | 1 | ---失败次数
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_status | OFF | ---半同步状态OFF了
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 349 |
| Rpl_semi_sync_master_tx_wait_time | 698 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
#insert 速度又很快了
192.110.103.41 : test > insert into tt values(3,'wang5');
Query OK, 1 row affected (0.01 sec)


测试可知,从库停止IO线程复制之后，第一次执行insert操作时等待了10秒之后才提交完事务，但是第二次insert的操作就很快了。因为第一次等待从库超时之后，半同步复制状态自动转换为异步了，所以第二次及以后都会很快了。

3）开启从库的IO线程复制状态

192.110.103.43 : test > start slave io_thread;
Query OK, 0 rows affected (0.00 sec)


4）再次查看主库的半同步状态

192.110.103.41 : test > show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 460 |
| Rpl_semi_sync_master_net_wait_time | 1842 |
| Rpl_semi_sync_master_net_waits | 4 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 2 |
| Rpl_semi_sync_master_status | ON | ---半同步状态ON了
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 349 |
| Rpl_semi_sync_master_tx_wait_time | 698 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)


5.3 测试半同步slave同步失败

1）模拟复制故障：

从库和主库数据不一致，导致同步异常。在从库insert一记录，然后在主库insert相同的记录，此时同步将出错。

#slave
192.110.103.43 : test > insert into tt values(4,'ma6');
#master
192.110.103.41 : test > insert into tt values(4,'ma6');


从库出错:

192.110.103.43 : test > show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.110.103.41
Master_User: repl
Master_Port: 3106
Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1062
Last_Error: Could not execute Write_rows event on table test.tt; Duplicate entry '4' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000007, end_log_pos 891
Skip_Counter: 0
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_SQL_Errno: 1062
Last_SQL_Error: Could not execute Write_rows event on table test.tt; Duplicate entry '4' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000007, end_log_pos 891
Retrieved_Gtid_Set: a01f3a3f-1eee-11ea-b1e4-a0369fac2de4:31-35
Executed_Gtid_Set: a01f3a3f-1eee-11ea-b1e4-a0369fac2de4:1-34,
bb35a83e-1fb2-11ea-b6e3-a0369fa6cd30:1
Auto_Position: 1


2）检查状态：

此时查看主库的半同步复制状态，发现其仍然为ON，如下所示，可见半同步复制和SQL线程没有关系：

#master
192.110.103.41 : test > show global status like '%Rpl_semi_sync_master_status%';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
+-----------------------------+-------+
1 row in set (0.00 sec)
#slave
192.110.103.43 : test > show global status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+


3）处理从库复制故障

登录slave进行复制故障处理。

#普通复制
stop slave;
set global sql_slave_skip_counter = 1;
start slave;


若是GTID复制,set global sql_slave_skip_counter会出错,要如下处理:

#若是GTID复制时将会出错
set global sql_slave_skip_counter = 1;
ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction
##
stop slave;
#slave
由于在这个GTID必须是连续的，正常情况同一个服务器产生的GTID是不会存在空缺的。所以不能简单的skip掉一个事务，只能通过注入空事物的方法替换掉一个实际操作事务。
show master status;
show global variables like '%gtid%';
192.110.103.43 : test >show slave status\G
当前Executed_Gtid_Set的值a01f3a3f-1eee-11ea-b1e4-a0369fac2de4:1-34
Retrieved_Gtid_Set: a01f3a3f-1eee-11ea-b1e4-a0369fac2de4:31-35
Executed_Gtid_Set: a01f3a3f-1eee-11ea-b1e4-a0369fac2de4:1-34,
bb35a83e-1fb2-11ea-b6e3-a0369fa6cd30:1
#处理:
set gtid_next='a01f3a3f-1eee-11ea-b1e4-a0369fac2de4:35'; --(last_executed_slave_gtid_on_master + 1)
begin;commit;
set gtid_next='AUTOMATIC';
start slave;
show slave status\G;


参考:

• 陶老师运维笔记-掘金畅读版 https://juejin.im/post/5df771dfe51d4557e87fd0fb


• https://dev.mysql.com/doc/refman/5.7/en/replication-options-master.html


• https://yq.aliyun.com/articles/135531


• 阿丧小威 MySQL半同步复制