MySQL binlog 与主从复制实战：ROW 格式选择、GTID 搭建、时间点恢复与延迟监控

  1.  Home
  2. Text  
    3. Share to：

备份只能救回"某个时间点"，复制才能救"服务不中断"

很多个人站长对数据安全的理解停留在"我每天备份了"。备份确实重要，但它解决的是"数据丢了能恢复"，解决不了"主库挂了网站就全站 500"。当你的站从一台 VPS 长到一个主库加几个只读副本，或者你想在不影响线上业务的前提下做统计、导出、跑慢查询，MySQL 的主从复制（replication）就是绕不过去的一课。

主从复制的核心机制是 binlog（二进制日志）：主库把每一条改变数据的语句或行记录写进 binlog，从库拉取这些日志并在本地重放，从而与主库保持近乎实时的数据一致。理解了 binlog，你不仅能搭主从，还能做时间点恢复（PITR）、做数据订阅、做审计。本文从零讲清楚 binlog 的三种格式怎么选、GTID 主从怎么搭、延迟和不一致的排查，以及个人站长最该避开的几个坑。

先选 binlog 格式：STATEMENT、ROW 还是 MIXED

binlog 记录数据变更的方式有三种，理解它们的差异是搭好复制的前提：

  • STATEMENT：记录的是 SQL 语句本身（比如 UPDATE t SET n=n+1 WHERE id=1）。日志小、可读性好，但遇到 NOW()、RAND()、UUID() 这类非确定性函数，主从执行结果可能不一致，还有基于条件的 UPDATE 在从库按不同索引执行会锁行不同。
  • ROW：记录每一行数据变化前后的实际值。数据一致性强，主从几乎不会因语句差异跑偏，是当前官方推荐、也是 MySQL 8.0 默认的格式。代价是批量 UPDATE 会产生大量日志，磁盘占用和网络传输更大。
  • MIXED：由 MySQL 自动判断，确定性语句用 STATEMENT，非确定性语句用 ROW。是过去折中的选择。

对个人站长而言，直接选 ROW。磁盘多花点没关系，主从数据不一致才是真的灾难。现代 MySQL 的 ROW 格式在磁盘和带宽上的开销已经可以接受，尤其是 8.0 加入了 binlog 行压缩之后。

第一步：主库开启 binlog 与唯一 server-id

在主库的配置文件（如 /etc/mysql/my.cnf 或 /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 = FULL
expire_logs_days = 7
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

几个要点：server-id 在复制拓扑里必须全局唯一，主库设 1，从库依次 2、3；log_bin 指定 binlog 的存放前缀；binlog_row_image = FULL 保证 ROW 格式记录完整的前后镜像（对某些恢复场景必要）；expire_logs_days = 7 自动清理七天前的旧日志以免把磁盘撑爆；sync_binlog = 1 与 innodb_flush_log_at_trx_commit = 1 一起确保每次事务提交都落盘，是金融级可靠性的"双 1"配置，代价是写入延迟略增，个人站也建议开启。

改完重启 MySQL，验证 binlog 是否生效：

mysql -uroot -p -e "SHOW VARIABLES LIKE 'log_bin';"
# log_bin  Value  ON
mysql -uroot -p -e "SHOW MASTER STATUS\G"
# 应该看到 File: mysql-bin.000001  Position: 157 等

第二步：用 mysqldump 做一致性初始快照

从库要开始复制，得先有一份主库当前的数据快照，并且记住快照对应的 binlog 位置。--single-transaction 能在不锁表的情况下拿到一致性快照（前提是表都是 InnoDB）：

mysqldump -uroot -p \
  --single-transaction \
  --master-data=2 \
  --routines --triggers --events \
  --all-databases > /tmp/master_dump.sql

--master-data=2 会把当前 binlog 的 File 和 Position 以注释形式写进 dump 文件头部，这就是从库的复制起点。导出完成后，在文件里找这行：

head -30 /tmp/master_dump.sql | grep -A2 "CHANGE MASTER"
# -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;

第三步：从库恢复数据并指向主库

在从库上先把快照导入（可以从空库开始）：

mysql -uroot -p < /tmp/master_dump.sql

然后配置从库的 server-id = 2（务必和主库不同），重启后执行复制指向。推荐用 GTID 模式，省去手动记 File/Position 的麻烦：

# 主库和从库都建议开启
# [mysqld]
# gtid_mode = ON
# enforce_gtid_consistency = ON

CHANGE MASTER TO
  MASTER_HOST='10.0.0.1',
  MASTER_USER='repl',
  MASTER_PASSWORD='一个强密码',
  MASTER_PORT=3306,
  MASTER_AUTO_POSITION=1;

START SLAVE;

SHOW SLAVE STATUS\G

主库上需要先建一个专用于复制的账号：

CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY '一个强密码';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';
FLUSH PRIVILEGES;

看 SHOW SLAVE STATUS\G 的关键字段：Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes，两个都是 Yes 才算复制正常；Seconds_Behind_Master 是延迟秒数，正常应接近 0；Last_Error、Last_IO_Error 有内容就说明出错了。

复制延迟与不一致的排查

从库落后主库（延迟飙升）是个人站长最常见的困扰，原因和排查方向大致分几类：

  • 从库单线程重放跟不上主库并发写：MySQL 5.7/8.0 支持多线程复制（MTS），设 slave_parallel_workers = 4 并按逻辑时钟或 WRITESET 并行可大幅降低延迟。
  • 从库被大查询占用：有人在从库上跑重查询、做统计，抢占了 SQL 线程的 CPU 和 IO。养成"只读从库也不跑重活"或另起一个专用从库的习惯。
  • 大事务：一次性 UPDATE 几百万行会在从库长时间重放，期间延迟不断累积。拆成小批量提交。
  • 网络抖动：跨机房复制对网络敏感，Relay_Log_Space 急剧增长往往是网络中断导致 IO 线程拉不到日志。

排查命令：SHOW PROCESSLIST 看从库是否有大查询；SHOW SLAVE STATUS 看 Retrieved_Gtid_Set 与 Executed_Gtid_Set 的差值定位 SQL 线程落后多少。

用 binlog 做时间点恢复（PITR）

搭好复制只是 binlog 的一半价值。它另一半、也是更救命的用途，是时间点恢复。设想场景：今天下午三点有人误执行了一条 DELETE FROM posts WHERE ...，删掉了三千篇文章。你昨天凌晨的备份是完整的，但直接从备份恢复意味着丢失一整天的新数据。这时候 binlog 就派上用场了。

恢复流程分两步：先用备份把数据库还原到备份点（比如昨天凌晨两点），再用 binlog 把从昨天两点到误删前一刻之间的所有变更重放一遍，让数据回到"事故前一秒"的状态。定位误删语句的位置，可以借助 mysqlbinlog 工具：

# 把误删那一刻前后的 binlog 导出成可读 SQL，找到事故的时间点
mysqlbinlog --base64-output=DECODE-ROWS -v \
  /var/log/mysql/mysql-bin.000003 | grep -n -i "delete from posts"

# 找到时间后，把备份恢复，再重放截止到该时间点之前的 binlog
mysqlbinlog --stop-datetime="2026-10-03 14:59:00" \
  /var/log/mysql/mysql-bin.00000* | mysql -uroot -p

--start-datetime 和 --stop-datetime 可以精确框定重放的时间区间，也可以按 position 框定。这套"全量备份 + binlog 增量重放"是数据库恢复的黄金标准，也是为什么 expire_logs_days 不能设得太短——至少要让 binlog 的保留窗口覆盖你两次备份之间的间隔，否则一旦需要 PITR 就会发现增量日志已经被清理了。个人站的合理做法：每天一次全量备份，binlog 至少保留七天。

复制延迟监控：别等出事才发现

把复制延迟纳入监控，是让这套体系真正可靠的关键。可以写一个简单的检查脚本挂到 cron 上，每五分钟查一次 Seconds_Behind_Master，超过阈值（比如 60 秒）就告警：

#!/bin/bash
lag=$(mysql -uroot -p"$PASS" -N -e "SHOW SLAVE STATUS\G" \
      | awk '/Seconds_Behind_Master/{print $2}')
io=$(mysql -uroot -p"$PASS" -N -e "SHOW SLAVE STATUS\G" \
      | awk '/Slave_IO_Running:/{print $2}')
sql=$(mysql -uroot -p"$PASS" -N -e "SHOW SLAVE STATUS\G" \
      | awk '/Slave_SQL_Running:/{print $2}')
if [ "$io" != "Yes" ] || [ "$sql" != "Yes" ] || [ "$lag" -gt 60 ]; then
  curl -s "https://你的告警webhook/xxx" -d "复制异常: IO=$io SQL=$sql 延迟=${lag}s"
fi

有了这个脚本，复制中断或延迟暴涨你会第一时间知道，而不是等到某天要从库读数据时才发现它已经落后了几个小时。监控延迟、保留足够长的 binlog、定期演练恢复流程——这三件事做好了，主从复制才从一个"搭着好看"的摆设，变成真正能兜底的数据基础设施。

从库该怎么用：读写分离的取舍

搭好复制之后，一个自然的想法是把读请求分一部分给从库，减轻主库压力，这就是读写分离。个人站规模不大时，这件事的价值有限，而且引入了一致性问题：用户在主库刚写完数据，紧接着的读请求如果路由到尚未同步完成的从库，就会"看不到自己刚提交的内容"，出现所谓的主从读写不一致。所以对个人站有两条务实建议：一是只把明显不需要即时一致性的读流量（比如后台的统计报表、批量导出、离线分析）丢到从库，用户的实时操作仍读主库；二是即便做分离，也要给应用一个"强制走主库"的开关，处理关键写后读场景。不要把读写分离当成性能银弹盲目铺开——它带来的一致性复杂度，往往比它省下的那点主库压力更值钱。

另外从库还能承担一个非常实用的角色：拿从库做备份源。在从库上执行 mysqldump 或 XtraBackup，就不会因为备份时锁表或大量读而影响主库的线上业务。这是从库在"分担读"之外，对个人站长价值最直接的一个用途。

五个必踩的坑

  1. server-id 重复或没设：主从 server-id 相同会导致复制直接报错，是最低级也最常见的错误。
  2. 从库写成可写还放业务：一旦应用连到从库写数据，主从就会冲突甚至损坏数据。从库一定要设 read_only = ON（超级用户可绕过，配合 super_read_only = ON 更稳）。
  3. 用 root 做复制账号：安全大忌。必须用最小权限的专用 repl 账号，并限制来源 IP。
  4. 忘了 binlog 磁盘占用：expire_logs_days 没设或设太长，binlog 无限膨胀把磁盘写满，主库直接宕。定期 SHOW BINARY LOGS 看总大小。
  5. 把主从当备份用：误删数据的语句会同步到从库，主从一起挂。复制不是备份，两者必须都有，且备份要离线或异地保留。

MySQL 主从复制是个人站长从"单机小站"迈向"能扛事的基础设施"的关键一步。它让你能在主库之外做只读分担、能无损做维护、能给数据多留一层物理冗余。把 ROW 格式、GTID、只读从库和延迟监控这四点做扎实，这套体系就足够支撑一个长期运营的站点了。

Last modification：October 4th, 2026 at 07:23 pm
© 允许规范转载

相关文章

Leave a Comment