MySQL 主从复制与读写分离实战：从异步复制到高可用切换的完整方案 原创

2026-09-24 服务器 浏览 (8)
温馨提示：
本文最后更新于 2026-09-24，已超过 1 天没有更新。 若文章内的图片失效（无法正常加载），请留言反馈或直接 联系我。

引言

单机 MySQL 在数据量增长或可用性要求提高后会遇到两个瓶颈：磁盘容量上限和单点故障风险。主从复制（Replication）是解决这两个问题的基础方案，配合读写分离还能显著提升整体吞吐。本文完整走通异步复制搭建、复制拓扑、读写分离、监控排障到生产切换的全流程。

一、复制原理：理解 binlog 三种格式

MySQL 主从复制的核心是 binlog。主库将数据变更写入二进制日志，从库拉取并重放：

  1. 主库执行事务并写入 binlog
  2. 从库 IO 线程连接主库，请求 binlog，写入本地 relay log（中继日志）
  3. 从库 SQL 线程读取 relay log，按顺序重放事件

binlog 有三种格式，选型直接影响复制一致性和性能：

格式 记录内容 优点 缺点
STATEMENT 原始 SQL 语句 日志小 NOW()、UUID()、LIMIT 等非确定性语句会导致主从不一致
ROW 每行变更前后镜像 一致性最强 大批量更新日志膨胀严重
MIXED 按语句自动选择 折中 仍有边缘不一致风险

生产建议：统一使用 ROW 格式（binlog_format=ROW）。现代磁盘和网络下日志体积已不是主要瓶颈，数据一致性价值远高于存储成本。

二、异步复制搭建实战

1. 主库配置（my.cnf）

[mysqld]
server-id=1
log-bin=mysql-bin
binlog_format=ROW
binlog_row_image=FULL
expire_logs_days=7
# 保证崩溃安全
innodb_flush_log_at_trx_commit=1
sync_binlog=1
# GTID（强烈建议开启，简化故障切换）
gtid_mode=ON
enforce_gtid_consistency=ON
log_replica_updates=ON

2. 创建复制账号

CREATE USER 'repl'@'10.0.%' IDENTIFIED BY 'ReplStrongPwd2026';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.%';
FLUSH PRIVILEGES;

3. 从库配置并建立复制

[mysqld]
server-id=2
relay-log=relay-bin
read_only=ON
gtid_mode=ON
enforce_gtid_consistency=ON
log_replica_updates=ON

使用 GTID 后建立复制极其简洁，不再需要传统的 binlog 文件位点：

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='10.0.0.10',
  SOURCE_PORT=3306,
  SOURCE_USER='repl',
  SOURCE_PASSWORD='ReplStrongPwd2026',
  SOURCE_AUTO_POSITION=1;

START REPLICA;

4. 验证复制状态

SHOW REPLICA STATUS\G

关键看两项都为 Yes：

Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Seconds_Behind_Source: 0
Retrieved_Gtid_Set / Executed_Gtid_Set  # 与主库对齐

三、半同步复制：数据安全的折中

异步复制存在数据丢失窗口：主库提交后立即返回客户端，若此时主库崩溃且 binlog 尚未传到从库，这部分事务永久丢失。半同步复制（semi-sync）要求至少一个从库确认收到 binlog 后主库才返回：

# 主库
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET GLOBAL rpl_semi_sync_source_enabled = 1;
SET GLOBAL rpl_semi_sync_source_timeout = 3000;  # 毫秒

# 从库
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
SET GLOBAL rpl_semi_sync_replica_enabled = 1;

半同步在从库超时未确认时会自动退化为异步，保证可用性。配合 rpl_semi_sync_source_wait_for_replica_count 可控制需要等待几个从库，在数据安全和延迟之间权衡。

四、读写分离架构

主从拓扑就绪后，读流量打到从库、写流量留在主库。实现方式分三类：

1. 中间件代理（推荐生产）

ProxySQL 是最主流的方案，它理解 MySQL 协议，能基于查询内容路由：

# 配置主从组
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES
(0,'10.0.0.10',3306),  # HG0 写组
(1,'10.0.0.20',3306),  # HG1 读组
(1,'10.0.0.21',3306);

# 路由规则：除 SELECT 外全部走写组
INSERT INTO mysql_query_rules(rule_id,match_digest,destination_hostgroup,apply)
VALUES
(1,'^SELECT.*FOR UPDATE$',0,1),
(2,'^SELECT',1,1);

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

ProxySQL 还能自动探测从库复制延迟，延迟超过阈值的从库会被临时踢出读组。

2. 应用层路由

框架层面区分读写连接，如 ShardingSphere-JDBC、Spring 的 AbstractRoutingDataSource。优点是少一跳网络，缺点是多语言栈难统一。

3. 连接级路由

简单场景用两个数据源配置：写库一个连接池、读库一个连接池，业务代码显式选择。适合规模小、查询模式清晰的系统。

五、复制延迟的成因与治理

Seconds_Behind_Source 是读写分离架构的核心风险指标。从库读到旧数据会造成业务异常（如刚下单查不到订单）。常见成因：

  • 单线程重放瓶颈：MySQL 5.7 前 SQL 线程是单线程，主库高并发写入时从库追不上
  • 大事务：一个 UPDATE 改百万行，从库重放耗时长
  • 从库硬件差：从库常被当作”备份机”低配部署，IO 能力不足
  • 长链路锁等待：从库上的读查询与重放线程争锁

治理手段：开启多线程复制（基于 WRITESET 并行）：

STOP REPLICA;
SET GLOBAL replica_parallel_workers=8;
SET GLOBAL replica_parallel_type='LOGICAL_CLOCK';
SET GLOBAL binlog_transaction_dependency_tracking='WRITESET';
START REPLICA;

WRITESET 模式通过分析事务修改的行集合判断冲突，能让本来串行的事务在从库并行重放，大幅降低延迟。

六、主从切换与高可用

主库故障时需要把一个从库提升为主。手工切换流程：

# 1. 确认从库已追平（GTID 无差异）
SHOW REPLICA STATUS\G

# 2. 从库停止复制并解除只读
STOP REPLICA;
RESET REPLICA ALL;
SET GLOBAL read_only=OFF;
SET GLOBAL super_read_only=OFF;

# 3. 其他从库指向新主
CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.0.0.20', SOURCE_AUTO_POSITION=1;
START REPLICA;

生产环境应使用自动化工具，避免人工在凌晨故障时手抖：

  • Orchestrator：GitHub 开源，自动检测拓扑、支持恢复钩子，是最主流的主从管理工具
  • MHA：经典方案，能从多个从库中补齐差异 binlog，但项目维护趋缓
  • MySQL InnoDB Cluster：官方方案，Group Replication + MySQL Router + MySQL Shell，开箱即用

七、常见故障排查

现象 可能原因 处理
IO 线程 Connecting 网络/防火墙、复制账号权限、密码错误 检查 3306 连通性、SHOW GRANTS
SQL 线程 Errno 1062 主键冲突（从库被误写） pt-table-checksum 查差异，pt-table-sync 修复
SQL 线程 Errno 1032 行不存在（ROW 格式找不到要更新的行） 数据已漂移，需重新校验同步
GTID 冲突 Errno 1062 从库执行过本地写事务 gtid_executed 注入空事务跳过，根治靠 super_read_only

关键纪律：从库必须设置 super_read_only=ON。普通 read_only 对 SUPER 权限用户无效，仍可能被误写导致数据漂移。

八、数据一致性校验

复制正常运行不等于数据一定一致。应定期用 Percona Toolkit 校验：

# 主库建校验
pt-table-checksum h=10.0.0.10,u=checksum,p=*** --replicate=percona.checksums

# 从库查看差异
pt-table-checksum h=10.0.0.20,u=checksum,p=*** \
  --replicate=percona.checksums --replicate-check-only

# 修复差异（先备份！）
pt-table-sync --execute h=10.0.0.10 h=10.0.0.20 \
  u=checksum,p=*** --replicate=percona.checksums

总结

MySQL 复制体系的演进路径很清晰：异步复制解决容量和备份，半同步堵住数据丢失窗口，GTID 简化拓扑管理，读写分离扩展读吞吐，Orchestrator/InnoDB Cluster 实现自动故障转移。落地顺序建议：先建 ROW+GTID 异步复制 → 加 ProxySQL 读写分离 → 上半同步 → 最后接入自动切换。每一步都验证稳定后再走下一步，比一上来就堆全套复杂方案可靠得多。

相关文章