mysqlbinlog 增量恢复实战:从全备加日志救回误删的 30 行数据

为什么单独拎出 mysqlbinlog 来说

备份这件事,大部分站长的理解是「每天凌晨把库导出一份 SQL,扔到别的机器上」。这个方案在数据量小、并发低的时候没问题,我自己的几个小站也是这么干的。但它有一个绕不过去的缺口:RPO(Recovery Point Objective,能容忍丢多少数据)等于备份间隔。凌晨三点备份,晚上八点数据库被人误删了一张表,你只能恢复到凌晨三点的状态,中间十七个小时的订单、评论、注册用户全部消失。

对内容站来说,评论丢十七个小时也许还能忍;但如果站上有付费下载、会员续费、订单号,那十七个小时就是实打实的账。真正把 RPO 从「17 小时」压到「几分钟」的手段只有一个:binlog 增量恢复。

而 binlog 增量恢复的所有麻烦,都不在「怎么恢复」这一步,而在「恢复之前你得先弄明白手里这堆文件是什么」。mysqlbinlog 就是那把唯一的钥匙。这篇文章不讲备份策略的大道理,只讲这个工具怎么用、哪里会坑人、以及如何把一次真实的误删恢复做完并且验证过。

先确认 binlog 到底是什么格式

MySQL 5.6 及以后版本,binlog_format 默认是 ROW。这个默认值很关键,因为三种格式对恢复的影响完全不同:

  • STATEMENT:记录原始 SQL。优点是可读,缺点是不确定函数(NOW()、UUID()、RAND())在高并发下回放会不一致,官方早就不推荐。
  • ROW:记录每一行数据变化前后的值。恢复最安全,但日志体积大,且默认不可读。
  • MIXED:由 MySQL 自行判断,哪边走。

用一条命令看清楚当前状态:

mysql -uroot -p -e "SHOW VARIABLES WHERE Variable_name IN
('log_bin','binlog_format','binlog_row_image','binlog_expire_logs_seconds','log_bin_basename','server_id');"

几个值需要特别盯:

log_bin 必须是 ON。如果是 OFF,说明你的数据库根本没开二进制日志,这篇文章后面的内容你现在做不了,得先改 my.cnf 加 log-bin 并重启。

binlog_row_image 在 ROW 格式下决定了记录多少列。默认 FULL(前后镜像都记全),如果是 MINIMAL,只记被改动的列,恢复时某些场景会缺上下文,做审计和恢复都更麻烦,建议保持 FULL。

binlog_expire_logs_seconds(8.0)或 expire_logs_days(5.7)决定日志自动清理周期。默认 30 天。如果你的备份策略是「每天全备保留 7 天」,那 binlog 至少要和全备一样长,否则会出现「有全备但没有衔接的 binlog」的死档。

另外,server_id 必须非 0 才会写 binlog,这个坑在单机 Docker 部署里很常见——很多人忘了配 server-id,结果 log_bin 显示 ON 但目录里一个文件都没有。

找到并读懂 binlog 文件列表

日志文件在你 datadir 目录下,命名形如 mysql-bin.000001、mysql-bin.000002。用 SQL 查比 ls 更可靠:

SHOW BINARY LOGS;
SHOW MASTER STATUS;   -- 8.0.22 之后改用 SHOW BINARY LOG STATUS

SHOW BINARY LOGS 给出文件名和大小,SHOW MASTER STATUS 给出当前正在写的文件和位置(Position)。这个 Position 是后面恢复时的终点。

很多人到这里会犯一个错:直接 rm 掉旧 binlog 释放空间。绝对不能这么做。PURGE BINARY LOGS TO 'mysql-bin.000035'; 才是正确姿势,因为它会同步更新索引文件 mysql-bin.index。手工删文件之后,索引里残留的条目会让 MySQL 启动失败,或者在 SHOW BINARY LOGS 里出现幽灵条目。

mysqlbinlog 的基本用法:先看,再导

ROW 格式的 binlog 直接 cat 是二进制乱码。用它自己的解析器:

mysqlbinlog --base64-output=DECODE-ROWS -v /var/lib/mysql/mysql-bin.000042 | less

--base64-output=DECODE-ROWS 把那些 base64 的 row event 翻译成可读的伪 SQL 注释,-v(verbose)会展开列名和值。-vv 再多一级,对于字段特别多、或者要确认某列到底改成什么值时很有用。

输出大概是这个样子:

### DELETE FROM `zz1984`.`typecho_contents`
### WHERE
###   @1=2211
###   @2='post'
###   @3='上传的文件不是它说的类型...'

注意 ### 开头的是注释,不是可执行 SQL。这非常关键——很多人把解码后的输出直接喂给 mysql 客户端,结果什么都没发生,还以为恢复成功。ROW 格式事件的真实回放要靠 mysqlbinlog 自己输出 BINLOG 语句段,你没加 --base64-output=DECODE-ROWS 而加了 --verify-binlog-checksum 之类的组合,才是最保险的原始输出形式。

所以正确的两种用法要分清楚:

  • 看:--base64-output=DECODE-ROWS -v,输出给人看。
  • 导:不加解码参数,直接输出原始 BINLOG 语句,交给 mysql 执行。

按时间点抽取,而不是整段重放

恢复的核心是「精确到语句」。binlog 里的事件都带时间戳,所以可以按时间窗口切:

mysqlbinlog \
  --start-datetime="2026-11-15 20:00:00" \
  --stop-datetime="2026-11-15 20:14:59" \
  /var/lib/mysql/mysql-bin.000042 \
  > /tmp/recover_part1.sql

但请注意一个真实的陷阱:--stop-datetime 的粒度是秒,如果误操作和正常写入在同一秒内,你就无法靠时间切干净。这时候要用位置:

mysqlbinlog --start-position=1048576 --stop-position=1053422 /var/lib/mysql/mysql-bin.000042

Position 的获取方式是在解码输出里找 end_log_pos。每一条 # at 12345 后面跟着的 end_log_pos 就是这条语句结束的位置。所以「恢复到误删前一条语句」的意思就是 --stop-position 填误删语句的 # at 值。

时间戳还有一个坑:binlog 里的时间是服务器本地时区写进去的。如果你的服务器时区是 UTC,而你在北京时间思考,那 --start-datetime 就要按 UTC 填。我在一个迁移过的机器上踩过这个坑,时间差 8 小时,切出来的窗口完全是另一段业务,白折腾了一晚上。查时区用 SELECT @@system_time_zone, @@time_zone;。

跨文件恢复:顺序、起点、断点

如果误删发生的时间跨度跨了多个 binlog 文件,你必须按文件名数字顺序依次处理。写脚本时直接 for f in mysql-bin.00004* 会把 000041 排在 000042 后面吗?shell 的 glob 是字典序,十位数以内没问题,一旦超过 000099 到 000100,位数相同所以字典序依然正确。真正的风险在于文件被轮转后新生成的文件名不连续,所以稳妥做法是从数据库查,而不是从文件系统扫:

SHOW BINARY LOGS;

把结果按顺序拼起来,前面接全备:

zcat /backup/zz1984-full-20261115.sql.gz | mysql -uroot -p zz1984
mysqlbinlog mysql-bin.000041 mysql-bin.000042 | mysql -uroot -p

注意第二个命令不要加库名参数。mysqlbinlog 输出的事件里已经带库上下文(use `zz1984`;),如果你指定了默认库,遇到跨库语句会因为库不存在而中断。这一点在 --rewrite-db 场景下更要小心。

还有一个很容易忽略的点:全备文件里通常也包含了 CHANGE MASTER、GTID_PURGED 之类的语句。如果主从环境里直接重放,可能会破坏复制关系。恢复前用 grep -n "GTID_PURGED\|CHANGE MASTER\|SET @@GLOBAL.GTID" 检查一遍,必要时注释掉。

一个真实场景:误删 30 行数据的完整恢复

场景是这样的:运营同事在后台批量删除了一批评论,以为删的是测试数据,实际删的是前一天的真实评论,共 30 条。发现时已经过了 40 分钟,这段时间里站点正常有新评论进来。

恢复的流程我按顺序写下来,因为这些步骤顺序错了就白干:

第一步,止血。立刻把应用层对那张表的写操作停掉,或者直接把站点切成维护页。不要急着恢复,因为你还不知道边界在哪,边恢复边写只会让状态更乱。

第二步,确认起点。找到最近一次全备的时间,从那个时间点开始扫 binlog,定位删除事件:

mysqlbinlog --start-datetime="2026-11-15 19:00:00" \
  --base64-output=DECODE-ROWS -v mysql-bin.000042 \
  | grep -n -A3 "DELETE FROM .typecho_comments"

拿到删除事件对应的 # at 位置,以及它前面最后一个正常事件的 end_log_pos。

第三步,建临时库演练。绝对不要直接在生产库上试。开一个 zz1984_recover 空库,先把全备导入,再把 binlog 重放到删除事件之前,确认数据形态是对的。这一步多花二十分钟,能救回你整个晚上。

第四步,生成反向语句。如果只是删了行、没有后续写入冲突,最省事的做法是用 mysqlbinlog 解出被删的完整行,手工拼 INSERT。ROW 格式配合 -v 输出,每列的值都有,主键、时间戳、状态字段都在,照着拼就行。行数不多(30 行)时这是最快的路。

如果行数上千,就不要手拼了,直接用全备 + binlog 重放到一个临时库,然后用 INSERT INTO ... SELECT 从临时库把缺失的行补回生产库:

INSERT INTO zz1984.typecho_comments
SELECT * FROM zz1984_recover.typecho_comments c
WHERE c.coid NOT IN (SELECT coid FROM zz1984.typecho_comments);

第五步,验证。数量对不对(30 条)、主键有没有冲突、外键关联的文章还在不在、created 时间戳有没有被改成当前时间。最后一项很多人漏,用 SELECT * 插入时如果表有 ON UPDATE CURRENT_TIMESTAMP,时间会被改写。

第六步,解冻并复盘。恢复完成后解除维护页,然后写下来:这次误操作的入口是什么、后台为什么没有软删除、备份的 RPO 是多少。这些结论比恢复本身更有价值。

常见报错与处理

「Could not find first log file name in binary log index file」:索引文件被破坏,或者你在错误的目录下执行。检查 datadir 下的 mysql-bin.index 是否存在、内容是否与实际文件对应。

「ERROR 1236 (HY000) at line N: Could not execute Write_rows event on table ... Duplicate entry」:你要重放的这段 binlog 里有主键冲突,说明目标库的数据状态和你假设的不一致——要么全备导入不完整,要么跳过了某段 binlog。不要用 --force 硬灌,先回去核对起点。

「mysqlbinlog: unknown variable 'default-character-set=utf8mb4'」:这个报错很经典。mysqlbinlog 读取 my.cnf 里的客户端配置,而某些版本不认识 default-character-set。加 --no-defaults 放在所有参数最前面即可:

mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -v mysql-bin.000042

输出里全是乱码或者中文变问号:连接层字符集不对。重放时加 --default-character-set=utf8mb4(作为 mysqlbinlog 的参数,不是 mysql 客户端的),或者在 mysql 客户端前面加 SET NAMES utf8mb4;。

binlog 文件巨大,一个 4GB:先用 --start-position 精确切,别整文件过管道。同时可以加上 --stop-position,两边一夹,IO 量能降两个数量级。

让它成为可重复的流程,而不是一次手忙脚乱

恢复能力的关键不在工具本身,而在你有没有演练过。我的做法是把上面六步写成一个 recover.md 放在运维目录里,每次演练都按它走一遍,把耗时的环节标出来。第一次演练我花了三个半小时,第二次一小时四十分钟,第三次四十分钟。差距全在「找位置」和「建临时库」这两步的熟练度上。

另外三件长期要做的事:

  • 把 binlog 备份到别的机器。binlog 和数据库在同一块盘上,盘挂了就是「全备和增量一起没」。用 rsync 或者 mysqlbinlog --raw 定时拉走。
  • 给 binlog 留够磁盘。ROW 格式下高频写入的表会让 binlog 增长很快。留至少 2 倍于数据库大小的余量,并监控 SHOW BINARY LOGS 的总大小。
  • 定期做「恢复演练」而不是「备份检查」。备份检查只能证明文件生成了,恢复演练才能证明文件能用。这两件事的差别,在真出事的那天会非常具体地体现出来。

最后提醒一句:mysqlbinlog 只是工具,真正的安全边界是你的备份链路完整度。binlog 能救你,前提是全备是好的、binlog 没被 PURGE 掉、时间窗口没断档。这三件事只要有一件不成立,工具再好也没用。

Last modification:November 15th, 2026 at 08:23 pm

Leave a Comment