MySQL找回误删的数据，数据恢复_binlog提取关键字

原创作品，未经同意，请勿转载；允许复制链接，对原文直接进行转发。

原创作者玉龙有着十几年大厂软件开发工作经验， 目前自由职业， 欢迎业务洽谈。

误删了几十万条MySQL记录， 要如何找回物理删除的数据呢？  查阅各种资料， 被误导做了些无效尝试， 现把成功经验和失败经验总结如下。

原理是通过mysqlbinlog 命令， 提取对应的删除事件的SQL语句，并重新写入数据库；基本步骤

1、 找到binlog文件； 2、 通过binlog生成，回滚删除的SQL文件；3、 执行SQL文件

  show variables like '%log_bin%';
  show master logs;

如上SQL找到binlog地址。

一、 失败尝试

sudo mysqlbinlog --start-position=123 --stop-position=789 /usr/local/mysql/data/binlog.000064 | mysql -uroot -p -v test1
 

这个尝试是把mysqlbinlog提取出来的BINLOG base64编码直接写入mysql 数据库；  原理性有错误， 直接写入是再次尝试删除一遍；  就是把binlog中记录的events回放一遍， 而不是逆向回滚一遍。

预计会遇到错误

ERROR 1032 (HY000) at line 8299: Can’t find record in ‘table_name’

意思是要删除的数据不存在， 因为已经早就删除了嘛；再直白的回放一次binlog自然不会起作用；

二、  成功尝试

需要对binlog生成的SQL 进行文本处理， 把DELETE 语句编程INSERT语句； 处理步骤和脚本如下：

1） 生成binlog SQL

sudo mysqlbinlog --start-datetime="2023-10-03 17:59:10" --stop-datetime="2023-10-03 17:59:13" -v --base64-output=decode-rows  /usr/local/mysql/data/binlog.000064>~/eventsb64.sql

通过日志，先缩小时间范围， 明确要挖掘的删除SQL的时间段， 注意边界情况，开始时间减去一秒，结束时间加上一秒；

生成好的binlog SQL有效部分截取如下

### DELETE FROM `autowater`.`capital_flow_min`
### WHERE
###   @1='00511.HK'
###   @2=202303311453
###   @3=-4605382
###   @4=0
###   @5=-1368498
###   @6=-2885925
###   @7=-798230
###   @8=447271
### DELETE FROM `autowater`.`capital_flow_min`
### WHERE
###   @1='00511.HK'
###   @2=202303311454
###   @3=-4605382
###   @4=0
###   @5=-1368498
###   @6=-2885925
###   @7=-798230
###   @8=447271
### DELETE FROM `autowater`.`capital_flow_min`

2) 通过脚本预处理SQL， 使得分段明确， 如下脚本另存为 prepare.awk

BEGIN{
    while(1){
                if(getline<=0){
                  printf "#END\n";
                  break;
                };
                if($0 ~/DELETE FROM/){
                        printf "#END\n";
                };
                if($0 ~/^### Extra row/){
                        printf "#END\n";
                };
                printf $0"\n";
        };
        printf "\n";
};

这样每段DELETE SQL就有了一个统一的终止符#END， 方便后续处理

### Extra row info for partitioning: partition: 135.  

这个分割部分是一些和分区相关的标记， 再他前面也是加上终止符；

执行脚本，对第一步生成的binlog SQL 完成预处理

awk -f prepare.awk eventsb64.sql > events66.sql 

3)  把DELETE SQL转变为INSERT SQL， 经过测试INSERT 语句是不需要填写字段名称的， 直接在VALUE里填写字段值， 这样下面这段脚本还是很通用的， 把如下awk脚本保存为replace.awk

/DELETE FROM/{
  while(1){
    gsub("### DELETE FROM","INSERT INTO",$0);
    gsub("### WHERE"," VALUES (",$0);
    gsub("###   @1="," ",$0);
    gsub("###   \@.*\=",",",$0);
    gsub(" \\(.*\\)","",$0);
    printf $0;
    getline;
    if($0 !~/^###/){
      printf ");\n";
      break;
    };
  }
}
 

gsub 是正则替换， 需要替换什么，各位客官自己完成； 

 gsub(" \\(.*\\)","",$0);   是我这里特有业务里的情况， 有很多Long类型的数据后面带了个（长串数字），需要删除； 大家视情况， 是否要删除；

执行该脚本， 即可获得INSERT SQL

awk -f replace.awk events66.sql > recovery.sql   

到这里就获得了完整的回滚SQL了， 但是里面可能还有一些杂质；

4） 提纯数据

依据业务关键字， 提取需要回滚的数据

grep "00511.HK" recovery.sql>00511.sql

5) 检查并执行修复SQL

打开SQL文件，人工检查一遍，

先登录进mysql 命令行，然后执行这些SQL

source ~/00511.sql

之后再检查一下修复的数据业务是否正常； 至此大功告成；