mysql同步(复制)延迟的原因及解决方案

爱被打了一巴掌 2022-01-19 23:53 780阅读 0赞

# 同步延迟的本质 #

当从库跟不上主库的更新进度时就会出现**同步(复制)延迟**，这时在从库里，未同步的修改在relay\_log里出现堆积，数据的版本也会渐渐跟主库差别越来越大。

# 同步延迟的原因 #

为了确定延迟的原因，我们需要确定是哪个复制线程出现问题了。在mysql中，一对主从同步的连接依赖三个不同的线程，其中两个由从库创建，一个由主库创建。

*  从库的`I/o线程`：当你在从库通过`Start Slave`命令配置了主库同步信息后，这个线程就会被从库创建。用来请求主库binlog日志的备份
 *  主库的`Bin log Dump线程`: 当从库连上主库后，这个线程就会被创建，然后会将其binlog发送给从库。
 *  从库`Slave SQL 线程`： 从库创建这个线程，然后从获取的binlog里读取内容并应用。

# 同步延迟的解决方案 #

当I/O线程或SQL线程无法处理对其提出的要求时，会导致复制延迟。

## I/O线程可能出现的问题 ##

1.  网络较慢
2.  传输数据量较大
3.  一个主从对中，binlog的读是串行的，而写是并行的

### I/O线程可能出现的问题解决方案 ###

1.  针对1.2.开启`slave_compressed_protocol`选项进行数据压缩
2.  针对3.库级别并行读

## SQL线程可能出现的问题 ##

1.  语句没有优化或者不合适造成从库同步很慢，如有很多长时事务或太多IO活动
2.  使用Row/Mixed格式时，从库的表没有primary key也会有问题。

### SQL线程可能出现的问题解决方案 ###

1.  减少长时事务或IO
2.  检查主、从库是否都有primary key

# 如何查看哪个线程出现了问题 #

## binlog文件名和位置的同步方式 ##

此种同步方式下使用`Show master status`和`show slave statue`命令可以查看你需要的信息，比如: Show master status命令看到的：

*  通过`postion`\-`Read_Master_Log_Pos`获得你的IO线程落后主库日志的字节数
 *  通过`postion`\-`Exec_Master_Log_Pos`获得你的SQL线程落后主库日志的字节数
 *  通过`Read_Master_Log_Pos`\-`Exec_Master_Log_Pos`获得你的SQL线程落后IO的字节数 Show slave status命令看到的：
 *  `seconds_behind_master`可以参考落后主库的秒数，不过不要过度依赖它，因为它的统计方式不是很准确

## GTIDs同步方式 ##

### 诊断所用指标(show slave status) ###

*  Executed GTIDs： 主从库上都能看到，返回执行的事务的id。也可以通过查询全局变量gtid\_executed来获取最近执行的事务id
 *  Retrieved GTIDs：只有从库上可以看到，用来返回IO线程获取的事务id
 *  Purged GTIDs：显示从binlog清除了哪些已经执行完的事务。一般你只会对从库中的这个日志感兴趣

获得GTID后，你可以使用`GTID_SUBTRACT()`函数计算从库与主库的差异。例如，以下对从库查询显示从二进制日志中读取的尚未应用的GTID（SQL线程延迟）:

slave> SELECT GTID_SUBTRACT('96985d6f-2ebc-11e7-84df-08002715584a:5-133', 
            '96985d6f-2ebc-11e7-84df-08002715584a:26-132') AS MissingGTIDs;
    +-----------------------------------------------+
    | MissingGTIDs                                  |
    +-----------------------------------------------+
    | 96985d6f-2ebc-11e7-84df-08002715584a:5-25:133 |
    +-----------------------------------------------+
    1 row in set (0.00 sec)
    复制代码

### GIIDs方式简介 ###

通过GTIDs【global transaction identifiers】,可以标识每一个事务，并且可以在其一旦提交追踪并应用于任何一个Slave上；这样 就不需要像BinaryLog复制依赖Log file 和位置。GTIDs完全基于事务，只要在Master提交的所有事务都在Slave上进行了Commit，那么就能保证Master和Slave之间的数据 一致性。你可以使用基于SBR或RBR的GTIDs来实现。推荐使用RBR【Row-based replication】.

# 参考 #

[What Causes Replication Lag?][What Causes Replication Lag]

[What Causes Replication Lag]: https://link.juejin.im?target=https%3A%2F%2Fblogs.oracle.com%2Fjsmyth%2Fwhat-causes-replication-lag