MySQL主从复制延迟原因及处理思路_master has sent all binlog to slave； waiting for m

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新软件测试全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上软件测试知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以添加V获取：vip1024b （备注软件测试）

正文

〇 主库DML请求频繁（tps较大）

即主库写请求较多，有大量insert、delete、update并发操作，短时间产生了大量的binlog。

【原因分析】
主库并发写入数据，而从库SQL Thread为单线程应用日志，很容易造成relaylog堆积，产生延迟。

【解决思路】
做sharding，通过scale out打散写请求。或考虑升级到MySQL 5.7+，开启基于逻辑时钟的并行复制。

〇 主库执行大事务

比如大量导入数据，INSERT INTO $tb1 SELECT * FROM $tb2、LOAD DATA INFILE等
比如UPDATE、DELETE了全表等
Exec_Master_Log_Pos一直未变，Slave_SQL_Running_State为Reading event from the relay log
分析主库binlog，看主库当前执行的事务也可知晓。

【原因分析】
假如主库花费200s更新了一张大表，在主从库配置相近的情况下，从库也需要花几乎同样的时间更新这张大表，此时从库延迟开始堆积，后续的events无法更新。

【解决思路】
拆分大事务，及时提交。

〇 主库对大表执行DDL语句

现象和主库执行大事务相近。
检查Exec_Master_Log_Pos一直未动，也有可能是在执行DDL。
分析主库binlog，看主库当前执行的事务也可知晓。

【原因分析】
1、DDL未开始，被阻塞，SHOW SLAVE STATUS检查到Slave_SQL_Running_State为waiting for table metadata lock，且Exec_Master_Log_Pos不变。
2、DDL正在执行，SQL Thread单线程应用导致延迟增加。Slave_SQL_Running_State为altering table，Exec_Master_Log_Pos不变

【解决思路】
通过processlist或information_schema.innodb_trx来找到阻塞DDL语句的查询，干掉该查询，让DDL正常在从库执行。
DDL本身造成的延迟难以避免，建议考虑：
① 业务低峰期执行
② set sql_log_bin=0后，分别在主从库上手动执行DDL（此操作对于某些DDL操作会造成数据不一致，请务必严格测试）

〇 主库与从库配置不一致：

【原因分析】
硬件上：主库实例服务器使用SSD，而从库实例服务器使用普通SAS盘、cpu主频不一致等
配置上：如RAID卡写策略不一致，OS内核参数设置不一致，MySQL落盘策略不一致等

【解决思路】
尽量统一DB机器的配置（包括硬件及选项参数）
甚至对于某些OLAP业务，从库实例硬件配置高于主库等

〇 表缺乏主键或唯一索引

binlog_format=row的情况下，如果表缺乏主键或唯一索引，在UPDATE、DELETE的时候可能会造成从库延迟骤增。
此时Slave_SQL_Running_State为Reading event from the relay log。
并且SHOW OPEN TABLES WHERE in_use=1的表一直存在。
Exec_Master_Log_Pos不变。
mysqld进程的cpu几近100%（无读业务时），io压力不大

【原因分析】
做个极端情况下的假设，主库更新一张500w表中的20w行数据，该update语句需要全表扫描
而row格式下，记录到binlog的为20w次update操作，此时SQL Thread重放将特别慢，每一次update可能需要进行一次全表扫描

【解决思路】
检查表结构，保证每个表都有显式自增主键，并建立合适索引。

〇 从库自身压力过大

【原因分析】
从库执行大量select请求，或业务大部分select请求被路由到从库实例上，甚至大量OLAP业务，或者从库正在备份等。
此时可能造成cpu负载过高，io利用率过高等，导致SQL Thread应用过慢。

【解决思路】
建立更多从库，打散读请求，降低现有从库实例的压力。

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注软件测试）

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！
npx-1713337685214)]

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！