MySQL 8.0时间点恢复:基础备份与binlog
只有昨日备份,不能自动恢复今天误删前的状态。MySQL时间点恢复需要可恢复的基础备份、与它准确对应的binlog起点,以及从该点到目标位置的连续日志。本文针对MySQL 8.0.26及以后8.0版本、GTID关闭的独立实例;MariaDB、GTID复制、加密binlog和跨版本迁移不能直接套用。
先确认材料足够,再动恢复实例
保存故障后的实例状态、原始备份和日志副本,暂停需要保留现场的清理或日志过期动作。不要执行RESET MASTER、清理binlog或覆盖原数据目录。恢复应在独立演练机完成,切断外部应用连接和自动发信、支付等动作。
| 必须具备 | 核对重点 |
|---|---|
| 已验证的基础备份 | 版本、备份结束状态、涵盖的库和对象,能够在隔离实例恢复。 |
| 对应日志坐标 | 准确的日志文件与位置,来自备份工具元数据;不是按文件时间猜。 |
| 完整连续binlog | 同一源实例,无缺段,目标事务所在日志也完整可读。 |
| 明确恢复目标 | 误操作发生的事务边界、时区和需要保留的正常业务记录。 |
普通业务库导出未记录日志坐标时,不能假定它就是可直接前滚的基础。未来可在匹配的备份方案中记录坐标,例如mysqldump的--source-data=2;它有权限与锁行为要求,不能事后给旧备份补一条猜出的坐标。
定位事件位置,不直接按一句时间跳过错误
在备份副本上用同版本mysqlbinlog查看目标附近日志。下列文件名只是示例:
umask 077
recovery_dir=$(mktemp -d /var/tmp/baole-pitr.XXXXXXXX) &&
mysqlbinlog --verify-binlog-checksum --base64-output=DECODE-ROWS -vv /srv/recovery/binlog.000022 > "$recovery_dir/inspect.txt"
先确认命令成功,再在受控环境阅读inspect.txt,核对事件位置、事务开始和提交。文件含业务数据,不上传公开网站。DECODE-ROWS用于阅读,生成的注释SQL不能直接作为恢复脚本。
时间参数适合缩小查找范围,但存在时区、同一秒多个事务和事务跨时间边界的问题。最终应选明确事件位置:从基础备份对应的起点开始,停止在误操作所在整个事务之前,不能只跳过事务中的某条语句,也不能停在BEGIN与COMMIT之间。
先恢复基础,再生成连续重放文件
把完整基础按原备份工具流程恢复到隔离MySQL 8.0实例,保留原数据库名称和依赖关系。源日志涉及的所有库应有对应基础状态;不能拿一个业务库导出,无筛选重放全实例日志,再用--force忽略缺库错误。普通导出步骤见MySQL备份与隔离恢复,它本身不等于完整时间点方案。
假设已核实起点位于binlog.000021的1234,目标之前的停止位置位于binlog.000022的5678。数字仅演示参数,必须换成你实际审定的事务边界:
每次演练重新建立私有工作目录,不复用上次的重放文件。先写入临时文件,只有生成成功才把它标记为可用:
if mkdir -m 700 "$recovery_dir/load-data" &&
mysqlbinlog --verify-binlog-checksum --base64-output=AUTO \
--local-load="$recovery_dir/load-data" \
--start-position=1234 --stop-position=5678 \
/srv/recovery/binlog.000021 /srv/recovery/binlog.000022 \
> "$recovery_dir/replay.sql.part" \
2> "$recovery_dir/replay-build.err"; then
mv "$recovery_dir/replay.sql.part" "$recovery_dir/replay.sql"
sha256sum "$recovery_dir/replay.sql"
else
echo "工作目录建立或重放生成失败,请停止并检查错误记录"
false
fi
start-position作用于列出的第一个文件,stop-position作用于最后一个文件;中间日志必须按正确顺序完整列入。检查退出码和错误后才继续。本次生成可执行内容时不使用DECODE-ROWS,不用文本搜索随意删行,也不加--database碰运气过滤跨库事务。
若源日志包含需要辅助文件的LOAD DATA事件,mysqlbinlog还会生成SQL引用的数据文件。必须保留load-data目录,核对文件和恢复实例的LOCAL INFILE许可后再执行;仅复制replay.sql并不完整。不要为跳过此类错误启用--force。
确认连接目标后,一次连接完成重放
下面3307为隔离实例示例端口。先核对实例身份、版本、GTID模式及事件调度状态,确认应用无法连接,事件调度已经关闭,恢复账号具有执行日志事件所需权限:
mysql --protocol=TCP -h 127.0.0.1 -P 3307 -u recovery_admin -p -e "SELECT @@server_uuid,@@hostname,@@port,@@version,@@gtid_mode,@@event_scheduler;"
结果中的GTID模式应为OFF,事件调度应为OFF;不符合时先停止并调整隔离实例。只有确认目标及基础恢复正确、重放材料完整后,才在同一个mysql连接里执行全部日志内容:
mysql --protocol=TCP -h 127.0.0.1 -P 3307 -u recovery_admin -p --binary-mode --skip-reconnect < "$recovery_dir/replay.sql"
--binary-mode避免二进制值被客户端当作特殊输入处理;--skip-reconnect禁止断线后换一个新会话继续,保护跨日志临时表及会话状态。连接中断就视为本次重放失败。
遇错立即停止并保留日志,不加--force继续,也不在已经部分重放的实例中反复执行同一文件。重新演练应从干净基础重新恢复,避免重复事务。binlog与客户端都必须适配源版本;压缩事务等特性也不能交给过旧客户端处理。
用业务证据确认恢复点
核对误操作之前的已知记录存在、错误事务尚未发生,以及表结构、关键计数和关联关系正确。只看到SQL执行结束不够。恢复到过去也会暂时排除目标之后的正常新数据,不能直接把这个旧状态覆盖回生产;应另外决定补回哪些记录或怎样处理后续合法事务。
记录使用的备份、日志列表、起止位置、执行结果和业务核验。基础材料缺失时先评估可恢复范围,不承诺能凭单个binlog或一个时间点找回全部数据。