MySQL深入27-误删数据的处理方案

MySQL相关的误删数据分类：

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

误删行

如果是使用delete语句误删了数据行，可以用Flashback工具通过闪回把数据回复回来。

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_rows 的话，binlog 里面记录了数据行修改前和修改后的值，对调这两行的位置即可。

如果误操作的是多行：

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

现在要把数据库恢复回这三个事务操作之前的状态，用Flashback工具解析binlog后，写回主库的命令：

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

如果误删数据涉及到了多个事务的话，需要将事务的顺序调过来再执行。

不建议直接在主库上执行上述操作

恢复数据比较安全的做法，是恢复出一个备份，或者找一个从库作为临时库，在这个临时库上执行这些操作，然后再将确认过的临时库的数据，恢复回主库。这是因为线上逻辑的主库，数据状态的变更往往是有关联的，如果直接去线上主库操作容易造成二次破环。

事前预防

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

在设置了sql_safe_updates=on情况下，如果真的要把一个小表的数据全部删掉，可以在 delete 语句中加上 where 条件，比如 where id>=0。

但是，delete 全表是很慢的，需要生成回滚日志、写 redo、写 binlog。所以，性能角度考虑，你应该优先考虑使用 truncate table 或者 drop table 命令。

但是使用 truncate /drop table 和 drop database 命令删除的数据，就没办法通过 Flashback 来恢复了。这是因为，即使配置了binlog_format=row，执行这三个命令时，记录的binlog还是statement格式。

误删库/表

这种情况下，要想恢复数据，就需要使用全量备份，加增量日志的方式。这个方案要求线上有定期的全量备份，并且实时备份binlog。

假设误删一个库，那么恢复数据的流程如下：

1. 取最近一次全量备份，假设这个库是一天一备，上次备份是当天 0 点；
2. 用备份恢复出一个临时库；
3. 从日志备份里面，取出凌晨 0 点之后的日志；
4. 把这些日志，除了误删除数据的语句外，全部应用到临时库。

注意点

1. 为了加速数据恢复，如果这个临时库上有多个数据库，你可以在使用 mysqlbinlog 命令时，加上一个–database 参数，用来指定误删表所在的库。
2. 在应用日志的时候，需要跳过误操作时间点的binlog
   • 有GTID：找到误删操作的GTID，加到临时库的GTID集合中。
   • 无GTID：在执行到误删操作时，先用-stop-position执行到误删操作之前的日志，再用-start-position从误删操作之后的日志继续执行。

不过，即使这样，使用mysqlbinlog方法恢复数据还是不够快，主要原因有两个：

• 如果是误删表，最好就是只恢复出这张表，也就是只重放这张表的操作，但是 mysqlbinlog 工具并不能指定只解析一个表的日志；
• 用 mysqlbinlog 解析出日志应用，应用日志的过程就只能是单线程。

延迟复制备库

虽然我们可以通过利用并行复制来加速恢复数据，但是这个方案仍然存在“恢复时间不可控”的问题。

如果一个库的备份特别大，或者误操作的时间距离上一个全量备份的时间较长，比如一周一备的实例，在备份之后的第6天发生误操作，就需要恢复6天的日志。对于比较核心的业务，如果不允许太长的恢复时间，可以考虑搭建延迟复制的备库。

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

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

比如你把 N 设置为 3600，这就代表了如果主库上有数据被误删了，并且在 1 小时内发现了这个误操作命令，这个命令就还没有在这个延迟复制的备库执行。这时候到这个备库上执行 stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。

预防误删库/表的方法

减少误删操作风险的建议：

• 账号分离

  • 只给业务开发同学 DML 权限，而不给 truncate/drop 权限。而如果业务开发人员有 DDL 需求的话，也可以通过开发管理系统得到支持。
  • 即使是 DBA 团队成员，日常也都规定只使用只读账号，必要的时候才使用有更新权限的账号。

• 制定操作规范，避免写错要删除的表名

  • 在删除数据表之前，必须先对表做改名操作。然后，观察一段时间，确保对业务无影响以后再删除这张表。

  • 改表名的时候，要求给表名加固定的后缀（比如加 _to_be_deleted)，然后删除表的动作必须通过管理系统执行。并且，管理系删除表的时候，只能删除固定后缀的表。

rm删除数据

只要不是恶意地把整个集群删除，而只是删掉了其中某一个节点的数据的话，HA 系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。要做的就是在这个节点把数据恢复回来，再接入整个集群。

参考

MySQL 实战 45 讲-极客时间 (opens new window)