线上数据被意外删除是一件可怕的事情，但可怕源于陌生，数据库领域发展了这么多年，早已经有了很多对策。学会这些对策，让你删库不用再跑路。

利用数据库日志还原

如果你只是删除了数据，比如 DELETE 时忘了加 WHERE 或者 WHERE 条件加错了，恢复起来其实很简单，可以直接用 Flashback 工具还原数据。

还原的原理，是使用了 MySQL 的操作日志 binlog，对错误语句进行逆操作：

  • DELETE -> INSERT
  • INSERT -> DELETE
  • UPDATE a to b -> UPDATE b to a

这里有个注意点，数据的任何还原都建议放到一个临时库或从库上执行，还原完确认正确后再恢复回主库。这是因为数据的更改是持续的，如果恢复期间数据继续更改了，那直接还原就会造成数据的二次破坏。

从上面的原理，你应该看的出来，这个恢复需要能将操作转化成逆操作才行，需要将 binlog_format 配置成 row 来支持才行。那假设配置不支持呢？

另外还有 TRUNCATE, DROP 这两个命令呢，这些不会生成回滚日志的命令怎么还原？

对于这两个问题，就要用到下面的备份还原了。

利用数据备份还原

到了这一步，就需要使用数据库备份文件了，采用“全量备份”加“增量日志”的方式还原数据。全量备份直接取离误操作时间点最近的备份文件，增量日志还原的方式有两种：

mysqlbinlog 方式

最直观的就是通过 mysqlbinlog 命令还原增量数据，流程图如下：

MySQL Restore mysqlbinlog

这里主要注意两点：

  • 还原记得要跳过误操作语句，否则就是无用功了。跳过可以根据 position 分段执行还原或者通过跳过 GTID 的方式。
  • 还原主要的问题是速度问题，增量日志上如果有多个数据库的话，记得使用 mysqlbinlog 命令的 –database 参数加快还原。

这里由于 mysqlbinlog 不支持指定表，另外还原只能单线程操作，如果预估的还原时间还是过长，那么可以采用下面的 slave 方式。

slave 方式

该还原是利用了主从的并行复制技术加快速度。完整步骤是：

  1. 利用全量备份恢复出一个临时实例后，将这个临时实例设置成线上备库的从库。
  2. 在 start slave 之前，先通过命令 CHANGE REPLICATION FILTER REPLICATE_DO_TABLE = (TBL_NAME) 让临时库只同步误操作表。

整个过程流程图如下：

MySQL Restore slave

上述 binlog 备份系统到线上备库有一条虚线，是因为由于时间太久备库上可能已经删除了 binlog 文件，此时需要先找回还原备库上丢失的 binlog 文件， 再启用主从同步。

“利用数据备份还原”即使加上了并行复制技术，依然会有恢复时间不确定的问题，下面再提供一种更稳定的还原方案“利用延时备库还原”。

利用延时备库还原

这个原理就是启用一个延迟一段时间（例如1个小时）的备库，这样的话只要误操作是在一个小时之内发现的，那就可以立即停止延时备库的同步， 然后根据上面的方法将这个延时备库作为全量备份还原数据，这样增量还原最多只有一个小时的日志了。

其中设置延时备库可以使用 CHANGE MASTER TO MASTER_DELAY = N，N 的单位是秒。

以上三种恢复数据的方法，足以应对大部分场景了。不过仅仅学会是不够的，你应该将上述数据恢复操作做成自动化工具，并且经常拿出来演练，做到一般不出事，出事了也没事。 因为出事时人一般都是慌的，激动的心颤抖的手，如果再造成不可挽回的操作就真的要跑路了，而工具一般比人冷静。

预防误删数据方法

数据恢复只是善后，更需要做的应该是预防，以下这些方法可以减少误删风险：

  • 对数据库进行账号分离，开发账号仅有 DML 权限，要做 TRUNCATE/DROP 等 DDL 操作需向上申请。
  • 增加删除数据表和库的流程，可以规定只允许删除特定后缀的表和库，这样的话删除操作之前就需要执行改表和库名，改名到删除之间还可以要求一定的观察期。
  • 将 sql_safe_updates 打开，这样 DELETE/UPDATE 语句在忘写 WHERE 或者 WHERE 未命中索引时就会报错提醒。
  • 对于批量数据更改（刷数据）增加操作规范，比如需要同时提供回滚和备份脚本等，增加流程审核。

参考

  • 《MySQL 实战 45 讲》