liuziyi

liuziyi

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
→ 校验数据

posted on 2026-11-19 15:43  刘子毅  阅读(49)  评论(0)    收藏  举报

导航