mysql"闪回"技术恢复误删除误更改的数据

    相信很多人都遇到过忘带where条件或者where条件漏写了一个和写错了的情况,结果执行了delete/update后把整张表的数据都给改了。传统的解决方法是:利用最近的全量备份+增量binlog备份,恢复到误操作之前的状态,但是此方法有一个弊端,那就是随着表的记录增大,binlog的增多,恢复起来会很费时费力。

 

现在有一个简单的方法,可以恢复到误操作之前的状态。听起来这方法似乎利用的是Oracle的闪回功能,但MySQL目前(包括最新的V5.7版本与MariaDB10.1分支版本)还不具有这样的功能,不过,我们完全可以模拟出这一功能来。使用该方法前,记得要将binlog日志设置为binlog_format = ROW,如果是STATEMENT,这个方法是无效的。切记!!

 

在讲解闪回恢复前,我们首先要了解一下闪回的概念。闪回就类似Windows里的回收站功能。MySQL实现闪回,是通过模拟的方式(解析binlog)来恢复数据。2012年,原淘宝彭立勋针对MySQL5.5版本,对rows格式的binlog可以进行逆向操作。delete反向生成insert,update生成反向的update。2016年,原支付宝楼方鑫针对MySQL5.6版本也同样实现了此功能。

 

但目前还没有针对MariaDB 10的版本,由于binlog格式与MySQL不同,无法使用此工具恢复数据,所以笔者也是通过解析binlog,用sed命令进行逆向操作,针对delete反向生成replace into,update反向生成replace into,适用于MySQL/MariaDB/Percona版本。

 

笔者借鉴了Percona官方博客的思路实现。https://www.percona.com/blog/2012/10/19/recovering-from-a-bad-update-statement/

 

注!DROP/TRUNCATE操作无法使用闪回,需要全量备份+binlog增量备份恢复。

 

下面笔者通过一个示例给大家演示一下。

 

u  误删除闪回步骤

现有一张test表,总共有10条记录,查询结果如图(1)所示。

 

图(1) test表数据

 

 

下面在执行delete删除操作的时候,忘加where条件,导致全表记录被删除,如图(2)所示。

 

图(2) test表的记录全表被删除

 

 

 

恢复过程

1、找到误删除的binlog保存下来          

# mysqlbinlog --base64-output=decode-rows -v -v --start-datetime="2016-05-04

13:15:00" mysql-bin.000001 | grep -B 50 '### DELETE FROM `test`.`test`' | more

 

通过时间定位到全表删除的语句,如图(3)所示。

 

图(3) delete删除

 

 

此时,请注意看笔者用红色框标出的@1,@2,@3,@4,这里对应原表字段分别是id,k,c,pad,后面的=等号则对应的原表被删除的记录。

 

下一步,只需把这一段binlog保存下来,以待后面的恢复,执行命令如下。

# mysqlbinlog --base64-output=decode-rows -v -v mysql-bin.000001 | sed -n

'/end_log_pos 1049/,/COMMIT/p' | tail -n +2 > /root/recover.binlog

 

查看recover.binlog,我们得到了如下内容:

# cat /root/recover.binlog

### DELETE FROM `test`.`test`

### WHERE

###   @1=1 /* INT meta=0 nullable=0 is_null=0 */

###   @2=0 /* INT meta=0 nullable=0 is_null=0 */

###   @3='' /* STRING(360) meta=61032 nullable=0 is_null=0 */

###   @4='qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' /*

STRING(180) meta=65204 nullable=0 is_null=0 */

### DELETE FROM `test`.`test`

### WHERE

###   @1=2 /* INT meta=0 nullable=0 is_null=0 */

###   @2=0 /* INT meta=0 nullable=0 is_null=0 */

###   @3='' /* STRING(360) meta=61032 nullable=0 is_null=0 */

###   @4='qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' /*

STRING(180) meta=65204 nullable=0 is_null=0 */

### DELETE FROM `test`.`test`

### WHERE

###   @1=3 /* INT meta=0 nullable=0 is_null=0 */

###   @2=0 /* INT meta=0 nullable=0 is_null=0 */

###   @3='' /* STRING(360) meta=61032 nullable=0 is_null=0 */

###   @4='qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' /*

STRING(180) meta=65204 nullable=0 is_null=0 */

### DELETE FROM `test`.`test`

### WHERE

###   @1=4 /* INT meta=0 nullable=0 is_null=0 */

###   @2=0 /* INT meta=0 nullable=0 is_null=0 */

###   @3='' /* STRING(360) meta=61032 nullable=0 is_null=0 */

###   @4='qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' /*

STRING(180) meta=65204 nullable=0 is_null=0 */

### DELETE FROM `test`.`test`

### WHERE

###   @1=5 /* INT meta=0 nullable=0 is_null=0 */

###   @2=0 /* INT meta=0 nullable=0 is_null=0 */

###   @3='' /* STRING(360) meta=61032 nullable=0 is_null=0 */

###   @4='qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' /*

STRING(180) meta=65204 nullable=0 is_null=0 */

### DELETE FROM `test`.`test`

### WHERE

###   @1=6 /* INT meta=0 nullable=0 is_null=0 */

###   @2=0 /* INT meta=0 nullable=0 is_null=0 */

###   @3='' /* STRING(360) meta=61032 nullable=0 is_null=0 */

###   @4='qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' /*

STRING(180) meta=65204 nullable=0 is_null=0 */

### DELETE FROM `test`.`test`

### WHERE

###   @1=7 /* INT meta=0 nullable=0 is_null=0 */

###   @2=0 /* INT meta=0 nullable=0 is_null=0 */

###   @3='' /* STRING(360) meta=61032 nullable=0 is_null=0 */

###   @4='qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' /*

STRING(180) meta=65204 nullable=0 is_null=0 */

### DELETE FROM `test`.`test`

### WHERE

###   @1=8 /* INT meta=0 nullable=0 is_null=0 */

###   @2=0 /* INT meta=0 nullable=0 is_null=0 */

###   @3='' /* STRING(360) meta=61032 nullable=0 is_null=0 */

###   @4='qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' /*

STRING(180) meta=65204 nullable=0 is_null=0 */

### DELETE FROM `test`.`test`

### WHERE

###   @1=9 /* INT meta=0 nullable=0 is_null=0 */

###   @2=0 /* INT meta=0 nullable=0 is_null=0 */

###   @3='' /* STRING(360) meta=61032 nullable=0 is_null=0 */

###   @4='qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' /*

STRING(180) meta=65204 nullable=0 is_null=0 */

### DELETE FROM `test`.`test`

### WHERE

###   @1=10 /* INT meta=0 nullable=0 is_null=0 */

###   @2=0 /* INT meta=0 nullable=0 is_null=0 */

###   @3='' /* STRING(360) meta=61032 nullable=0 is_null=0 */

###   @4='qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' /*

STRING(180) meta=65204 nullable=0 is_null=0 */

# at 1049

#160504 13:16:08 server id 17128  end_log_pos 1076      Xid = 3225

COMMIT/*!*/;

 

2、逆向将delete转为replace into可执行SQL语句

# cat recover.binlog | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE

FROM/\nREPLACE INTO/g;s/WHERE/SELECT/g' | sed -r 's/(@4.*),/\1;/g' | sed

's/@[1-9].*=//g' > /root/recover.sql

 

注:@4代表字段的最后一位,如果你的表有10个字段,要将其改为@10

 

查看recover.sql,我们得到了如下内容:

# cat /root/recover.sql

REPLACE INTO `test`.`test`

SELECT

  1 ,

  0 ,

  '' ,

  'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' ;

 

REPLACE INTO `test`.`test`

SELECT

  2 ,

  0 ,

  '' ,

  'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' ;

 

REPLACE INTO `test`.`test`

SELECT

  3 ,

  0 ,

  '' ,

  'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' ;

 

REPLACE INTO `test`.`test`

SELECT

  4 ,

  0 ,

  '' ,

  'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' ;

 

REPLACE INTO `test`.`test`

SELECT

  5 ,

  0 ,

  '' ,

  'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' ;

 

REPLACE INTO `test`.`test`

SELECT

  6 ,

  0 ,

  '' ,

  'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' ;

 

REPLACE INTO `test`.`test`

SELECT

  7 ,

  0 ,

  '' ,

  'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' ;

 

REPLACE INTO `test`.`test`

SELECT

  8 ,

  0 ,

  '' ,

  'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' ;

 

REPLACE INTO `test`.`test`

SELECT

  9 ,

  0 ,

  '' ,

  'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' ;

 

REPLACE INTO `test`.`test`

SELECT

  10 ,

  0 ,

  '' ,

  'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' ;

 

 

3、恢复

最激动人心的一幕到来了,现在就要进行最后一步的恢复操作了,把这些binlog转换成SQL语句,然后将其导入进去,执行命令如下:

# source /root/recover.sql

大功告成!你也快动手试试吧!友情提醒:千万不要在生产环境下测试哦。

 

u  误更改update闪回步骤

1、同理第一步

 

2、逆向将delete转为replace into可执行SQL语句

 

# cat recover.binlog | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;' | sed -n '/UPDATE

`test`.`test`/,/SET/p'| sed 's/UPDATE/REPLACE

INTO/g;s/WHERE/SELECT/g;s/SET/\n/g' |sed -r 's/(@4.*),/\1;/g' | sed

's/@[1-9].*=//g' > /root/recover.sql

 

3、同理第三步

posted @ 2017-12-28 09:53  一米八大高个儿  阅读(1689)  评论(0编辑  收藏  举报