阅读量:12
目录
传统的架构不能预防误删数据，因为主库的一个drop table命令，会通过binlog传给所有从库和级联从库，进而导致整个集群的实例都会执行这个命令。
MySQL相关的误删除数据分类：
1、使用delete语句误删数据行
2、使用drop table 或者 truncate table 语句误删数据表
3、使用drop database语句误删数据库
4、使用rm命令误删整个MySQL实例
误删行
若使用delete语句误删数据行，可以用Flashback工具通过闪回把数据恢复。
原理：修改binlog内容，拿回原库重放。
前提：binlog_fromat = row 和binlog_row_image = FULL
具体措施：
1、对于insert语句，对应的binlog event 类型为WrIte_rows event，将其改为Delete_rows event
2、同理，对于delete，将Delete_rows event 改为Write_rows event
3、对于Update_rows，binlog记录了数据行修改前和修改后的值，对调两行位置即可
如果误操作是多个，如：
(A)delete ...
(B)insert ...
(C)update ...
若要恢复这三个事务之前状态，用Flashback工具解析binlog后，写回主库：
(reverse C)update ...
(reverse B)delete ...
(reverse A)insert ...
恢复数据的比较安全的做法是恢复出一个备库，或者找一个从库作为临时库，在这个临时库上执行这个操作，然后再将确认过的临时库的数据，恢复回主库。
这是由于发现数据问题时间比较晚，导致会有在误操作的基础上的逻辑，如果单独恢复这个几行数据，会对数据造成二次破坏。
事前预防误删行数据方法
1、把sql_safe_updates参数设置为on。这样如果忘记在delete或者update语句中写where条件，或者where条件里没有包含索引字段，语句执行报错
2、代码上线前，必须经过SQL审计
误删表/库
需要删除一个表时，delete全表很慢，需要生成回滚日志、写redo、写binlog，所以常常使用truncate table 或者 drop table。
直接drop表和delete每行的最大区别就是，binlog对delete有详细的删除行内容，可是drop表后binlog就只有一个drop语句，恢复不了数据。
使用truncate/drop误删除数据时的恢复方法 ：使用全量备份，加增量日志。要求对线上有定期的全量备份，并且实时备份binlog。
如果中午误删了库，恢复数据流程如下：
1、取最近一次全量备份，假设这个库是一天一备，上次备份是当天0点
2、用备份恢复出一个临时库
3、从日志被分离，取出0点之后的日志
4、把这些日志，除了误删除的数据的语句外，全部应用到临时库
加速恢复数据
1、如果临时库有多个数据库，可以指定误删表所在的库，这样就避免了恢复数据时还要应用其他库日志。
2、应用日志时，跳过误操作的语句
不过这样使用mysqlbinlog方法恢复数据不够快。
mysqlbinlog恢复数据不够快的原因：不能指定特定数据表、单线程操作
另外的加速方法
在用备份恢复出临时实例之后，将这个临时实例设置成线上备库的从库。
具体流程：
1、在start slave 之前，执行change replication filter replicate_do_table = (tbl_name),让临时库只同步误操作的表
2、可以使用并行复制技术，加速数据恢复
延迟复制备库
如果一个库备份很大，或者误操作的时间举例上一个全备份的时间较长，可以搭建延迟复制的备库缩短恢复数据恢复需要的时间。
一般的主备复制结构存在问题：如果主库有个表被误删了，这个命令很快也会被发给所有从库，进而导致所有从库的数据表也都一起被误删。
可以主动加大同步延迟，通过CHANGE MASTER TO MASTER_DELAY = N 命令，指定这个备库始终与主库有N秒延迟。如果把N设置为3600，代表如果主库上有数据被误删了，并且在1h之内发现了这个误操作，该命令此时没有在延迟复制的备库执行，所以可以到备库上stop slave，然后跳过误操作命令，恢复数据。
事前预防误删库/表方法
1、账号分离。
只给业务开发人员DML权限，不给truncate/drop权限。
DBA团队成员，也只使用只读账号，必要时使用有更新权限的账号
2、指定操作规范，避免写错要删除的表名。
在删除数据表之前，对表进行改名操作，并观察一段时间，若对业务无影响，则删除
改表名时要给表名加固定的后缀如_to_be_deleted，然后删除表的动作必须通过管理系统执行。并且管理系统只能删除固定后缀的表。
