📖 本篇简介：主从复制是 MySQL 高可用和读写分离的基石，但复制链路本身坑很多——server_id 冲突、大事务延迟、从库被误写、GTID 跳号、半同步超时退回异步。本章梳理主从复制搭建与运行中的 11 个高频踩坑。示例基于 MySQL 8.0（本机无服务，命令按官方语法书写）。读完你能搭稳复制链路、排查延迟和中断。
目录
- 坑 1：从库还在写导致数据不一致
- 坑 2：server_id 冲突从库互踢
- 坑 3：大事务导致主从延迟小时级
- 坑 4：从库 SQL 线程单线程回放慢
- 坑 5：binlog_format 用 STATEMENT 的坑
- 坑 6：主库误删从库跟着删
- 坑 7：半同步超时退回异步没告警
- 坑 8：复制过滤 replicate_do_db 跨库失效
- 坑 9：主从延迟大切流脑裂
- 坑 10：relay log 损坏跳过事务修复
- 坑 11：GTID 没配对从库追不上
- 速查表：主从复制速记
- 注意事项：复制链路 Checklist
- 自测：你懂主从复制吗
坑 1：从库还在写导致数据不一致
场景 / 错误示范：
-- 开发者图方便，在从库上建了张临时表跑分析
CREATE TABLE从库.tmp_analysis AS SELECT ...; -- 在从库写！
-- 或业务方误以为从库"空闲"，把一些写也打到从库
-- 主从数据从此分叉，下次复制同样的主键直接冲突报错
为什么是坑：从库的职责是"原样回放主库的 binlog"。一旦有人在从库上直接写，从库就有了自己的私有数据。等主库 binlog 里出现同样主键/同样行，回放时冲突，复制直接中断。
正确做法：
-- 从库设成只读（super 也要遵守）
SET GLOBAL read_only = ON; -- 普通账号不能写
SET GLOBAL super_read_only = ON; -- 连 super 也不能写，彻底防误写
-- 真要在从库跑分析，建临时库/临时表在单独的分析实例上做，别碰复制从库
-- 确认从库只读生效
SHOW VARIABLES LIKE 'read_only'; -- ON
SHOW VARIABLES LIKE 'super_read_only'; -- ON（连 super 也不能写）
-- super_read_only=ON 才是真只读；只开 read_only，root 照样能写
加分点：从库一律开 super_read_only。要写分析数据，去专门的分析库，别在复制从库上动。
坑 2：server_id 冲突从库互踢
场景 / 错误示范：
-- 搭了两个从库，server_id 都没改，默认或都写成 1
SHOW VARIABLES LIKE 'server_id'; -- 两个从库都是 1
-- 两个从库用同一个 server_id 连主库，互相把对方踢掉，复制反复断
为什么是坑：主从复制靠 server_id 区分每个实例。多个实例 server_id 相同，主库无法区分，后连的把先连的踢掉，复制链路反复抖动。这是搭主从最基础的坑。
正确做法：
-- 每个实例 server_id 全局唯一
-- my.cnf：
-- server_id=1 （主库）
-- server_id=2 （从库1）
-- server_id=3 （从库2）
SHOW VARIABLES LIKE 'server_id'; -- 逐个确认不重复
加分点：搭复制前第一件事就是给每个实例分配唯一 server_id，别用默认值。
坑 3：大事务导致主从延迟小时级
场景 / 错误示范：
-- 主库一个事务删了几百万行
START TRANSACTION;
DELETE FROM log_t WHERE create_time < '2024-01-01'; -- 几百万行
COMMIT;
-- 主库提交了，从库要把这几百万行的变更一条条回放，延迟越拉越大
为什么是坑：主从延迟 = 主库执行耗时 + 从库回放耗时。主库一个大事务瞬间提交，从库要花同样甚至更久去回放 binlog。期间从库数据远远落后，读写分离读到旧数据。
正确做法：
-- 主库把大操作拆成小事务（呼应第53章）
DELETE FROM log_t WHERE create_time < '2024-01-01' ORDER BY id LIMIT 2000;
-- 循环分批，每批小事务，从库跟得上，延迟平滑
-- 监控延迟：Seconds_Behind_Source 超阈值告警
-- 查看从库延迟
SHOW REPLICA STATUS\G
-- 关键三行：
-- Replica_IO_Running: Yes -- 拉 binlog 线程正常
-- Replica_SQL_Running: Yes -- 回放线程正常
-- Seconds_Behind_Source: 0 -- 延迟秒数
-- 两个 Yes 且延迟为 0，才是健康的复制
-- 常见延迟根因排查顺序：
-- 1. 主库有没有刚跑大事务？（查主库 binlog 大事件）
-- 2. 从库回放是单线程还是并行 worker 够不够？
-- 3. 从库机器 IO/CPU 是不是被别的任务打满？
-- 三步定位完，延迟问题基本能找到根因
加分点：主从延迟的根源大多在主库的大事务。把大操作拆小，是控延迟最有效的手段。
坑 4：从库 SQL 线程单线程回放慢
场景 / 错误示范：
-- 主库写入并发很高，从库单 SQL 线程回放追不上，延迟持续拉大
SHOW REPLICA STATUS\G; -- Seconds_Behind_Source 越来越大
为什么是坑：传统从库只有一个 SQL 线程回放 relay log，主库是多线程并发写的，从库单线程天然追不上高并发主库。延迟随写入量累积。
正确做法：
-- 8.0 开启基于组提交的并行复制（默认已较好）
SHOW VARIABLES LIKE 'replica_parallel_workers'; -- 调大并行 worker 数
SHOW VARIABLES LIKE 'replica_parallel_type'; -- LOGICAL_CLOCK 并行
-- 8.0 多线程复制已经很成熟，合理配置 worker 数能显著追平
-- 8.0 多线程复制相关参数
SHOW VARIABLES LIKE 'replica_parallel_workers'; -- 并行 worker 数，建议 4~16
SHOW VARIABLES LIKE 'replica_parallel_type'; -- LOGICAL_CLOCK
-- 查看回放线程数状态
SHOW STATUS LIKE 'Slave_open_temp_tables';
-- worker 数不是越多越好，太多会互相竞争，按 CPU 和延迟调
加分点：8.0 的 LOGICAL_CLOCK 并行复制能把"同一时刻组提交"的事务并行回放，是追高并发主库的关键配置。
坑 5：binlog_format 用 STATEMENT 的坑
场景 / 错误示范：
-- 主库 binlog_format 是 STATEMENT，记录的是 SQL 原文
UPDATE t SET create_time = NOW() WHERE id = 1;
-- 从库回放这条 SQL 时 NOW() 是从库的时间，和主库实际执行时间不同！
为什么是坑：STATEMENT 格式记 SQL 原文，像 NOW()、UUID()、LOAD_FILE() 这类函数在主从执行结果不同，从库回放出来的数据就和主库不一致。这是主从数据悄悄分叉的隐蔽原因。
正确做法：
-- 生产一律用 ROW 格式，记录每行实际变更
SET GLOBAL binlog_format = 'ROW';
-- ROW 记的是"这行从什么值改成什么值"，主从重放结果绝对一致
-- 配合从库 read_only，一致性最好
加分点：ROW 格式 binlog 更大，但保证主从一致。现在生产几乎都用 ROW，STATEMENT 只在特殊兼容场景用。
坑 6：主库误删从库跟着删
场景 / 错误示范：
-- 主库上误 DROP DATABASE 或 DELETE 大表
-- binlog 把这个操作复制到从库，从库跟着删
-- 本来指望从库"还有一份"，结果主从一起被删光
为什么是坑：复制是单向全量回放——主库干什么，从库跟着干什么。主库的误操作会忠实复制到从库，从库并不是"备份"。指望从库救误删，往往一起完蛋。
正确做法：
-- 从库不是备份。误删恢复靠的是全量备份 + binlog（见第59章）
-- 主库高危操作（DROP/DELETE）前先确认、在从库也拦不住
-- 真正的安全网是备份，不是从库
-- 澄清：从库 vs 备份 的定位
-- 从库：高可用备胎 + 读扩展，数据是主库的实时副本，会跟着主库误操作走
-- 备份：某时刻的静态快照 + binlog，用于误删/损坏后的恢复
-- 想防"误删"，靠第59章的备份体系，别指望从库
加分点：澄清一个常见误解——从库是"读副本/高可用备胎"，不是"防误删的备份"。防误删靠备份+binlog。
坑 7：半同步超时退回异步没告警
场景 / 错误示范：
-- 配了半同步，以为主库写一定等到从库收到才返回
-- 但从库网络抖了一下，超过半同步超时（默认 10ms），自动退回异步
-- 之后主库写不等人，从库没收到就丢了数据，无告警
为什么是坑：半同步有个"超时降级"机制——等不到从库 ACK 超时后，主库自动退回异步复制。这个退回是静默的，一旦发生，"半同步保证"就没了，数据可能丢。
正确做法：
-- 监控半同步状态
SHOW VARIABLES LIKE 'rpl_semi_sync_source_enabled';
SHOW STATUS LIKE 'Rpl_semi_sync_source_status'; -- ON=半同步在生效，OFF=已降级
-- 一旦发现降级，立刻告警并排查从库
-- 半同步关键状态变量
SHOW STATUS LIKE 'Rpl_semi_sync_source_await_snd_back_log'; -- 等待 ACK 次数
-- 等待次数持续升高 = 从库 ACK 越来越慢，可能要超时降级
-- 把 Rpl_semi_sync_source_status=OFF 接进监控，一旦出现立刻报警
加分点：半同步不是"配了就一直同步"，它会超时降级。必须监控半同步状态，降级了要报警。
坑 8：复制过滤 replicate_do_db 跨库失效
场景 / 错误示范：
-- 从库只想同步 app_db 这个库，配了 replicate_do_db=app_db
-- 主库执行：USE other_db; DELETE FROM app_db.user WHERE id=1;
-- USE 的是 other_db，过滤条件按"当前库"判断，这条 DELETE 被跳过了！
为什么是坑：基于库名的复制过滤（replicate_do_db）是按"当前 USE 的库"判断的，不是按"操作的表在哪个库"。跨库操作（A 库上下文改 B 库的表）会被错误过滤，数据不一致。
正确做法：
-- 用基于表的过滤更可靠，或干脆不过滤、全量同步
-- replicate_wild_do_table=app_db.% -- 按表名前缀过滤，跨库也认
-- 更稳：从库全量同步，业务上不读不需要的库
加分点：复制过滤是数据不一致的高发区，能不用就不用；真要用，用 wildcard 表级过滤，别用库级。
坑 9：主从延迟大切流脑裂
场景 / 错误示范：
-- 主库挂了，把从库提升为新主
-- 但此时从库延迟了 30 秒，落后主库 30 秒的数据没同步完
-- 老主库又活过来，两个主都在写，数据脑裂分叉
为什么是坑：主从延迟大时做切换，从库数据不全；老主库如果还活着又没被隔离，就两个主同时写，脑裂。这是高可用切换最危险的场景（详见第 61 章）。
正确做法：
-- 切换前必须确认从库追平：Seconds_Behind_Source=0
-- 切换时先把老主库彻底隔离（关停/防火墙），再提从库
-- 自动化切换工具（MHA/Orchestrator）会先等追平再切
-- 健康切换的检查清单（自动化工具内部也是这套逻辑）：
-- 1. 确认从库 Seconds_Behind_Source=0（追平）
-- 2. 把老主库隔离：STOP 它的写入（防火墙/关库），防止它复活又写
-- 3. 把从库提升：STOP REPLICA; RESET REPLICA ALL; 改成可写主
-- 4. 把其余从库重新指向新主
-- 任何一步跳过，都可能脑裂或丢数据
加分点：延迟大时绝不贸然切主。先等从库追平、隔离老主，再提新主，三步缺一不可。
坑 10：relay log 损坏跳过事务修复
场景 / 错误示范：
-- 从库回放中断，relay log 坏了或某条 SQL 冲突
-- 开发者直接跳过：STOP REPLICA; SET GLOBAL sql_replica_skip_counter=1;
-- 跳了一个事务，主从数据又不一致了
为什么是坑：跳过一个事务能让复制继续，但代价是这部分数据从库永远没有了，主从分叉。"跳事务继续跑"是饮鸩止渴，跳一次就少一段数据。
正确做法：
-- 优先用 GTID 方式精准处理，而不是盲目 skip counter
-- 真正因为数据冲突中断时，先评估这条事务能不能补：
-- 1. 如果是从库多写导致的冲突，删掉从库多余数据，让它继续
-- 2. 实在要跳过，记录跳过了什么、之后怎么补，别默默跳
STOP REPLICA;
SET GLOBAL sql_replica_skip_counter = 1; -- 知道代价：少这段数据
START REPLICA;
加分点：skip_counter 是最后手段，跳了就要记录、之后补数据。盲目跳事务是在给未来埋不一致的雷。
坑 11：GTID 没配对从库追不上
场景 / 错误示范：
-- 搭新从库，用 GTID 复制，但从库的 gtid_executed 和主库对不上
CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION=1 ...;
-- 启动后从库说"我已经有这些 GTID 了"，跳过了主库的事务，数据缺一块
为什么是坑：GTID 复制靠"双方 GTID 集合差异"来决定补哪些事务。如果从库的数据备份不是从同一 GTID 点来的，GTID 集合对不上，从库会以为某些事务"已经执行过"而跳过，数据悄悄缺。
正确做法：
-- 用 mysqldump --set-gtid-purged 导出，保证从库起点 GTID 干净
-- 或备份时记录正确的 GTID 位点
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='主库', SOURCE_AUTO_POSITION=1;
START REPLICA;
-- 启动后查 Seconds_Behind_Source，确认真的在追
-- 查看 GTID 集合
SELECT @@global.gtid_executed; -- 本库已经执行过的所有事务 GTID
-- 搭从库后确认它真的在追，而不是"以为追实际跳过"
SHOW REPLICA STATUS\G
-- 看 Retrieved_Gtid_Set 和 Executed_Gtid_Set 在增长
加分点：GTID 复制省心，但前提是搭建时 GTID 集合对齐。搭完务必确认从库在正常追，不是"以为在追实际跳过了"。
速查表：主从复制速记
| 坑点 | 错误做法 | 正确做法 |
|---|---|---|
| 从库写 | 在从库建表/写 | super_read_only 只读 |
| server_id | 都用默认 | 每个实例全局唯一 |
| 大事务 | 一个事务删百万行 | 分批小事务 |
| 回放慢 | 单线程 | LOGICAL_CLOCK 并行 worker |
| binlog 格式 | STATEMENT | ROW 保证一致 |
| 误删 | 指望从库救命 | 从库不是备份 |
| 半同步 | 配了就放心 | 监控降级告警 |
| 复制过滤 | replicate_do_db | wildcard 表级或不过滤 |
| 切主 | 延迟大就切 | 追平+隔离老主 |
| relay 中断 | 盲目 skip counter | 评估后补数据 |
| GTID | 集合没对齐 | 搭时对齐 GTID |
注意事项：复制链路 Checklist
- [ ] 每个实例 server_id 唯一吗？
- [ ] 从库开了 super_read_only 吗？
- [ ] binlog_format 是 ROW 吗？
- [ ] 监控主从延迟（Seconds_Behind_Source）了吗？
- [ ] 半同步状态监控降级了吗？
- [ ] 从库回放并行 worker 配了吗？
- [ ] 切主前等追平、隔离老主了吗？
- [ ] 大事务在主库拆小了吗？
- [ ] relay 中断时知道跳事务的代价吗？
一句话：从库只读、server_id 唯一、binlog 用 ROW、延迟要监控、切主等追平——这五条做对，主从链路基本稳。
自测：你懂主从复制吗
题 1：为什么从库上不能随便写？
<details>
<summary>参考答案</summary>
从库直接写会产生私有数据，主库 binlog 回放同样主键时冲突，复制中断、数据分叉。从库要开 super_read_only。
</details>
题 2：多个从库 server_id 相同，会发生什么？
<details>
<summary>参考答案</summary>
主库无法区分，后连的把先连的踢掉，复制反复抖动。每个实例 server_id 必须全局唯一。
</details>
题 3：主库一个大事务删了几百万行，从库为什么延迟小时级？
<details>
<summary>参考答案</summary>
主库瞬间提交，从库要逐条回放几百万行变更。应在主库把大操作拆成小事务，从库才追得平。
</details>
题 4：binlog_format 为什么推荐 ROW 而不是 STATEMENT？
<details>
<summary>参考答案</summary>
STATEMENT 记 SQL 原文，NOW()/UUID() 等函数主从执行结果不同，导致数据分叉。ROW 记每行实际变更，回放绝对一致。
</details>
题 5：主库误删了表，从库能不能保住数据？
<details>
<summary>参考答案</summary>
不能。复制忠实回放主库操作，从库跟着删。从库是读副本不是备份，防误删要靠全量备份+binlog。
</details>
题 6：半同步复制配好了，为什么还可能丢数据？
<details>
<summary>参考答案</summary>
半同步等不到从库 ACK 超时会自动退回异步，这个降级是静默的。要监控半同步状态，降级即告警。
</details>
题 7：replicate_do_db=app_db，跨库操作为什么会漏同步？
<details>
<summary>参考答案</summary>
它按"当前 USE 的库"过滤，不是按操作的表所在库。USE A 库改 B 库的表会被误过滤。用 wildcard 表级过滤更可靠。
</details>
题 8：主从延迟 30 秒时直接切主，有什么风险？
<details>
<summary>参考答案</summary>
从库数据不全，丢 30 秒数据；老主若还活着没被隔离，两个主同时写导致脑裂。必须先等追平、隔离老主再切。
</details>
题 9：从库回放中断用 sql_replica_skip_counter 跳过，有什么代价？
<details>
<summary>参考答案</summary>
跳过的事务数据从库永远没有了，主从分叉。这是最后手段，跳了要记录并之后补数据，别默默跳。
</details>
题 10：监控主从延迟看哪个指标？
<details>
<summary>参考答案</summary>
SHOW REPLICA STATUS 的 Seconds_Behind_Source（Seconds_Behind_Master）。持续大于 0 且不下降，说明从库追不上，要查大事务/单线程回放。
</details>
题 11：SHOW REPLICA STATUS 里 Replica_SQL_Running=No，说明什么？
<details>
<summary>参考答案</summary>
SQL 回放线程停了，复制中断。要看 Last_SQL_Error 找原因（通常是主键冲突/relay log 损坏），处理后再 START REPLICA。IO 线程 No 则是连不上主库或网络问题。
</details>
下一篇预告：第 61 章 高可用切换踩坑
文章回复
0 条公开回复