专栏首页DBA随笔一次MySQL线上数据恢复过程

一次MySQL线上数据恢复过程

一次线上数据恢复过程

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

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

有两张业务表,利用了外键进行了关联,她想删除被关联的表中的某一条数据,但是忘记写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方向的同事买了两杯瑞幸咖啡。哈哈,此处非广告。

本文分享自微信公众号 - DBA随笔(gh_acc2bbc0d447)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-07-29

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 分布式及高可用元数据采集原理

    元数据采集是元数据产品的核心部分,如何提升采集效率是需要仔细斟酌的事情,既要保持稳定性也要保持跟上主流技术的发展趋势。元数据产品从最初集中式WEB应用系统到现在...

    yuanyi928
  • mysql 类型自动化转换问题

    询问了一个朋友,他给了一个方向,mysql会自动转换类型,当你select语句的条件和数据中的type不一致的时候; 一拍大腿,是呀,之前有碰到过类似问题,如下...

    千往
  • MyCAT让人诟病的配置文件,说说破局的思路

    最近在做MySQL分布式环境的统筹管理,目前碰到的痛点是对于集群的配置管理目前是松散的,几套环境还能忍受,如果环境多了之后还是很容易凌乱,所以我们需要一套机制...

    jeanron100
  • Mysql优化-索引

    单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上; 用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引);

    剑行者
  • PyQt+SQLite构建一个简单的账号管理GUI程序

    相信有很多的测试同学,在日常的工作中都会需要去写一些辅助测试的小工具或者脚本,我们除了保证工具的可用性之外,有时还需要做一些图形界面上的开发以便在公司或者小组内...

    测试邦
  • MySql数据库大表添加字段的方法

    但是,线上的一张表如果数据量很大,执行加字段操作就会锁表,这个过程可能需要很长时间甚至导致服务崩溃,那么这样操作就有风险。

    剑行者
  • 常用SQL语句分享

    日常工作或学习过程中,我们可能会经常用到某些SQL,建议大家多多整理记录下这些常用的SQL,这样后续用到会方便很多。笔者在工作及学习过程中也整理了下个人常用的S...

    MySQL技术
  • SAS-Sql的这些用法你都会了么?

    见上图:第一部分是一个简单的查询结构,第二部分小编设置快捷键(输入Sql后)自动弹出的结构(一个左连接的基本结构),快捷键的设置见小编历史推送(点击此处自动跳转...

    Setup
  • Katalon Studio处理数据库

    在日常的测试工作中需要经常要用到:数据库的处理,执行SQL语句。 Katalon Studio可以通过定制关键字的方式连接到数据库,并执行SQL语句。 基本实现...

    Altumn
  • MySql数据库表快速复制

    剑行者

扫码关注云+社区

领取腾讯云代金券