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 里修改前后的两行位置即可。

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

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

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

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

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

• 设置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，然后删除表的动作必须通过管理系统执行，且删除表只能删除固定后缀的表。

rm 删除数据

只要不是恶意删除整个集群，只是删除其中某一个节点的数据的话，HA 系统会选出一个新主库，从而保证整个集群的正常工作。

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

如果出现批量下线机器的操作，导致整个 MySQL 集群的所有节点都全军覆没。这种情况，建议只能说尽量将备份跨机房，或最好是跨城市保存。

行业拓展

分享一个面向研发人群使用的前后端分离的低代码软件——JNPF。

基于.NET 8 技术框架封装的快速开发平台，包含多个开箱即用功能，具备 RBAC 权限管理、前后端代码自动生成、多存储系统、多租户等功能及模块。平台支持多业务系统与多服务并行开发，设计灵活可扩展、可移植，能满足高并发需求，同时兼容本地化、私有云、公有云部署，支持 SaaS 模式应用。

核心技术以.NET 8 为主要框架，搭配多个中间件；前端基于 Vue 框架定制开发，为企业服务化中台整合、数字化中台转型提供强力支撑。

 技术领先：平台配备全新用户界面，极大优化产品整体用户体验。采用前后端分离技术架构，后端使用 .NET 8 主流开发技术栈，前端使用 Vue3 全家桶 + Ant Design Vue 技术栈；

 组件丰富：集成多个开箱即用的通用组件，如消息中心、文件服务、第三方登录等，全面满足各类开发需求；

 Saas 应用支持能力：可无缝切换至 SaaS 模式，支持企业搭建专属 SaaS 应用，灵活适应多样化业务模式；