MySQL 主从复制指南

MySQL 主从复制是高可用数据库架构的基础。核心流程：主库 Binlog → IO 线程拉取 → Relay Log → SQL 线程执行。 架构：一台主库（Master）是唯一写入入口，所有 INSERT/UPDATE/DELETE 都在主库执行；多台从库（Slave）被动同步主库数据、主要承担读请求。

为什么生产必须用主从复制（4 大核心价值）

单库 MySQL 撑不住高并发、大数据量场景，主从复制是 4 大生产刚需的基石：

1. 读写分离，提升并发：互联网业务读多写少，主库扛写、从库扛读，成倍提升数据库 QPS
2. 数据热备份，防丢失：从库实时同步主库数据，主库宕机可快速切换，避免数据丢失
3. 故障高可用，快速切换：主库故障时将从库提升为主库，保障业务不中断
4. 分离业务压力：数据分析、报表统计、慢查询等耗时操作全部放从库，不影响主库性能

复制原理深拆：3 组件 × 3 线程 × 6 步流程

主从复制的本质：主库记录数据变更日志，从库拉取日志、本地重放执行，全程异步。

三大核心组件

三大核心线程

• 主库 Dump 线程：从库连上后主库开启，实时监听 Binlog 变化，有新日志就推送给从库 IO 线程
• 从库 IO 线程：常驻后台，主动连接主库、拉取 Binlog、写入本地 Relay Log
• 从库 SQL 线程：常驻后台，读取 Relay Log、在从库本地逐条重放执行，最终实现主从数据一致

完整同步流程（6 步）

1. 主库开启 Binlog，所有增删改执行完成后有序写入 Binlog
2. 从库 IO 线程携带同步位点/GTID 发起连接，请求同步日志
3. 主库 Dump 线程响应，读取 Binlog 新增日志发送给从库 IO 线程
4. 从库 IO 线程把收到的内容持久化写入本地 Relay Log
5. 从库 SQL 线程读取 Relay Log，逐条重放执行
6. 主从数据一致，循环往复实时同步

复制类型对比

Binlog 格式选择

GTID 复制

GTID = source_id:transaction_id，自动化复制位点管理。

[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON

-- 关键命令
SHOW MASTER STATUS;
SHOW SLAVE STATUS\G
SELECT @@GLOBAL.gtid_executed;

-- 手动跳过失败事务（GTID 模式）
STOP SLAVE;
SET GTID_NEXT = 'source_id:transaction_id';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START SLAVE;

常见架构与搭建

搭建前提条件（4 项）

• 主从库 MySQL 版本一致（或从库版本 ≥ 主库）
• 主从库网络互通，3306 端口开放
• 主从库服务器 server-id 唯一（相同会直接导致复制冲突、同步中断）
• 关闭主从库防火墙、SELinux

主库配置（my.cnf）

[mysqld]
server-id = 1                        # 主库必须为 1，从库依次为 2、3...
log-bin = mysql-bin                  # 开启 binlog
gtid_mode = ON                       # 开启 GTID 模式（核心）
enforce_gtid_consistency = ON
binlog-ignore-db = mysql             # 忽略不需要同步的库，减少日志体积
binlog-ignore-db = information_schema
expire_logs_days = 7                 # 日志过期自动清理（8.0 已废弃，改用 binlog_expire_logs_seconds，7 天 = 604800）
binlog_format = ROW                  # 生产推荐行模式

systemctl restart mysqld
mysql -e "SHOW VARIABLES LIKE '%log_bin%';"   # 验证 binlog 已开启

主库创建同步账号（禁止用 root 同步）

CREATE USER 'repl'@'%' IDENTIFIED BY 'Repl@123456';  -- 专用同步账号，权限过大存在安全风险
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;                                   -- 记录 GTID 信息

从库配置（my.cnf）

[mysqld]
server-id = 2                        # 必须和主库不同
relay-log = relay-bin                # 开启中继日志
gtid_mode = ON
enforce_gtid_consistency = ON
read_only = ON                       # 只读模式（超级用户除外）
super_read_only = ON                 # 连超级用户也只读，防误写

重启从库服务 systemctl restart mysqld。

数据初始化 + 关联主库（一主一从标准流程）

# 主库备份（带 binlog 位点信息）
mysqldump --single-transaction --source-data=2 --all-databases > full_backup.sql

# 从库恢复
mysql -u root -p < full_backup.sql

-- 从库配置 GTID 复制
STOP SLAVE;
RESET SLAVE ALL;                     -- 重置原有同步状态
CHANGE MASTER TO
  MASTER_HOST = 'master.example.com',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'repl_password',
  MASTER_AUTO_POSITION = 1;          -- GTID 核心：自动匹配位点
START SLAVE;

-- 验证：SHOW SLAVE STATUS\G
-- Slave_IO_Running: Yes    ← 两个都 Yes 才算搭建成功
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0

MySQL 8.0.22+ 新语法：CHANGE MASTER TO → SET REPLICATION SOURCE TO、START SLAVE → START REPLICA、SHOW SLAVE STATUS → SHOW REPLICA STATUS。

搭建成功校验：除双 Yes 外，还要实测——主库建库插入数据，确认从库自动同步。

架构选择

半同步复制配置

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 10000;  -- 10秒超时降级异步

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;

故障排查

IO 线程 Connecting

信号： Slave_IO_Running: Connecting + Last_IO_Error: error connecting to master

排查： ping / telnet / 权限检查 / max_connections

SQL 线程错误

信号： Last_SQL_Error: Could not execute ... Can't find record

修复：

-- 方案 A：跳过（临时）
SET GTID_NEXT = 'source_id:transaction_id';
BEGIN; COMMIT;

-- 方案 B：修复数据一致
pt-table-sync h=master h=slave --databases=test --execute

-- 方案 C：重新初始化从库

复制延迟过大

正常同步延迟应在 1 秒以内。延迟的 6 大根因（单线程重放瓶颈 / 大事务 / 硬件差距 / 网络 / 索引缺失 / 参数不合理）、真实案例与万能四步排查流程，见专页 mysql-replication-lag-troubleshooting。排查入口：SHOW PROCESSLIST / Innodb_log_waits / 网络延迟。

快速止血——开启并行复制：

STOP SLAVE;
SET GLOBAL slave_parallel_workers = 16;     -- CPU 核数 50%~80%
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_preserve_commit_order = ON;
START SLAVE;

GTID 被清除（fatal error 1236）

根因： 主库 purged 了从库需要的 Binlog

修复： 从主库用 mysqldump --source-data=2 重新备份恢复。

监控与检查脚本

#!/bin/bash
# check_mysql_replication.sh
STATUS=$(mysql -e "SHOW SLAVE STATUS\G" 2>/dev/null)
IO=$(echo "$STATUS" | grep "Slave_IO_Running:" | awk '{print $2}')
SQL=$(echo "$STATUS" | grep "Slave_SQL_Running:" | awk '{print $2}')
LAG=$(echo "$STATUS" | grep "Seconds_Behind_Master:" | awk '{print $2}')

echo "IO: $IO | SQL: $SQL | 延迟: ${LAG}s"
[ "$IO" != "Yes" ] && echo "[严重] IO 线程异常"
[ "$SQL" != "Yes" ] && echo "[严重] SQL 线程异常"
[ "$LAG" -gt 300 ] 2>/dev/null && echo "[严重] 延迟 > 5分钟"

数据一致性校验

# 自动校验（pt-table-checksum）
pt-table-checksum --replicate=test.checksums --databases=test
mysql -e "SELECT db, tbl, DIFFS FROM test.checksums WHERE DIFFS > 0;"

# 手动校验
mysql -h master -N -e "SELECT COUNT(*), SUM(id) FROM test.orders" > /tmp/master.txt
mysql -h slave -N -e "SELECT COUNT(*), SUM(id) FROM test.orders" > /tmp/slave.txt
diff /tmp/master.txt /tmp/slave.txt && echo "一致" || echo "不一致"

排障速查表

运维清单

• 每日： SHOW SLAVE STATUS / SHOW PROCESSLIST
• 每周： 工作者线程状态 / Innodb_log_waits
• 每月： pt-table-checksum 一致性校验 / 故障切换演练 / 清理旧 Binlog

搭建侧避坑清单

• 禁止主从 server-id 相同：直接导致复制冲突、同步中断
• 禁止用 root 账号同步：权限过大存在安全风险，生产规范禁止，必须用专用 repl 账号
• 从库不要写数据：read_only + super_read_only 双开，防误写导致主从数据不一致、复制报错
• 优先使用 GTID 复制：摒弃传统位点复制，自动容错、运维极简
• 延迟类避坑（大事务、索引、硬件）见 mysql-replication-lag-troubleshooting

关联页面