MySQL 主从复制搭起来不难，跑稳了是另一回事：我踩过的 5 个坑

AI摘要

本文介绍了MySQL主从复制的搭建过程，强调虽然搭建相对简单，但运行稳定却面临诸多挑战。作者分享了个人在搭建过程中遇到的五个问题，包括同步中断、数据不一致和GTID配置等，并详细描述了解决这些问题的方法和经验。文章强调保持主从版本一致、合理配置主库和从库、使用GTID模式进行同步的重要性，并提供了相应的配置示例。

之前项目上要给 MySQL 加读扩展能力，我就搭了一套主从。搭的时候觉得挺简单的，跑起来之后才发现坑比想象的多。同步断过、数据不一致过、GTID 也折腾过好几回。这篇把当时的情况和我后来怎么处理的记一下，省得下次再踩同样的坑。

先说说为什么要搞主从

我们的场景很典型：读多写少。一个后台管理系统，查询接口占了 80% 以上的请求量。单机 MySQL 跑到后面 CPU 经常 70-80%，加索引、调慢查询都试过了，治标不治本。

主从复制的思路很直接：写操作走主库，读请求分散到从库上。从库还可以顺便当个热备份，主库挂了至少有个兜底。

但要清楚主从解决不了什么问题。写瓶颈它管不了，数据一致性也不是它擅长的。金融类业务如果靠主从复制做读写分离，早晚要出事。我们当时评估过，我们的业务能接受几百毫秒的延迟，所以才决定上。

我搭的架构

最简单的一主一从，两台机器：

主库 (master): 192.168.1.10  MySQL 8.0.32
从库 (slave):  192.168.1.11  MySQL 8.0.32

版本保持一致这个事我多说一句：5.7 和 8.0 的复制协议有差异。我之前试过混用，没当场报错，但跑了一段时间后出现了一些奇怪的编码问题。后来统一升级到 8.0 就没事了。

主库那边的配置

my.cnf 里改这几个：

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

server-id 全局唯一就行，主库我写 1，从库写 2。log-bin 开二进制日志，这个是复制的前提，不开就没法同步。

binlog-format 这个参数我纠结了一阵。STATEMENT 格式的日志量小，但在有 NOW()、UUID()、RAND() 这些函数的场景下会导致主从数据不一致。MIXED 是折中方案，MySQL 自己判断什么时候用 STATEMENT 什么时候用 ROW。我一开始用的 MIXED，后来有一次出了个诡异的同步不一致，排查了半天发现是 MIXED 模式下一条涉及临时表的语句被错误地用 STATEMENT 记录了。从那以后全改 ROW，日志大就大点，省心。

sync_binlog = 1 加 innodb_flush_log_at_trx_commit = 1 这个组合是最安全的，每次事务提交都刷盘，断电也不丢数据。代价是写入性能会下降。如果业务能接受极端情况下丢少量事务，sync_binlog 可以改成 100 甚至 0，性能提升很明显。我们当时跑了一下基准测试，改成 100 之后写入 TPS 提升了大概 40%。但考虑到我们的数据不能丢，最后还是保持了双 1 配置。

改完重启，建个复制账号：

CREATE USER 'repl'@'192.168.1.11' IDENTIFIED BY '这里填个强密码';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.11';
FLUSH PRIVILEGES;

我这里限制了只能从从库 IP 连过来。如果你的从库有好几台，@'%' 也行，但生产环境最好写死 IP。

从库那边

[mysqld]
server-id = 2
relay-log = relay-bin
read_only = 1
super_read_only = 1

read_only 和 super_read_only 这两个一起开。read_only 只挡住普通用户，SUPER 权限的用户照样能写。super_read_only 把 SUPER 也挡住了。

这个配置太重要了。我有一次就是忘了开 super_read_only，一个同事在从库上跑了一条 INSERT（他以为那是测试库），结果同步直接停了。后面修数据不一致花了两小时。从那以后我搭从库第一件事就是开 super_read_only，没有例外。

从库不用开 log-bin，除非你要做级联复制（从库后面再挂从库）或者用从库做备份时需要记录 binlog。

开始同步

MySQL 8.0 用 GTID 模式比传统的 file+position 好用太多。GTID 给每个事务一个全局唯一 ID，从库通过这个 ID 来追踪同步位置。传统模式下你要手动对 binlog 文件名和偏移量，对不上就完了。GTID 模式自动定位，省了很多事。

主库和从库都加上 GTID 配置：

gtid_mode = ON
enforce_gtid_consistency = ON

重启两边。在从库上执行：

CHANGE MASTER TO
  MASTER_HOST = '192.168.1.10',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = '这里填个强密码',
  MASTER_AUTO_POSITION = 1;

START SLAVE;

MASTER_AUTO_POSITION = 1 告诉从库用 GTID 自动定位同步位置。

确认状态：

SHOW SLAVE STATUS\G

主要看两个字段：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

都是 Yes 就在跑了。再看看 Seconds_Behind_Master，这个数字是从库落后主库的秒数，正常应该是 0 或者很小的数。

主库有存量数据怎么办

上面的流程假设主库是空白的。如果主库已经在跑了，有数据要迁移，就得先把存量数据搬过去。

mysqldump 是最简单的方式：

mysqldump -u root -p --all-databases \
  --master-data=2 --single-transaction \
  --set-gtid-purged=ON \
  | gzip > full_backup.sql.gz

--single-transaction 保证导出的时候数据是一致的（只对 InnoDB 有效）。--master-data=2 在 dump 文件里以注释形式记录 binlog 位置。--set-gtid-purged=ON 把 GTID 信息也带上，这样从库导入后就知道从哪里开始同步。

数据量小的话（几个 GB 以内）mysqldump 够用了。数据量大了就很慢，几十 GB 的库能跑一两个小时。这种情况我推荐用 xtrabackup，物理备份，速度快很多。我之前一个 40GB 的库，mysqldump 要跑一个半小时，xtrabackup 十五分钟就搞定了。

mydumper 也是个选项，多线程版的 mysqldump，速度比单线程快不少。不过它需要额外安装，我用的时候还碰到过中文编码的问题，后来就放弃了。

踩过的几个坑

在从库上手贱执行了写操作

前面提到过，同事在从库上 INSERT 了一条数据，同步直接断了。SHOW SLAVE STATUS 的 SQL 线程显示报错，说目标行已存在。

当时的处理方式：

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

跳过那条错误，同步恢复了。但问题是，主从数据已经不一致了。那条 INSERT 在主库上不存在，从库上多了一条。

后来我写了个脚本，定期用 pt-table-checksum 跑一遍全表校验，发现不一致就用 pt-table-sync 修。这两个工具是 Percona Toolkit 里的，装一下就行。跑校验的时候会对表加锁，建议在业务低峰期跑。

一条大事务把同步延迟拉到 3 分钟

有次业务做了一次批量更新，UPDATE 了差不多 200 万行。这个事务在主库上跑了 3 分钟才提交。从库回放这个事务也要差不多 3 分钟，因为 MySQL 从库默认是单线程回放的，主库上一个大事务跑了多久，从库就至少要多久。

那 3 分钟里 Seconds_Behind_Master 一直在涨，读从库的业务拿到了过期数据，运营那边报了好几次说数据不对。

后来我做了两件事。第一，在从库上开了多线程回放：

slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4

LOGICAL_CLOCK 让主库上并行执行的事务在从库上也能并行回放。workers 数量我测下来 4 比较合适，8 的时候性能反而下降了，可能是锁竞争的原因。

第二，跟业务那边沟通，大事务拆小。每次 UPDATE 1000 行，中间 sleep 0.5 秒。改完之后单次事务不到 1 秒就能提交，从库回放也跟得上了。

GTID 模式下跳过错误的方式不一样

传统模式下 sql_slave_skip_counter 就能跳过复制错误，但 GTID 模式下这个变量不好使。我第一次在 GTID 模式下想跳过错误的时候，设了 sql_slave_skip_counter 结果没用，折腾了一阵。

正确做法是注入一个空事务来跳过出问题的 GTID：

-- 先看当前状态
SHOW SLAVE STATUS\G
-- 记下 Retrieved_Gtid_Set 和 Executed_Gtid_Set

STOP SLAVE;

-- 把出问题的 GTID 替换成空事务
SET GTID_NEXT = '那个出问题的GTID';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';

START SLAVE;

这个操作要仔细。我第一次做的时候 GTID 编号看错了，跳过了一个不该跳的事务，后面又出了一次数据不一致。现在每次操作之前我会先把 SHOW SLAVE STATUS 的完整输出存到文件里，确认好几遍再执行。

主库被强制重启后从库连不上

有次主库服务器因为硬件故障被强制重启了。主库恢复后，从库的 IO 线程显示连不上。

原因是主库重启后 binlog 文件轮转了，从库还在尝试读旧的文件位置。如果是 GTID 模式，这个问题一般不会发生，因为 GTID 自动定位。但我当时那套是老项目，还在用 MySQL 5.6 的 file+position 模式。

修复方式是在主库上查新的 binlog 位置，然后在从库重新 CHANGE MASTER：

-- 主库
SHOW MASTER STATUS;
-- 记下 File 和 Position

-- 从库
STOP SLAVE;
CHANGE MASTER TO
  MASTER_LOG_FILE = '新的文件名',
  MASTER_LOG_POS = 新的位置;
START SLAVE;

这次之后我把那套老项目也升级到了 GTID 模式。file+position 在故障恢复场景下太容易出问题了。

从库磁盘被 relay log 撑满

relay log 正常情况下回放完就自动删了，但如果从库回放速度跟不上（比如前面说的大事务），relay log 就会堆积。我有一次没注意，relay log 堆了十几个 GB，磁盘直接满了，从库的 SQL 线程报错停了。

配置里确认这两个参数：

relay_log_purge = 1
relay_log_recovery = 1

relay_log_purge 默认是开的，但有时候会被误关（比如调参的时候不小心删了）。relay_log_recovery 在从库重启时自动清理不需要的 relay log。

另外建议给从库磁盘加个使用率告警。我当时没加，等磁盘满了同步断了才发现，那时候延迟已经好几个小时了。现在我所有数据库机器都接了 Prometheus + Grafana，磁盘超过 80% 就发钉钉通知。

怎么监控同步状态

靠手动 SHOW SLAVE STATUS 肯定不行，得有自动化的东西盯着。

我写过一个简单的 shell 脚本，每分钟 crontab 跑一次：

#!/bin/bash
STATUS=$(mysql -e "SHOW SLAVE STATUS\G" 2>/dev/null)

IO_RUNNING=$(echo "$STATUS" | grep "Slave_IO_Running:" | awk '{print $2}')
SQL_RUNNING=$(echo "$STATUS" | grep "Slave_SQL_Running:" | awk '{print $2}')
BEHIND=$(echo "$STATUS" | grep "Seconds_Behind_Master:" | awk '{print $2}')

if [ "$IO_RUNNING" != "Yes" ] || [ "$SQL_RUNNING" != "Yes" ]; then
    echo "复制断了 IO=$IO_RUNNING SQL=$SQL_RUNNING" | mail -s "MySQL复制告警" ops@example.com
fi

if [ "$BEHIND" != "NULL" ] && [ "$BEHIND" -gt 60 ]; then
    echo "同步延迟 ${BEHIND}秒" | mail -s "MySQL复制延迟" ops@example.com
fi

后来迁移到 Prometheus 体系后，改用 mysqld_exporter，它自带 mysql_slave_status 指标，在 Grafana 上配个面板就能看到复制状态和延迟趋势，比脚本方便多了。告警规则也直接在 Grafana 里配，延迟超过 30 秒就发钉钉。