MySQL主备复制延迟场景及解决方案

本文介绍了 MySQL主备复制延迟场景及解决方案。

场景描述

主库执行了大事务。

原因分析

大事务是指一个事务中包含大量的数据变更操作，例如一个事务包含上万次（insert，update，delete）等操作、一条SQL语句批量更新了上万行数据等，大事务往往本身的执行时间很长（分钟级）。当主实例执行了大事务后，会产生大量的Binlog日志，备机拉取这些Binlog耗时比一般事务长，且至少需要花费与主库相同的时间来回放这些事务的更新，从而导致备机出现复制延迟。

解决方案

• 为了保证主从数据的一致性，需要等待大事务执行完成，主备复制延迟才能恢复。
• 业务侧避免此类大事务，可以将大事务拆分为小事务，分批执行。例如，通过where条件或limit语句限制每次要更新的数据量。

 

主备复制延迟持续增长后自动恢复的方法。

场景描述

MySQL实例复制延迟很大，在一段时间内持续增长，在业务低峰期自动恢复。

原因分析

查看数据库主从延时是在一定时间内延时变大且一段时间内持续增长，业务低谷期恢复正常的，根据该现象可以推测此场景为大事务，业务高峰期，或DDL操作导致。可以通过控制台查看慢日志，分析慢SQL，以及观察事务记录是否有大事务或DDL操作。

查看控制台慢查询分析，分析慢查询语句，使用索引优化SQL语句时应当要考虑业务峰值，后面查看控制台事务记录发现，业务正在高峰期，有大量的instert操作，但发现有增加，删除索引的DDL操作，因为表的数据量上千万，耗时长，从而导致从节点在回放该DDL时复制延迟持续增长，回放完DDL后延迟恢复正常。

解决方案

• 该场景属于正常现象，等待DDL执行完成后，延迟会自动恢复。
• 索引的改动是涉及整个表的，建议在业务低谷期进行增删索引的操作。