《MySQL实战45讲》——学习笔记31 “误删数据的解决方案（删行/删表/删库/删实例）“

偏执的太偏执、 2023-09-26 22:16 51阅读 0赞

本篇介绍MySQL误删数据的几种情况以及误删后的处理方法，包括：

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

### 使用 delete 语句误删数据行 ###

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

Flashback恢复数据的原理是修改binlog的内容，拿回原库重放；而能够使用这个方案的前提是，需要确保binlog\_format=row和binlog\_row\_image=FULL；

#### binlog\_format ####

>  *  binlog\_format=statement 时，binlog 里面记录的就是 SQL 语句的原文，包括注释；statement格式下，若语句中有limit，这个命令可能是unsafe的，如delete+limit，当走到不同索引时，执行结果不同；因此，statement格式尽管日志量小节省IO，但可能导致主备不一致；
>  *  binlog\_format=row 时，binlog里面记录了真实删除行的主键id，不会有主备删除不同行的问题；但会产生大量的日志，消耗更多的IO资源；
>  *  MYSQL取了个折中，也提供了mixed格式日志，判断这条SQL语句是否可能引起主备不一致，如果有可能，就用row格式，否则就用statement格式；也就是说，mixed格式可以利用statment格式日志量小的优点，同时又避免了数据不一致的风险；
> 
> 尽管MYSQL提供了mixed格式日志，也推荐把MySQL的binlog格式设置成row，其中一个重要的好处就是：恢复数据；能精准定位到被修改的那条记录，并根据原SQL恢复；

#### binlog\_row\_image ####

>  *  before image：前镜像，即数据库表中修改前的内容；
>  *  after image：后镜像，即数据库表中修改后的内容；
>  *  binlog\_row\_image三种设置及异同 binlog\_row\_image参数可以设置三个合法值：FULL、MINIMAL、NOBLOB；设置FULL时，Log all columns in both the before image and the after image，即binlog日志记录所有前镜像和后镜像，确保数据修改前后的内容都能保留从而方便恢复；

**具体恢复数据时，对单个事务做如下处理**：

*  对于insert语句，对应的binlogevent类型是Write\_rowsevent，把它改成Delete\_rowsevent即可；
 *  对于delete语句，也是将Delete\_rowsevent改为Write\_rowsevent；
 *  如果是Update\_rows的话，binlog里面记录了数据行修改前和修改后的值，对调这两行的位置即可；

举例，比如下面三个事务：

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

现在要恢复数据，用Flashback工具解析binlog后，写回主库的命令是：

(reverseC) update...
    (reverseB) delete...
    (reverseA) insert...

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

#### 建议：不建议你直接在主库上根据binlog执行数据恢复的SQL命令 ####

因为这是因为，一个在执行线上逻辑的主库，数据状态的变更往往是有关联的；可能由于发现数据误操作问题的时间晚了一点儿，就导致已经在之前误操作的基础上，业务代码逻辑又继续修改了其他数据；

所以，如果这时候单独恢复这几行数据，而又未经确认的话，就可能会出现对数据的二次破坏；

#### 生产环境恢复数据的推荐方案 ####

*  如果是**复杂的数据恢复**，需要业务人员确定恢复数据的方案，除被误操作的数据以外，还需充分考虑关联数据的恢复方案，甚至是备操作数据的缓存也需做对应的恢复，然后写一个恢复数据的任务脚本来执行数据恢复；
 *  如果仅仅是**简单的数据恢复**，则可以在测试环境验证SQL命令的正确性，确保对业务无额外影响，再去生产环境执行，可以先尝试单条数据的恢复，验证无误再恢复剩余数据，总之就是直接操作线上MYSQL数据一定需要慎重；

#### SQL DELETE命令误删数据的预防 ####

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

### 使用drop table或者truncate table语句误删数据表 ###

#### 如何删除表数据？ ####

最直接的可以通过SQL的DELETE命令逐条删除；如果你确定这个删除操作没问题的话，可以在delete语句中加上where id>=0条件；

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

使用delete命令删除的数据，你还可以用Flashback来恢复；而使用truncate table/drop table/drop database命令删除的数据，就没办法通过Flashback来恢复了；为什么呢？

这是因为，即使我们配置了binlog\_format=row，执行上面这3个删表命令时，记录的binlog还是statement格式，也就是说binlog里面就只有一个truncate/drop语句，这些信息是恢复不出数据的；

#### drop / truncate table后如何恢复？——全量数据备份+增量日志备份 ####

使用drop/truncate删除表数据，这种情况下，恢复数据就需要使用全量数据备份，加增量binlog日志的方式了；这个方案要求线上有定期的全量备份，并且实时备份binlog；

在这两个条件都具备的情况下，假如有人中午12点误删了一个库，恢复数据的流程如下：

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

#### 如何跳过误删数据语句对应的binlog？ ####

*  如果原实例没有使用GTID模式，只能在应用到包含12点的binlog文件的时候，先用–stop-position参数执行到误操作之前的日志，然后再用–start-position从误操作之后的日志继续执行；
 *  如果实例使用了GTID模式，就方便多了；假设误操作命令的GTID是gtid1，那么只需要执行 setgtid\_next=gtid1;begin;commit; 先把这个GTID加到临时实例的GTID集合，之后对临时示例按顺序执行binlog的时候，就会自动跳过误操作的语句；

### 使用drop database语句误删数据库 ###

因为binlog是整库级别的，因此上述方案也可以用于drop database时做数据恢复；

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

那么，我们有什么方法可以**缩短恢复数据需要的时间**呢？

#### 延迟复制的备库 ####

如果有非常核心的业务，不允许太长的恢复时间，我们可以考虑搭建延迟复制的备库；这个功能是MySQL5.6版本引入的；

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

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

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

通过这个延迟复制的备库（N=3600），就随时可以得到一个，只需要最多再追1小时，就可以恢复出数据的临时实例，也就缩短了整个数据恢复需要的时间；

#### 预防误删库 / 表的方法 ####

*  **第一条建议是，账号权限分离；**

这样做的目的是，避免写错命令；比如：我们只给业务开发同学DML权限，而不给truncate/drop权限；而如果业务开发人员有DDL需求的话，也可以通过开发管理系统得到支持；即使是DBA团队成员，日常也都规定只使用只读账号，必要的时候才使用有更新权限的账号；

*  **第二条建议是，制定操作规范；**

这样做的目的是，避免写错要删除的表名；比如：在删除数据表之前，必须先对表做改名操作；然后，观察一段时间，确保对业务无影响以后再删除这张表；改表名的时候，要求给表名加固定的后缀（比如加\_to\_be\_deleted)，然后删除表的动作必须通过管理系统执行；并且，管理系删除表的时候，只能删除固定后缀的表；

### 使用rm命令误删整个MySQL实例 ###

其实，对于一个有高可用机制的MySQL集群来说，最不怕的就是rm删除数据了；只要不是恶意地把整个集群删除，而只是删掉了其中某一个节点的数据的话，HA系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作；

这时，你要做的就是在这个节点上把数据恢复回来，再接入整个集群；

当然了，现在不止是DBA有自动化系统，SA（系统管理员）也有自动化系统，所以也许一个批量下线机器的操作，会让你整个MySQL集群的所有节点都全军覆没；

**其实针对上述误删数据问题，无论使用哪种数据恢复方案，恢复到什么程度，它的前提最核心的思路就是****做好数据备份**；针对集群rm命令这种情况，建议只能是说尽量把数据备份跨机房、或者跨地保存；

本文参考：[31 | 误删数据后除了跑路，还能怎么办？][31 _]

[31 _]: https://time.geekbang.org/column/article/78658?screen=full