binlog 开启与 point-in-time 恢复

导学

备份是"快照"，只能恢复到备份时刻。binlog（二进制日志）记录了备份之后的所有数据变更，两者配合才能实现"精确到秒"的 point-in-time 恢复（PITR）。这是数据库运维的终极兜底手段——误删表、误更新、程序 bug 导致的数据污染，都能通过 PITR 挽回。

定义

binlog（Binary Log）：MySQL 服务器层记录所有数据变更（DDL 和 DML）的二进制日志文件，用于主从复制和 point-in-time 恢复。Point-in-Time Recovery（PITR）：先恢复到最近的全备，再通过重放 binlog 将数据推进到误操作之前的精确时间点。

场景一：确认 binlog 已开启

操作语句：

SHOW VARIABLES LIKE 'log_bin';

SHOW VARIABLES LIKE 'binlog_format';

结果解读：

• log_bin = ON 是 PITR 的前提。如果为 OFF，需在 my.cnf 中加 log_bin=mysql-bin 并重启
• binlog_format = ROW 是推荐格式：记录每行数据的变更前后镜像，point-in-time 恢复最精确
• STATEMENT 格式记录 SQL 语句，某些函数（UUID()、RAND()）主从不一致；MIXED 自动选择但不可控

场景二：查看 binlog 文件列表和当前位置

操作语句：

SHOW BINARY LOGS;

SHOW MASTER STATUS;

结果解读：

• SHOW BINARY LOGS 列出所有 binlog 文件及大小，默认达到 max_binlog_size（1GB）后自动切换新文件
• SHOW MASTER STATUS 显示当前正在写入的 binlog 文件名和位置
• 全备时记录这个坐标，恢复时就知道从哪个文件的哪个位置开始重放

场景三：清理过期 binlog

操作语句：

-- 清理 2024-01-01 之前的所有 binlog
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';

-- 或清理到指定文件为止（保留该文件及之后的）
PURGE BINARY LOGS TO 'mysql-bin.000015';

验证：

SHOW BINARY LOGS;

结果解读：

• binlog 文件会持续增长，需定期清理避免撑爆磁盘
• 清理前确保：1. 所有从库已消费完要清理的 binlog；2. 已有全备覆盖要清理的时间段
• 也可在 my.cnf 中设置 expire_logs_days = 7 自动清理 7 天前的 binlog

场景四：基于时间的 point-in-time 恢复

事故场景：今天 14:30 有人执行了 DROP TABLE employees;，全备是今天凌晨 02:00 的 xtrabackup 物理备份。

恢复步骤：

# 步骤 1：查看全备中的 binlog 坐标
cat /backup/sunday_full/xtrabackup_binlog_info
# 输出：mysql-bin.000014	5678901

# 步骤 2：停止 MySQL，恢复全备（prepare + copy-back，略）

# 步骤 3：找到误删操作的时间范围，导出 binlog
mysqlbinlog \
  --start-datetime="2024-01-15 02:00:00" \
  --stop-datetime="2024-01-15 14:29:59" \
  --database=company \
  /var/lib/mysql/mysql-bin.000014 \
  /var/lib/mysql/mysql-bin.000015 \
  > /tmp/recover.sql

# 步骤 4：执行恢复的 binlog
mysql -u root -p < /tmp/recover.sql

验证：

USE company;
SELECT emp_name FROM employees;

结果解读：

• --start-datetime 从全备时刻开始，--stop-datetime 精确到误操作前一秒（14:29:59）
• --database=company 只导出 company 库的变更，减少恢复数据量
• 如果误操作是 UPDATE 而非 DROP，需要更精确地定位到具体事件位置，时间精度可能不够
• 恢复后数据状态是 14:29:59 时的状态，DROP TABLE 事件被跳过

场景五：基于位置的精确恢复

问题：--stop-datetime 精度只有秒级，如果 14:30:00 同时有合法操作和误操作，按时间恢复可能多恢复或少恢复。

操作语句：

# 步骤 1：查看 binlog 内容，找到 DROP TABLE 的精确位置
mysqlbinlog \
  --start-datetime="2024-01-15 14:25:00" \
  --stop-datetime="2024-01-15 14:35:00" \
  --base64-output=DECODE-ROWS -v \
  /var/lib/mysql/mysql-bin.000015 | grep -n "DROP TABLE"

预期输出（示例）：

# at 12345678
#240115 14:30:00 server id 1  end_log_pos 12345750 CRC32 0x12345678	Query	thread_id=123	e	exec_time=0	error_code=0
SET TIMESTAMP=1705312200/*!*/;
DROP TABLE `employees` /* generated by server */

关键信息：DROP TABLE 事件从位置 12345678 开始，到 12345750 结束。

# 步骤 2：恢复到 DROP TABLE 之前的位置（12345678 之前）
mysqlbinlog \
  --start-position=5678901 \
  --stop-position=12345677 \
  /var/lib/mysql/mysql-bin.000015 \
  | mysql -u root -p

结果解读：

• --base64-output=DECODE-ROWS -v 将 ROW 格式的 binlog 解码为人类可读格式
• # at 12345678 是该事件的起始位置，end_log_pos 12345750 是结束位置
• --stop-position=12345677 表示重放到位置 12345677 为止，不包含 DROP TABLE 事件
• 位置恢复比时间恢复更精确，是生产环境 PITR 的标准做法

场景六：从 binlog 中提取误删数据

场景：没有全备，但 binlog 是 ROW 格式且未被清理，误删了 employees 表中的白歌记录。

操作语句：

# 解码 binlog，查找 DELETE 事件
mysqlbinlog \
  --base64-output=DECODE-ROWS -v \
  /var/lib/mysql/mysql-bin.000015 | \
  grep -A 5 "DELETE FROM company.employees"

预期输出（ROW 格式解码后）：

### DELETE FROM company.employees
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='白歌' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
###   @3='技术部' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
###   @4=NULL /* DECIMAL(5,2) meta=2 nullable=1 is_null=1 */

手动构造恢复语句：

INSERT INTO employees (emp_id, emp_name, dept, score) VALUES (2, '白歌', '技术部', NULL);

结果解读：

• ROW 格式的 binlog 记录了每行变更的前后镜像，DELETE 事件包含被删除行的完整数据
• 通过 mysqlbinlog --base64-output=DECODE-ROWS -v 可以提取这些值，手动构造 INSERT 恢复
• 这是"没有备份时的最后一根稻草"，前提是 binlog 未过期且格式为 ROW

常见误区

面试考点

Q：point-in-time 恢复的完整流程？

1. 恢复最近的全备（xtrabackup copy-back 或 mysqldump 导入）；2. 找到全备时刻的 binlog 位置；3. 用 mysqlbinlog 导出从全备位置到误操作前一秒（或前一位置）的 binlog；4. 执行导出的 SQL 文件；5. 验证数据完整性。

Q：binlog 三种格式的区别？备份恢复用哪种？

STATEMENT：记录 SQL 语句，体积小，但某些函数主从不一致。ROW：记录每行变更前后镜像，体积大，但数据一致性最好，PITR 最精确。MIXED：自动选择，不可控。备份恢复推荐ROW。

Q：误删数据后没有备份，只有 binlog，能恢复吗？

如果 binlog 格式是 ROW 且未被清理，可以通过 mysqlbinlog --base64-output=DECODE-ROWS -v 解析出被删除/修改的原始数据，手动构造 SQL 恢复。但如果是 TRUNCATE 或 DROP TABLE，ROW 格式不记录行数据，无法直接恢复内容，只能恢复表结构。

Q：如何设计备份保留策略？

推荐 3-2-1 原则：3 份副本、2 种介质、1 份异地。具体：每天 xtrabackup 全备保留 7 天；binlog 保留 7~14 天；每周全备保留 4 周；每月全备保留 12 个月。定期做恢复演练。

小结

• binlog 是 point-in-time 恢复的增量数据源，必须开启且格式为 ROW
• PITR = 全备恢复 + binlog 重放，两者缺一不可
• 按时间恢复（--stop-datetime）简单但精度低；按位置恢复（--stop-position）更精确
• ROW 格式 binlog 可解码提取原始数据，是误操作后的最后一根稻草
• 定期清理 binlog（PURGE 或 expire_logs_days），但清理前确保已有全备覆盖

下一章引子：备份恢复是事后补救，而主从复制是实时冗余——让数据同时在两台服务器上存在，一台故障时另一台立即接管。