MySQL主库出问题了，从库怎么办？备库为什么会延迟好几个小时？

课程：B站大学
记录学习极客时间团队MySQL45讲，进阶数据分析和数据处理

备库为什么会延迟好几个小时？

一、问题背景

不论是偶发性的查询压力，还是备份，对备库延迟的影响一般是分钟级的，而且在备库恢复正常以后都能够追上来。

但是，如果备库执行日志的速度持续低于主库生成日志的速度，那这个延迟就有可能成了小时级。而且对于一个压力持续比较高的主库来说，备库很可能永远都追不上主库的节奏。

这就涉及到今天的话题：备库并行复制能力。

谈到主备的并行复制能力，我们要关注的是图中黑色的两个箭头：一个代表客户端写入主库，另一个代表备库上sql_thread执行中转日志（relay log）。如果用箭头的粗细来代表并行度的话，真实情况就如图1所示，第一个箭头要明显粗于第二个箭头。

• 主库：影响并发度的原因是各种锁。由于 InnoDB 支持行锁，除了所有并发事务都在更新同一行的极端场景外，对业务并发度的支持很友好。并发压测线程32通常比单线程总体吞吐量高。
• 备库：日志的执行，就是备库上sql_thread更新数据的逻辑。如果用单线程，就会导致备库应用日志不够快，造成主备延迟。

在官方 5.6 版本之前，MySQL 只支持单线程复制，由此在主库并发高、TPS 大的场景下会出现严重的主备延迟问题。从单线程复制到最新版本的多线程复制，中间经历了好几个版本。

核心思想：把所有多线程复制机制，都是要把"只有一个线程的sql_thread"拆成多个线程。

图2中，coordinator就是原来的sql_thread，不过现在它不再直接更新数据了，只负责读取中转日志和分发事务；真正更新数据的，变成了worker线程。worker线程的个数由参数slave_parallel_workers决定。根据经验，这个值设置为8~16之间最好（32核物理机的情况），毕竟备库还可能要提供读查询，不能把 CPU 都吃光。

coordinator 分发的两点基本要求

1. 不能造成更新覆盖：更新同一行的两个事务，必须被分发到同一个 worker。
2. 同一个事务不能拆开：必须放到同一个 worker。

反例思考：事务能否按轮询分给各 worker？不行——CPU 调度可能导致后分发的先执行，若两事务更新同一行，主备执行顺序相反，导致主备不一致。同一事务的多个更新能否分给不同 worker？也不行——会看到"更新了一半"的中间结果，破坏隔离性。

各版本多线程复制都遵循这两条原则。

二、MySQL 5.5：按表分发 & 按行分发

官方 5.5 不支持并行复制。作者针对严重的主备延迟问题，先后写了两个版本的并行策略：按表分发和按行分发。

2.1 按表分发策略

基本思路：如果两个事务更新不同的表，就可以并行。因为数据存储在表里，按表分发可以保证两个 worker 不会更新同一行。跨表事务需把涉及的表一起考虑。

每个 worker 对应一个 hash 表，key 是库名.表名，value 表示队列中有多少事务修改这个表。分配规则：

1. 若跟所有 worker 都不冲突→ 分配给最空闲的 worker；
2. 若跟多于一个 worker 冲突→ coordinator等待，直到冲突 worker 只剩 1 个；
3. 若只跟一个 worker 冲突→ 分配给这个 worker。

优缺点：在多个表负载均匀的场景效果很好；但热点表（所有更新都涉及某一张表）时，所有事务都分到同一 worker，退化为单线程。

2.2 按行分发策略

解决热点表的并行复制问题。核心思路：两个事务没有更新相同的行，就可以并行。显然，这要求 binlog 格式必须是row。

此时 key 是库名 + 表名 + 唯一键的值。仅有主键 id 还不够，还需考虑唯一索引：

CREATETABLE`t1`(idint(11)NOTNULL,aint(11)DEFAULTNULL,bint(11)DEFAULTNULL,PRIMARYKEY(`id`),UNIQUEKEY`a`(`a`))ENGINE=InnoDB;INSERTINTOt1VALUES(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5);

若两个事务分别更新id=1和id=2，主键值不同，但若分到不同 worker，可能因执行顺序导致唯一键冲突（如a=1还未更新完）。因此 hash 表 key 还要包含唯一索引，即库名+表名+索引名+值。

约束条件（恰好也是 DBA 线上规范）：

1. binlog 必须能解析出表名、主键、唯一索引值 → 格式须为row；
2. 表必须有主键；
3. 不能有外键（级联更新不记录在 binlog，冲突检测不准）。

大事务退化：操作很多行时，按行策略会耗费大量内存和 CPU。超过行数阈值（如 10 万行）时退化为单线程：

1. coordinator hold 住事务；
2. 等待所有 worker 执行完变为空队列；
3. coordinator 直接执行该事务；
4. 恢复并行模式。

三、MySQL 5.6：按库并行

官方 5.6 支持并行复制，粒度是按库并行。hash 表的 key 就是数据库名。

优点：

1. 构造 hash 值快（只需库名），且实例 DB 数不多，不会出现百万级项；
2. 不要求 binlog 格式，statement 也能拿到库名。

缺点：若所有表都在同一个 DB，或不同 DB 热点差异大（业务库 vs 配置库），就没有并行效果。理论上可拆 DB 强行使用，但因需移动数据，用得不多。

四、MariaDB：基于组提交(commit_id)的并行复制

利用 redo log 组提交（group commit）优化特性：

1. 能在同一组里提交的事务，一定不会修改同一行；
2. 主库上可并行执行的事务，备库上也一定可并行执行。

实现：

1. 同一组一起提交的事务有相同的commit_id，下一组commit_id+1；
2. commit_id写入 binlog；
3. 备库上相同commit_id的事务分发到多个 worker 执行；
4. 整组执行完，coordinator 再取下一批。

这个策略相当惊艳——目标是**“模拟主库的并行模式”**，而非"分析 binlog 拆分"。

但问题：未真正模拟主库并发度。主库上，一组事务提交时，下一组事务是同时处于执行中的；而 MariaDB 策略在备库要等整组执行完下一组才能开始，吞吐量受限。

此外，大事务会拖后腿：若 trx2 是超大事务，trx1/trx3 完成后只能等 trx2，期间只有一个 worker 工作，浪费资源。即便如此，该策略仍是优雅的创新。

五、MySQL 5.7：LOGICAL_CLOCK 策略

MySQL 5.7 提供类似功能，由slave-parallel-type控制：

• DATABASE：使用 5.6 的按库策略；
• LOGICAL_CLOCK：类似 MariaDB，但做了并行度优化。

核心问题：同时处于"执行状态"的所有事务，是否可并行？不能——其中可能有因锁冲突而等待的事务，分到不同 worker 会导致主备不一致。

MariaDB 策略核心是"处于 commit 状态的事务可并行"（已通过锁冲突检验）。回顾两阶段提交：

其实不用等到 commit，只要到达redo log prepare 阶段，就表示已通过锁冲突检验。因此 5.7 策略思想：

1. 同时处于prepare 状态的事务，备库可并行；
2. 处于prepare 状态的事务，与处于commit 状态的事务之间，备库也可并行。

结合 binlog 组提交的两个参数（故意拉长 write→fsync 时间，制造更多"同时 prepare"的事务，提升备库并行度）：

• binlog_group_commit_sync_delay：延迟多少微秒才调用 fsync；
• binlog_group_commit_sync_no_delay_count：累积多少次才调用 fsync。

这两个参数既可"故意让主库提交慢些"，又可"让备库执行快些"。处理备库延迟时可调整它们提升并行度。

六、MySQL 5.7.22：基于 WRITESET 的并行复制

2018年4月发布的 5.7.22 新增基于WRITESET的并行复制，参数binlog-transaction-dependency-tracking：

hash 值通过库名+表名+索引名+值计算。与 5.5 按行分发类似，但官方实现优势明显：

1. writeset 在主库生成后直接写入 binlog，备库无需解析 event 行数据，省计算量；
2. 不需要扫整个事务 binlog 来决定分发，更省内存；
3. 分发策略不依赖 binlog 内容，statement 格式也可用。

对于"表没主键""外键约束"场景，WRITESET 也无法并行，会退化为单线程。

主库出问题了，从库怎么办？

一、问题背景

前面的文章介绍了 MySQL 主备复制的基础结构，但都是一主一备的结构。大多数互联网应用场景读多写少，业务发展先遇到的是读性能瓶颈。在数据库层解决读性能问题，就要用到一主多从架构。

本文先聊一主多从的切换正确性，下一篇再聊一主多从的查询逻辑正确性（读写分离）。

基本的一主多从结构：

• 虚线箭头表示主备关系：A 与 A’ 互为主备；
• 从库 B、C、D 指向主库 A；
• 一般用于读写分离：主库负责所有写入和一部分读，从库分担其余读请求。

主库故障后的主备切换结果：

相比一主一备，一主多从切换完成后，A’ 成为新主库，从库 B、C、D 都要改接到 A’。正是多了"从库重新指向"这个过程，切换复杂性相应增加。

二、基于位点的主备切换

把节点 B 设置为节点 A’ 的从库，需要执行CHANGE MASTER命令：

CHANGE MASTERTOMASTER_HOST=$host_name MASTER_PORT=$port MASTER_USER=$user_name MASTER_PASSWORD=$password MASTER_LOG_FILE=$master_log_name MASTER_LOG_POS=$master_log_pos

• 前 4 个参数：新主库 A’ 的 IP、端口、用户名、密码；
• 后 2 个参数MASTER_LOG_FILE/MASTER_LOG_POS：同步位点（主库对应的 binlog 文件名和偏移量）。

问题：节点 B 原本是 A 的从库，本地记录的是 A 的位点；而相同日志在 A 与 A’ 上的位点不同。因此 B 切换时需要先经过"找同步位点"逻辑，而这个位点很难精确取到，只能取个大概。

取同步位点的方法

为保证切换过程不丢数据，找位点时要"稍微往前"，再跳过从库 B 上已执行的事务：

1. 等待新主库 A’ 把中转日志（relay log）全部同步完成；
2. 在 A’ 上执行show master status，得到当前最新的 File 和 Position；
3. 取原主库 A 故障的时刻 T；
4. 用mysqlbinlog解析 A’ 的 File，得到 T 时刻的位点。

mysqlbinlog File --stop-datetime=T --start-datetime=T

图中end_log_pos后面的值"123"，就是 A’ 在 T 时刻写入新 binlog 的位置，可把它作为$master_log_pos用在 B 的CHANGE MASTER命令里。

为什么不精确？

设想一种情况：T 时刻主库 A 已执行完一个 insert 插入行 R，并且 binlog 已传给 A’ 和 B，传完瞬间 A 掉电。此时状态：

1. 从库 B 上，R 已存在（已同步 binlog）；
2. 新主库 A’ 上，R 也已存在，日志写在 123 之后；
3. B 执行CHANGE MASTER指向 A’ 的 File 的 123 位置，会再把"插入 R"的 binlog 同步到 B 执行。

→ B 的同步线程报Duplicate entry 'id_of_R' for key 'PRIMARY'，主键冲突，停止同步。

因此切换时通常要主动跳过这些错误，两种常用方法：

方法一：跳过事务

setglobalsql_slave_skip_counter=1;startslave;

切换过程可能重复执行多个事务，需要在 B 刚接到 A’ 时持续观察，每次报错就执行一次跳过命令，直到不再停下。

方法二：设置跳过指定错误

-- 1062：插入时唯一键冲突；1032：删除时找不到行setglobalslave_skip_errors='1032,1062';

注意：这种直接跳过指定错误的方法，仅用于主备切换时找不到精确同步位点的场景，前提是清楚此时跳过 1032/1062 是无损的。主备同步关系建立并稳定运行后，应把该参数置空，避免后续真的主从不一致也被跳过。

三、GTID

sql_slave_skip_counter和slave_skip_errors虽然能建立主备关系，但操作复杂、易出错。MySQL 5.6 引入GTID，彻底解决了"找同步位点"的困难。

基本概念

GTID（Global Transaction Identifier，全局事务 ID）：事务提交时生成，是该事务的唯一标识。格式：

GTID = server_uuid : gno

• server_uuid：实例首次启动时自动生成，全局唯一；
• gno：整数，初始 1，每次提交事务时分配并加 1。

官方文档写成source_id:transaction_id。这里transaction_id易误解——事务 id 在执行过程中就分配（回滚也递增），而 gno 在提交时才分配。GTID 通常是连续的，用 gno 更易理解。

开启 GTID 模式：

gtid_mode=on enforce_gtid_consistency=on

在 GTID 模式下，每个事务与一个 GTID 一一对应，gtid_next决定生成方式：

1. gtid_next=automatic（默认）：MySQL 分配server_uuid:gno；
   • 记录 binlog 时先写一行SET @@SESSION.GTID_NEXT='server_uuid:gno';
   • 将该 GTID 加入本实例的 GTID 集合。
2. gtid_next=指定值（如set gtid_next='current_gtid'）：
   • 若该 GTID 已存在于实例 GTID 集合中 → 后续事务被忽略；
   • 若不存在 → 分配给后续事务，不再生成新 GTID，gno 不加 1。

每个实例维护一个 GTID 集合，对应"该实例执行过的所有事务"。

基本用法示例

实例 X 中建表并插入数据：

CREATETABLE`t`(idint(11)NOTNULL,cint(11)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDB;INSERTINTOtVALUES(1,1);

事务BEGIN前有一条SET @@SESSION.GTID_NEXT命令。若实例 X 有从库，同步执行时先执行这两个 SET，从库的 GTID 集合就被加入这两个 GTID。

场景：实例 X 是 Y 的从库，Y 上执行insert into t values(1,1)，其 GTID 为aaaaaaaa-cccc-dddd-eeee-fffffffffff:10。X 同步该事务会出现主键冲突，处理方法：

setgtid_next='aaaaaaaa-cccc-dddd-eeee-fffffffffff:10';begin;commit;setgtid_next=automatic;startslave;

前三条语句通过提交空事务，把这个 GTID 加入实例 X 的 GTID 集合。start slave前set gtid_next=automatic恢复默认分配行为（新事务继续分配 gno=3）。之后同步线程再执行 Y 传来的事务时，因该 GTID 已存在，X 会直接跳过，不再主键冲突。

四、基于 GTID 的主备切换

GTID 模式下，备库 B 设为新主库 A’ 的从库：

CHANGE MASTERTOMASTER_HOST=$host_name MASTER_PORT=$port MASTER_USER=$user_name MASTER_PASSWORD=$password master_auto_position=1;-- 1 表示使用 GTID 协议

MASTER_LOG_FILE和MASTER_LOG_POS不再需要指定，"找位点"的痛点消失。

切换逻辑（设 A’ 的 GTID 集合为set_a，B 的为set_b）：

1. B 指定主库 A’，基于主备协议建立连接；
2. B 把set_b发给 A’；
3. A’ 计算set_a - set_b（存在于 A’、不存在于 B 的 GTID 集合）：
   • 若 A’ 本地不包含差集所需的全部 binlog → 报错（所需 binlog 已被删除）；
   • 若全部包含→ 从 binlog 中找出第一个不在set_b的事务发给 B；
4. 从该事务开始顺序取 binlog 发给 B 执行。

设计思想：GTID 主备关系要求主库发给备库的日志是完整的，若 B 需要的日志已不存在，A’ 拒绝发送。这与基于位点的协议不同——位点协议由备库指定位点、主库照发，不做完整性判断。

一主多从切换时，从库 B、C、D 只需分别执行CHANGE MASTER指向 A’ 即可。严格说不是"不需要找位点"，而是找位点工作在 A’ 内部自动完成，对 HA 系统开发者非常友好。

切换后 GTID 集合：A’ 自己生成的 binlog 为server_uuid_of_A':1-M；B 原本为server_uuid_of_A:1-N，切换后变为server_uuid_of_A:1-N, server_uuid_of_A':1-M。A’ 此前也是 A 的备库，故 A’ 与 B 的 GTID 集合一致，达到预期。

五、GTID 与在线 DDL

第22讲提到：业务高峰期因索引缺失导致慢查询，可先在备库加索引再切换。双 M 结构下为避免 DDL 传回主库，曾用set sql_log_bin=off关闭 binlog——但这会导致"库里有索引、binlog 却没记录"的数据/日志不一致问题。用 GTID 可更好解决。

实例 X（当前主库）与 Y 互为主备，均开启 GTID。切换流程：

1. 在 X 上执行stop slave；
2. 在 Y 上执行 DDL（无需关闭 binlog），记下该 DDL 的 GTID =server_uuid_of_Y:gno；
3. 到 X 上执行：

setGTID_NEXT="server_uuid_of_Y:gno";begin;commit;setgtid_next=automatic;startslave;

既让 Y 的更新有 binlog 记录，又确保不会在 X 上真正执行这条 DDL。之后完成主备切换，再照此流程执行一遍即可。

六、核心总结

结论：若 MySQL 版本支持 GTID，建议尽量用 GTID 模式做一主多从切换。

实践是检验真理的唯一标准