mysql多进程复制

悠悠 2022-05-20 01:19 187阅读 0赞

并发复制（Parallel Replication）  Enhanced Multi-threaded Slaves

--------------------

首先梳理下传统MySQL/MariaDB主备复制基本原理：

主从复制通过三个线程来完成，在master节点运行的binlog dump的线程，I/O线程和SQL线程运行在slave 节点

*          master节点的Binlog dump线程，当slave节点与master正常连接的时候，master把更新的binlog 内容推送到slave节点。
 *          slave节点的I/O 线程 ，该线程通过读取master节点binlog日志名称以及偏移量信息将其拷贝到本地relay log日志文件。
 *          slave节点的SQL线程，该线程读取relay log日志信息，将在master节点上提交的事务在本地回放，达到与主库数据保持一致的目的。

问题1：

Master节点的数据库实例并发跑多个线程同时提交事务，提交的事务按照逻辑的时间（数据库LSN号）顺序地写入binary log日志，,slave节点通过I/O线程写到本地的relay log日志，但是slave节点只有SQL单线程来执行relay log中的日志信息重放主库提交得事务，造成主备数据库存在延迟（lag）

思考1：

那么为了减少主备数据同步延迟时间，由于备库只有单线程补偿数据的原因而造成延迟，那么能否使slave节点同时运行多个如SQL线程一样的功能来重放在主库执行的事务？答案当然是：可以！但是我们需要解决以下问题：

1、slave本地的relay log记录的是master 的binary log日志信息，日志记录的信息按照事务的时间先后顺序记录，那么为了保证主备数据一致性，slave节点必须按照同样的顺序执行，如果顺序不一致容易造成主备库数据不一致的风险。

如：

在master节点提交T1和T2事务按照以下顺序

1.  State0: x= 1, y= 1

2.  T1: \{ x:= Read(y);

3.          x:= x+1;

4.          Write(x);

5.          Commit; \}

6.    
State1: x= 2, y= 1

7.  T2: \{ y:= Read(x);

8.            y:=y+1;

9.           Write(y);

10.          Commit; \}

11.   
State2: x= 2, y= 3

slave节点执行T1和T2相反的顺序：

1.  State0: x= 1, y= 1

2.  T2: \{ y:= Read(x);

3.            y:= y+1;

4.            Write(y);

5.            Commit; \}

6.    
State1: x= 1, y= 2

7.  T1: \{ x:= Read(y);

8.            x:=x+1;

9.            Write(x);

10.           Commit; \}

11.   
State2: x= 3, y= 2

MySQL 5.6改进：

MySQL 5.6版本引入并发复制（schema级别），基于schema级别的并发复制核心思想：“不同schema下的表并发提交时的数据不会相互影响，即slave节点可以用对relay log中不同的schema各分配一个类似SQL功能的线程，来重放relay log中主库已经提交的事务，保持数据与主库一致”。可见MySQL5.6版本的并发复制，一个schema分配一个类似SQL线程的功能。

实现1：

slave节点开启并发复制（slave\_parallel\_workers＝3）如下图，当前的slave的SQL线程为Coordinator（协调器），执行relay log日志的线程为worker(当前的SQL线程不仅起到协调器的作用，同时也可以重放relay log中主库提交的事务)

1.  +-----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+

3.  +-----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+

问题2:

MySQL 5.6基于schema级别的并发复制能够解决当业务数据的表放在不同的database库下，但是实际生产中往往大多数或者全部的业务数据表都放在同一个schema下，在这种场景即使slave\_parallel\_workers>0设置也无法并发执行relay log中记录的主库提交数据。 高并发的情况下，由于slave无法并发执行同个schema下的业务数据表，依然会造成主备延迟的情况。

思考2：

那么如果slave同时可以用多线程的方式，同时执行一个schema下的所有业务数据表，将能大大提高slave节点执行ralay log中记录的主库提交事务达到与主库数据同步的目的，实现该功能我们需要解决什么问题？

*  1、前面提到过为了保证主库数据一致性，master节点写入的binary log日志按照数据库逻辑时间先后的顺序并且slave节点执行relay log中主库提交的事务必须按照一致的顺序否则会造成主备数据不一致的情况。
 *  2、既然要实现scehma下所有的业务数据表能够并发执行，那么slave必须得知道并发执行relay log中主库提交的事务不能相互影响而且结果必须和主库保持一致。

实现2:

MySQL 5.7 引入Enhanced Muti-threaded slaves,当slave配置slave\_parallel\_workers>0并且global.slave\_parallel\_type＝‘LOGICAL\_CLOCK’,可支持一个schema下，slave\_parallel\_workers个的worker线程并发执行relay log中主库提交的事务。但是要实现以上功能，需要在master机器标记binary log中的提交的事务哪些是可以并发执行，虽然MySQL 5.6已经引入了binary log group commit，但是没有将可以并发执行的事务标记出来。

我们用命令 mysqlbinlog -vvv mysqlbinlog.0000003 | grep -i last\_committed    在MySQL 5.7的master机器上可以看到last\_committed 和sequence\_number

1.  \#151223 15:11:28 server id 15102  end\_log\_pos 14623 CRC32 0x767a33fa GTID      last\_committed=18         sequence\_number=26

3.  \#151223 15:11:28 server id 15102  end\_log\_pos 15199 CRC32 0x7dd1bf05 GTID     last\_committed=26         sequence\_number=27

5.  \#151223 15:11:28 server id 15102  end\_log\_pos 15773 CRC32 0xb01dc76e GTID     last\_committed=26         sequence\_number=28

7.  \#151223 15:11:28 server id 15102  end\_log\_pos 16347 CRC32 0x7a8e0ee8 GTID     last\_committed=26         sequence\_number=29

9.  \#151223 15:11:28 server id 15102  end\_log\_pos 16921 CRC32 0x92516d17 GTID     last\_committed=26         sequence\_number=30

10.

11. \#151223 15:11:28 server id 15102  end\_log\_pos 17495 CRC32 0xeb14a51e GTID     last\_committed=26         sequence\_number=31

12.

13. \#151223 15:11:28 server id 15102  end\_log\_pos 18071 CRC32 0x750667d0 GTID     last\_committed=26         sequence\_number=32

14.

15. \#151223 15:11:28 server id 15102  end\_log\_pos 18645 CRC32 0xcaed6159 GTID     last\_committed=26         sequence\_number=33

16.

17. \#151223 15:11:28 server id 15102  end\_log\_pos 19219 CRC32 0x62408408 GTID     last\_committed=26         sequence\_number=34

18.

19. \#151223 15:11:28 server id 15102  end\_log\_pos 19793 CRC32 0x5cf46239 GTID     last\_committed=33         sequence\_number=35

slave机器的relay log中 last\_committed相同的事务（sequence\_num不同）可以并发执行。从上面截取的信息可以看出last\_committed=26的事务一共有8个：从sequence\_number=27~24。假设当slave\_parallel\_workers=7时，Coordinator线程（SQL线程）分配这一组事务到worker中排队去执行。这里可以看出增加master库binary log group commit组中事务的数量可以提高slave机器并发处理事务的数量，MySQL5.7引入 binlog\_group\_commit\_sync\_delay和 binlog\_group\_commit\_sync\_no\_delay\_count参数即提高binary log组提交并发数量。MySQL等待binlog\_group\_commit\_sync\_delay毫秒的时间直到binlog\_group\_commit\_sync\_no\_delay\_count个事务数时，将进行一次组提交。

总结:

MySQL 5.7 GA版本推出的 Enhanced Multi-threaded Slaves功能，彻底解决了之前版本主备数据复制延迟的问题，开启该功能参数如下：

1.  \# slave机器

2.  slave-parallel-type=LOGICAL\_CLOCK

3.  ＃slave-parallel-type=DATABASE \#兼容MySQL 5.6基于schema级别的并发复制

4.  slave-parallel-workers=16 ＃开启多线程复制

5.  master\_info\_repository=TABLE

6.  relay\_log\_info\_repository=TABLE

7.  relay\_log\_recovery=ON

从MySQL5.5.X版本开始，增加了relay\_log\_recovery参数，这个参数的作用是：当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该功能是关闭的，将relay\_log\_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启。

关于relay\_log\_recovery参数的介绍，请参见MySQL5.5手册：

把relay.info记录在slave\_relay\_log\_info表里有两个好处：

1.relay.info明文存储不安全，把relay.info中的信息记录在table中相对安全。

2.可以避免relay.info更新不及时，SLAVE 重启后导致的主从复制出错。  
  
  
执行下述查询，检查relay\_log\_info\_repository,master\_info\_repository值是否为table，  
relay\_log\_recovery 是否开启。  
  
  
SHOW VARIABLES WHERE variable\_name IN ('relay\_log\_recovery','relay\_log\_info\_repository','master\_info\_repository');

relay\_log\_info\_repository,master\_info\_repository值如果为FILE，建议将其修改为TABLE.

修改步骤如下：

**1. stop slave;  
  
  
2. set GLOBAL  relay\_log\_info\_repository='TABLE';  
  
  
3.在my.cnf中设置**  
relay\_log\_info\_repository = TABLE  
master\_info\_repository    = TABLE  
relay\_log\_recovery        = on  
  
  
**4.restart mysql  
  
  
5.start slave;**  
  
  
6. 检查relay\_log\_info\_repository是否修改成功。  
show variables where variable\_name in  ('relay\_log\_info\_repository','master\_info\_repository');

relay\_log\_info\_repository,master\_info\_repository值设置为TABLE后，可以利用如下SQL查询主从同步的信息：

select  \*   from mysql.slave\_master\_info;

select  \*   from mysql.slave\_relay\_log\_info;