MySQL -- 数据恢复

本文主要是介绍MySQL -- 数据恢复，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

DELETE

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

DROP / TRUNCATE

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

mysqlbinlog

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

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

Master-Slave

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

延迟复制备库

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

参考资料

《MySQL实战45讲》

这篇关于MySQL -- 数据恢复的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---