转载

学会这招,误删数据也不需要跑路?!

作者介绍

贺春旸

从事数据库管理工作多年(DBA Leader),有管理和维护高并发海量数据的经验,曾经任职于中国移动飞信,聚美优品,机锋网,普惠金融,致力于MySQL、Linux等开源技术的研究,主要主责MySQL性能调优和架构设计。

《MySQL 5.5管理之道:性能调优、高可用与监控》一书作者。

引言

相信很多人在执行 Sql 操作的时候都遇到过:

  • 忘带where条件
  • 或者where条件漏写了一个
  • 或者where条件写错了的情况

结果 执行了delete/update 后把整张表的数据都给改了(OMG,光想想都会出一声冷汗-_-|||)。

说明:之前有数据库大拿已经开发了基于MySQL 5.5和5.6的此类工具,本文主要是针对 MariaDB 10 版本,当然也适用MySQL。

传统解法是什么?

利用最近的全量备份+增量binlog备份,恢复到误操作之前的状态。

但是此方法有一个弊端,那就是 随着表的记录增大,binlog的增多,恢复起来会很费时费力

更简单的方法有么?

答案是肯定的:有!有一个简单的方法,可以恢复到误操作之前的状态。

听起来这方法似乎利用的是Oracle的闪回功能,但MySQL目前(包括最新的V5.7版本与MariaDB10.1分支版本)还不具有这样的功能,不过,我们完全可以模拟出这一功能来。

注意:

使用该方法前,记得要将binlog日志设置为 binlog_format = ROW ,如果是STATEMENT,这个方法是无效的。切记!!

先了解下什么是“闪回”?

闪回就 类似Windows里的回收站功能 。MySQL实现闪回,是通过模拟的方式(解析binlog)来恢复数据的。

注意:

DROP/TRUNCATE操作无法使用闪回,需要 全量备份+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官方博客的思路实现。

如何闪回,步骤是什么?

情景再现:

1、有一张test表,总共有10条记录,查询结果如下所示:

学会这招,误删数据也不需要跑路?!

test表数据

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

学会这招,误删数据也不需要跑路?!

test表的记录全表被删除

恢复过程:

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

学会这招,误删数据也不需要跑路?!

2、通过时间定位到全表删除的语句,如下所示:

学会这招,误删数据也不需要跑路?!

delete删除

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

3、保存这一段binlog,以待后面的恢复,执行命令如下:

学会这招,误删数据也不需要跑路?!

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

学会这招,误删数据也不需要跑路?!

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

学会这招,误删数据也不需要跑路?!

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

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

学会这招,误删数据也不需要跑路?!

5、恢复

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

# source /root/recover.sql

大功告成,所以你也快动手试试吧!

友情提醒

千万不要在生产环境下测试哦,万一……你懂得

误更改update的闪回步骤、以及通过打开MySQL安全模式禁用delete/update不加where条件,这里不加以阐述。

各位可参考作者的书《MySQL管理之道·第二版》!

原文出处——高效运维微信公众号

原文  http://www.yunweipai.com/archives/7443.html
正文到此结束
Loading...