误删数据

无论数据再怎么无限恢复，最重要的是做好事前预防：

• 将sql_safe_updates 参数设置为on。这样一来执行delete以及update语句时，忘了加where条件(确定了真要删除整张表，可以where id>=0)或者是where条件没有包含索引字段，这条更新操作就会报错。
• 代码上线之前经过SQL审计。
• 根据职责划分DB操作权限：业务开发用DML权限账号，日常使用只读账号。
• 操作规范：比如删除表前，可以先改整个表的表名，如果对业务无影响再删表；要删的表表名加固定的后缀，删表时必须通过管理(后台执行。

1.误删行

误删行可以利用binlog恢复行数据，核心就是将binlog_format设置为row格式保存列字段值，以及binlog_row_image设置为FULL记录每行的完整记录。

无论误执行了更新、插入、删除操作，恢复时只需要反序执行对应的事务和操作的行记录即可。

执行删除操作时，需要特别注意：

• 性能方面考虑，使用truncate/drop table进行删表操作
• row格式下，使用delete删除恢复；而使用truncate/drop则恢复不了数据，因为binlog记载的只有statement格式的truncate语句，并没有记录整张表的行记录。

2.误删库表

使用全量备份+全量日志的方式进行恢复。需要线上定期全量备份，并且实时备份binlog。

2.1mysqlbinlog应用日志备份系统的日志

场景：0点执行了误删除操作。

1. 首先取出距离误操作最近一次的全量备份，用备份恢复出一个临时库(使用mysqlbinlog命令加上-database参数，指定恢复的误删表所在的临时库)。
2. 从"日志备份系统"当中，取出0点之后的日志。
3. 将这些日志除了误删库表数据的语句以外，全部应用到临时库。具体临时库如何跳过误删除操作如下：
   • 使用了GTID模式，则直接拿到误删命令事务的gtid1；在临时库上执行一个空事务，将gtid1加入到临时库的GTID集合当中。
   • 如果没有使用GTID模式，则使用-stop-position参数执行到误操作之前的日志，然后-start-position指定从误操作事务之后的日志开始。

💣存在的问题：过于依赖mysqlbinlog,不仅只能单线程应用日志进行同步，同时还会重放解析其它“并没有误删的表”。

2.2临时实例应用线上备库的日志

另一种加速的方法如下，将整个临时实例设置成线上备库的从库。

1. 首先还是根据全量备份创建一个临时实例。

2. 在临时实例start slave之前，执行如下命令，指定“临时实例”只同步“线上备库”的那张误删表的所有日志记录。

   change replication filter replicate_do_table=(tbl_name);
   

   如果临时实例所需要的binlog在备库上没有(时间太久备库删除了)，那么只能从“备份系统”找到并下载所需要的binlog：

   • 从日志备份系统下载缺失的binlog到“线上备库”中，并加入master.index文件，最后重启"线上备库"
   • 再由“线上备库”将误删日志同步到“临时实例”当中

3. 此时不仅可以仅同步误删表，同时还可以利用并行复制技术加速恢复。

💣存在的问题：恢复时间不可控。假设全量备份一周一备，而恰巧在上一次备份的第六天发生了误操作，那么拿到临时实例后就需要恢复6天的日志。

3.延迟复制的库表

延迟复制的备库：通过CHANGE MASTER TO MASTER DELAY =N命令，指定当前备库的更新同步与主库有N秒的延迟。

通过搭建这么一个延迟备库，只要在N秒内发现出现了误操作命令，那么直接在该备库上stop slave，同时在备库上跳过误操作命令，最多追1h的binlog后再主从切换。这样大大缩短恢复数据所需要的时间。