MySQL数据库备库复制延迟的原因及解决办法

背景

今天有人问我主从复制延迟会影响高可用切换的 RTO 怎么办，这个不需要做实验，我可以直接回答，所以有了以下赶鸭子的文章，都是一线运维经验之谈，建议四连：点赞、收藏、转发。

复制延迟的原因及解决办法

一般情况下，复制延迟大概率是从库的 sql thread 应用 relay log 慢导致的，很少是因为 io thread 慢导致的。io thread 慢的话是一些故障导致的，是罕见的，可能磁盘慢或者网络慢导致。所以下面我总结了 6 种常见的 sql thread 应用复制延迟发生的原因和解决办法。

1. 主从服务器的配置或者压力不一，从实例的IO能力太弱。

这个需要技术管理手段去规避，务必要保证主备库无论从硬件配置、操作系统配置、mysqld 配置都要一致，不会出现主库比备库性能好很多的情况。不建议备库开读写分离，有财力的公司读写分离的读可以放在另外的只读从库上。

2. 主库的TPS太高

在 MySQL5.5 及之前版本，从库是不支持并行复制功能的，主库上会有并发事务，利用多核 CPU，多个连接同时写入数据，但从库 sql thread 线程应用 relay log 时不支持并行回放，只能单线程回放，那么主库只要并发高的时候，从库永远是复制延迟的。

MySQL5.6 版本优化了这个问题，从库只支持基于 database 的并行复制，也就是如果主库上多个并发事务必须在不同 database 上跑，在从库上才能并行复制，这就非常鸡肋，大多数业务的并发 SQL 都在同一个业务库的，所以这并不能并行复制，这个问题是 MySQL5.6 复制延迟的常见原因，请务必升级到 MySQL5.7 以支持基于逻辑时钟的并行复制。

MySQL5.7 版本的基于逻辑时钟的并行复制，是基于组提交的原理来实现的，首先在主库满足能组提交的事务，都是可以并行回放，因为这些事务都已进入到事务的 prepare 阶段，则说明事务之间没有任何冲突（否则就不可能提交）。但他没有完全模拟主库上的并发执行，所以在从库上的回放力度依然没有主库高，但比 MySQL5.6 鸡肋的基于 database 的并行复制强多了，这个时候已经能消灭大多数复制延迟了。MySQL5.7.22 开始官方推出了基于 writeset 的并行复制，他能把并行复制推到了全新的高度，怎么说呢？就是主库原本不是并行的 SQL，只要不冲突在从库上都能并行，极限情况下，从库回放速度甚至比主库还高！这基本上是消灭了这个原因下的复制延迟。

并行复制是有相关参数推荐的，请视情况调整，如有疑问可以与我联系。

skip_slave_start                    =0                              
relay_log                           =/database/mysql/log/relaylog/3307/relay-bin
relay_log_recovery                  =1
master_info_repository              =table                       
relay_log_info_repository           =table                       
slave_parallel_type                 =logical_clock               
slave_parallel_workers              =4                           
loose-rpl_semi_sync_master_enabled        =1                     
loose-rpl_semi_sync_slave_enabled         =1                     
loose-rpl_semi_sync_master_timeout        =1000                  
slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_group_commit_sync_delay      =500                         
binlog_group_commit_sync_no_delay_count = 13                     
binlog_transaction_dependency_tracking  = WRITESET               
transaction_write_set_extraction        = XXHASH64

3. 备份导致写入阻塞

我们有一部分的 MySQL 备份计划是采用备库上每天凌晨执行物理全备的方法做备份。物理全备采用的是开源工具 xtrabackup，实际上无论使用 xtrabackup 或者 mysqldump，备份工具都需要执行 FLUSH TABLES WITH READ LOCK  (FTWRL)命令，这个命令需要获取一些锁。

1. 如果此时在备库上有大查询，就堵塞 FTWRL 操作，备库 show processlist 显示“Waiting for table flush”，这个 FTWRL 被阻塞会从而导致这张表上的事务无法提交，也就是影响扩大了，影响到表的写入了。这个一般不常见，因为如果这个从库定位为备库一般就规范使用会不作为读写分离的只读库使用，上面不应该有大查询。
2. 如果库里 MyISAM 表比较多，为了保证备份数据的一致性，FTWRL 操作持续时间会较长，直到所有 MyISAM 表拷贝完成，期间任何写入都会被阻塞，卡在 “Waiting for global read lock”，解决这个问题的办法就是永远不要使用 MyISAM 存储引擎的表，请将他们修改为 InnoDB 存储引擎的表。

#设置以下参数保证没有人能使用MyISAM表，保平安。 
[mysqld]
...
default_storage_engine=innodb                     #  MySQL5.7以后官方默认值
default_tmp_storage_engine=innodb                 #  MySQL5.7以后官方默认值
disabled_storage_engines=ARCHIVE,BLACKHOLE,EXAMPLE,FEDERATED,MEMORY,MERGE,NDB,MyISAM

因为MySQL5.7中MySQL元数据还有MyISAM表，所以以上设置会影响MySQL5.7的小版本升级，需要一些小技巧解决，MySQL8.0的话就直接干吧。

这两种情况是备份导致的从库阻塞，情况 1 不常见，我建议的解决办法就是如果备份 FTWRL 操作 5 秒内获取不到锁就放弃备份，备份脚本/程序一小时后再尝试备份。情况 2 这种问题要技术管理方法来规避，永远不要使用 MyISAM，只用 InnoDB。

如果能保证数据库只用 InnoDB 存储引擎，并且使用 MySQL8.0 最新版本，那么 FTWRL 不是问题了，因为最新的 xtrabackup8 备份 MySQL 在只有 InnoDB 存储引擎下是不会使用 FTWRL 的，只会使用轻量级的 backup lock(MySQL8.0 新特性)，那就可以避免因为 FTWRL 引发的相关问题。

另外例如 MHA 这种三机高可用架构，第一个从库可以作为优先切换从，也就是备库，第二个从库可以设置不参与切换的从库，用于备份专用库，那么情况1、2均不需要解决了。

4. DDL导致（alter table,create index,optimize table,repair table等等）

DDL 基本就是大事务了，大事务操作会导致复制延迟，因为主库执行完提交后从库才可以回放，他运行时间一般较长，所以 DDL 务必在业务低峰期执行。如果您非常介意主库写锁或者从库复制延迟的话，DDL 是需要专业 DBA 评估和操作的，鉴定是否能采用原生 online DDL 或者可以通过开源工具 pt-osc 或 gh-ost 来执行以加快 DDL、避免长时间锁定或者降低复制延迟。

5. 运行大事务

任何数据库都不应该有大事务，无论 MySQL、PostgreSQL 或者 Oracle，而 MySQL 对大事务是非常敏感的，大事务会导致非常多的问题，不单只复制延迟。同事提到无法拆分大事务，我觉得应该不存在无法拆分的大事务，前面提到大事务操作会导致复制延迟，因为主库执行完提交后从库才可以回放，如果一个事务主库执行 1 小时才能完成，那么从库就要 1 小时后才能开始执行，所以复制延迟就高达 1 小时！如果您使用的是 load data local infile 入库，这是安全上不建议的，安全给的规范是建议关闭 local_infile 参数，也就是禁止使用 load data local infile 操作，您可以修改为 insert，每 5000 条数据 insert 一次，做成 insert xxx into values(),()...() (5000 个 values) 这种一句式的 SQL，循环执行直到跑批完成，如果不冲突，您可以做成并行跑批。如果依然希望使用 load data local infile，毕竟他是最快的入库方式，那您可以拆分每次 load data 的数据量，使大事务变小事务，同样的如果没有冲突，您可以考虑并行 load data。每个 SQL 入库数据量建议不大于 10MB。

6. 对无主键的表进行删除或者更新

这条其实和 5 都是开发规范相关的，应该用技术管理手段——《MySQL开发规范》来限制开发人员，表结构必须要有主键。这个要么是上线 SQL 审核工具拒绝没有主键的表(例如 SQLE，没收钱恰饭，纯公益广告。。)，要么就编写《MySQL开发规范》，需要扯皮时甩开发同事脸上。因为无主键的SQL 容易造成复制延迟，而且这种复制延迟导致的结果是灾难性的，这个复制延迟可能高达一个月，甚至直到世界末日。

举个例子:

delete * from XXX limt 5000;

这种 delete + limit 的句子设定了一次只删 5000 行数据，可以有效防止大事务，非常棒。

这个 sql 在主库执行很快。在 binlog_format=statement 格式下，从库也快，因为从库执行的也是 delete * from XXX limt 5000; 本身，复制延迟就是这个 SQL 执行的时间。

如果 binlog_format=Mixed 设置下(腾讯云默认设置)，区别于delete * from XXX(全表删除)，从库不会转化，binlog 走的 statement 格式，使用 limit 因从库无法确认具体删除的是具体哪一行数据，这个和数据的组织排序强相关，为了保证主从删除的数据是一致的，其 binlog 会转化为 row 格式。row 格式在复制时要求必须有主键的，这才是性能最高的，因为他是需要根据主键去定位每行数据的位置，然后逐条删除的。如果没有主键，每行都定位不到位置，需要对整张表的记录做一次全表扫描。从库每删除一行数据都要全表扫描一次，你只是删除 5000 行啊，他就要全表扫描 5000 次，此开销非常巨大，主从延迟会很严重。如果你表足够大，删的行数足够多，那你复制延迟永远追不平，此时唯一解决办法就是删除从库基于主库重新备份重做从库。(当然了，重做从库前请先给主库检查每张表，都添加上主键，避免后续又此类问题了，折腾不起，这就芭比Q了)。

我们生产上的安全设置 binlog_format=row，所以问题就更严重了，不带 limit 的 delete * from XXX 也不行了，很慢。总之，没主键就是不行。

实际上，没主键但有区分度高的索引一定程度可以加快回放速度，但由于篇幅和时间的关系，我不可能很轻易讲明白其中原理，我只能说一句有主键才是yyds。

除了前面提到的 SQL 上线审核工具外，还可以考虑上 MySQL8.0.30，开启强制主键和自动创建隐式主键的功能参数，这样您的 MySQL 永远不会有没有主键的表。

sql_require_primary_key=on
sql_generate_invisible_primary_key=on