DELETE

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

DROP / TRUNCATE

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

mysqlbinlog

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

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

Master-Slave

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

延迟复制备库

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

参考资料

《MySQL实战45讲》