MySQL 主从复制指南

MySQL 主从复制是高可用数据库架构的基础。核心流程：主库 Binlog → IO 线程拉取 → Relay Log → SQL 线程执行。

复制类型对比

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;

常见架构与搭建

一主一从标准搭建

# 主库备份
mysqldump --single-transaction --source-data=2 --all-databases > full_backup.sql

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

-- 从库配置 GTID 复制
CHANGE MASTER TO
  MASTER_HOST = 'master.example.com',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'repl_password',
  MASTER_AUTO_POSITION = 1;
START SLAVE;

-- 验证
SHOW SLAVE STATUS\G
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0

架构选择

半同步复制配置

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：重新初始化从库

复制延迟过大

排查： 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

关联页面