微信号:QunarTL

介绍:Qunar技术沙龙是去哪儿网工程师小伙伴以及业界小伙伴们的学习交流平台.我们会分享Qunar和业界最前沿的热门技术趋势和话题;为中高端技术同学提供一个自由的技术交流和学习分享平台.

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

2016-06-17 10:23 贺春旸 高效运维

引言

相信很多人在执行 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官方博客的思路实现,请参考如下链接:

https://www.percona.com/blog/2012/10/19/recovering-from-a-bad-update-statement/

如何闪回,步骤是什么?

情景再现:

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管理之道·第二版》!

转自:高效运维

长按识别二维码,浏览器打开

秒下大讲堂在线课程APP

支持Android和IOS

Qunar最新最热门的在线视频课程尽在其中!



 
Qunar技术沙龙 更多文章 CTO寄语应届生 高效能人士的七个习惯 百度员工离职总结:如何做个靠谱好员工? 学不进去?!去看看大脑怎么想 一个故事教你轻松学会课程设计
猜您喜欢 【Day One】阿里百川创业大赛实战训练营 iOS-你真的会用UIMenuController吗? iOS 9.1 深坑体验及其破解之道 从Theano到Lasagne:基于Python的深度学习的框架和库(译文) Paxos理论介绍(1): 朴素Paxos算法理论推导与证明