mysql主从复制主库宕机，如何解决主从数据库同步延迟问题?

MySQL 主从复制的延时一直是业界困扰已久的问题。延时的出现会降低主从读写分离的价值，不利于数据实时性较高的业务使用 MySQL。

UDB 是 UCloud 推出的云数据库服务，上线已达六年，运营了数以万计的 UDB MySQL 实例。除了提供高可用、高性能、便捷易用的产品特性，团队还平均每天帮助用户解决 2-3 起 MySQL 实例主从复制延时的问题。从大量实践中我们总结了主从复制延时的各种成因和解决方法，现分享于此。

延时问题的重要性

主从复制机制广泛应用在 UDB 的内部实现中：UDB 创建的从库和主库就采用了“主从复制”的数据复制；另外，UDB 的主打产品“UDB MySQL 高可用实例”，也是采用 2 个数据库互为主从的“双主模式”来进行数据复制，而双主模式的核心就是主从复制机制。

如果主从复制之间出现延时，就会影响主从数据的一致性。

在高可用复制场景下，我们在 UDB 高可用容灾设计上考虑到，若出现主备数据不一致的场景，默认是不允许进行高可用容灾切换的。因为在主备数据不一致的情况下，此时发生容灾切换，且在新的主库写入了数据，那么从业务角度上，会产生意想不到的严重后果。

复制延时问题，不仅在 UDB 高可用中会带来不良后果，在只读从库的场景下，若从库产生复制延时，也可能会对业务造成一定影响，比如在业务上表现为读写不一致——新增 / 修改数据查不到等现象。

由此可见，主从复制的延时问题在数据库运营中需要特别关注。一般来说，DBA 在库上执行’SHOW SLAVE STATUS’，并且观察 ‘Seconds_Behind_Master’ 的值，就能够了解当前某个数据库和它的主库之间的数据复制延时。这个值是如此的重要，因此在 UDB 的监控界面上，我们将这个值单独抽取来，设计了“从库同步延时”监控项，以便于运维人员能够直接在控制台上观察。

生产环境中延时问题的分析及解决

我们将最常见的主从复制延时案例总结为几类，以下是相关案例的现象描述、原因分析和解决方法汇总。

案例一：主库 DML 请求频繁

某些用户在业务高峰期间，特别是对于数据库主库有大量的写请求操作，即大量 insert、delete、update 等并发操作的情况下，会出现主从复制延时问题。

现象描述

我们通过观察主库的写操作的 QPS 的值，会看到主库的写操作的 QPS 值突然升高，伴随主从复制延时的上升，可以判断是由于主库 DML 请求频繁原因造成的。

如上图，可以看出，在 17:58 分左右 QPS 突增，查看控制台上的写相关 QPS，也有相应提升。而 QPS 突增的时间，对应的延时也在逐步上升，如下图所示。

原因分析

经过分析，我们认为这是由于主库大量的写请求操作，在短时间产生了大量的 binlog。这些操作需要全部同步到从库，并且执行，因此产生了主从的数据复制延时。

从深层次分析原因，是因为在业务高峰期间的主库写入数据是并发写入的，而从库 SQL Thread 为单线程回放 binlog 日志，很容易造成 relaylog 堆积，产生延时。

解决思路

如果是 MySQL 5.7 以下的版本，可以做分片 (sharding)，通过水平扩展 (scale out) 的方法打散写请求，提升写请求写入 binlog 的并行度。

如果是 MySQL 5.7 以上的版本，在 MySQL 5.7，使用了基于逻辑时钟 (Group Commit) 的并行复制。而在 MySQL 8.0，使用了基于 Write Set 的并行复制。这两种方案都能够提升回放 binlog 的性能，减少延时。

案例二：主库执行大事务

大事务指一个事务的执行，耗时非常长。常见产生大事务的语句有：

使用了大量速度很慢的导入数据语句，比如：INSERT INTO tb、SELECT∗FROMtb、SELECT∗FROMtb、LOAD DATA INFILE 等；使用了 UPDATE、DELETE 语句，对于一个很大的表进行全表的 UPDATE 和 DELETE 等。

当这个事务在从库执行回放执行操作时，就有可能会产生主从复制延时。

现象描述

我们从 SHOW SLAVE STATUS 的结果进行分析，会发现 Exec_Master_Log_Pos 字段一直未变，且 second_behinds_master 持续增加，而 Slave_SQL_Running_State 字段的值为 “Reading event from the relay log”；同时，分析主库 binlog，看主库当前执行的事务，会发现有一些大事务，这样基本可以判定是执行大事务的原因导致的主从复制延时。

原因分析

当大事务记录入 binlog 并同步到从库之后，从库执行这个事务的操作耗时也非常长，这段时间，就会产生主从复制延时。

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

解决思路

对于这种情况引起的主从复制延时，我们的改进方法是：拆分大事务语句到若干小事务中，这样能够进行及时提交，减小主从复制延时。

案例三：主库对大表执行 DDL 语句

DDL 全称为 Data Definition Language ，指一些对表结构进行修改操作的语句，比如，对表加一个字段或者加一个索引等等。当 DDL 对主库大表执行 DDL 语句的情况下，可能会产生主从复制延时。

现象描述

从现象上，如果从库执行 SHOW SLAVE STATUS 的输出中，检查 Exec_Master_Log_Pos 一直未动，在排除主库执行大事务的情况下，那么就有可能是在执行大表的 DDL。这一点结合分析主库 binlog，看主库当前执行的事务就可以进行确认。

DDL 语句的执行情况，可以进一步细分现象来更好地判断：

DDL 未开始，被阻塞，这时 SHOW SLAVE STATUS 的结果能检查到 Slave_SQL_Running_State 为 waiting for table metadata lock，且 Exec_Master_Log_Pos 不变；

DDL 正在执行，SQL Thread 单线程应用导致延时增加。这种情况下观察 SHOW SLAVE STATU 的结果能发现 Slave_SQL_Running_State 为 altering table，而 Exec_Master_Log_Pos 不变。

如果有上述的现象，那么很有可能主库对大表执行 DDL 语句，同步到从库并在从库回放时，就产生了主从复制延时。

原因分析

DDL 导致的主从复制延时的原因和大事务类似，也是因为从库执行 DDL 的 binlog 较慢而产生了主从复制延时。

解决思路

遇到这种情况，我们主要通过 SHOW PROCESSLIST 或对 information_schema.innodb_trx 做查询，来找到阻塞 DDL 语句，并 KILL 掉相关查询，让 DDL 正常在从库执行。

DDL 本身造成的延时难以避免，建议考虑：

避免业务高峰，尽量安排在业务低峰期执行 ；set sql_log_bin=0 后，分别在主从库上手动执行 DDL（此操作对于某些 DDL 操作会造成数据不一致，请务必严格测试），这一条如果用户使用云数据库 UDB，可以联系 UCloud UDB 运维团队进行协助操作。

案例四：主库与从库配置不一致

如果主库和从库使用了不同的计算资源和存储资源，或者使用了不同的内核调教参数，可能会造成主从不一致。

现象描述

我们会详细比对主库和从库的性能监控数据，如果发现监控数据差异巨大，结合查看主从的各个配置情况，即可作出明确判断。

原因分析

各种硬件或者资源的配置差异都有可能导致主从的性能差异，从而导致主从复制延时发生：

硬件上：比如，主库实例服务器使用 SSD 磁盘，而从库实例服务器使用普通 SAS 盘，那么主库产生的写入操作在从库上不能马上消化掉，就产生了主从复制延时；配置上：比如，RAID 卡写策略不一致、OS 内核参数设置不一致、MySQL 落盘策略不一致等，都是可能的原因。

解决思路

考虑尽量统一 DB 机器的配置（包括硬件及选项参数）。甚至对于某些 OLAP 业务，从库实例硬件配置需要略高于主库。

案例五：表缺乏主键或合适索引

如果数据库的表缺少主键或者合适索引，在主从复制的 binlog_format 设置为’row’的情况下，可能会产生主从复制延时。

现象描述

我们进行数据库检查时，会发现：

观察 SHOW SLAVE STATUS 的输出，发现 Slave_SQL_Running_State 为 Reading event from the relay log；SHOW OPEN TABLES WHERE in_use=1 的表一直存在；观察 SHOW SLAVE STATUS 的 Exec_Master_Log_Pos 字段不变；mysqld 进程的 CPU 接近 100%（无读业务时），IO 压力不大。

这些现象出现的情况下，可以认为很可能有表缺乏主键或唯一索引。

原因分析

在主从复制的 binlog_format 设置为’row’的情况下，比如有这样的一个场景，主库更新一张 500 万表中的 20 万行数据。binlog 在 row 格式下，记录到 binlog 的为 20 万次 update 操作，也就是每次操作更新 1 条记录。如果这条语句恰好有不好的执行计划，如发生全表扫描，那么每一条 update 语句需要全表扫描。此时 SQL Thread 重放将特别慢，造成严重的主从复制延时。

解决思路

这种情况下，我们会去检查表结构，保证每个表都有显式自增主键，并协助用户建立合适索引。

案例六：从库自身压力过大

有时候，从库性能压力很大的情况下，跟不上主库的更新速度，就产生了主从复制延时。

现象描述

观察数据库实例时，会发现 CPU 负载过高，IO 利用率过高等现象，这些导致 SQL Thread 应用过慢。这样就可以判断是因为从库自身压力过大引起主从复制延时。

原因分析

部分 UCloud 用户对于数据库的主从会使用读写分离模式，读请求大部分在从库上执行。在业务有大量读请求的场景下，从库会产生比主库大得多的性能压力。有的用户甚至会在从库运行十分耗费计算资源的 OLAP 业务，这也对从库造成了更高的性能挑战，这些都会造成主从复制的延时。

解决思路

这种情况下，我们会建议用户建立更多从库，打散读请求，降低现有从库实例的压力。对于 OLAP 业务来说，可以专门建立一个从库来做 OLAP 业务，并对这个从库，允许适当的主从复制延时。

总结

在使用 MySQL 的主从复制模式进行数据复制时，主从复制延时是一个需要考量的关键因素。它会影响数据的一致性，进而影响数据库高可用的容灾切换。

在遇到数据库之间出现主从复制延时的情况下，我们团队基于过往经验，归纳出以下方法与流程来协助排查问题：

通过 SHOW SLAVE STATUS 与 SHOW PROCESSLIST 查看现在从库的情况。（顺便也可排除在从库备份时的类似原因）；若 Exec_Master_Log_Pos 不变，考虑大事务、DDL、无主键，检查主库对应的 binlog 及 position 即可；若 Exec_Master_Log_Pos 变化，延时逐步增加，考虑从库机器负载，如 IO、CPU 等，并考虑主库写操作与从库自身压力是否过大。

UDB 的高可用、高性能、便捷易用，可以大量减轻使用者的运维负担。在使用过程中，UDB 团队也会利用多年累积的运营经验，帮助用户及时分析、排查问题原因，并给出合理的解决方法。

本文来源：UCloud 技术（微信公众号：ucloud_tech）

原文链接：https://mp.weixin.qq.com/s/aqEx4Gyb4jl1yky5ueXmPQ