「MySQL」- 恢复数据到特定时间点

  CREATED BY JENKINSBOT

问题描述

数据误删、统计某个时间点前的数据(没有时间戳字段)等等,有很多场景需要我们将数据恢复到特定时间点。

本文笔记将记录将数据恢复到特定时间点的方法。

解决方法

方法:将数据恢复到特定时间点:(1)要么拥有在该时间点创建的备份;(2)要么需要知晓 SQL 语句执行历史,在曾经备份基础上进行 SQL 重放。

对比:对于(1)方法,不具有普适性,很难预测未来需要哪些时间点的数据。我们通常使用(2)方法,在已有备份的基础上,使用 binlog 重放。

补充:当然除了 binlog 以外,只要有 SQL 执行历史,在理论上都可以用于重放。

mysqldump + binlog

过程:将使用 mysqldump 创建的备份导入到数据库,然后再进行 binlog 重放。
要求:对于使用 mysqldump 备份,需要在创建备份时记录 binlog 位置,以在重放时指定该位置。

XtraBackup + binlog

过程:将使用 Xtrabackup 创建的备份导入到数据库,然后再进行 binlog 重放。
补充:对于使用 XtraBackup 备份,在备份目录中保存有 binlog 位置,可以直接恢复数据。

使用 XtraBackup + binlog 恢复

前提条件及注意事项

1)要拥有历史备份数据,以作为 binlog 重放的基础;
2)在 binlog 中包含众多数据库的修改历史,注意过滤以防止影响其他数据库;

第一步、使用备份恢复

使用 Xtrabackup 恢复数据,数据恢复过程不再赘述。

第二步、确定重放范围(关键步骤)

查看数据库当前 binlog 文件状态,以确定我们即将使用的 binlog 文件仍存在:

mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       126 |
| mysql-bin.000002 |      1306 |
| mysql-bin.000003 |       126 |
| mysql-bin.000004 |       497 |
+------------------+-----------+

查看当前正在使用的 binlog 文件(File):

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      497 |              |                  |
+------------------+----------+--------------+------------------+

查看在创建 XtraBackup 备份时 binlog 的位置:

# cat /path/to/backup/xtrabackup_binlog_info
mysql-bin.000003      57

现在我们需要确定数据恢复的结束时间点(数据毁坏之前):

# 我们之需要关注  mysql-bin.000003 与 mysql-bin.000004 之间的历史
mysqlbinlog "/path/to/datadir/mysql-bin.000003" "/path/to/datadir/mysql-bin.000004" \
    --start-position=57 > mybinlog.sql

通过上述步骤,可以确定重放开始位置以及结束时间,接下来进行重放即可。

第三步、进行历史重放

#!/bin/sh

mysqlbinlog /path/to/datadir/mysql-bin.000003 /path/to/datadir/mysql-bin.000004 \
    --start-position=57 --stop-datetime="11-12-25 01:00:00" | mysql -u root -p

参考文献

Percona XtraBackup Docs/Point-In-Time recovery