MySQL 复制延迟怎么处理

‍我们在工作过程中，可能多多少少会遇到主从延迟的情况，这一节内容我们就来聊聊什么情况可能出现主从延迟，怎样判断延迟，存在延迟怎么处理。

根据笔者以往的经验，导致复制延迟可能的原因有这些：

• 大表在做 DDL，一方面 DDL 会产生元数据锁，可能产生阻塞，另外一方面，DDL 是在主库执行完，才写入 Binlog ，因此从库是在主库执行完才开始执行的。如果是单线程复制，这期间从库其他事务需要等待。所以可能产生比较久的延迟。一般建议使用 Online DDL 工具或者在业务低峰做大表 DDL 操作；‍‍
• 大事务，当有大事务时，主库可能在这个事务执行的时候，其他事务也能并发执行。但是在从库，单线程复制的情况下，其他事务只能等这个事务执行完，才能在从库执行；
• 主库增删改并发大，主库多个并发执行增删改操作，同样从库是单个 sql 线程解析 relay log 的内容，把数据写入从库，也就是主库并发，从库单线程，显然，主库 DML 并发大的时候，也会导致主从延迟；
• 从库配置差，处理能力比主库差很多，也可能会导致延迟。

怎样判断延迟呢？

方法一

一种常规的方法就是 show slave status 查看 Seconds_Behind_Master，这个参数表示从库延迟的秒数。

如果是0，表示可能没有延迟。这里为什么是可能呢？

我们来解释一下 Seconds_Behind_Master 的原理，表示从库服务器当前的时间戳与二进制日志中的事件的时间戳（在主库上的写入时间）相对比得到的。

在某些情况下，Seconds_Behind_Master 并不一定准确。比如网络中断时，Seconds_Behind_Master = 0 ，并不能代表主从无延迟。

因此，有比这个更准确的一种方法：对比位点或 GTID。

方法二

如果是基于位点的复制，则判断 Master_Log_File 跟 Relay_Master_Log_File 是否相等，如果 Relay_Master_Log_File 落后 Master_Log_File，则表示主从存在延迟。

其中

• Master_Log_File 表示 IO 线程正在读取的主库 binlog 文件名
• Relay_Master_Log_File 表示SQL 线程最近执行的事务对应的主库 binlog 文件名

或者判断 Read_Master_Log_Pos 跟 Exec_Master_Log_Pos 是否相等，如果后者落后前者很多，则表示延迟比较高。

其中

• Read_Master_Log_Pos 表示IO 线程正在读取的主库 binlog 文件中的位点
• Exec_Master_Log_Pos 表示 SQL 线程最近读取和执行的事务对应的主库 binlog 文件中的位点

方法三

如果开启了 GTID 复制，则可以对比 Retrieved_Gtid_Set 和 Executed_Gtid_Set 是否相等，如果 Executed_Gtid_Set 落后很多，则表示存在延迟。

其中

• Retrieved_Gtid_Set：从库收到的所有日志的 GTID 集合；
• Executed_Gtid_Set：从库已经执行完的 GTID 集合。

主从延迟怎么处理呢？

方法一

在前面我们聊到了，很多主从延迟的原因，都因为从库是单线程，所以可以考虑开启并行复制。并行复制具体介绍和开启方式，可以参考笔者 7 月份出版的新书《MySQL DBA 精英实战课》9.5 节：MySQL并行复制。点击文末阅读原文可跳转京东购买链接，目前可参与满 100 减 50 活动。关于书的介绍可跳转：我们的 MySQL 新书出版啦。

方法二

另外可以尝试调整参数。比如 innodb_flush_log_at_trx_commit 和 sync_binlog。

这里我们来讲一下两个参数的作用。

innodb_flush_log_at_trx_commit：控制重做日志刷新到磁盘的策略，有 0 、1 和 2 三种值。

0：master线程每秒把 redo log buffer 写到操作系统缓存，再刷到磁盘；

1：每次提交事务都将 redo log buffer 写到操作系统缓存，再刷到磁盘；

2：每次事务提交都将 redo log buffer 写到操作系统缓存，由操作系统来管理刷盘。

sync_binlog：控制 binlog 的刷盘时机，可配置 0、1 或者大于 1 的数字。

0：二进制日志从不同步到磁盘，依赖OS刷盘机制；

1：二进制日志每次提交都会刷盘；

n(n>1) : 每n次提交落盘一次。

所以当从库有延迟的时候，可以把从库的 innodb_flush_log_at_trx_commit 设置为 0，也就是 master 线程每秒把 redo log buffer 写到操作系统缓存，再刷到磁盘，而不是每个事务都刷一次，这可以大大缓解从库的压力。

另外就是可以把 sync_binlog 调整为大于 100 的数字，表示 100 多次事务，才会做一次二进制日志的落盘。

方法三

前面还讲到，可能从库配置比主库差，导致延迟，这个时候，如果从库也提供查询服务，并且业务不能仍受延迟，可以考虑提高从库的配置。

方法四

如果延迟是大事务导致的，可以在源头上做规范，避免大事务。

方法五

另外，我们有事也会遇到这种场景，比如某张表比较大，延迟经常是他导致的，从库上读取时又用不上他，就可以考虑把大表单独创建一个从库进行复制。然后在原来的从库忽略这张表的复制，业务查询原来的从库就基本没延迟了。