关于MySQL主从复制的数据同步延迟问题

浅浅的花香味﹌ 2023-10-01 23:05 36阅读 0赞

关于MySQL主从复制的原理及环境搭建，可参考前文：

[MySQL高可用之主从复制][MySQL]

这种主从复制环境在单机应用的时候没有问题，但是在实际的生产环境中，会存在 **复制延迟** 的问题。

![format_png][]

### 查看从库同步状态 ###

在从库中执行 `show slave status\G` ：

mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.2.158
                      Master_User: root
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: master-bin.000001
              Read_Master_Log_Pos: 2560
                   Relay_Log_File: mysql-slave-node01-relay-bin.000002
                    Relay_Log_Pos: 2292
            Relay_Master_Log_File: master-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 2560
                  Relay_Log_Space: 2512
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 1
                      Master_UUID: c28a6a7f-2a61-11eb-91e9-000c2959176f
                 Master_Info_File: /var/lib/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    复制代码

这里有几个比较重要的参数：

*  **Master\_Log\_File** ：slave中的**IO线程**正在读取的主服务器的**二进制日志文件的名称**
 *  **Read\_Master\_Log\_Pos** ：在当前的主服务器二进制日志中，slave中的**IO线程已经读取的位置**
 *  **Relay\_Log\_File** ： **SQL线程** 当前正在读取和执行的**中继日志文件的名称**
 *  **Relay\_Log\_Pos** ：在当前的中继日志中，**SQL线程已经读取和执行的位置**
 *  **Relay\_Master\_Log\_File** ：由**SQL线程**执行的包含多数近期事件的主服务器二进制日志文件的名称
 *  **Slave\_IO\_Running** ： **IO线程是否启动并成功的连接到主服务器上**
 *  **Slave\_SQL\_Running** ：SQL线程是否启动
 *  **Seconds\_Behind\_Master** ：从服务SQL线程和从服务器IO线程之间的时间差（秒）

值得一提的是 **Seconds\_Behind\_Master** ，这个参数直接就给出了当前从库延迟了多长时间。

那么这个值是如何计算的呢？

*  Master执行完成一个事务，写入 `binlog` ，这个时刻记为 **T1** ；
 *  Master传输 `binlog` 给Slave，Slave接收完 `binlog` 的时刻记为 **T2** ；
 *  Slave执行完这个事务的时刻记为 **T3** ；

**主从复制延迟时间** 就是同一个事务，在从库执行完成的时间和主库执行完成的时间之间的差值，也就是 **T3 - T1**。

**SBM（Seconds Behind Master）** 在进行计算的时候就是按照这样的方式，每个事务的 `binlog` 中都有一个时间字段，用于记录主库写入的时间，从库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值，得到SBM。

### SBM时间差值产生的原因 ###

通过SBM计算方式的分析，我们分析一下这个时间差产生的可能原因，以便于我们能在实际生产环境中解决问题。

*  **大事务执行**

比如主库的一个事务执行了N分钟，而 `binlog` 的写入必须要等待事务完成之后，才会传入从库，那么此时从库在开始执行的时候就已经延迟了N分钟了。

*  **从库随机操作数据**

主库的写操作是 **顺序写** `binlog` ，从库单线程去主库 **顺序读** `binlog` ，从库取到 `binlog` 之后在本地执行。

MySQL的主从复制都是单线程的操作，由于主库是顺序写，所以效率很高，而从库也是顺序读取主库的日志，此时的效率也是比较高的，但是 **当数据拉取回来之后变成了随机的操作，而不是顺序的，所以此时成本会提高** 。

*  **从库同步时与查询线程发生lock征用**

从库在同步数据的同时，可能跟其他查询的线程发生锁抢占用的情况，此时也会发生延时。

*  **主库TPS高**

当主库的TPS并发非常高的时候，产生的DDL数量超过了一个线程所能承受的范围的时候，那么也可能带来延迟。

*  **网络问题**

主从在进行 `binlog` 日志传输的时候，如果网络带宽也不是很好，那么网络延迟也可能造成数据同步延迟。

### 复制延迟问题解决方案 ###

#### 从sync\_binlog参数配置下手 ####

![format_png 1][]

通过这个图我们可以看到，每个线程都有自己的 **binlog cache** ，但是共用同一份 `binlog` 文件。

其中的 **write** ，就是 **把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度快** 。

**fsync** ，才是将数据持久化到磁盘的操作。一般情况下，我们认为 **fsync才占用磁盘的IOPS** 。

而 **write** 和 **fsync** 的时机就是 **由参数sync\_binlog来进行控制的** 。

*  sync\_binlog=0：表示每次提交事务都只write，不fsync
 *  sync\_binlog=1：表示每次提交事务都执行fsync
 *  sync\_binlog=N：表示每次提交事务都write，但积累N个事务后才fsync

在大部分应用场景中，建议将此参数的值设置为1，因为这样的话能够保证数据的安全性。

但是如果出现主从复制的延迟问题，可以考虑将此值设置为100~1000中的某个数值，非常不建议设置为0，因为设置为0的时候没有办法控制丢失日志的数据量。

**TIP**：如果是对安全性要求比较高的业务系统，这个参数产生的意义就不是那么大了。

#### 禁用salve上的binlog ####

直接禁用salve上的binlog，当从库的数据在做同步的时候，**有可能从库的binlog也会进行记录，此时肯定也会消耗IO的资源**，因此可以考虑将其关闭。

**TIP**：如果你搭建的集群是 **级联** 的模式的话，那么此时的 `binlog` 也会发送到另外一台从库里方便进行数据同步，此时这个配置项也不会起到太大的作用。

#### 设置innodb\_flush\_log\_at\_trx\_commit ####

**innodb\_flush\_log\_at\_trx\_commit** 是用来表示每一次的事务提交是否需要把日志都写入磁盘（都写入磁盘耗时）。

\*\*innodb\_flush\_log\_at\_trx\_commit \*\* 一共有三个属性值：

*  =0 每次写到服务缓存，一秒钟刷写一次
 *  =1 每次事务提交都刷写一次磁盘
 *  =2 每次写到OS缓存，一秒钟刷写一次

**一般情况下设置成2**，这样就算MySQL的服务宕机了，写在OS缓存中的数据也会进行持久化。

### 从根本上解决 ###

#### 并行复制 ####

\*\*并行复制 \*\* 是MySQL 5.6版本之后引入的：

![format_png 2][]

并行复制就是在中间加了一个 **分发** 任务的环节，也就是说原来的 **SQL Thread** 变成了现在的 **Coordinator** 组件，当日志来了之后， **Coordinator** 负责 **读取日志信息以及分发事务** ，真正的日志执行的过程是放在了 **worker线程** 上，**由多个线程并行的去执行**。

**并行复制操作**

查看并行的slave的线程的个数，默认是0，表示单线程：

show global variables like 'slave_parallel_workers';
    复制代码

根据实际情况设置开启多少线程：

set global slave_parallel_workers = 4;
    复制代码

设置并发复制的方式，默认是**一个线程处理一个库**，值为database：

show global variables like '%slave_parallel_type%';
    复制代码

设置slave\_parallel\_type属性值：

set global slave_parallel_type='logical_lock';
    复制代码

查看线程数

show full processlist;
    复制代码

#### MySQL 5.7的并行复制策略 ####

MySQL 5.7版本优化了自己的并行复制策略，并且可以通过参数 **slave-parallel-type** 来控制并行复制的策略：

*  当配置的值为DATABASE的时候，则使用5.6版本的 **数据库级别** 的并行复制策略；
 *  当配置的值为LOGICAL\_CLOCK的时候，则使用MySQL 5.7全新的并行复制策略。

MySQL 5.7并行复制策略的思路是：

所有处于 **redo log prepare** 阶段的事务，都可以并行提交，原因是这些事务都已经经过了锁资源争用的阶段，都是没有冲突的。

反之，如果这些事务之间有冲突，则后来的事务会等待前面的事务释放锁之后才能执行，因此，这些事务就不会进入prepare阶段。

总结一下就是，**一个组提交（group commit）的事务都是可以并行回放，因为这些事务都已进入到事务的prepare阶段，则说明事务之间没有任何冲突（否则就不可能提交）**。

*  binlog\_group\_commit\_sync\_delay 表示延迟多少微秒后才调用 fsync;
 *  binlog\_group\_commit\_sync\_no\_delay\_count 表示累积多少次以后才调用 fsync。

基于这样的处理机制，为了增加一组事务内的事务数量提高从库组提交时的并发量引入了 `binlog_group_commit_sync_delay=N` 和 `binlog_group_commit_sync_no_delay_count=N` 参数，MySQL等待 `binlog_group_commit_sync_delay` 毫秒直到达到 `binlog_group_commit_sync_no_delay_count` 事务个数时，将进行一次组提交。

### 基于GTID的主从复制 ###

之前搭建主从复制环境的时候，在Slave上执行：

change master to master_host='192.168.2.158',master_user='root',master_password='123456',master_port=3306,master_log_file='master-bin.000001',master_log_pos=154
    复制代码

这种写法限制我们必须知道具体的 `binlog`是哪个文件，同时在文件的哪个位置开始复制，正常情况下也没有问题。

但是如果是一个主备主从集群，那么如果主机宕机，当从机开始工作的时候，那么备机就要同步从机的位置，此时位置可能跟主机的位置是不同的，因此在这种情况下，再去找位置就会比较麻烦，所以在5.6版本之后出来一个 **基于GTID的主从复制** 。

**GTID(Global Transaction ID)** 是对于一个已提交事务的编号，并且是一个全局唯一的编号。

GTID实际上是由 **UUID + TID** 组成的，其中UUID是MySQL实例的唯一标识，TID表示该实例上已经提交的事务数量，并且随着事务提交单调递增。

这种方式保证事务在集群中有唯一的ID，强化了主备一致及故障恢复能力。

#### 配置基于GTID的集群环境 ####

> 虚拟机环境与 [MySQL高可用之主从复制][MySQL] 中的集群环境一致。

三台MySQL实例配置文件修改：

192.168.2.158

[mysqld]
    # binlog
    log-bin=master-bin
    log-slave-updates=true
    binlog-format=ROW
    
    # GTID
    server-id=158
    gtid_mode=on
    
    # 强制GTID一致性，开启后对于特定create table不被支持
    enforce_gtid_consistency=on
    
    # 从库：禁止开启IO线程和SQL线程，防止破坏从库
    skip_slave_start=1
    复制代码

192.168.2.159

# binlog
    log-bin=master-bin
    log-slave-updates=true
    binlog-format=ROW
    
    # GTID
    server-id=159
    gtid_mode=on
    
    # 强制GTID一致性，开启后对于特定create table不被支持
    enforce_gtid_consistency=on
    
    # 从库：禁止开启IO线程和SQL线程，防止破坏从库
    skip_slave_start=1
    复制代码

192.168.2.157

[mysqld]
    # binlog
    log-bin=master-bin
    log-slave-updates=true
    binlog-format=ROW
    
    # GTID
    server-id=157
    gtid_mode=on
    
    # 强制GTID一致性，开启后对于特定create table不被支持
    enforce_gtid_consistency=on
    
    # 从库：禁止开启IO线程和SQL线程，防止破坏从库
    skip_slave_start=1
    复制代码

三台MySQL实例均重启。

在两个从库执行：

mysql> stop slave;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> change master to master_host='192.168.2.158',master_user='root',master_password='123456',master_auto_position=1;
    Query OK, 0 rows affected, 2 warnings (0.01 sec)
    复制代码

关于**master\_auto\_position**

*  master\_auto\_position = 0，表示采用老的binlog复制
 *  master\_auto\_position = 1，表示采用GTID复制

#### 验证 ####

在Master上执行：

mysql> show slave hosts;
    +-----------+------+------+-----------+--------------------------------------+
    | Server_id | Host | Port | Master_id | Slave_UUID                           |
    +-----------+------+------+-----------+--------------------------------------+
    |       157 |      | 3306 |       158 | c269d11a-2a61-11eb-bf0d-000c29599fb3 |
    |       159 |      | 3306 |       158 | c2cba590-2a61-11eb-ac0e-000c2900ba99 |
    +-----------+------+------+-----------+--------------------------------------+
    2 rows in set (0.00 sec)
    复制代码

可以看到，有两个从节点的MySQL实例。

在从节点上， `show slave status\G`

![format_png 3][]

#### 基于GTID复制的原理 ####

当一个事务在Master提交时，该事务就被赋予了一个GTID，并记录在主库的binlog；

主库的binlog会被传输到从库的relay log中，从库读取此GTID并生成gtid\_next系统参数；

从库验证此GTID并没有在自己的binlog中使用，则应用此事物在从库上。

![format_png 4][]

如果我们不开启gtid，分组信息该如何保存呢？ 其实是一样的，当没有开启的时候，数据库会有一个 **Anonymous\_Gtid** ，用来保存组相关的信息。

![4ccda21edc014f01bda15045277ad31b.jpeg][]

[MySQL]: https://juejin.cn/post/6895639421270851597
[format_png]: https://img-blog.csdnimg.cn/img_convert/e7927aa84d32a297bb7d189f5bab7333.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/5a89b3c8aa8b0a5088e6e0fb23325a56.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/f6a97e648d8cc0c0cd762d980790cbab.webp?x-oss-process=image/format,png
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/e4158125b77b4225fb6fbb030e6fc3ee.webp?x-oss-process=image/format,png
[format_png 4]: https://img-blog.csdnimg.cn/img_convert/fd7003a45ebefe3737f097f3388cc7d6.webp?x-oss-process=image/format,png
[4ccda21edc014f01bda15045277ad31b.jpeg]: https://img-blog.csdnimg.cn/4ccda21edc014f01bda15045277ad31b.jpeg