binlog 与时间点恢复 - MySQL 教程

讲解

MySQL 有几类日志各司其职：错误日志记录启动关闭和异常；慢查询日志记录慢 SQL（前面学过）；二进制日志 binlog 记录所有对数据的变更（DDL 和 DML），它有两个使命——主从复制的数据源、时间点恢复的素材。InnoDB 内部还有 redo log（崩溃恢复）和 undo log（事务回滚与 MVCC），属于引擎内部机制，日常管理不直接操作。

binlog 有三种格式：STATEMENT 记 SQL 原文（有不确定性函数时主从不一致的风险）、ROW 记每一行的变化（默认、最可靠、体积大）、MIXED 混合。保留时长由 binlog_expire_logs_seconds 控制（默认 30 天）。FLUSH BINARY LOGS 切换到一个新的 binlog 文件，SHOW MASTER STATUS 看当前写到哪个文件、什么位置，SHOW BINLOG EVENTS 可以翻看日志里的事件。

时间点恢复（PITR）解决的是这类事故：昨晚 2 点做了全量备份，今天下午 3 点有人误删了表。恢复流程是：① 恢复昨晚的全量备份；② 用 mysqlbinlog 工具把备份之后到误删之前这段时间的 binlog 导出成 SQL（--start-datetime/--stop-position 划定区间）；③ 重放这段 SQL。这样数据回到误删前一秒，比「只能回到昨晚」少损失近一天的数据。这也是主从复制的底层原理，后面的复制章节会再见到 binlog。

示例

查看 binlog 的当前状态：写到哪个文件、格式、保留策略：

SHOW MASTER STATUS;

SHOW BINARY LOGS;

SHOW VARIABLES LIKE 'binlog_format';

SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

制造一些变更，然后用 FLUSH BINARY LOGS 切换到新文件（模拟「备份后产生的增量」落进了新的 binlog）：

CREATE TABLE recovery_demo (id INT PRIMARY KEY, note VARCHAR(50));

INSERT INTO recovery_demo VALUES (1, '全量备份时已有的数据'), (2, '备份之后新增的数据');

FLUSH BINARY LOGS;

SHOW MASTER STATUS;

时间点恢复的关键一步：用 mysqlbinlog 把指定时间段的 binlog 导出成 SQL 再重放（服务器端工具，示意流程）：

# 仅示意：mysqlbinlog 需直接读取 binlog 文件，本验证容器的镜像未包含该工具
# 导出 14:00 到误操作前（14:30）之间的所有变更
mysqlbinlog --start-datetime="2026-08-10 14:00:00" \
            --stop-datetime="2026-08-10 14:30:00" \
            /var/lib/mysql/binlog.000003 > /tmp/recover.sql

# 在恢复出的全量备份之上重放增量
mysql -uroot -p shop < /tmp/recover.sql

不用任何工具，也可以直接翻看 binlog 里记录的事件类型：

SHOW BINLOG EVENTS LIMIT 5;

常见坑

根本没开 binlog：8.0 默认开启（log_bin=ON），但老版本或某些精简配置默认关闭。出了事才发现没 binlog，PITR 无从谈起。部署时第一件事确认 log_bin。
binlog 和数据同盘：磁盘损坏时数据文件和 binlog 一起没了。重要系统把 binlog 目录放到独立磁盘。
保留期设太短：binlog_expire_logs_seconds 设成一天，遇到「上周的数据想找回」就没素材了。保留期要覆盖备份周期。
恢复时业务还在写：PITR 重放期间没有暂停写入，新旧数据交叉污染。恢复操作必须先停业务或切流量。

小结

binlog 记录全部变更，是复制与时间点恢复的基础：全量备份 + mysqlbinlog 截取重放 = 恢复到任意时间点；ROW 格式默认且最可靠，保留期要覆盖备份周期。下一章换个话题：数据的批量导入导出。