第 38 章 MySQL踩坑：主从复制与高可用篇

📖 本篇简介：本篇是《MySQL 从入门到精通》踩坑系列的第 6 篇，专门梳理主从复制与高可用（MHA / Keepalived / MGR 思路）上线后最容易踩的 12 个坑。前置知识建议先复习第 22 章主从复制原理，以及第 14 章事务、第 21 章 InnoDB 原理。学完本篇你将能独立排查复制延迟、半同步失效、切换丢数据、脑裂双写等线上事故。本示例基于 MySQL 8.0，复制术语统一使用 8.0 的 REPLICA 写法（旧版 SLAVE 为别名，仍兼容）。

目录

• 坑 1：主从复制延迟持续增大
• 坑 2：binlog 格式选错导致主从不一致
• 坑 3：半同步复制超时误配引发雪崩
• 坑 4：主库切换时未追平 relay log 丢数据
• 坑 5：server_id 重复导致复制线程异常退出
• 坑 6：从库被误写造成数据分叉
• 坑 7：GTID 模式下错误跳过事务
• 坑 8：大事务塞满 relay log 拖垮从库
• 坑 9：复制过滤规则在主从不一致
• 坑 10：VIP 切换脑裂导致双写
• 坑 11：监控缺失导致 SQL 线程停摆无人知
• 坑 12：并行复制配置不当延迟不降反升
• 本篇速查表
• 运维注意事项
• 动手练习

坑 1：主从复制延迟持续增大

场景 / 错误示范：

业务高峰期从库查询报"数据刚写入却查不到"，运维只看了一眼 Seconds_Behind_Source 就回滚代码，没有定位根因。

-- 在从库执行，只看了一眼延迟数字就下结论（MySQL 8.0 语法）
SHOW REPLICA STATUS\G
-- 输出里 Seconds_Behind_Source = 300，但没人继续看下面的线程状态

为什么是坑：主从延迟绝大多数不是网络问题，而是从库单线程重放（或并行度不够）跟不上主库写入、大事务、从库慢查询抢锁、relay log 磁盘 IO 打满导致的。只盯着延迟数字"重启复制"治标不治本，高峰期延迟会越滚越大。

正确做法：

-- 1) 同时看 IO/SQL 两个线程状态与延迟来源
SHOW REPLICA STATUS\G
-- 重点看：
--   Replica_IO_Running = Yes
--   Replica_SQL_Running = Yes
--   Seconds_Behind_Source = 实际延迟秒数
--   Exec_Source_Log_Pos 与 Read_Source_Log_Pos 的差

-- 2) 看从库当前正在重放什么
SELECT * FROM performance_schema.threads
 WHERE NAME = 'sql/thread_worker'
 ORDER BY PROCESSLIST_TIME DESC;

-- 3) 主库找当前最大事务，判断是否大事务拖慢
SELECT * FROM information_schema.INNODB_TRX
 ORDER BY TRX_ROWS_LOCKED DESC LIMIT 5;

加分点：用 pt-heartbeat 做毫秒级延迟监控，比 Seconds_Behind_Source 更准；延迟超过阈值（如 10s）自动把读流量切回主库。

坑 2：binlog 格式选错导致主从不一致

场景 / 错误示范：

图省事在 my.cnf 里把 binlog 格式设成 STATEMENT，上线几天后发现主从同一行数据对不上。

-- 错误的 my.cnf 配置：语句级复制，函数时间不确定
[mysqld]
binlog_format = STATEMENT

-- 在主库执行 NOW() / UUID() 类函数，主从结果不同
UPDATE user SET last_login = NOW() WHERE id = 1;
-- STATEMENT 模式只记 SQL 语句，从库重放时 NOW() 变成从库自己的时间

为什么是坑：STATEMENT 格式只记录 SQL 原文，遇到 NOW()、UUID()、LOAD_FILE()、RAND() 等非确定函数，主库与从库重放结果不同；AUTO_INCREMENT 自增列在并发下也可能错位。MySQL 8.0 默认推荐 ROW 格式。

正确做法：

-- 正确的 my.cnf：行级复制，记录每行的前后镜像
[mysqld]
binlog_format = ROW
binlog_row_image = FULL          -- 记录完整行镜像，便于误删闪回
binlog_row_metadata = FULL       -- 8.0 可选，附带更多元数据

-- 动态在线修改（无需重启，谨慎在低峰执行）
SET GLOBAL binlog_format = 'ROW';
-- 验证
SHOW VARIABLES LIKE 'binlog_format';

加分点：ROW 模式 binlog 体积会变大，配合 binlog_row_image = MINIMAL 可减少日志量；大事务拆小也是降低 binlog 体积的关键。

坑 3：半同步复制超时误配引发雪崩

场景 / 错误示范：

为了"不丢数据"开了半同步，却把超时时间设得极短，从库一抖动主库就退回异步，业务还以为有保护。

-- 错误配置：超时 1 毫秒，等于没开
SET GLOBAL rpl_semi_sync_source_timeout = 1;
SET GLOBAL rpl_semi_sync_source_enabled = ON;

为什么是坑：半同步的含义是"主库提交后至少等 N 个从库 ACK 才返回客户端"。超时时间太短，从库稍微 GC、网络抖动就超时退回异步，此时主库宕机照样丢已提交事务；超时太长又会让主库提交堆积雪崩。两个极端都不可取。

正确做法：

-- 正确的 my.cnf 半同步参数（MySQL 8.0 命名）
[mysqld]
plugin_load_add = semi_sync_source.so
rpl_semi_sync_source_enabled = 1
rpl_semi_sync_source_timeout = 1000    -- 1 秒，不是 1 毫秒
rpl_semi_sync_source_wait_for_replicas = 1
rpl_semi_sync_source_wait_no_replica = ON

-- 在从库开启半同步客户端
SET GLOBAL rpl_semi_sync_replica_enabled = ON;
-- 观察状态：是否真的在等 ACK
SHOW STATUS LIKE 'Rpl_semi_sync_source_status';

加分点：生产建议半同步 + 超时降级 + 报警三结合：一旦 Rpl_semi_sync_source_status = OFF（已退异步）立即告警，而不是盲目相信"我们开了半同步"。

坑 4：主库切换时未追平 relay log 丢数据

场景 / 错误示范：

主库宕机，MHA 或手工 failover 直接把从库提升为主，跳过了"等 relay log 重放完"这一步。

# 错误操作：主库挂了，直接把从库切成新主，不等 SQL 线程追平
mysql -e "STOP REPLICA; RESET REPLICA ALL;"
mysql -e "SET GLOBAL read_only = OFF;"
# 此时 relay log 里可能还压着几百个未应用的事务

为什么是坑：从库的 IO 线程已经把主库 binlog 拉到了本地 relay log，但 SQL 线程还没重放完。如果直接提升，这部分"已拉到但未执行"的事务就永久丢失，且业务无感知。

正确做法：

-- 正确步骤 1：在待提升的从库上，先把 IO 停掉、SQL 追平
STOP REPLICA IO_THREAD;

-- 正确步骤 2：循环等待，直到 Exec 位置等于 Read 位置
SHOW REPLICA STATUS\G
-- 直到：Read_Source_Log_Pos == Exec_Source_Log_Pos
--       Relay_Log_Pos 不再变化

-- 正确步骤 3：确认无延迟后再提升
STOP REPLICA;
RESET REPLICA ALL;
SET GLOBAL super_read_only = OFF;   -- 比 read_only 更彻底，防 super 用户误写

加分点：MHA 等高可用工具内部会自动做"保存 relay log 并追平"，但手工切换时务必自己写一个等待脚本：轮询直到 Seconds_Behind_Source = 0 再提升。

坑 5：server_id 重复导致复制线程异常退出

场景 / 错误示范：

搭第二套从库时直接复制了第一套从库的 my.cnf，忘了改 server_id，结果复制线程起不来。

-- 错误：两台从库 server_id 相同
[mysqld]
server_id = 101   -- 主库也是 101？或另一台从库也是 101

-- 从库启动复制后报错
START REPLICA;
-- 报错：Fatal error: The slave I/O thread stops because master and slave have
-- equal MySQL server ids; these ids must be different.

为什么是坑：MySQL 用 server_id 在复制拓扑里唯一标识每个实例。主库发现 IO 线程带的 server_id 跟自己一样，认为"自己连自己"，直接拒绝。一主多从时尤其容易因为克隆配置而重复。

正确做法：

-- 正确：每个实例 server_id 全局唯一，建议规划成 IP 尾号
[mysqld]
server_id = 101     -- 主库
server_id = 102     -- 从库 A
server_id = 103     -- 从库 B

-- 动态查看所有实例 id（配合 GTID 更直观）
SHOW VARIABLES LIKE 'server_id';
-- GTID 模式下顺便看 UUID
SHOW VARIABLES LIKE 'server_uuid';

加分点：8.0 推荐用 GTID 复制，server_uuid 自动全局唯一；但 server_id 仍必须手工保证唯一，部署清单里把"改 server_id"写成上线必查项。

坑 6：从库被误写造成数据分叉

场景 / 错误示范：

开发临时在从库跑了个脚本改数据，觉得"从库只读，不会有事"，结果因为没开 super_read_only，root 一把写进去了。

-- 错误：从库只设了 read_only，super 权限用户仍可写
SET GLOBAL read_only = ON;

-- 用 root 登录从库，照样能改
UPDATE orders SET status = 'paid' WHERE id = 9527;
-- 语句执行成功！从库这条数据与主库分叉

为什么是坑：read_only = ON 只阻止普通用户写，拥有 SUPER 权限的用户（如 root）照样能写。一旦从库被写，复制会继续，但双方数据已经不一致，后续冲突极难排查。

正确做法：

-- 正确：从库同时开 super_read_only，连 super 用户也不许写
SET GLOBAL super_read_only = ON;

-- 验证：再执行写操作应被拒绝
UPDATE orders SET status = 'paid' WHERE id = 9527;
-- 报错：The MySQL server is running with the SUPER_READ_ONLY option

-- my.cnf 固化，重启不失效
[mysqld]
read_only = ON
super_read_only = ON

加分点：切换主库时记得 SET GLOBAL super_read_only = OFF，否则新主起来拒绝写入，等于切换失败；切换脚本务必把"关 super_read_only"写进去。

坑 7：GTID 模式下错误跳过事务

场景 / 错误示范：

从库复制报错，老办法 STOP REPLICA; SET GLOBAL sql_slave_skip_counter = 1; START REPLICA;，在 GTID 模式下直接报错。

-- 错误：GTID 模式下用老方法跳事务
STOP REPLICA;
SET GLOBAL sql_slave_skip_counter = 1;
-- 报错：GTID_MODE = ON 时不能使用 sql_slave_skip_counter
START REPLICA;

为什么是坑：GTID 模式下每个事务有全局唯一 ID，sql_slave_skip_counter 是基于文件位点时代的产物，GTID 下被禁用。盲目跳事务还会让 GTID 集合出现空洞，未来重建从库时 auto_position 会认为你缺事务而卡住。

正确做法：

-- 正确做法 1：先看报错到底是哪个事务、什么错误
SHOW REPLICA STATUS\G
-- 看 Retrieved_Gtid_Set / Executed_Gtid_Set / Last_Error

-- 正确做法 2：构造一个空事务"占位"跳过该 GTID
STOP REPLICA;
SET GTID_NEXT = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:12345';  -- 换成报错的 GTID
BEGIN;
COMMIT;                       -- 空事务，标记该 GTID 已执行
SET GTID_NEXT = 'AUTOMATIC';  -- 恢复自动分配
START REPLICA;

加分点：跳事务是"止血"不是"治病"，跳完必须把缺的数据在主库补一遍；能用 pt-table-checksum + pt-table-sync 修数据就别靠跳 GTID。

坑 8：大事务塞满 relay log 拖垮从库

场景 / 错误示范：

业务上线一个凌晨跑批的 UPDATE，一条语句改了 500 万行，从库 SQL 线程被卡 40 分钟，期间所有查询延迟雪崩。

-- 错误示范：一条语句改全表 500 万行
UPDATE order SET status = 'archived'
 WHERE create_time < '2024-01-01';
-- 主库瞬间提交，从库单线程重放这一条事务，堵 40 分钟

为什么是坑：主从复制是"一个事务要么全发要么不发"，大事务在主库很快提交，但从库重放时这一大坨日志会独占 SQL 线程，后面所有小事务全部排队；relay log 也会被瞬间撑大占满磁盘。

正确做法：

-- 正确：按主键分批提交，每批 1000~5000 行
-- 用存储过程或脚本循环执行
UPDATE order SET status = 'archived'
 WHERE create_time < '2024-01-01'
 ORDER BY id LIMIT 2000;      -- 每批 2000 行，执行 N 次

-- 批与批之间短暂 sleep，给从库追的时间
-- 伪代码：while 影响行数 > 0 { 执行一批; sleep 0.2; }

-- 配合限制单事务大小
[mysqld]
max_binlog_cache_size = 256M   -- 超大事务直接报错，倒逼拆分

加分点：用 binlog_transaction_compression = ON（8.0.20+）压缩事务日志，能显著降低大事务对从库 IO 的冲击，但根治仍靠拆小事务。

坑 9：复制过滤规则在主从不一致

场景 / 错误示范：

在从库 my.cnf 配了 replicate_do_db = appdb 只同步业务库，结果主库上写了 use mysql; update appdb.t1 ...，从库根本不执行。

-- 错误：基于库名的过滤陷阱
[mysqld]
replicate_do_db = appdb

-- 在主库先切到 mysql 系统库，再写 appdb
USE mysql;
UPDATE appdb.orders SET status = 'x' WHERE id = 1;
-- STATEMENT/默认逻辑下，从库看"当前库是 mysql"，被 replicate_do_db 过滤掉

为什么是坑：replicate_do_db 过滤的是"当前默认库"，不是"被修改的库"。跨库写（USE 别的库; UPDATE 业务库.表）会被误过滤。replicate_wild_do_table 按表名过滤才是行为可预期的。

正确做法：

-- 正确：用通配表级过滤，明确到库.表
[mysqld]
replicate_wild_do_table = appdb.%
replicate_wild_ignore_table = appdb.tmp_%

-- 验证过滤规则是否生效
SHOW REPLICATION FILTERS;
-- 输出 Replica_Do_Table / Replica_Wild_Do_Table 等

加分点：能不做过滤就不做过滤，过滤规则越多，主从数据分叉的概率越高；必须过滤时，主从两边规则写进同一份配置管理系统，避免两边漂移。

坑 10：VIP 切换脑裂导致双写

场景 / 错误示范：

用 Keepalived 做 VIP 高可用，主库因为网络抖动被判定挂了，VIP 飘到从库，但旧主库其实还活着，两边同时接业务写入。

# 错误：只靠网络心跳判断，没做"旧主主动下线"
# 网络分区时，从库侧认为主死了，抢 VIP；旧主却还在接业务
systemctl stop keepalived   # 没有自动 stop mysql 的脚本

为什么是坑：VIP 高可用最经典的脑裂：发生在"主库活着但和从库心跳断了"的时候。两边同时可写，数据立即分叉，且事后修复代价极大。VRRP 本身不仲裁，必须配合"强制旧主停写"的 fencing 机制。

正确做法：

# 正确思路：切换脚本里强制把旧主置为只读甚至停库
# keepalived 配置 notify 脚本示例（伪代码）
# master 接管时执行:
#   mysql -e "SET GLOBAL super_read_only = OFF;"
# slave 接管时执行:
#   mysql -e "SET GLOBAL super_read_only = ON; SHUTDOWN;"

-- 更稳妥：用带仲裁的方案（MHA / Orchestrator / MGR）
-- MGR 本身需要多数派，自动防脑裂
SELECT * FROM performance_schema.replication_group_members;

加分点：VIP 方案一定要配"fencing（隔离）"动作：接管新主前，先通过 IPMI / 云 API 把旧主强制关机，而不是只赌网络心跳。

坑 11：监控缺失导致 SQL 线程停摆无人知

场景 / 错误示范：

从库因为一条主键冲突停了 3 天，没人发现，直到某次查从库报表数据"怎么不更新了"才察觉。

-- 没人主动跑这条命令
SHOW REPLICA STATUS\G
-- Replica_SQL_Running = No，Last_Error = 1062 Duplicate entry...

为什么是坑：复制线程停止后，从库照常提供查询服务，表面"活着"，但数据早已停更。这种"静默故障"比主库宕机更危险——业务还在读陈旧数据做决策。

正确做法：

-- 把下面两个值纳入监控，任一异常即告警
SHOW REPLICA STATUS\G
-- 告警条件：
--   Replica_IO_Running != Yes
--   Replica_SQL_Running != Yes
--   Seconds_Behind_Source > 30（持续 1 分钟）

# 伪代码：监控脚本每分钟采集一次
import pymysql  # 需 pip install pymysql，本环境仅作示范无服务器可连
def check_replica():
    conn = pymysql.connect(host='从库IP', user='monitor', password='xxx')
    cur = conn.cursor()
    cur.execute("SHOW REPLICA STATUS")
    row = cur.fetchone()
    # 取出 IO/SQL 线程状态与延迟，异常则发短信/企业微信告警

加分点：监控要同时覆盖"线程是否在跑"和"延迟是否在涨"，两个维度缺一不可；告警收敛后还要有人值班，不能只报警不处理。

坑 12：并行复制配置不当延迟不降反升

场景 / 错误示范：

听说 8.0 并行复制快，直接在从库把 slave_parallel_workers 设成 64，结果延迟不降反升。

-- 错误：worker 开太多，锁争抢加剧
[mysqld]
slave_parallel_workers = 64
slave_parallel_type = LOGICAL_CLOCK

为什么是坑：并行复制不是 worker 越多越快。worker 之间要维护事务提交顺序（prepare 队列 / commit 队列），开太多反而增加队列锁争抢、上下文切换；且如果主库事务全是更新同一行热点，再并行也串行排队。

正确做法：

-- 正确：按 CPU 核数与业务模型调，通常 4~8 个 worker 足够
[mysqld]
slave_parallel_workers = 8
slave_parallel_type = LOGICAL_CLOCK
binlog_transaction_dependency_tracking = WRITESET   -- 8.0 基于写集合依赖
transaction_write_set_extraction = XXHASH64

-- 观察并行复制实际效果
SHOW STATUS LIKE 'Slave_worker_threads';
SHOW STATUS LIKE 'Slave_retried_transactions';

加分点：WRITESET 模式下，不修改相同行的事务可真正并行重放，是 8.0 降低延迟的关键开关；调参后必须压测验证延迟变化，别凭感觉拍数字。

本篇速查表

| 坑号 | 现象关键词 | 根因一句话 | 一句话解法 |

|---|---|---|---|

| 1 | 主从延迟大 | 单线程重放跟不上 | 看线程状态 + 拆事务 + 并行复制 |

| 2 | 主从数据对不上 | STATEMENT 非确定函数 | binlog_format = ROW |

| 3 | 半同步形同虚设 | 超时太短退异步 | timeout 1s 以上 + 退异步告警 |

| 4 | 切换后少数据 | relay log 未追平 | 等 Read_Pos == Exec_Pos 再提升 |

| 5 | 复制起不来 | server_id 重复 | 每实例全局唯一 server_id |

| 6 | 从库被写分叉 | 只开 read_only | 开 super_read_only |

| 7 | 跳事务报错 | GTID 下禁 sql_slave_skip_counter | 空事务占位跳 GTID |

| 8 | 从库卡几十分钟 | 单条大事务 | 按主键分批更新 |

| 9 | 某库没同步过来 | replicate_do_db 按默认库过滤 | 改用 replicate_wild_do_table |

| 10 | 两边同时可写 | 脑裂无 fencing | 切换强制旧主停写/关机 |

| 11 | 数据静默不更新 | SQL 线程停无人知 | 监控 IO/SQL 线程 + 延迟 |

| 12 | 开并行反而更慢 | worker 过多锁争抢 | 4~8 个 + WRITESET 依赖 |

运维注意事项

1. 上线新从库前，把"改 server_id、开 binlog、开 gtid、配 super_read_only"写成 checklist，逐项打勾。

2. 半同步、并行复制、GTID 都是"看起来很美"的功能，每一个都要在预发环境验证行为后再上生产。

3. 任何手工 failover 都要写 runbook（操作手册），步骤细化到每条 SQL，禁止现场凭记忆敲命令。

4. 复制过滤、跳事务、手工改从库数据，都是高风险操作，执行前必须备份并双人复核。

5. 高可用不是配完就完事，每季度至少做一次"真实主库宕机"演练，验证切换脚本与告警。

动手练习

练习 1：从库 SHOW REPLICA STATUS 显示 Seconds_Behind_Source = 0，但业务反馈"刚下单查不到订单"。请列出至少 3 个排查方向。

<details>

<summary>参考答案</summary>

1. 用 pt-heartbeat 等毫秒级工具确认是否真延迟（Seconds_Behind_Source 可能不准）；2. 检查业务是否连到了别的从库 / ProxySQL 路由错误；3. 检查主库该事务是否还没提交（隔离级别 / 未提交读问题）；4. 检查从库是否被误写、数据分叉。

</details>

练习 2：为什么 read_only = ON 不足以保护从库？应该用什么参数替代？

<details>

<summary>参考答案</summary>

因为 read_only 只阻止普通用户写，拥有 SUPER 权限的 root 仍可写。应改用 super_read_only = ON，它同时阻止普通用户和 super 用户写。切换主库时再显式关闭。

</details>

练习 3：设计一个简单的复制监控采集 SQL，要求能发现"SQL 线程停止"和"延迟超过 30 秒"两类故障。

<details>

<summary>参考答案</summary>

执行 SHOW REPLICA STATUS\G，在应用层取 Replica_SQL_Running、Replica_IO_Running、Seconds_Behind_Source 三个字段；任一 Running != Yes 或 Seconds_Behind_Source > 30 持续 60 秒即触发告警。

</details>

下一篇预告：第 39 章 MySQL踩坑：备份恢复与迁移篇