读mysql45讲-误删数据

误删数据的几种分类：

误删行

如果使用delete语句删除了数据，可以用FlashBack工具通过闪回把数据恢复过来，FlashBack恢复数据的原理是修改binlog的内容，把修改过的binlog日志拿回原库重放。使用这个方案的前提是binlog_format=row,binlog_row_image=FULL。

具体恢复数据时，如果是单个操作误删的可以：

• 如果是insert操作，对应的binlog event类型是Write_rows event,把他改为Delete_rows event。
• 同理，对于delete操作，将其改为Write_rows event。
• 如果是update操作，binlog日志里面会记录更新前和更新后的值，顺序颠倒一下就可以。

如果误操作不是单个操作，而是多个操作：

(A)delete …
(B)insert …
©update …

那么在FlashBack工具解析binlog日志后，重新放回到原库的日志如下：

(reverse C)update …
(reverse B)delete …
(reverse A)insert …

也就是误删操作有多个的话，就将执行顺序颠倒过来再执行一遍。

不建议直接在主库上执行FlashBack解析过来修改完的binlog日志，因为误删操作过后有些误操作的数据可能已经再次被更改过了，直接恢复数据的话，会导致业务上数据不一致。

恢复数据比较安全的做法是恢复出一个备份，或者找一个从库作为临时库，在这个临时库上恢复数据，然后再将恢复的数据恢复回主库。

预防做法：

• 设置参数sql_safe_updates为on，这样在delete或者update语句中没有where条件，或者是where条件中不包含索引字段，执行不通过。
• sql审计

如果需要删除一个表的数据，delete是很慢的，需要一行一行的生成日志，所以优先考虑truncate tableName或者drop tableName。
但是如果是使用truncate table或者drop table来清空数据的话，哪怕是设置了binlog_format=row也是不能通过FalshBack工具来恢复数据的，因为truncate和drop的命令在binlog日志记录中只会记录一个语句，而不是一行一行数据的格式。

误删库/表

这种情况下要想恢复数据，就需要使用全量备份，加增量日志的方式来。这种方案的前提是定期有全量备份，而且有实时的binlog备份。

在这两个条件都具备的条件下，如果12点发生了删库，那么恢复的流程大致这样：

• 取最近一次的全量备份，假设是每天的0点进行备份的
• 用备份数据恢复一个临时库
• 取0点到12点的binlog日志
• 将binlog日志中除了误删操作的日志内容都应用到临时库中

为了加速数据恢复，在使用mysqlbinlog命令的时候，可以加上- datebase的参数，指定误删操作的数据所在的库，这样可以避免有其他库的日志。在应用备份binlog日志的时候，需要跳过12点那个时候的误操作， 如果没有开启GTID模式，先用-stop -position参数执行到误操作日志的那块停止一下，然后再用-start -position从误操作之后的日志开始；如果开启了GTID模式，假设误操作的GTID是gid1；那么就执行一下set gtid_next=gid1;begin;commit;将误操作的gtid都加到当前实例的gtid集合中，这样在执行binlog的时候，就会自动跳过误操作的事务。

使用mysqlbinlog恢复数据还是不够快，因为如果mysqlbinlog工具无法指定只解析一个表的日志，用mysqlbinlog工具解析出日志，然后将解析出的日志应用到临时库只能是单线程的跑。

可以加速的方法是将备份恢复的临时库设置为备库的从库

• 在start slave之前，执行change replication filter replicate_do_table = (tbl_name) 命令，这样在临时库中只会同步从库中涉及指定的表的数据。
• 因为变为了备库，就可以用上备库的并行复制了，就会快不少。

这个是大概的流程，binlog备份的虚线是指如果时间太长，备库中已经没有临时库需要的binlog日志的话，就需要从binlog备份系统中找到需要的日志，重新放回到备库中进行同步。

假设临时库需要的日志是从master.005开始的，但是在备库中执行show binlog命令查询的日志最早的是master.007，那么就需要取binlog备份系统找到master.005和master.006的日志。

1. 从备份系统下载master.000005和master.000006这两个文件，放到备库的日志目录下；
2. 打开日志目录下的master.index文件，在文件开头加入两行，内容分别是 “./master.000005”和“./master.000006”;
3. 重启备库，目的是要让备库重新识别这两个日志文件；
4. 现在这个备库上就有了临时库需要的所有binlog了，建立主备关系，就可以正常同步了。