MySQL一主一从实战：GTID配置与延迟监控

  1.  Home
  2. Text  
    3. Share to：

一、个人站为什么也需要主从

「我一个个人站，日 PV 几千，搞什么主从复制？」这是我最初的想法。直到某天凌晨做表结构变更，一条 ALTER TABLE 把数据库锁了四分钟，站点整整四分钟打不开，第二天又被搜索引擎抓到大量 5xx。那时候我才认真考虑：是不是该有个备份实例，能在我动手的时候顶上。

主从复制（replication）解决的问题不止是读写分离。对个人站来说，它真正值钱的地方有三个：

  • 动手时的安全网：改表、批量删数据、升级 MySQL 版本，先在从库上演练一遍，确认没问题再动主库。
  • 实时的异地副本：主库磁盘挂了，从库上的数据是几分钟前的，比每天凌晨的一次全量备份强太多。备份文件恢复要几十分钟，从库提升为主只要几秒。
  • 报表和大查询不打扰业务：后台统计、导出、全表扫描这类查询扔给从库，主库的 IO 和锁竞争立刻降下来。

代价是复杂度：多一台机器、多一份运维负担、多一类故障（复制中断）。但如果你的站点已经开始有收入，或者数据丢了会真的心疼，这份复杂度是值得的。这篇文章记录我在两台 2 核 4G 的 VPS 上搭 MySQL 8.0 主从的完整过程，包括参数选择、监控指标和几个必须知道的坑。

二、动手之前：版本、拓扑与前置条件

先说清三个前提，不满足就先补齐，否则后面一步都走不通。

  1. 主从 MySQL 大版本必须一致，小版本尽量一致。从 8.0.32 复制到 8.0.28 通常可以，但反过来某些新特性（比如新的字符集默认值）会导致复制报错。我的做法是两台都用同一个发行版仓库装同一个版本。
  2. 从库的版本不能低于主库，这是硬性要求。
  3. 两台机器时间要同步，装 chrony 或 systemd-timesyncd，时间漂移超过几秒会让 GTID 和 binlog 定位出现奇怪的偏差，排查起来很痛苦。

拓扑上，个人站用「一主一从」就够了，不要一上来就搞级联或双主。双主看起来能双向写，实际上冲突处理、自增 ID 交错、环路复制的坑一堆，收益远小于风险。

先检查两台机器的基础状态：

mysql -e "SELECT VERSION(), @@server_id, @@log_bin, @@binlog_format, @@gtid_mode;"

输出里如果 log_bin 是 OFF，说明 binlog 没开，主库必须先开起来。如果 server_id 两台都是 1（默认值），必须先改成不同的值，否则复制会立刻失败。

三、主库配置：binlog 是复制的根

编辑主库的 /etc/mysql/mysql.conf.d/mysqld.cnf，在 [mysqld] 段落加入：

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
binlog_row_image = MINIMAL
expire_logs_days = 7
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
gtid_mode = ON
enforce_gtid_consistency = ON

逐条说为什么这么配：

  • server_id：复制拓扑里每台机器的唯一标识，必须全局唯一。
  • binlog_format = ROW：记录每一行的变更前后值，而不是 SQL 语句。STATEMENT 格式在遇到 NOW()、RAND()、自增时会导致主从不一致，这在 5.7 之后就默认 ROW 了，但配置里显式写一遍更安全。
  • binlog_row_image = MINIMAL：只记录变更的列，binlog 体积能小一半以上。个人站 binlog 占用磁盘是个真问题，这个参数很有用。
  • expire_logs_days = 7：binlog 保留 7 天。太短（比如 1 天）一旦从库断连超过一天就需要重新做全量；太长会吃满磁盘。7 天对个人站是合理值，磁盘吃紧就配合 max_binlog_size。
  • sync_binlog = 1 与 innodb_flush_log_at_trx_commit = 1：这两个都是「每次事务提交都刷盘」，性能会降一些，但保证主库崩溃后从库不会缺数据。个人站的数据量下这点性能代价可以忽略。
  • gtid_mode = ON 与 enforce_gtid_consistency = ON：开启 GTID，让复制基于事务 ID 而不是文件位置。这是 8.0 时代最省心的做法，切换主库、跳过一个坏事务都简单得多。注意开启 GTID 需要重启 MySQL，并且最好在从库上先开（顺序有讲究）。

改完重启主库：

systemctl restart mysql
mysql -e "SHOW MASTER STATUS\G"

输出里的 File 和 Position 记下来，后面做全量时会用到；如果用 GTID 方式，记下 Executed_Gtid_Set 的值。

接着创建复制账号。这个账号只需要 REPLICATION SLAVE 权限，不要图省事给 ALL PRIVILEGES：

CREATE USER 'repl'@'10.0.0.%' IDENTIFIED WITH mysql_native_password BY '强密码写这里';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';
FLUSH PRIVILEGES;

这里的一个坑：MySQL 8.0 默认用 caching_sha2_password 认证插件，从库用这个插件连主库时如果没有 TLS，会报「Authentication plugin 'caching_sha2_password' reported error」。

两个解法：一是上面那样显式指定 mysql_native_password；二是在主从之间配置 TLS。个人站一般在同一个内网或者走 SSH 隧道，用第一种更省事。用 mysql_native_password 时密码仍然要足够强，别用弱口令，因为这个账号是有网络登录能力的。

四、从库配置与全量同步

从库的配置和主库基本一致，只有两处不同：

[mysqld]
server_id = 2
relay_log = /var/log/mysql/relay-bin
log_slave_updates = ON
read_only = ON
super_read_only = ON
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_format = ROW

要点：

  • server_id = 2：与主库不同。
  • log_slave_updates = ON：从库把收到的事件也写进自己的 binlog，为将来「从库提升为主」做好准备。不开启的话，从库变成主库后没有 binlog，接不上新从库。
  • read_only = ON 与 super_read_only = ON：从库禁止写入。前者对普通用户生效，后者连 SUPER 权限的用户也禁止，能有效防止「在从库上随手改了个数据」这类事故。先开 read_only 再开 super_read_only，顺序反了会报错。

全量同步用 mysqldump。这是个人站最稳的方式，几 GB 的库十几分钟能搞定：

mysqldump -uroot -p \
  --single-transaction --master-data=2 --set-gtid-purged=ON \
  --routines --triggers --events \
  --databases zz1984 > /tmp/full_$(date +%F).sql

参数解释：

  • --single-transaction：在一个事务里导出一致性快照，不锁表，主库业务不受影响（前提是 InnoDB）。
  • --master-data=2：把主库当前的 binlog 位置以注释形式写进 dump 文件。加 2 表示写成注释，方便查看而不是自动执行。
  • --set-gtid-purged=ON：在 dump 里带上 GTID 信息，从库导入后就能知道从哪个事务开始接。
  • --routines --triggers --events：存储过程、触发器、定时事件一并导出，不加的话从库会缺这些对象。

导出完成后传到从库并导入：

scp /tmp/full_2026-11-04.sql root@slave:/tmp/
mysql -uroot -p < /tmp/full_2026-11-04.sql

导入之后从库会和主库数据一致，但复制关系还没建立。下面这一步是关键。

五、建立复制关系：GTID 方式

如果主库开了 GTID 且 dump 里带了 GTID，从库直接用 GTID 方式建立复制，不需要手工填文件名和位置：

CHANGE MASTER TO
  MASTER_HOST='10.0.0.11',
  MASTER_USER='repl',
  MASTER_PASSWORD='强密码写这里',
  MASTER_AUTO_POSITION=1;

START SLAVE;

（MySQL 8.0.23 之后 CHANGE MASTER 已被 CHANGE REPLICATION SOURCE 取代，老语法仍然可用但有弃用警告。新版本建议写成 CHANGE REPLICATION SOURCE TO SOURCE_HOST=...、START REPLICA。）

然后检查状态：

SHOW SLAVE STATUS\G

需要确认四件事：

  • Slave_IO_Running: Yes，且 Slave_SQL_Running: Yes。两个都必须是 Yes，只有一个 Yes 说明只连上了但回放失败。
  • Last_IO_Error 与 Last_SQL_Error 都为空。
  • Seconds_Behind_Master 是 0 或者很小的数字。
  • Retrieved_Gtid_Set 与 Executed_Gtid_Set 一致。

第一次看到两个 Yes 的时候是很爽的，但别急着庆祝——真正的功夫在之后的保持上。

六、延迟监控：不要等出事才发现从库落后

Seconds_Behind_Master 这个指标很容易骗人。它只反映「SQL 线程执行到的时间戳与当前时间差」，在大事务场景下会长期显示 0，实际上从库已经落后了几百 MB 的 binlog。尤其开了并行复制（slave_parallel_workers）之后，这个数字更加不可信。

更可靠的监控方式是看 GTID 差额。下面这条 SQL 能算出从库落后多少个事务：

SELECT
  GTID_SUBTRACT(RECEIVED_TRANSACTION_SET, @@GLOBAL.GTID_EXECUTED) AS pending_gtids
FROM performance_schema.replication_connection_status
WHERE CHANNEL_NAME = '';

输出里如果是一串很长的事务 ID 列表，就说明 SQL 线程跟不上 IO 线程，积压了事务。再结合下面这条看具体卡在哪个事务上：

SELECT * FROM performance_schema.replication_applier_status_by_worker\G

字段 LAST_ERROR_MESSAGE 有值就是复制中断了，APPLYING_TRANSACTION 显示当前正在执行的事务。

IO 线程是否健康，看这个：

SELECT
  SERVICE_STATE,
  LAST_ERROR_NUMBER,
  LAST_ERROR_MESSAGE,
  TIMESTAMPDIFF(SECOND, LAST_HEARTBEAT_TIMESTAMP, NOW()) AS hb_age
FROM performance_schema.replication_connection_status;

hb_age 超过 30 秒基本可以确定网络断了或主库挂了。

把这几个查询写成一个每天跑的脚本，结果是异常就发一封邮件，比肉眼每天登上去看强：

#!/bin/bash
out=$(mysql -N -e "
SELECT IF(
  RECEIVED_TRANSACTION_SET = @@GLOBAL.GTID_EXECUTED
  AND NOT EXISTS (SELECT 1 FROM performance_schema.replication_connection_status
                  WHERE SERVICE_STATE != 'ON'),
  'OK', 'LAG')
FROM performance_schema.replication_connection_status LIMIT 1;")
[ "$out" = "OK" ] || echo "replication alert on $(hostname): $out" | mail -s "slave lag" ops@example.com

七、复制中断的几种常见形态

复制跑一段时间后，一定会碰到中断。常见的原因有这几类：

  1. 有人直接在从库上写了数据：报 Duplicate entry 或 Cannot execute statement in read-only。开了 super_read_only 能挡住大部分，但 root 用 SET GLOBAL read_only=OFF 还是能绕开。规范做法是根本不要在从库上执行写操作。
  2. 大事务导致回放超时：一次 DELETE FROM big_table 在主库上跑了 20 分钟，从库回放同样要 20 分钟，期间延迟持续增长。解决方式是批量删（每批 1000 行），别一次性删。
  3. DDL 阻塞：主库上的 ALTER TABLE 在从库执行时因为从库负载高而卡住，后续所有事务排队。做 DDL 时先看从库的负载。
  4. 字符集/排序规则不一致：从库和主库的 character_set_server 或 collation_server 不同，某些字符串比较会不一致。建库时统一写成 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci。
  5. 主库 binlog 被清理得太快：从库断连超过 expire_logs_days，恢复时发现需要的 binlog 已经没了，只能重新做全量。

跳过单个坏事务（谨慎使用）：

STOP SLAVE;
SET GTID_NEXT='源GTID';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';
START SLAVE;

这样做之前必须搞清楚这个事务为什么要跳过，以及跳过会不会导致数据不一致。我见过的「跳过」操作十次里有三次在事后造成了数据错乱，不得不重新做全量。

八、从库能不能扛读？先把路由想清楚

很多文章一上来就说「主写从读」，实现读写分离。但对个人博客来说，把读请求分到从库要小心两件事：

  • 复制延迟导致的「读不到刚写的数据」：用户刚发完评论，页面刷新要显示自己的评论，如果读走从库而复制延迟 200ms，用户就会以为评论没发出去。常见做法是写后的一段时间内强制读主库（cookie 或 session 里标记）。
  • 从库挂掉后要有降级：应用层探测到从库不可用，要自动回到主库，别让整个站点因为从库挂了而不可用。

对个人站来说，最务实的方案是：只在后台统计、导出、批量脚本里显式指定连从库，前台业务全部走主库。这样既拿到了「大查询不打扰主库」的收益，又完全没有延迟问题。等流量真的上来了再考虑用 ProxySQL 之类的中间件做自动路由。

九、演练：主库挂了到底多快能恢复

搭好主从之后一定要做一次演练，否则真出事的时候手忙脚乱。演练步骤：

  1. 在从库上 STOP SLAVE; 确认已追平（Retrieved_Gtid_Set 等于 Executed_Gtid_Set）。
  2. 记录从库上 SHOW MASTER STATUS 的结果。
  3. 把应用的数据库连接指向从库。
  4. 在从库上执行 SET GLOBAL super_read_only=OFF; SET GLOBAL read_only=OFF; 让它可写。
  5. 把原来的主库降级为从库（如果它还能起来），用下面这句让它从新主库同步：
CHANGE MASTER TO
  MASTER_HOST='10.0.0.12',
  MASTER_USER='repl',
  MASTER_PASSWORD='...',
  MASTER_AUTO_POSITION=1;
START SLAVE;

演练时把每一步的耗时记下来。我第一次演练花了 11 分钟，其中 8 分钟是在找配置文件、确认 DNS 怎么改。第二次按清单走只用了 90 秒。这 90 秒和 11 分钟的差别，就是演练的全部价值。

十、几个容易踩的细节

  • 从库也要备份：从库不是备份，它是实时副本。主库误删数据，从库会在几百毫秒后也删掉。备份还是要单独做，最好放到第三台机器或对象存储。
  • 从库的 innodb_buffer_pool_size 不要设太小：从库回放事务同样需要缓冲池，设成 128MB 会导致回放极慢，延迟持续增长。按内存的 50%~60% 配。
  • 监控磁盘：binlog 和 relay log 都在 /var/log/mysql，容易把根分区写满。单独挂一块盘或者定期检查。
  • 连接数：从库要预留足够的 max_connections，回放线程和监控连接也要算进去。
  • GTID 开启顺序：先在所有节点上开 enforce_gtid_consistency=ON（可动态），再开 gtid_mode=OFF_PERMISSIVE、ON_PERMISSIVE、ON（这几个是动态的，不用重启），最后才改配置文件。直接改配置文件重启是另一种做法，但对线上有影响。

十一、小结

个人站搭主从，核心是把三件事做对：主库的 binlog 配置（ROW 格式、MINIMAL 行镜像、合理的保留天数）、从库的只读保护（read_only 加 super_read_only）、以及基于 GTID 而不是文件位置的复制关系。监控上不要只看 Seconds_Behind_Master，用 GTID 差额加回放线程状态才是可靠的判据。最后别忘了演练——一套没演练过的主从，在真出事的时候可能比没有更糟。

Last modification：November 4th, 2026 at 10:29 pm
© 允许规范转载

相关文章

Leave a Comment