MySQL PITR(时间点恢复)实施步骤与命令
PITR 的核心是 “全量备份 + 二进制日志(binlog)回放”:先还原某个时间点的全量备份,再把该备份之后到目标时间点之间的 binlog 事件重新应用一遍,从而把数据库精确恢复到误操作或故障发生前的状态。
一、实施前提(必须先确认)
-- 1. 确认 binlog 已开启(log_bin=ON)
SHOW VARIABLES LIKE 'log_bin';
-- 2. 确认 binlog 格式为 ROW(基于 binlog 挖掘恢复数据的前提)
SHOW VARIABLES LIKE 'binlog_format';
-- 3. 确认 server_id 已设置、GTID 模式(如使用 GTID 恢复)
SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'gtid_mode';
-- 4. 确认 binlog 保留时间足够覆盖“上次全备 → 目标时间点”
SHOW VARIABLES LIKE 'expire_logs_days'; -- 5.7
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'; -- 8.0
前提条件汇总:
| 项目 | 要求 |
|---|---|
log_bin |
ON |
binlog_format |
ROW(数据恢复的前提) |
server_id |
非 0 |
| binlog 保留期 | 必须覆盖“最近一次全量备份”到“目标恢复点”的整个区间 |
| 备份方式 | 全量备份必须记录 binlog 位点或 GTID |
二、备份阶段
1. 备份前切换 binlog(便于划分恢复区间)
FLUSH LOGS;
SHOW MASTER STATUS;
SHOW MASTER STATUS 返回当前的 File 和 Position,作为后续增量恢复的起点参考。MySQL 8.0.22 起该命令也可用 SHOW BINARY LOG STATUS。
2. 方式一:mysqldump 逻辑全量备份(记录位点)
mysqldump --opt \
--single-transaction \
--master-data=2 \
--flush-logs \
--routines --triggers --events \
--default-character-set=utf8mb4 \
-S /tmp/mysql.sock -uroot -p \
--all-databases > /backup/full_$(date +%Y%m%d%H%M).sql
关键参数说明:
| 参数 | 作用 |
|---|---|
--single-transaction |
InnoDB 一致性快照,不锁表 |
--master-data=2 |
把备份起点的 binlog 文件名和位置以注释形式写入备份文件头部 |
--flush-logs |
备份开始时切换一个新的 binlog,使恢复区间边界清晰 |
--routines --triggers --events |
一并备份存储过程、触发器、事件 |
从备份文件头部提取位点:
grep -i "CHANGE MASTER" /backup/full_202611190930.sql
# 输出示例:
# -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=154;
MySQL 8.0.26 起
--master-data已弃用,建议改用--source-data=2,否则备份文件首行可能写入未注释的弃用警告,恢复时执行到该行会报ERROR 1064。
3. 方式二:XtraBackup 物理全量备份(大库推荐)
xtrabackup --defaults-file=/etc/my.cnf \
--host=127.0.0.1 --port=3306 \
--user=backupuser --password='******' \
--backup \
--target-dir=/backup/full_20261119
备份目录中的 xtrabackup_binlog_info 记录了 binlog 位点和 GTID:
cat /backup/full_20261119/xtrabackup_binlog_info
# 示例:mysql-bin.000012 154 3f9a...:1-1024
4. binlog 的持续保留与归档
# 定期把 binlog 从数据目录归档到备份存储(与全备分开存放)
cp /var/lib/mysql/mysql-bin.0000* /backup/binlog/
# 查看现有 binlog 列表
SHOW BINARY LOGS;
注意:若 binlog 在备份完成前被 purge,备份中记录的起点位点将不存在,直接导致 PITR 失败。因此 binlog 保留期必须覆盖“全备周期 + 恢复窗口”,并且归档副本不要与数据目录放在同一块盘上。
三、恢复阶段
1. 恢复前准备
# 停止应用写入,避免恢复期间产生新的数据变更
# 记录当前状态作为对照基线
-- 记录恢复前的 GTID 集合与 binlog 位点
SELECT @@GLOBAL.gtid_executed;
SHOW MASTER STATUS;
如果目标实例上已有数据,建议先把当前数据目录整体备份一份,再执行恢复,防止恢复过程不可逆。
2. 还原全量备份
mysqldump 逻辑备份还原:
mysql -uroot -p -S /tmp/mysql.sock < /backup/full_202611190930.sql
XtraBackup 物理备份还原(三步):
# ① prepare:使数据文件达到一致状态
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/backup/full_20261119
# ② 确认数据目录为空后 copy-back
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/backup/full_20261119
# ③ 修正属主并启动
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld
3. 定位目标恢复点
按时间定位(适合误操作时间明确):
mysqlbinlog --base64-output=decode-rows -vv \
--start-datetime="2026-11-19 09:20:00" \
--stop-datetime="2026-11-19 10:15:00" \
/backup/binlog/mysql-bin.000012 > /tmp/inspect.sql
在输出中确认误操作语句(如 DROP TABLE、DELETE)对应的 end_log_pos 和 SET TIMESTAMP。
按位置定位(更精确,推荐):
mysqlbinlog --base64-output=decode-rows -vv /backup/binlog/mysql-bin.000012 | \
grep -n -A5 -B5 "DROP TABLE"
记下误操作事件之前的 end_log_pos,作为 --stop-position。
4. 回放 binlog
方式 A:按时间点回放
mysqlbinlog \
--start-datetime="2026-11-19 09:30:00" \
--stop-datetime="2026-11-19 10:15:00" \
/backup/binlog/mysql-bin.000012 \
/backup/binlog/mysql-bin.000013 \
| mysql -uroot -p -S /tmp/mysql.sock
--start-datetime/--stop-datetime的边界是按事件时间戳匹配的,属于“模糊”定位;如果同一秒内既有正常事务又有误操作,就可能多恢复或少恢复。
方式 B:按位置回放(推荐)
# 起始位置取全备文件头部的 MASTER_LOG_POS,结束位置取误操作前事件的 end_log_pos
mysqlbinlog \
--start-position=154 \
--stop-position=4810 \
/backup/binlog/mysql-bin.000012 \
/backup/binlog/mysql-bin.000013 \
| mysql -uroot -p -S /tmp/mysql.sock
mysqlbinlog 支持在命令行中一次性列出多个 binlog 文件,会按顺序连续解析,不需要逐个文件分别执行。
方式 C:按 GTID 回放
mysqlbinlog --skip-gtids \
--include-gtids='3f9a1b2c-1111-11ee-9999-005056a1b2c3:1-1024' \
/backup/binlog/mysql-bin.000012 \
/backup/binlog/mysql-bin.000013 \
| mysql -uroot -p -S /tmp/mysql.sock
说明:
--include-gtids限定只回放指定 GTID 区间;--skip-gtids让回放时不校验 GTID 是否已执行,避免与目标实例已有 GTID 冲突;- 若目标实例
gtid_executed为空,也可以直接使用备份文件中记录的SET @@GLOBAL.GTID_PURGED来设置 GTID 集合。
5. 恢复后验证
-- 1. 校验目标表记录数与恢复前基线是否一致
SELECT COUNT(*) FROM your_db.your_table;
-- 2. 校验关键数据是否已回到误操作前状态
SELECT * FROM your_db.your_table WHERE id = 12345;
-- 3. 确认 GTID 与位点状态
SELECT @@GLOBAL.gtid_executed;
SHOW MASTER STATUS;
-- 4. 检查错误日志
tail -100 /var/log/mysql/error.log
验证通过后再恢复应用写入。
四、常见误区与注意事项
| 问题 | 说明 |
|---|---|
| binlog 未开或格式非 ROW | 无法做基于 binlog 的数据级 PITR,只能回到全备时刻 |
| binlog 被 purge | 备份记录的起点位点不存在,PITR 直接失败;需保证保留期覆盖恢复窗口 |
--master-data=2 弃用警告 |
MySQL 8.0.26+ 恢复时报 ERROR 1064,应改用 --source-data=2,或手动注释警告行 |
ERROR 1840 |
@@GLOBAL.GTID_PURGED 只能在 gtid_executed 为空时设置;多次恢复前需先 RESET MASTER 清空 |
| 时间点边界模糊 | --stop-datetime 按事件时间戳匹配,秒级内多事务可能误恢复;优先用 --stop-position |
| 恢复方向错误 | 不要把 binlog 回放到原库后又重新导入全备,顺序必须是“先全备、后 binlog” |
| 恢复环境未隔离 | 建议先恢复到临时实例验证数据,再切回生产,避免二次损坏 |
| 大 binlog 解析空间不足 | mysqlbinlog 输出到 /tmp 时可能因空间不足报 Errcode 28,应输出到大容量目录 |
五、典型完整流程(一句话版)
FLUSH LOGS → 记录位点 → mysqldump --master-data=2 全备 → 持续归档 binlog
→ 故障发生 → 停止写入 → 还原全备 → 定位误操作前 end_log_pos
→ mysqlbinlog --start-position=全备位点 --stop-position=误操作前位点 | mysql
→ 校验数据
浙公网安备 33010602011771号