MySQL 主从复制与高可用

概述

主从复制是 MySQL 高可用的基石，也是读写分离、数据备份、在线迁移的基础设施。MySQL 8.0 主力方案：异步/半同步复制 + GTID + MGR（组复制）。

复制原理

Master                                    Slave
┌──────────┐                    ┌──────────────────┐
│ Binlog   │                    │ Relay Log → 回放  │
│ Dump     │ ── I/O 线程拉取 ──→│ I/O Thread        │
│ Thread   │                    │ SQL Thread        │
└──────────┘                    └──────────────────┘

三步走

步骤	组件	工作
1. Binlog 写入	Master	事务提交 → 写入 Binlog
2. I/O 线程拉取	Slave I/O Thread	连接 Master → 读 Binlog → 写 Relay Log
3. SQL 线程回放	Slave SQL Thread	读 Relay Log → 解析执行

传统复制：单 SQL 线程回放，高并发场景是瓶颈。MySQL 5.7+ 支持并行复制（Logical Clock）。

三种复制模式

模式	同步方式	数据一致性	性能影响
异步复制	Master 写 Binlog 即返回，不管 Slave	可能丢数据	✅ 无影响
半同步复制	Master 等待至少 1 个 Slave 确认收到 Binlog	不丢（但 Slave 可能还没回放）	⚠️ 有延迟
MGR 组复制	Paxos 共识，多数派确认	✅ 强一致	⚠️ 延迟较大

半同步配置

-- Master 端
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = ON;
SET GLOBAL rpl_semi_sync_master_timeout = 1000;  -- 超时降级为异步（ms）

-- Slave 端
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = ON;

半同步的取舍

# after_sync（推荐）：Master 等 Slave 收到 Binlog
# - Slave 崩溃 → Master 不丢数据
# - Master 崩溃 → Slave 有完整 Binlog

# after_commit（旧）：Master 等 Slave 收到 Binlog + 提交
# - 存在 Master/Slave 均提交但返回超时的不一致风险
rpl_semi_sync_master_wait_point = AFTER_SYNC

GTID 全局事务标识

-- GTID 格式：server_uuid:transaction_id
-- 示例：3E11FA47-71CA-11E1-9E33-C80AA9429562:1-100

GTID 的优势

对比	传统复制（位点）	GTID
主从切换	需手动查找 MASTER_LOG_FILE + MASTER_LOG_POS	自动定位，MASTER_AUTO_POSITION=1
重连	复杂	简单，Slave 知道哪些事务已执行
skip 错误	复杂	SET GTID_NEXT 精确跳过
拓扑变更	困难	轻松，GTID 全局唯一

GTID 主从搭建（快速版）

-- 1. Master 创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- 2. Slave 配置 GTID
SET GLOBAL gtid_mode = ON;
SET GLOBAL enforce_gtid_consistency = ON;

-- 3. 建立复制
CHANGE MASTER TO
    MASTER_HOST = 'master_host',
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'repl_password',
    MASTER_AUTO_POSITION = 1;   -- GTID 自动化

START SLAVE;

-- 4. 检查状态
SHOW SLAVE STATUS\G
-- 重点关注：Slave_IO_Running(Yes) & Slave_SQL_Running(Yes)
-- Seconds_Behind_Master: 延迟秒数，建议 < 5

主从延迟排查

常见原因

原因	特征	排查/解决
Master 并发写高，Slave SQL 单线程	Seconds_Behind_Master 持续增长	开启并行复制 + slave_parallel_workers=4~8
大事务（一次更新百万行）	延迟突然飙升	拆分成小事务
Slave 磁盘 IO 慢	Relay Log 堆积	SSD + 优化 sync_relay_log
网络抖动	Slave_IO_Running=No	检查网络 + slave_net_timeout
无主键表	全表扫描回放	全表加主键

-- 开启并行复制（MySQL 8.0）
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 8;
SET GLOBAL slave_preserve_commit_order = ON;  -- 保持提交顺序（GTID 一致性）

延迟监控查询

-- 精确延迟（比 Seconds_Behind_Master 更准确）
SELECT
    MASTER_POS_WAIT('master-bin.000001', 12345, 5) AS waited_sec;
-- 返回 NULL 表示超时（>5s），返回秒数表示等待时间

-- 对比 GTID 执行差异
-- Master：
SELECT @@GLOBAL.gtid_executed;
-- Slave：
SELECT @@GLOBAL.gtid_executed;
-- 求差集：Master 有而 Slave 没有的 GTID

MGR（MySQL Group Replication）

架构

     ┌──────────┐
     │  Primary │ (读写)
     └────┬─────┘
    ┌─────┴─────┐
    │   Paxos    │
    └─────┬─────┘
 ┌──────┐┌──────┐
 │Secondary│Secondary│ (只读)
 └──────┘└──────┘

三种模式

模式	写节点	一致性	适用
单主模式	仅 Primary	最终/强一致	多数场景
多主模式	所有节点	乐观锁冲突检测	低冲突场景（不推荐）
InnoDB Cluster	MGR + Router + Shell	自动故障转移	生产推荐

MGR vs 传统主从

对比	传统主从	MGR
故障转移	手动/脚本	自动（秒级）
数据一致性	异步/半同步	多数派强一致
读写分离	手动配置	Router 自动路由
扩展性	有限	水平扩展
性能开销	低	Paxos 共识有开销

-- MGR 核心参数
SET GLOBAL group_replication_group_name = 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee';
SET GLOBAL group_replication_local_address = '192.168.1.1:33061';
SET GLOBAL group_replication_group_seeds = '192.168.1.1:33061,192.168.1.2:33061,192.168.1.3:33061';
SET GLOBAL group_replication_bootstrap_group = ON;  -- 仅第一个节点
START GROUP_REPLICATION;

高可用方案对比

方案	故障转移	数据一致性	复杂度	K8s 友好
主从 + 手动切换	分钟级	可能丢	低	❌
MHA	秒级	半同步不丢	中	❌
Orchestrator	自动	取决于复制模式	中	❌
MGR + Router	自动秒级	强一致	中	❌
MySQL Operator	自动	半同步	低	✅
TiDB / Vitess	原生	多 Raft	高	✅

并行复制详解

为什么需要并行复制

传统单线程复制：
Master 并发写入 100 TPS → Slave SQL Thread 串行回放 → Seconds_Behind_Master 持续增长

MySQL 5.6 库级别并行 → 5.7 LOGICAL_CLOCK → 8.0 WRITESET。每次迭代都在提升并行度。

MySQL 8.0 三种并行模式

SET GLOBAL slave_parallel_type = 'DATABASE';          -- 5.6，库级并行（废弃）
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';     -- 5.7+，组提交并行
SET GLOBAL slave_parallel_type = 'WRITESET';          -- 8.0+，推荐

模式	并行粒度	原理	适用场景	性能
DATABASE	库级别	不同库的事务可并行回放	多库场景	低
LOGICAL_CLOCK	Commit Parent	同组提交的事务可并行	通用	中
COMMIT_ORDER	同上，更保守	仅依赖提交顺序	保守/低风险	中
WRITESET	行级别	修改不同行的事务可并行	推荐	最高

WRITESET 详解（推荐首选）

SET GLOBAL slave_parallel_type = 'WRITESET';
SET GLOBAL slave_parallel_workers = 8;
SET GLOBAL slave_preserve_commit_order = ON;

-- WRITESET 依赖 hash 计算
SET GLOBAL binlog_transaction_dependency_tracking = 'WRITESET';
-- 计算方式：提取事务修改的每行的主键/唯一键 hash → 相同 hash = 冲突序列化，不同 hash = 可并行

Master 三个并发事务：
T1: UPDATE t SET val=1 WHERE id=1    → hash(id=1)
T2: UPDATE t SET val=2 WHERE id=2    → hash(id=2)   } 互不冲突 → Slave 可并行
T3: UPDATE t SET val=3 WHERE id=3    → hash(id=3)

T4: UPDATE t SET val=4 WHERE id=1    → hash(id=1)   } 与 T1 冲突 → 串行

WRITESET 生效前提：

• 表必须有主键或唯一键
• binlog_format = ROW
• transaction_write_set_extraction = XXHASH64

-- 验证 WRITESET 并行效果
SELECT * FROM performance_schema.replication_applier_status_by_worker;
-- 查看各 Worker 线程的工作量分布（应该均匀）

并行复制监控

-- 查看 Worker 状态
SELECT
    worker_id,
    thread_id,
    service_state,
    last_error_number,
    last_error_message,
    last_applied_transaction
FROM performance_schema.replication_applier_status_by_worker;

-- 查看事务重试（并行冲突）
SHOW STATUS LIKE 'slave_retried_transactions';
-- 增大 slave_transaction_retries（默认 10）减少重试失败

-- WRITESET 依赖追踪状态
SELECT * FROM performance_schema.replication_applier_configuration;

延迟精细监控

使用 pt-heartbeat（推荐生产使用）

# 1. Master 端定期更新心跳表
pt-heartbeat --user=root --password=xxx \
  --database=percona --table=heartbeat \
  --update --interval=1 --create-table

# 2. Slave 端监控延迟
pt-heartbeat --user=root --password=xxx \
  --database=percona --table=heartbeat \
  --monitor --interval=1
# 输出：0.00s [  0.00s,  0.00s,  0.00s ]
#        ^当前   ^1min   ^5min   ^15min

# 3. 集成到 Prometheus 导出
pt-heartbeat --database=percona --table=heartbeat \
  --monitor --file=/tmp/heartbeat.log &
# 配合 node_exporter textfile collector

延迟原因诊断脚本

-- 综合延迟诊断查询
SELECT
    '当前延迟(秒)' AS metric,
    TIMESTAMPDIFF(SECOND, ts, NOW()) AS value
FROM percona.heartbeat ORDER BY ts DESC LIMIT 1

UNION ALL

SELECT
    'Slave_IO_Running',
    IF(VARIABLE_VALUE = 'Yes', 1, 0)
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Slave_IO_Running'

UNION ALL

SELECT
    'Slave_SQL_Running',
    IF(VARIABLE_VALUE = 'Yes', 1, 0)
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Slave_SQL_Running'

UNION ALL

SELECT
    'Relay_Log_Space(MB)',
    ROUND(VARIABLE_VALUE / 1024 / 1024, 2)
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Relay_Log_Space';

Orchestrator —— 主从拓扑管理与自动故障切换

Orchestrator 是 GitHub 开源的 MySQL 高可用管理工具，通过可插拔的故障检测和恢复钩子，实现自动故障切换。

核心能力

功能：
  ├── 拓扑发现与可视化（Web UI 展示主从关系）
  ├── 自动故障检测（基于 pt-heartbeat 监控延迟）
  ├── 自动/手动故障转移（Leader 选举）
  ├── 故障转移钩子（集成 ProxySQL/VIP/Hooks）
  └── 拓扑重构（主从切换、级联复制调整）

Orchestrator 的故障转移决策

1. 检测 Master 不可达（多节点投票，避免孤立判断）
2. 检查是否有中间级主故障 → 先恢复中间级
3. 选举最优 Slave：
   - GTID 最新（或 Binlog 位点最靠前）
   - 数据一致性验证通过（orchestrator 高优先级验证）
   - 复制过滤规则最少
4. 执行故障转移：
   - 旧 Master 设置为只读（如果可以连上）
   - 新 Master 提升：STOP SLAVE; RESET SLAVE ALL
   - 其他 Slave 切换到新 Master
5. 调用用户定义的 Hook 脚本（更新 VIP/ProxySQL/DNS）

ProxySQL 集成（读写分离）

-- ProxySQL 自动感知拓扑变化
-- 原理：Orchestrator 故障转移后 → 调用 Hook 脚本 → 更新 ProxySQL 的 mysql_servers 表

-- ProxySQL 读/写组配置
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES
    (10, 'master', 3306),     -- Writer 组
    (20, 'slave1', 3306),     -- Reader 组
    (20, 'slave2', 3306);

-- 查询路由规则（自动读写分离）
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup)
VALUES (1, 1, '^SELECT.*FOR UPDATE', 10);   -- SELECT FOR UPDATE → Writer
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup)
VALUES (2, 1, '^SELECT', 20);               -- SELECT → Reader
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup)
VALUES (3, 1, '.*', 10);                    -- 其他 → Writer

读写分离中间件对比

工具	模式	优点	缺点
ProxySQL	智能代理，SQL 解析路由	零侵入、高性能、查询缓存	多一层代理延迟
MySQL Router	InnoDB Cluster 官方组件	轻量、自动感知拓扑	仅支持 InnoDB Cluster
ShardingSphere-Proxy	分库分表 + 读写分离	生态丰富、支持多 DB	重量级、延迟较高
应用层实现	代码选择数据源	灵活、可控	代码侵入、维护成本高

InnoDB Cluster 快速搭建

# MySQL Shell 搭建 InnoDB Cluster（推荐方式）
# 假设有 3 个节点：mysql-1, mysql-2, mysql-3

# 1. 在每个节点上配置参数
cat >> /etc/my.cnf << 'EOF'
[mysqld]
server_id = 1                    # 每个节点不同
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_checksum = NONE
disabled_storage_engines = "MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
EOF

# 2. 使用 MySQL Shell 创建集群
mysqlsh --uri root@mysql-1:3306

# Shell 内执行：
dba.createCluster('myCluster')
cluster.status()

# 3. 添加节点
cluster.addInstance('root@mysql-2:3306')
cluster.addInstance('root@mysql-3:3306')

# 4. 部署 MySQL Router（读写分离 + 自动路由）
mysqlrouter --bootstrap root@mysql-1:3306 --user=mysqlrouter
systemctl start mysqlrouter

# Router 会自动将读请求路由到 Secondary，写请求路由到 Primary

关联知识

• MySQL 体系架构与存储引擎 — Binlog 是复制的核心
• MySQL 事务与锁机制 — 事务提交顺序影响复制一致性
• MySQL 备份恢复与慢查询优化 — 从 Slave 备份避免影响 Master

参考资源

• MySQL 复制官方文档：https://dev.mysql.com/doc/refman/8.0/en/replication.html
• MGR 指南：https://dev.mysql.com/doc/refman/8.0/en/group-replication.html

学习时间

阶段	时间	备注
初次学习	2026-07-14	复制原理 + GTID + MGR + 高可用对比

---

状态: 📖 已掌握 下次复习日期: 2026-08-14