MySQL 主从复制配置与延迟排查：从搭建到 Seconds_Behind_Master 不准

一、结论先给

主从复制分三步：主库开 binlog 并建复制账号 → 从库导入一致性快照 → 从库 CHANGE MASTER 并 START SLAVE。真正难的不是搭建，是延迟排查和不一致修复。

问题	第一反应
`Slave_IO_Running: No`	连不上主库（网络/账号/权限/server_id 冲突）
`Slave_SQL_Running: No`	SQL 重放出错（数据冲突、表不存在）
`Seconds_Behind_Master` 一直涨	从库追不上（单线程、大事务、缺索引、硬件差）
显示 0 但从库数据还是旧	`Seconds_Behind_Master` 不准，要用 GTID 或位点比对

Seconds_Behind_Master 在 IO 线程断掉时显示 NULL，在主库空闲时显示 0，在大事务执行中是"事件时间戳差"而非真实延迟——它只能当参考，不能当判定依据。

👉 复制报错日志排查可以用日志分析工具直接定位错误行。

二、搭建：主库配置

# 主库 my.cnf
[mysqld]
server_id            = 1              # 必须唯一，不能和从库重复
log_bin              = /var/lib/mysql/mysql-bin
binlog_format        = ROW
sync_binlog          = 1
innodb_flush_log_at_trx_commit = 1
gtid_mode            = ON             # 推荐开 GTID
enforce_gtid_consistency = ON
binlog_expire_logs_seconds = 604800

重启后建复制账号：

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

-- 记下当前位点（导出快照时用）
SHOW MASTER STATUS;
-- +------------------+----------+--------------+------------------+
-- | File             | Position | Binlog_Do_DB | Executed_Gtid_Set |
-- | mysql-bin.000001 |      154 |              |                  |

三、搭建：从库配置与数据初始化

# 从库 my.cnf
[mysqld]
server_id       = 2                  # 与主库不同
relay_log       = /var/lib/mysql/relay-bin
read_only       = ON                 # 从库只读（对 super 用户无效，MySQL 8 用 super_read_only）
super_read_only = ON
gtid_mode            = ON
enforce_gtid_consistency = ON
log_bin         = /var/lib/mysql/mysql-bin   # 从库也开 binlog，方便级联
log_slave_updates = ON               # 8.0 默认开启

3.1 一致性快照导出（用 mysqldump）

mysqldump -u root -p --single-transaction --master-data=2 \
  --routines --triggers --events --all-databases > full.sql

--master-data=2 会把 CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=... 写进注释，从库导入后直接照着配。

3.2 大库用 XtraBackup（不锁表、速度快）

xtrabackup --backup --target-dir=/data/full --user=root --password='pwd'
xtrabackup --prepare --target-dir=/data/full
# 拷到从库后看位点
cat /data/full/xtrabackup_binlog_info
# mysql-bin.000001    154

四、启动复制

4.1 位点模式

CHANGE MASTER TO
  MASTER_HOST='10.0.0.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='Repl_Pass123!',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;

START SLAVE;
SHOW SLAVE STATUS\G

4.2 GTID 模式（推荐，8.0 也支持传统写法）

CHANGE MASTER TO
  MASTER_HOST='10.0.0.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='Repl_Pass123!',
  MASTER_AUTO_POSITION = 1;      -- 关键：交给 GTID 自动定位

START SLAVE;

GTID 的好处：不用记 binlog 文件名和位置，主从切换、故障恢复时不会选错位。强烈建议新环境直接用 GTID。

五、SHOW SLAVE STATUS 逐字段读法

SHOW SLAVE STATUS\G

字段	正常值	含义
`Slave_IO_Running`	Yes	从库是否连上主库拉 binlog
`Slave_SQL_Running`	Yes	从库是否在重放 SQL
`Seconds_Behind_Master`	0 或个位数	延迟秒数（仅参考）
`Master_Log_File` / `Read_Master_Log_Pos`	—	IO 线程读到哪
`Relay_Master_Log_File` / `Exec_Master_Log_Pos`	—	SQL 线程执行到哪
`Last_IO_Error` / `Last_SQL_Error`	空	最近一次错误
`Retrieved_Gtid_Set` / `Executed_Gtid_Set`	应相同	GTID 模式下判断延迟最准
`Slave_IO_State`	Waiting for source to send event	IO 线程空闲等待

判断真实延迟的正确方法（GTID 模式）：

-- 主从各自执行，比较 Executed_Gtid_Set 的差值
SELECT @@GLOBAL.gtid_executed;
-- 或在从库上：
SELECT GTID_SUBTRACT(
  (SELECT @@GLOBAL.gtid_executed FROM (SELECT 1) x),   -- 示意，实际要在主库取
  @@GLOBAL.gtid_executed
) AS 还差多少事务;

更简单实用的办法（MySQL 8 自带）：

-- 从库上执行，看还有多少 relay log 没重放
SHOW SLAVE STATUS\G   -- 比较 Read_Master_Log_Pos 与 Exec_Master_Log_Pos 的差

六、复制报错与修复

6.1 `Slave_IO_Running: No`

SHOW SLAVE STATUS\G   -- 看 Last_IO_Error

错误	原因	处理
`error connecting to master`	网络/防火墙/端口	`telnet 主库 3306`、查安全组
`Access denied for user 'repl'`	密码错或没授权	重建账号、确认 host 段
`The server is not configured as slave`	`server_id` 没设	检查 my.cnf
`Master and slave have equal MySQL server ids`	server_id 冲突	两台必须不同
`Got fatal error 1236 ... could not find first log file`	主库 binlog 已被清理	重做从库

6.2 `Slave_SQL_Running: No`（数据冲突）

常见错误码：

错误码	场景	处理
1062	主键冲突（从库已有这行）	删掉冲突行，或 `SET GLOBAL sql_slave_skip_counter=1`
1032	找不到行（UPDATE/DELETE 时从库没有）	补数据或跳过
1146	表不存在	建表后跳过
1050	表已存在	跳过

跳过错误的代价是数据不一致，只在确认可以接受时使用：

-- 传统模式：跳过 1 个事件
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

-- GTID 模式：必须先停，注入空事务跳过指定 GTID
STOP SLAVE;
SET GTID_NEXT='3a2b1c:12345';       -- 填出错的那个 GTID
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';
START SLAVE;

更稳妥的做法：让从库只读，从不在从库上写数据，就不会有冲突。冲突多半是因为有人直接在从库改了数据。

七、主从延迟的六类成因与解法

成因	现象	解法
从库单线程重放	主库并发高，从库追不上	开并行复制（见下）
大事务	一次删 500 万行，从库卡住几十分钟	拆成小事务
从库缺索引	UPDATE/DELETE 在从库全表扫	补齐与主库一致的索引
从库硬件差	CPU/IO 打满	提升配置或只读分离
从库承担大量查询	读压力大，抢资源	加从库、读写分离
主库大批量写入（跑批）	每天凌晨延迟飙升	跑批错峰、限流、分批提交

7.1 并行复制（最有效的一招）

-- MySQL 8.0 默认就是 LOGICAL_CLOCK 并行，查看：
SHOW VARIABLES LIKE 'slave_parallel_type';
SHOW VARIABLES LIKE 'slave_parallel_workers';   -- 默认可能是 4，可以调大

-- 设置（需停 slave）
STOP SLAVE;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 8;          -- 按 CPU 核数的一半到全量
START SLAVE;

MySQL 8.0 还增加了 binlog_transaction_dependency_tracking = WRITESET，能进一步提升并行度：

SET GLOBAL binlog_transaction_dependency_tracking = 'WRITESET';
SET GLOBAL transaction_write_set_extraction = 'XXHASH64';
-- 写入 my.cnf 永久生效

7.2 延迟监控脚本

#!/usr/bin/env bash
# 延迟超过 60 秒告警
LAG=$(mysql -u root -p"$PW" -N -B -e "SHOW SLAVE STATUS\G" | awk -F': ' '/Seconds_Behind_Master/{print $2}')
IO=$(mysql -u root -p"$PW" -N -B -e "SHOW SLAVE STATUS\G" | awk -F': ' '/Slave_IO_Running/{print $2}')
SQL=$(mysql -u root -p"$PW" -N -B -e "SHOW SLAVE STATUS\G" | awk -F': ' '/Slave_SQL_Running/{print $2}')

[ "$IO" != "Yes" ] || [ "$SQL" != "Yes" ] && echo "复制线程异常 IO=$IO SQL=$SQL"
[ "$LAG" != "NULL" ] && [ "$LAG" -gt 60 ] && echo "延迟 ${LAG} 秒"

八、主从不一致：检测与修复

8.1 检测（pt-table-checksum）

pt-table-checksum --host=主库 --user=root --password=pwd \
  --databases=app --replicate=percona.checksums --no-check-binlog-format

# 在从库查看差异
pt-table-sync --print --sync-to-master h=从库,u=root,p=pwd --databases=app

--print 先看会改什么，确认后再换成 --execute。

8.2 修复

小范围不一致：pt-table-sync --execute 同步。

严重不一致（从库数据被污染）：重做从库是最干净的方案。

# 1. 从库停复制
STOP SLAVE; RESET SLAVE ALL;
# 2. 主库重新导出一致性快照
mysqldump -u root -p --single-transaction --master-data=2 --all-databases > full.sql
# 3. 从库导入后重新 CHANGE MASTER

九、常见误区

信 Seconds_Behind_Master：IO 线程断时是 NULL，大事务中是时间戳差，只有 SQL 线程卡住且 IO 正常时才接近真实延迟。
在从库上写数据：必然导致主键冲突和不一致，从库必须 super_read_only=ON。
server_id 忘了改：两台一样，IO 线程直接起不来。
跳过错误不记录：跳过就是接受不一致，必须记下来事后修复。
从库索引和主库不一样：主库 1 秒的 DELETE 在从库要跑 10 分钟，延迟就是这么来的。
主库跑批不限流：一次 500 万行的事务，从库必然延迟几十分钟。

十、几条纪律

新环境一律用 GTID，省去位点管理的各种坑。
从库必须 read_only + super_read_only，严禁在从库写。
从库表结构（含索引）必须与主库一致，改表两边都改。
监控三项：IO 线程、SQL 线程、延迟秒数（并配 GTID 差值做校验）。
开并行复制（slave_parallel_workers + WRITESET），这是降延迟性价比最高的一招。
大事务一律拆小（每批 5000 行），跑批要限流错峰。
定期（每月）pt-table-checksum 校验一致性，别等故障才发现。

十一、延伸阅读

👉 相关在线工具：Linux 命令速查 · 日志分析，免登录、浏览器内处理。