MySQL PITR

PITR（Point-In-Time Recovery，时间点恢复）

一、什么是 MySQL PITR（一句话）

PITR = 全量备份 + Binlog -> 恢复到“任意时间点 / 任意事务位置”

能把数据库恢复到：

误删数据之前
误更新 SQL 执行前 1 秒
主库宕机最后一个已提交事务

二、PITR 能解决什么问题？

场景	是否能恢复
DROP TABLE	✅
DELETE FROM table;	✅
UPDATE 没加 WHERE	✅
主库磁盘损坏	✅
人为误操作	✅
Binlog 被删	❌
全量备份不可用	❌

👉 PITR 依赖 Binlog，Binlog 丢了 = 无法 PITR

三、PITR 的核心组成

1️⃣ 全量备份（Base Backup）

常见方式：

mysqldump
mysqlpump
Percona XtraBackup（生产推荐）

2️⃣ Binlog（二进制日志）

Binlog 记录了：

所有 DDL
所有 DML（INSERT / UPDATE / DELETE）
提交顺序

四、PITR 的前提条件（必须满足）

✅ 1. 开启 Binlog

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800   # 7 天

检查：

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';

✅ 2. 知道全量备份时的 Binlog 位点

备份时必须记录：

SHOW MASTER STATUS;

得到：

File: mysql-bin.000123
Position: 456789

这是 PITR 的起点

五、PITR 的完整恢复流程（重点）

场景：

今天 10:30 有人误执行 DELETE
希望恢复到 10:29:59

🟢 第 1 步：恢复全量备份

使用 XtraBackup（推荐）

xtrabackup --prepare --target-dir=/backup/full
systemctl stop mysqld
rsync -av /backup/full/ /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

🟢 第 2 步：确定恢复终点时间

目标时间：2025-12-16 10:29:59

🟢 第 3 步：回放 Binlog（核心）

按时间点恢复（最常用）

mysqlbinlog \
  --start-position=456789 \
  --stop-datetime="2025-12-16 10:29:59" \
  mysql-bin.000123 mysql-bin.000124 \
| mysql -u root -p

📌 说明：

start-position -> 来自全量备份时
stop-datetime -> 精确到秒
可跨多个 binlog 文件

🟢 第 4 步：验证数据

SELECT COUNT(*) FROM table;

确认无误后：

SET GLOBAL read_only = OFF;

六、常见 PITR 方式对比

方式	说明	适合
stop-datetime	恢复到某个时间	人为误操作
stop-position	恢复到某个事务	精确控制
跳过某条 SQL	手工过滤 binlog	高级运维

七、如何跳过“误操作 SQL”（高级）

1️⃣ 查看 Binlog 内容

mysqlbinlog --base64-output=DECODE-ROWS -vv mysql-bin.000124

定位到：

DELETE FROM orders;

2️⃣ 手动过滤

mysqlbinlog mysql-bin.000124 \
| sed '/DELETE FROM orders/,+5d' \
| mysql -u root -p

⚠️ 需要非常熟悉 binlog 结构，不推荐新手

八、PITR 的生产最佳实践（非常重要）

✅ 1. 使用 ROW 模式

binlog_format = ROW

记录真实行变化
避免主从不一致
PITR 更可靠

✅ 2. Binlog 与数据分盘

log-bin = /data/binlog/mysql-bin

✅ 3. Binlog 必须做备份

rsync /data/binlog backup-server:/binlog/

否则：

数据库挂了 + binlog 丢失 = 无法 PITR

✅ 4. 全量 + Binlog 自动化

推荐周期：

类型	周期
全量备份	每天
Binlog 备份	实时 / 每小时

✅ 5. 恢复必须在新实例验证

不要在生产直接恢复！

九、PITR 常见问题

Q1：PITR 的前提条件？

开启 Binlog + 有完整全量备份 + Binlog 未丢失

Q2：PITR 能恢复 truncate 吗？

❌ TRUNCATE 是 DDL，无法回滚，只能恢复到 TRUNCATE 之前

Q3：为什么推荐 ROW Binlog？

记录行级变化，避免 STATEMENT 模式的非确定性问题

Q4：PITR 和主从有什么关系？

主从依赖 Binlog，PITR 本质也是回放 Binlog