加班到2点，一不小心我把MySQL删了

大家好，我是Leo。目前在常州从事Java后端。上一篇文章我们介绍了线上数据库挂了一个节点之后，应该如何排查节点宕机问题。从select 1 ，外部统计，内部统计等一系列流程方案的介绍。这一篇我们介绍一下线上数据库误删数据后，到底是跑路还是该如何解决!

思路

本篇文章的介绍思路以下图的思维导图为大纲。也有利于读者更好的分辨可读性!

误删行

误删行这种情况应该是比较常见的，有些时候为了解决数据问题，我们直接删了这一行。删完之后才反应过来删错了。接下来我们介绍一下，我们应该如何处理!

提到误删行，就必须涉及到两个参数 binlog_format=row binlog_row_image=FULL

binlog_format=row

这个参数我们在前面介绍binlog日志的时候介绍过。主要分row， statement，mixed

这里为什么必须设置为row呢，因为只有记录详细的日志信息，作恢复数据的时候才好操作。statement肯定是不够的。mixed也是不符合的，因为完全不需要判断!

binlog_row_image=FULL

这个是由上列参数同时引入的一个新的参数。当前有两个选择项，FULL记录每一行的变更，minimal只记录影响后的行。默认使用FULL。

步入正题了。。。。。。

可以通过Flashback 工具通过闪回把数据恢复回来。数据恢复的原理就是修改binlog内容，拿回主库重新加载。要使用当前方法同时也要对事物进行修改操作如下。

• 对于 insert 语句，对应的 binlog event 类型是 Write_rows event，把它改成 Delete_rows event 即可;
• 同理，对于 delete 语句，也是将 Delete_rows event 改为 Write_rows event;
• 而如果是 Update_rows 的话，binlog 里面记录了数据行修改前和修改后的值，对调这两行的位置即可。

如果执行的是多个事务，比如原本是A,B,C。想要数据恢复的话那就直接顺序反过来即可，也就是C,B,A

建议： 不过不建议主库直接执行，比较安全的做法是恢复出一个备份，或者找一个从库作为临时库，在这个临时库上执行这些操作。然后再将确认过的临时库数据，恢复回主库。

预防

• 把 sql_safe_updates 参数设置为 on。这样一来，如果我们忘记在 delete 或者 update 语句中写 where 条件，或者 where 条件里面没有包含索引字段的话，这条语句的执行就会报错。
• 代码上线前，必须经过 SQL 审计。

如果要删除表的数据量比较大，并且确认数据是无用的，不建议使用delete。这样会生成并写入redo log，binlog，回滚日志等。采用truncate table 或者 drop table 命令可以节省性能

为什么采用truncate table 或者 drop table可以节省性能?

上文我们说到， 必须设置 binlog_format=row 。这里我们要说明一下，虽然我们配置的是没问题的，但是内部机制的问题。使用这两个命令会自动设置成statement 所以这两个命令保存的日志比较简单。恢复不了数据。性能比较好。

如果真删了呢?

误删表/库

如果真删了还是有办法的。不过稍微比较费事。这也是最低的底牌了。全量备份+增量备份 。这种方案要求线上有定期的全量备份，并且实时备份。

这个方案类似于Redis的AOF和RDB。那么他们是如何操作的呢?

假如有人中午12点误删了一个库

取最近的一次全量备份，假如备份时间是凌晨3点，一天一备。

用备份恢复出一个临时库;

从日志备份里面，取出凌晨 3 点之后的日志;

把这些日志，除了误删除数据的语句外，全部应用到临时库。

扩展

• 上述在做数据恢复的时候，如果这个临时库有多个数据库。在使用mysqlbinlog命令时加一个-database参数。指定表所在的库避免恢复数据时还要查找其他库的日志情况。
• 如果使用了GTID模式，就省事多了，只需要将未执行的gtid1加到临时实例的GTID集合中，之后按顺序执行binlog就可以了。
• 如果没有使用GTID模式，还是比较麻烦的。只能在应用到包含 12 点的 binlog 文件的时候，先用–stop-position 参数执行到误操作之前的日志，然后再用–start-position 从误操作之后的日志继续执行;

性能优化

这样的流程从性能上考虑还是比较慢的，因为操作的话往往是一个库，一个实例。如果恢复的是一个表的话就多此一举了。也不是多此一举，只是mysql并不能指定只解析一个表的日志。

加速方法

用备份恢复临时实例之后，将这个临时实例设置成线上备库的从库。在保存主从配置之前，先通过执行change replication filter replicate_do_table = (tbl_name)

命令，就可以让临时库只同步误操作的表。这样做也可以用之前介绍的并行复制技术，来加速整个数据恢复过程。

日志遗失

如果在寻找日志恢复实例时，备库上已经删除了临时实例需要的binlog的话，我们可以从binlog备份系统中找到需要的binlog，再放回备库中。具体操作如下

1. 先下载两个遗失的日志，放到备库的日志目录下
2. 打开日志目录下的 master.index 文件，在文件开头加入两行，内容分别是 ./master.丢失001和 ./master.丢失002
3. 重启备库，重新加载这两个日志。这个时候建立主从关系就可以正常同步了。

必须要求备份系统定期备份全量日志，考虑磁盘硬件需求。可以适当的保存固定的天数

延迟复制备库

这个方案是属于一个日志延迟方案。比如在从库写入一个数据，这个数据不会立即同步到备库上。然后采用延迟的手法同步到备库。

比如我们延迟1个小时。主库写入数据之后，1个小时之后会同步到从库。那么如果1个小时内发现了数据有误，就可以使用stop slave 命令把这个写入的数据停止。

可以通过 CHANGE MASTER TO MASTER_DELAY = N 命令，可以指定这个备库持续保持跟主库有 N 秒的延迟。

预防表/库方法

账号分离，不同的业务人员拥有不同的操作权限。避免写错命令。

制定操作规范。这样做的目的，是避免写错要删除的表名

rm 删除数据

这个风险还是比较高的，一般出现这种情况，只能采用集群的方式恢复了，如果没有集群的话只能嗝屁了。

如果只是删除一个节点的话，HA系统就会开始工作，先选出一个新的主库，然后就是在这个节点上把数据恢复然后接入整个集群。这样就可以解决了。

为了保险起见，一般rm命令危害比较大，建议分机房，跨城市保存数据

总结

今天介绍了数据被删后，除了跑路我们还可以有哪些处理方式以及数据被删后的应对方案和应急方案。

原文链接：https://mp.weixin.qq.com/s/o0BJZ434V-T5vFiY25I2nA