MySQL -- 数据恢复

DELETE

使用DELETE语句误删除了数据行，可以使用Flashback通过闪回把数据恢复
Flashback恢复数据的原理：修改binlog的内容，然后拿到原库重放
- 前提：binlog_format=ROW和binlog_row_image=FULL
针对单个事务
- 对于INSERT语句，将Write_rows event改成Delete_rows event
- 对于DELETE语句，将Delete_rows event改成Write_rows event
- 对于UPDATE语句，binlog里面记录了数据行修改前和修改后的值，对调两行的位置即可
针对多个事务
- 误操作
  - (A)DELETE
  - (B)INSERT
  - (C)UPDTAE
- Flashback
  - (REVERSE C)UPDATE
  - (REVERSE B)DELETE
  - (REVERSE A)INSERT
不推荐直接在主库上执行上述操作，避免造成二次破坏
- 比较安全的做法是先恢复出一个备份或找一个从库作为临时库
- 在临时库上执行上述操作，然后再将确认过的临时库的数据，恢复到主库
预防措施
- sql_safe_updates=ON，下列情况会报错
  - 没有WHERE条件的DELETE或UPDATE语句
  - WHERE条件里面没有包含索引字段的值
- 上线前，必须进行SQL审计
删全表的性能
- DELETE全表很慢，因为需要生成undolog、写redolog和写binlog
- 优先考虑使用DROP TABLE或TRUNCATE TABLE

DROP TABLE、TRUNCATE TABLE和DROP DATABASE，是无法通过Flashback来恢复的
- 即使配置了binlog_format=ROW，执行上面3个命令，binlog里面记录的依然是STATEMENT格式
- binlog里面只有一个TRUNCATE/DROP语句，这些信息是无法恢复数据的
这种情况如果想要恢复数据，需要使用全量备份和增量日志的方式
- 要求线上定期全量备份，并且实时备份binlog

假设有人中午12点删除了一个库，恢复数据的流程

取最近一次全量备份，假设一天一备，即当天0点的全量备份
用全量备份恢复出一个临时库
从binlog备份里，取出凌晨0点以后的日志
把这些日志，除误删数据的语句外，全部应用到临时库
为了加快数据恢复，如果临时库上有多个数据库，可以加上--database参数，指定应用某个库的日志
跳过12点误操作语句的binlog
- 如果原实例没有使用GTID模式，只能在应用到包含12点的binlog文件的时候
  - 先用--stop-position参数执行到误操作之前的日志
  - 再用--start-position从误操作之后的日志继续执行
- 如果原实例使用GTID模式，假设误操作命令的GTID为gtid1
  - 只需执行SET gtid_next=gtid1;BEGIN;COMMIT;
  - 把gtid1加入到临时库的GTID集合，之后按顺序执行binlog时，会自动跳过误操作的语句
使用mysqlbinlog的方法恢复数据的速度还是不够快，主要原因
- 如果误删表，最好是只重放这张表的操作，但mysqlbinlog并不能指定只解析一个表的日志
- 用mysqlbinlog解析出日志来应用，应用日志的过程只能是单线程的
另外一个加速的方法：Master-Slave

在START SLAVE之前，先通过执行CHANGE REPLICATION FILTER REPLICATE_DO_TABLE=(tbl_name)
- 让临时库只同步误操作的表，利用并行复制技术，来加速整个数据恢复过程
binlog备份到线上备库之间是一条虚线
- 虚线指的是如果由于时间太久，线上备库有可能已经删除了临时实例所需要的binlog
  - 可以从binlog备份系统中找到需要的binlog，再放到备库中
- 举例说明
  - 例如当前临时实例需要的binlog是从master.000005开始
  - 但在线上备库上执行SHOW BINARY LOGS显示最小的binlog文件是master.000007
  - 意味着少了两个binlog文件
  - 这时需要到binlog备份系统找到这两个文件，把之前删掉的binlog放回备库执行以下步骤
  - 从备份系统下载master.000005和master.000006，放到备库的日志目录下
  - 打开master.index，在文件头加入两行：./master.000005和./master.000006
  - 重启备库，目的是为了让备库重新识别这两个日志文件
  - 现在备库上就有了临时实例所需要的所有binlog，建立主备关系，就可以正常同步了

上面Master-Slave的方案利用了并行复制来加速数据恢复的过程，但恢复时间不可控
- 如果一个库特别大，或者误操作的时间距离上一个全量备份的时间较长（一周一备）
针对核心业务，不允许太长的恢复时间，可以搭建延迟复制的备库（MySQL 5.6引入）
延迟复制的备库是一种特殊的备库
- 通过CHANGE MASTER TO MASTER_DELAY=N命令来指定备库持续与主库有N秒的延迟
- 假设N=3600，如果能在一小时内发现误删除命令，这个误删除的命令尚未在延迟复制的备库上执行
- 这时在这个备库上执行STOP SLAVE，再通过前面的方法，跳过误删除命令，就可以恢复数据
- 这样，可以得到一个恢复时间可控（最多1小时）的备库