Mysql 系列 | 误删数据

误删数据是数据库操作过程中不可避免会遇到的问题。

误删分为几种，误删行、误删库/表、误删整个实例。

遇到问题就要分析原因，并对症下药解决问题。

误删行

使用 delete 语句误删数据行。

此时可以用 Flashback 工具通过闪回恢复数据。

• 原理是修改 binlog 内容，拿回原库重放，前提是确保 binlog_format=row binlog_row_image=FULL

• 对于 insert 语句，binlog event 类型 Write_rows event => Delete_rows event

• 对于 delete 语句，Delete_rows event => Delete_rows event

• 对于 update 语句，将 binlog 中记录的修改前后的值对调即可

• 涉及多个事务时，将事务顺序调过来再执行

误删库/表

delete 删除全表数据，需要生成回滚日志、redolog、binlog会很慢，应该优先考虑 truncate 或 drop。

此时 binlog 中只有一个 truncate/drop 语句，无法恢复出删除前的数据。

这种情况下，要使用全量备份和增量日志。要求线上有定期的全量备份，并且实时备份 binlog

• 取最近一次全量备份，恢复出一个临时库

• 从日志备份中取出全量备份后的日志，除了误删数据语句外，全部应用到临时库

  • 临时库有多个数据库时，mysqlbinlog 命令配合 -database 参数，指定误操作所在库

  • 配合 -stop-position 参数设置执行的开始和结束位置

事前预防

误删数据要及时处理，更重要的是做到事前预防

• 设置 sql_safe_updates=on 一旦忘记在 delete 或者 update 语句中写 where 条件，或者条件中没有包含索引字段，这条语句执行就会报错

• 代码上线前，必须进行 SQL 审计

• 定期检查备份的有效性

• 把数据恢复功能做成自动化工具，并且经常拿出来演练

• 考虑搭建延迟复制的备库

• 账号分离，严格给不同人员分配不同权限，尽量减少删除操作，必要时才使用有更新权限的账号

• 指定操作规范，避免写错要删除的表名。比如删除前必须先修改表名为规定的后缀，确保对业务无影响后再删除固定后缀的表