作者介绍
贺春旸
从事数据库管理工作多年(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管理之道·第二版》!
原文出处——高效运维微信公众号
本文链接:https://www.yunweipai.com/7443.html
网友评论comments