MySQL -- 数据恢复

我就是我 2022-12-18 09:00 128阅读 0赞

## 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点删除了一个库，恢复数据的流程  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70_pic_center]

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 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70_pic_center 1]

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讲》

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70_pic_center]: /images/20221122/d9af630f8f3a406081799759f7285620.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221122/3ef62d5799184693becf7374d39c25c7.png