6.5.4 增量备份与二进制日志恢复（PITR）

本节聚焦增量备份与二进制日志恢复（PITR），目标是在最小数据丢失（RPO）与可控恢复时间（RTO）下，实现从全量备份到指定时间点的完整回滚能力。核心由三部分组成：定期全量备份、连续 binlog 归档、可验证的恢复流程。

原理草图（全量备份 + binlog 时间点恢复）：

文章图片

一、前置配置与安装校验
1）确认 MySQL 已启用 binlog（需重启生效） 

# /etc/my.cnf
[mysqld]
server_id=1
log_bin=/var/lib/mysql/binlog
binlog_format=ROW
binlog_expire_logs_seconds=604800
gtid_mode=ON
enforce_gtid_consistency=ON

参数说明：
- log_bin：启用二进制日志并指定路径
- binlog_format=ROW：记录行级变化，适合精准恢复
- binlog_expire_logs_seconds：日志保留周期
- gtid_mode：启用 GTID 便于跨实例回放

2）重启与检查 

systemctl restart mysqld
mysql -uroot -p -e "SHOW VARIABLES LIKE 'log_bin';"
mysql -uroot -p -e "SHOW BINARY LOGS;"

预期效果：log_bin 为 ON，并能看到 binlog.00000x 列表。

二、增量备份方案示例（全量 + binlog 归档）
1）全量备份（逻辑示例） 

mkdir -p /backup/full
mysqldump -uroot -p --single-transaction --routines --triggers \
  --master-data=2 --set-gtid-purged=OFF \
  --all-databases > /backup/full/full_20240101.sql

关键说明：
- --single-transaction：避免锁表
- --master-data=2：在备份中记录 binlog 位点
- --set-gtid-purged=OFF：避免 GTID 冲突

2）binlog 归档（每日轮转或实时同步） 

mkdir -p /backup/binlog
mysql -uroot -p -e "FLUSH BINARY LOGS;"
rsync -av /var/lib/mysql/binlog.* /backup/binlog/

预期效果：备份目录中有完整连续的 binlog 文件。

三、PITR 恢复流程（按时间点）
场景：误删发生在 2024-01-01 10:15:00，需恢复到 10:14:59。

1）恢复全量备份 

systemctl stop mysqld
rm -rf /var/lib/mysql/*
systemctl start mysqld

mysql -uroot -p < /backup/full/full_20240101.sql

2）定位 binlog 时间范围 

mysqlbinlog --base64-output=DECODE-ROWS -vv /backup/binlog/binlog.000123 \
  | head -n 40

说明：通过 mysqlbinlog -vv 查看事件时间戳，确定起止范围。

3）生成 PITR 回放 SQL 

mysqlbinlog \
  --start-datetime="2024-01-01 00:00:00" \
  --stop-datetime="2024-01-01 10:14:59" \
  /backup/binlog/binlog.000123 /backup/binlog/binlog.000124 \
  > /backup/pitr_20240101.sql

4）回放到目标实例 

mysql -uroot -p < /backup/pitr_20240101.sql

5）验证与切换 

mysql -uroot -p -e "SELECT COUNT(*) FROM app.orders;"

预期效果：业务关键表行数与业务日志一致。

四、GTID 环境回放示例 

mysqlbinlog --include-gtids='uuid:1-1000' /backup/binlog/binlog.000123 \
  > /backup/pitr_gtid.sql
mysql -uroot -p < /backup/pitr_gtid.sql

说明：GTID 模式下以事务范围精确控制回放。

五、常见排错与处理
1）报错：The server is not configured as a slave
- 原因：误使用 --read-from-remote-server
- 处理：使用本地 binlog 文件回放

2）报错：Duplicate entry
- 原因：重复回放或恢复基线不一致
- 处理：重新从正确全量基线开始恢复，或使用 --stop-position 控制

3）报错：GTID_PURGED can only be set when GTID_EXECUTED is empty
- 原因：导入备份时 --set-gtid-purged=ON
- 处理：重新导出备份时设置 --set-gtid-purged=OFF

六、实战练习
1）练习一：构造误删并 PITR
- 在测试库执行： 

mysql -uroot -p -e "CREATE DATABASE testpitr; CREATE TABLE testpitr.t(id INT);"
mysql -uroot -p -e "INSERT INTO testpitr.t VALUES(1),(2),(3);"
  • 记录当前时间后执行误删： 
date
mysql -uroot -p -e "DELETE FROM testpitr.t WHERE id=2;"
  • 使用 PITR 恢复到误删前 1 秒，并验证结果行数为 3。

2）练习二：定位 binlog 精确位置
- 使用 mysqlbinlog -vv 查找 DELETE 语句时间戳
- 用 --stop-datetime 回放到准确时间

七、要点小结
- PITR 依赖“可用全量备份 + 完整 binlog”。
- 必须确保 binlog 归档可靠、连续且可访问。
- 恢复流程要标准化：基线恢复 → 过滤回放 → 校验切换。