一次MySQL线上数据恢复过程

AsiaYe

发布于 2019-11-06 17:38:01

7400

发布于 2019-11-06 17:38:01

文章被收录于专栏：DBA随笔

一次线上数据恢复过程

这个周末过得相当充实，当我们做一些有意思的事情的时候，就会觉得周末的时间特别长。周天晚上还发烧了，幸亏楼下的药店还开着门，下去买了点儿药，吃完睡了一觉，才感觉缓了过来，满血复活，继续上班。。。

废话不多说了，今天写一次线上的数据恢复过程，今天有一个运维的女同事不小心误删了一张表里面的数据，来找我恢复，一副很焦急的样子，当时我询问了故障的发生过程，大概如下：

有两张业务表，利用了外键进行了关联，她想删除被关联的表中的某一条数据，但是忘记写where条件了，但是好的一点是mysql中对于外键有校验，就是删除被关联的表的时候，会报一个错误：can not delete or update a parent row，及时给出了提醒。但是，重点来了，这个同事当时可能比较晕，直接把外键也给干掉了，然后紧接着把表数据删除了，当她看到:Query OK,1334 rows affected的时候，才发现自己删错了数据，本来应该只删除一部分数据的。

了解到这个情况之后，我首先询问这个表现在还有没有数据写入，答案是没有，这个表只有在每天晚上固定的时间有数据写入，嗯，这是一个好现象，没有数据写入，那么恢复起来就不是什么大问题，当时我能想到的大体上可以通过下面几个方法来恢复：

1、每天凌晨的备份，这个备份是前一天的数据，可能得到一些备份数据，但是很有可能不全，还需要应用增量的binlog来恢复数据。

2、使用DML闪回的方法。

这个场景下，数据只有1000多条，很适合使用DML闪回的方法，直接从binlog中解析出数据，但是有一个先决条件，就是binlog的模式应该是row模式，binlog_row_images的值是image，否则是无法恢复的。如下：

mysql:(none) 22:22:28>>show variables like '%binlog_row%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| binlog_row_image             | FULL  |
| binlog_rows_query_log_events | ON    |
+------------------------------+-------+
2 rows in set (0.00 sec)

接下来就要介绍DML闪回的工具了，其实也比较常见，就是binlog2sql工具，顾明思义，就是把binlog转化成sql的语句，这个工具其实是github上面的一个开源工具，这里给出地址：https://github.com/danfengcao/binlog2sql

它可以比较快速的将binlog中的DML语句进行闪回，翻译成反向的SQL语句，也就是说：

delete可以转化成insert

insert可以转化成delete

update可以转化成反向的update

大家有兴趣可以fork下来试一把。它的文件一般包含下面这么几个：

[dba_mysql binlog2sql]$ ll
total 36
-rw-r--r-- 1 root root  6603 Aug 22  2017 binlog2sql.py
-rw-r--r-- 1 root root 10293 Aug 22  2017 binlog2sql_util.py
-rw-r--r-- 1 root root  9712 Aug 22  2017 binlog2sql_util.pyc
-rw-r--r-- 1 root root    92 Aug 22  2017 __init__.py

一般我们只需要执行一下binlog2sql.py的这个文件就行，恢复的命令如下：

python binlog2sql.py -h"主机IP" -P"端口号" -u"用户名" -p"密码"  -d"数据库名称" 
-t"表名称" --start-file='binlog名称' --start-datetime='开始时间' --stop-datetime='截止时间'

这里我简单测试一下它的功能吧：

首先我们创建一张表test，然后插入数据，写一些常见的DML，如下

mysql:(none) 22:33:24>>flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql:(none) 22:33:32>>create database yeyz;Query OK, 1 row affected (0.00 sec)

mysql:(none) 22:33:42>>use yeyz;
Database changed
mysql:yeyz 22:33:50>>create table test (id int,age int);
Query OK, 0 rows affected (0.00 sec)

mysql:yeyz 22:33:52>>insert into test values (1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql:yeyz 22:33:59>>delete from test where id=2;
Query OK, 1 row affected (0.00 sec)

mysql:yeyz 22:34:09>>update test set age=4 where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

为了看的更加清楚，我们在创建表之前使用了flush logs语句对binlog进行了刷新，这样会产生一个新的binlog，有利于我们找到自己想要的那些内容。

我们进行了create，insert，delete，以及update操作，binlog中的日志如下

create table test (id int,age int)/*!*/;
# at 3184
#190729 22:33:54 server id 170  end_log_pos 329 CRC32 0x6be0c78f       Anonymous_GTID  last_committed=11       sequence_number=12

#190729 22:33:59 server id 192  end_log_pos 4272 CRC32 0xcde79f48       Rows_query
# insert into test values (1,1),(2,2),(3,3)
# at 4272
#190729 22:33:59 server id 192  end_log_pos 4320 CRC32 0x4fba59a4       Table_map: `yeyz`.`test` mapped to number 122
# at 4320
#190729 22:33:59 server id 192  end_log_pos 4382 CRC32 0x560dfd1b       Write_rows: table id 122 flags: STMT_END_F

BINLOG '
1wM/XR3AAAAAQQAAALAQAACAAClpbnNlcnQgaW50byB0ZXN0IHZhbHVlcyAoMSwxKSwoMiwyKSwo
MywzKUif580=
1wM/XRPAAAAAMAAAAOAQAAAAAHoAAAAAAAEABHlleXoABHRlc3QAAgMDAAOkWbpP
1wM/XR7AAAAAPgAAAB4RAAAAAHoAAAAAAAEAAgAC//wBAAAAAQAAAPwCAAAAAgAAAPwDAAAAAwAA
ABv9DVY=
'/*!*/;
### INSERT INTO `yeyz`.`test`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2=1 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `yeyz`.`test`
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2=2 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `yeyz`.`test`
### SET
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
###   @2=3 /* INT meta=0 nullable=1 is_null=0 */

#190729 22:34:09 server id 192  end_log_pos 6373 CRC32 0x7df990b7       Rows_query
# delete from test where id=2
# at 6373
#190729 22:34:09 server id 192  end_log_pos 6421 CRC32 0xf0be3527       Table_map: `yeyz`.`test` mapped to number 122
# at 6421
#190729 22:34:09 server id 192  end_log_pos 6465 CRC32 0xcefe8ec1       Delete_rows: table id 122 flags: STMT_END_F

BINLOG '
4QM/XR3AAAAAMwAAAOUYAACAABtkZWxldGUgZnJvbSB0ZXN0IHdoZXJlIGlkPTK3kPl9
4QM/XRPAAAAAMAAAABUZAAAAAHoAAAAAAAEABHlleXoABHRlc3QAAgMDAAMnNb7w
4QM/XSDAAAAALAAAAEEZAAAAAHoAAAAAAAEAAgAC//wCAAAAAgAAAMGO/s4=
'/*!*/;
### DELETE FROM `yeyz`.`test`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2=2 /* INT meta=0 nullable=1 is_null=0 */
# at 6465
#190729 22:34:09 server id 192  end_log_pos 6496 CRC32 0x64b2d84f       Xid = 38224


update test set age=4 where id=3
# at 10233
#190729 22:34:34 server id 192  end_log_pos 10281 CRC32 0xc3e0e780      Table_map: `yeyz`.`test` mapped to number 122
# at 10281
#190729 22:34:34 server id 192  end_log_pos 10335 CRC32 0x13134f34      Update_rows: table id 122 flags: STMT_END_F

BINLOG '
+gM/XR3AAAAAOAAAAPknAACAACB1cGRhdGUgdGVzdCBzZXQgYWdlPTQgd2hlcmUgaWQ9M9rLlWM=
+gM/XRPAAAAAMAAAACkoAAAAAHoAAAAAAAEABHlleXoABHRlc3QAAgMDAAOA5+DD
+gM/XR/AAAAANgAAAF8oAAAAAHoAAAAAAAEAAgAC///8AwAAAAMAAAD8AwAAAAQAAAA0TxMT
'/*!*/;
### UPDATE `yeyz`.`test`
### WHERE
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
###   @2=3 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
###   @2=4 /* INT meta=0 nullable=1 is_null=0 */
# at 10335
#190729 22:34:34 server id 192  end_log_pos 10366 CRC32 0x09cab7dd      Xid = 38257

这个时候，我们使用binlog2sql工具来测试一发：

[root binlog2sql]#  python binlog2sql.py -h127.0.0.1 -P4306 -udba_admin -pXXXXXXXXX  -dyeyz
 --start-file='mysqlbin.000006' --start-datetime='2019-07-29 22:30:33' --stop-datetime='2019-07-29 22:35:33'|grep yeyz
create database yeyz;
USE yeyz;
INSERT INTO `yeyz`.`test`(`age`, `id`) VALUES (1, 1); #start 4070 end 4382 time 2019-07-29 22:33:59
INSERT INTO `yeyz`.`test`(`age`, `id`) VALUES (2, 2); #start 4070 end 4382 time 2019-07-29 22:33:59
INSERT INTO `yeyz`.`test`(`age`, `id`) VALUES (3, 3); #start 4070 end 4382 time 2019-07-29 22:33:59
DELETE FROM `yeyz`.`test` WHERE `age`=2 AND `id`=2 LIMIT 1; #start 6185 end 6465 time 2019-07-29 22:34:09
UPDATE `yeyz`.`test` SET `age`=4, `id`=3 WHERE `age`=3 AND `id`=3 LIMIT 1; #start 10040 end 10335 time 2019-07-29 22:34:34

可以发现，这个工具将binlog解析成了上面的语句，然后我们可以看到自己执行过这些SQL，每个SQL后面还打上了时间戳。

那么问题来了，我们想要的不是这些，是反向的SQL命令，当然，在这个例子中，完全可以手动的去改这些SQL，但是当SQL很多的时候，有必要使用一种方法来得到反向的恢复SQL，这个需求的解决办法如下，其实上面的命令就加一个参数就行：

[root@ binlog2sql]#  python binlog2sql.py -h127.0.0.1 -P4306 -udba_admin -pXXXXXXXXXX  -dyeyz 
--start-file='mysqlbin.000006' --start-datetime='2019-07-29 22:30:33'
 --stop-datetime='2019-07-29 22:35:33' --flashback|grep yeyz 
UPDATE `yeyz`.`test` SET `age`=3, `id`=3 WHERE `age`=4 AND `id`=3 LIMIT 1; #start 10040 end 10335 time 2019-07-29 22:34:34
INSERT INTO `yeyz`.`test`(`age`, `id`) VALUES (2, 2); #start 6185 end 6465 time 2019-07-29 22:34:09
DELETE FROM `yeyz`.`test` WHERE `age`=3 AND `id`=3 LIMIT 1; #start 4070 end 4382 time 2019-07-29 22:33:59
DELETE FROM `yeyz`.`test` WHERE `age`=2 AND `id`=2 LIMIT 1; #start 4070 end 4382 time 2019-07-29 22:33:59
DELETE FROM `yeyz`.`test` WHERE `age`=1 AND `id`=1 LIMIT 1; #start 4070 end 4382 time 2019-07-29 22:33:59

可以看到，在上面的命令中加上--flashback就可以完成SQL命令的闪回了，注意结果中的两个点：

第一、结果中的SQL和我们执行的SQL顺序是相反的，也就是倒序的

第二、只出现了update、insert、以及delete操作，而没有出现create table对应的drop table操作，这也就是为什么它叫做DML闪回的原因，它只会闪回DML操作，对于DDL操作，它不会做闪回操作。

第二个特性也就是说，如果你使用了drop table的操作，那么对不起，你只能使用备份+增量binlog重放的方法来恢复数据了。还有一种情况，就是数据量比较大，binlog文件比较大的情况下，这个工具的解析速度会比较慢，一般情况下，对于几百M的数据量还是可以hold住的，一般也就几分钟搞定，如果文件大于2G，就不建议使用了，因为恢复需要的时间太长了。

总结一下：

1、binlog2sql是一个开源工具，可以提供binlog中的SQL提取功能，和DML闪回功能

2、参数一般有：用户名、IP地址、密码、账号名、数据库名称、表名称、binlog名称、开始时间、截止时间，其中账号所需要的最小权限是select、replication slave、replication client权限

3、需要binlog的格式为row格式，binlog_row_image的值为full

4、文件的大小不超过2个G

5、仅仅对DML进行闪回，对于DDL则无能为力。

最后，我们利用这个方法帮助那个运维的小姑娘恢复了数据，作为回报，她给我和另外一个MySQL方向的同事买了两杯瑞幸咖啡。哈哈，此处非广告。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2019-07-29，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server