MySQL 主备复制与高可用

梳理 MySQL 主备复制的工作原理、备库延迟的根因与解决方案、主库故障切换的流程，以及读写分离的正确姿势。

目录

主备复制原理

同步流程

sequenceDiagram
    participant M as 主库 (Master)
    participant B as 备库 (Slave)

    B->>M: 建立长连接（I/O Thread）
    M-->>B: 发送 binlog（binlog dump）
    Note over B: I/O Thread 写入 relay log
    B->>B: SQL Thread 读取 relay log，重放执行

三种 binlog 格式对复制的影响

生产环境推荐 row 格式，主从数据一致性最好，且支持精确恢复。

双主结构与循环复制

互为主备（A ↔ B）：两个节点互相是对方的备库，同时只有一个节点对外提供写服务。

防止循环复制

binlog 中的每个事务都带有 server_id。备库重放 binlog 时，会检查 server_id：

A 写入事件（server_id=1）→ 同步到 B → B 重放
B 同步回 A 时：A 发现 server_id=1 是自己 → 丢弃，不再重放

配置要求：主备节点的 server_id 必须不同。

备库延迟的原因

主要原因

并行复制（MySQL 5.6+）

-- 5.6：基于 database 的并行（不同 DB 的事务并行）
-- 5.7：基于组提交的并行（同一组提交的事务并行，效果更好）
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 4;  -- 并行线程数

大事务导致延迟的根治方案

拆分大事务：不要一次性删除/更新大量行，改为分批处理。

主库故障切换

基于 binlog 位点的切换（传统方式）

-- 将从库 B 切换为新主库 A' 的从库
CHANGE MASTER TO
  MASTER_HOST='A_prime_host',
  MASTER_PORT=3306,
  MASTER_USER='repl',
  MASTER_PASSWORD='xxx',
  MASTER_LOG_FILE='binlog.000003',  -- 需要人工确定同步位点
  MASTER_LOG_POS=123;

问题：位点难以精确定位，可能导致同步数据重复（幂等操作）或遗漏，需要依赖 sql_slave_skip_counter 跳过错误。

基于 GTID 的切换（推荐，MySQL 5.6+）

GTID = Global Transaction ID，格式：server_uuid:transaction_id

-- 开启 GTID
SET GLOBAL gtid_mode = ON;
SET GLOBAL enforce_gtid_consistency = ON;

-- 切换时不需要指定位点，MySQL 自动计算
CHANGE MASTER TO
  MASTER_HOST='A_prime_host',
  MASTER_PORT=3306,
  MASTER_USER='repl',
  MASTER_PASSWORD='xxx',
  MASTER_AUTO_POSITION=1;  -- 自动基于 GTID 同步

GTID 切换优势：

• 自动找位点，无需人工计算
• 已执行过的事务（GTID 已记录）自动跳过，不会重复执行

读写分离的坑

过期读问题

读写分离后，写请求走主库，读请求走从库。从库有延迟时，读到的可能是旧数据（过期读）。

几种解决方案

-- 方案：等 GTID 同步后再读从库
-- 主库写完后获取 GTID
-- SELECT @@GLOBAL.gtid_executed;  → 'uuid:1-100'

-- 从库等待该 GTID 集合同步完成（超时返回 1，未超时返回 0）
SELECT WAIT_FOR_EXECUTED_GTID_SET('uuid:1-100', 1);

如何判断主库是否正常

select 1 的缺陷

-- 这个命令不访问任何表，只检查连接是否存活
-- 无法检测到：并发线程过多导致的 innodb_thread_concurrency 打满
select 1;

更可靠的健康检查

-- 方案1：查一个业务表（检测真实读写能力）
SELECT * FROM health_check;

-- 但如果 binlog 空间满了导致所有更新卡住，读操作仍然正常 → 假健康

-- 方案2：定期更新 health_check 表（同时检测写入能力）
UPDATE health_check SET t_modified = NOW() WHERE id = @@server_id;

-- 方案3：使用 performance_schema 检测（更全面）
SELECT * FROM performance_schema.replication_connection_status;

innodb_thread_concurrency 的陷阱

-- 当并发线程数超过 innodb_thread_concurrency（默认 0=不限制）时
-- 新的查询会等待，select 1 仍然能成功
-- 但 "SELECT * FROM t" 会超时或阻塞
-- 建议设置合理值（如 64），超过时报 ERROR 1205 而不是无响应
SET GLOBAL innodb_thread_concurrency = 64;

参考资料

• 《MySQL 实战 45 讲》— 第 23 讲：MySQL 是怎么保证数据不丢的？
• 《MySQL 实战 45 讲》— 第 24 讲：MySQL 是怎么保证主备一致的？
• 《MySQL 实战 45 讲》— 第 25 讲：MySQL 是怎么保证高可用的？
• 《MySQL 实战 45 讲》— 第 26 讲：备库为什么会延迟好几个小时？
• 《MySQL 实战 45 讲》— 第 27 讲：主库出问题了，从库怎么办？
• 《MySQL 实战 45 讲》— 第 28 讲：读写分离有哪些坑？
• MySQL - Replication