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 集合。

主从延迟怎么处理呢？

方法一在前面我们聊到了，很多主从延迟的原因，都因为从库是单线程，所以可以考虑开启并行复制。

方法二另外可以尝试调整参数。比如 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 多次事务，才会做一次二进制日志的落盘。

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

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

转自

MySQL 复制延迟怎么处理
https://mp.weixin.qq.com/s?__biz=MzIyOTUzNjgwNg==&mid=2247484672&idx=1&sn=c768d7842d5e3c69fe2942d28639cfdd&chksm=e8406049df37e95f5e94be4fcfae31ce306a1dd308fc973ccd5f480a865787a03d73aac38e75&scene=178&cur_album_id=2660112142796636163#rd