如何理解MySQL多线程复制

共计 5153 个字符，预计需要花费 13 分钟才能阅读完成。

这篇文章给大家介绍如何理解 MySQL 多线程复制，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。

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.  +—–+————-+———–+——+———+——-+——————————————————–+——————+

2.  | Id  | User        | Host      | db   | Command | Time  | State            
                     
               | Info             |

3.  +—–+————-+———–+——+———+——-+——————————————————–+——————+

4.  |   1 | system
user |           | NULL | Connect | 29923 | Slave has read all relay log; waiting for more updates | NULL             |

5.  |   2 | system
user |           | NULL | Connect | 29923 | Waiting for an event
from Coordinator            
     | NULL          
  |

6.  |   3 | system
user |           | NULL | Connect | 29923 | Waiting for an event
from Coordinator            
     | NULL          
  |

7.  |   4 | system
user |           | NULL | Connect | 29923 | Waiting for an event
from Coordinator            
     | NULL          
  |

问题 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

2.   

3.  #151223 15:11:28 server id 15102
 end_log_pos 15199 CRC32 0x7dd1bf05  GTID  last_committed=26  sequence_number=27

4.   

5.  #151223 15:11:28 server id 15102
 end_log_pos 15773 CRC32 0xb01dc76e  GTID  last_committed=26  sequence_number=28

6.   

7.  #151223 15:11:28 server id 15102
 end_log_pos 16347 CRC32 0x7a8e0ee8  GTID  last_committed=26  sequence_number=29

8.   

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

关于如何理解 MySQL 多线程复制就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。