测试环境
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跳过相应的事件。