Percona XtraBackup基于时间点恢复单个数据库

Percona XtraBackup基于时间点恢复单个数据库

测试环境

mysql> show tables from test;
+----------------+
| Tables_in_test |
+----------------+
| bar            |
| baz            |
| foo            |
+----------------+
3 rows in set (0.00 sec)

第一次备份时，表foo和bar都含有五行记录。

mysql> select count(*) from foo;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)
​
mysql> select count(*) from bar;
+----------+
| count(*) |
+----------+
| 5        |
+----------+
1 row in set (0.00 sec)
​
mysql> select count(*) from baz;
+----------+
| count(*) |
+----------+
| 0        |
+----------+
1 row in set (0.00 sec)

既然我们只是想恢复有些表，在做备份之前，需要做一些准备：记录数据库结构。用mysqldump命令即可。本次测试，我会记录每个数据库的结构，便于做基于时间点的部分恢复，当然，你也可以使用--all-databases，记录所有数据库的结构：

mysqldump --no-data --set-gtid-purged=OFF --triggers --routines --events test > test_structure.sql
mysqldump --no-data --set-gtid-purged=OFF --triggers --routines --events sbtest > sbtest_structure.sql

然后，开始执行备份操作：

xtrabackup --parallel=8 --target-dir=./full_backup --backup

备份结束。

现在，开始做一些测试工作。

mysql> update foo set f1=f1*2;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5 Changed: 5 Warnings: 0
​
mysql> select * from foo;
+----+------+
| id | f1   |
+----+------+
|  1 |    2 |
|  2 |    4 |
|  3 |    6 |
|  4 |    8 |
|  5 |   10 |
+----+------+
5 rows in set (0.00 sec)

先更新。然后删除：

mysql> drop table foo;
Query OK, 0 rows affected (0.02 sec)
​
mysql> delete from bar;
Query OK, 5 rows affected (0.01 sec)

最终，再插入一些数据：

mysql> insert into bar(f1) values(6),(7),(8),(9),(10);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
​
mysql> insert into baz(f1) values(1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

假设这里的drop、delete都是误操作。现在需要还原表foo、bar。

 

 

首先、需要prepare备份

既然，我们只是想还原test数据库中的部分表，在对备份做prepare的时候，需要加上参数--export，导出表空间以便后面可以导入。

xtrabackup --prepare --export --target-dir=./full_backup

现在数据库test目录不仅包含表定义文件(.frm,仅限于8.0之前版本)，也包含了表空间文件(.idb)和配置文件(.cfg)。

因为需要将表还原到drop和delete之前，所以要找到备份的二进制日志和具体的位置。这些可以从xtrabackup_binlog_info文件找到：

$ cat full_backup/xtrabackup_binlog_info
master-bin.000004 1601 0ec00eed-87f3-11eb-acd9-98af65266957:1-56

现在，可以执行恢复了。

 

首先，从备份中还原表foo。还原单个表空间，需要执行alter table ... import tablespace命令。该命令假设表已经存在于server上。然而，在我们这个测试中，表已经被drop了，需要重新创建它。

我们可以用指向备份的数据库结构test_structure.sql来创建整个库。

如果不想这些管理性的语句被复制，可以在session级别关闭binlog：

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source test_structure.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
....

表重建后，将其discard掉：

mysql> alter table foo discard tablespace;
Query OK, 0 rows affected (0.01 sec)

在另外一个终端，从备份中将表空间和配置文件拷贝到数据库目录：

cp full_backup/test/foo.{ibd,cfg} var/mysqld.1/data/test/

最后，导入表空间：

mysql> alter table foo import tablespace;
Query OK, 0 rows affected (0.05 sec)

test库中，其它的表，重复上面的操作即可。

现在可以开启binlog了。

 

也可以使用一个脚本：

for table in `mysql test --skip-column-names --silent -e "show tables"`
> do
>   mysql test -e "set sql_log_bin=0; alter table $table discard tablespace"
>   cp full_backup/test/$table.{ibd,cfg} var/mysqld.1/data/test/
>   mysql test -e "set sql_log_bin=0; alter table $table import tablespace"
> done

 

现在表被还原了，但是没有备份之后的数据。需要从binlog中恢复数据。

mysql> select * from foo;
+----+------+
| id | f1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
+----+------+
5 rows in set (0.00 sec)

mysql> select * from bar;
+----+------+
| id | f1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
+----+------+
5 rows in set (0.00 sec)

mysql> select * from baz;
Empty set (0.00 sec)

首先要找到，误操作的GTID。

查看一下有哪些binlog：

mysql> show binary logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |   1527476 |
| master-bin.000002 |      3035 |
| master-bin.000003 |      1987 |
| master-bin.000004 |      2466 |
| master-bin.000005 |       784 |
+-------------------+-----------+
5 rows in set (0.00 sec)

我们需要从master-bin.000004的1601位置开始解析：

mysqlbinlog --start-position=1601 -vvv --base64-output=decode-rows --database=test master-bin.000004 master-bin.000005 > binlog_test.sql

 

这个文件不是用来做恢复的，只是为了找到drop table和delete事件。

这里在位置2007和2123找到了对应的GTID：0ec00eed-87f3-11eb-acd9-98af65266957:58和0ec00eed-87f3-11eb-acd9-98af65266957:59

# at 2007
#210321 13:29:58 server id 1 end_log_pos 2123 CRC32 0xd1eb9854 Query thread_id=138 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1616322598/*!*/;
DROP TABLE `foo` /* generated by server */
/*!*/;
# at 2123
#210321 13:30:08 server id 1 end_log_pos 2188 CRC32 0xfc9b2088 GTID last_committed=7 sequence_number=8 rbr_only=yes original_committed_timestamp=0 immediate_commit_timestamp=0 transaction_length=0
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=0 (1970-01-01 02:00:00.000000 EET)
# immediate_commit_timestamp=0 (1970-01-01 02:00:00.000000 EET)
/*!80001 SET @@session.original_commit_timestamp=0*//*!*/;
/*!80014 SET @@session.original_server_version=0*//*!*/;
/*!80014 SET @@session.immediate_server_version=0*//*!*/;
SET @@SESSION.GTID_NEXT= '0ec00eed-87f3-11eb-acd9-98af65266957:59'/*!*/;
# at 2188
#210321 13:30:08 server id 1 end_log_pos 2260 CRC32 0x1d525b11 Query thread_id=138 exec_time=0 error_code=0
SET TIMESTAMP=1616322608/*!*/;
BEGIN
/*!*/;
# at 2260
#210321 13:30:08 server id 1 end_log_pos 2307 CRC32 0xb57ecb73 Table_map: `test`.`bar` mapped to number 226
# at 2307
#210321 13:30:08 server id 1 end_log_pos 2387 CRC32 0x6770a7e2 Delete_rows: table id 226 flags: STMT_END_F
### DELETE FROM `test`.`bar`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=1 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`bar`
### WHERE
...

要注意，解码出的row event，每个受影响的行都包含一个delete事件。

我们还可以根据”Rotate to“找到对应的事件属于哪个binlog。在我们这个例子中，在找到的位置后发现了”Rotate to master-bin.000005“，所以我们只需要master-bin.000004。

 

再次执行mysqlbinlog，找出我们需要的数据：

mysqlbinlog  --start-position=1601 --exclude-gtids=0ec00eed-87f3-11eb-acd9-98af65266957:58-59 --database=test --skip-gtids=true master-bin.000004 master-bin.000005 > binlog_restore.sql

 

binlog_restore.sql文件包含了从备份到drop操作之前的所有更新操作。现在可以还原了：

mysql test < binlog_restore.sql

至此，还原结束。

 

总结

你可能保存了使用基于时间点恢复的时间，使用单个数据库还原的方法。但是要考虑到以下一些因素：

1.mysqlbinlog不支持过滤单个表，因此，你要么恢复所有的数据库，要么使用一个假的mysql server(参考：https://www.percona.com/blog/2017/10/23/mysql-point-in-time-recovery-right-way/)

2.单个数据库过滤，在基于语句格式的binlog中依赖use语句。因此，在基于行格式的binlog中，--database才可以被认为是安全。

3.如果没有使用gtid，仍然可以上面的方法。需要使用--start-position和--stop-position跳过相应的事件。

https://www.percona.com/blog/2021/04/02/percona-xtrabackup-point-in-time-recovery-for-the-single-database/