MySQL 31 误删数据后除了跑路，还能怎么办？

正在检查是否收录...

传统的高可用架构不能预防误删数据的，因为主库的一个drop table命令，会通过binlog传给所有从库和级联从库，进而导致整个集群的实例都会执行这个命令。

MySQL相关误删数据主要有以下几种可能：

使用delete语句误删数据行；
使用drop table或truncate table语句误删数据表；
使用drop database误删数据库；
使用rm误删整个MySQL实例。

误删行

如果使用delete语句误删数据行，可以用Flashback工具通过闪回将数据恢复。原理是修改binlog的内容，拿回原库重放。能使用该方案的前提是需要确保binlog_format=row和binlog_row_image=FULL。

具体而言：

对于insert，对应的binlog event类型是Write_rows event，改成Delete_rows event即可；
对于delete，将Delete_rows event改为Write_rows event；
对于update，对调binlog里修改前后的两行位置即可。

如果误操作不止一个，比如是三个事务：

(A)delete (B)insert (C)update

若要恢复，用Flashback解析binlog后，写回主库的命令是：

(reverse C)update (reverse B)delete (reverse A)insert

即误操作涉及多事务时，需要将事务顺序反过来执行。

恢复数据比较安全的做法，是找一个从库作为临时库，在临时库上执行这些操作，然后再将确认过的临时库的数据，恢复回主库。因为一个执行线上逻辑的主库，数据状态的变更往往是有关联的，可能由于误操作导致后续逻辑修改了其他关联数据，这时如果单独恢复误操作的数据，可能会出现对数据的二次破坏。

当然，更重要的是做到事前预防，有两个建议：

设置sql_safe_updates=on，这样如果delete或update中没写where，或where条件里没有包含索引字段，该语句的执行就会报错；
代码上线前，必须经过SQL审计。

那么当设置sql_safe_updates=on，想要删除一个小表的全部数据，该怎么办呢？

可以在delete语句加上where id>=0。该方法很慢，性能不好；
使用truncate table/drop table，缺点是无法通过Flashback恢复，因为binlog里只有一个truncate/drop语句，恢复不出数据。

误删库/表

此时想恢复数据，需要使用全量备份+增量日志的方式，因此方案要求线上有定期的全量备份，并且实时备份binlog。

假设中午12点误删了一个库，那么恢复流程如下：

取最近一次全量备份，假设该库一天一备，上次备份为当前0点；
用备份恢复出一个临时库；
从日志备份里取出凌晨0点后的日志；
将这些日志，除了误删数据的语句，全应用到临时库。

该过程有几个需要说明的地方：

为加速数据恢复，若临时库上有多个数据库，可以在使用mysqlbinlog命令时加上-database参数指定误删表所在的库，避免在恢复数据时还要应用其他库日志的情况；
应用日志需要跳过误操作语句的binlog：
- 如果原实例没有使用GTID模式，只能在应用到包含12点的binlog文件时，先用-stop-position参数执行到误操作前的日志，再用-start-position从误操作后的日志继续执行；
- 如果实例使用GTID模式，假设误操作命令的GTID是gtid1，只需执行set gtid_next=gtid1;begin;commit;，先将这个GTID加到临时实例的GTID集合，之后按顺序执行binlog时就会自动跳过误操作的语句。

但该方法恢复数据还是不够快，主要两个原因：

如果是误删表，最好是只恢复这张表，但mysqlbinlog并不能指定只解析一个表的日志；
应用日志的过程只能是单线程。

一种加速的方法是，在用备份恢复出临时实例后，将这个临时实例设置成线上备库的从库，这样：

在start slave之前，先通过执行change replication filter replicate_do_table=(tbl_name)，可以让临时库只同步误操作的表；
这样做可以用上并行复制。

不论是把mysqlbinlog工具解析出的binlog文件应用到临时库还是把临时库接到备库，两个方案的共同点是：误删表/库后，恢复的思路主要是通过备份，再加上应用binlog的方式。即都要求备份系统定期备份全量日志，且需确保binlog在从本地删除前已经做了备份。

但一个系统不可能备份无限的日志，还需要根据成本和磁盘空间资源设定一个日志保留的天数。

延迟复制备库

虽然可以利用并行复制来加速恢复数据的过程，但该方案仍存在恢复时间不可控的问题。如果一个库的备份特别大，或误操作时间距离上一个全量备份的时间较长，比如一周一备的实例，在备份后的第6天发生误操作，那就需要恢复6天的日志，该恢复时间可能会按天计算。

如果有非常核心的业务，不允许太长的恢复时间，可以考虑搭建延迟复制的备库。

一般的主备复制结构存在的问题是，如果主库上有个表被误删，该命令很快会被发给所有从库，进而导致所有从库的数据表一起被误删。

延迟复制的备库是一种特殊备库，通过CHANGE MASTER TO MASTER_DELAY=N，可以指定这个备库持续保持跟主库有N秒延迟。比如设置N=3600，表示如果主库上有数据被误删，且在1小时内发现了该误操作命令，这个命令就还没在延迟复制的备库执行，此时到备库上执行stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。

这样就得到了一个只需要最多再追一小时，就能恢复出数据的临时实例，也就缩短了整个数据恢复需要的时间。

预防误删库/表的方法

这里给出两条建议：

账号分离，目的是避免写错命令，如：
- 只给业务开发DML权限而不给truncate/drop权限，如果业务开发有DDL需求，也可以通过开发管理系统得到支持；
- 即使是DBA团队成员，日常也只使用只读账户，必要时才使用有更新权限的账户。
制定操作规范，目的是避免写错要删除的表名，如：
- 删除数据表前，必须先对表做改名，然后观察一段时间，确保对业务无影响后再进行删除；
- 改表名时，要求给表名加固定后缀如_to_be_deleted，然后删除表的动作必须通过管理系统执行，且删除表只能删除固定后缀的表。