mysql误删恢复

恢复方案

有以下几种数据恢复方式： 方法一: 恢复到上次的备份，无二进制日志（丢失上次备份到当前的数据） 方法二: 恢复到上次的备份，把上次备份到现在的二进制日志中相关表到删除语句之前的日志进行回放（可以恢复到删除语句之前的数据） 方法三: 如果数据量比较小，但是整体库很大，从压缩中提取出来比较慢，那么可以解析出二进制日志的delete sql，手动改写为insert（数据量小的表也等于是全量恢复）

Tips.

1. 查询binlog的时候带的参数-d xxx --base64-output=DECODE-ROWS --set-charset=UTF8，但是恢复数据的时候不要带这些参数
2. 如果恢复过程中如果主键数据有问题或者有其他错误，但仍然想恢复这个表数据，可以mysql -uroot -p'xxx' -f 来出现错误的时候强制继续

如何恢复

场景1

如果表创建以及各种操作的binlog都还存在，那么直接拿出这些binlog，指定start时间和stop时间进行恢复，比如：

mysqlbinlog --start-datetime="2021-08-08 01:00:00" --stop-datetime="2021-08-08 12:11:00" --database=hsjc_bi /tmp/binlog.000005 | mysql -uroot -p'xxx'

实际的操作应该是先从定时备份中恢复表，然后找到 binlog中表备份之后的时间到数据被删之前的时间段日志，进行应用

场景2

表已经用过一段时间，表被truncate。那么需要一个最近有效的备份，并且备份到最近时间的binlog日志。需要找到truncate的语句，从binlog中，然后truncate语句上下的pos，回放这两个pos前后所有的sql

mysqlbinlog --start-position="126577910" --stop-position="126649749" --database=xxx /tmp/binlog.000004 | mysql -uroot -p'xxx'
mysqlbinlog --start-position="126649944" --stop-position="126678302" --database=xxx /tmp/binlog.000004 | mysql -uroot -p'xxx'

场景3

没有备份，或者不使用备份的情况下。当然表也没有被truncate，只是被修改或者删除

1. 提取出当前binlog的sql语句

/opt/mysql/8/bin/mysqlbinlog -vv -d oa --base64-output=DECODE-ROWS --set-charset=UTF8 /opt/mysql/8/data/binlog.000063 > fact_xxx.t

2. 把提取出来的全部sql语句，仅过滤出所需表的delete语句

cat fact_ypdlb.t | awk '/DELETE FROM/ && (/oa.fact_ypdlb/ || /`oa`.`fact_ypdlb`/){
while(1){
    print $0;
    getline;
    if($0 !~ /^###/){
        break;
    };
}
}' > oa.fact_ypdlb.delete.txt

3. 处理语句

sed -i 's/^### //g' oa.fact_ypdlb.delete.txt
sed -i "s/^DELETE FROM/INSERT INTO/g" oa.fact_ypdlb.delete.txt
sed -i "s/^WHERE/VALUES(/g" oa.fact_ypdlb.delete.txt

4.处理语句

sed -i '/@13=.*/a );' oa.fact_ypdlb.delete.txt
cat oa.fact_ypdlb.delete.txt  | awk -F"=|/*" '{
    if($0 ~ /^INSERT|^VALUES|^);/){
        print $0;
    }else{
        printf $2",";
    };
}' > oa.fact_ypdlb.delete.sql
sed -i "s/,);$/);/g" oa.fact_ypdlb.delete.sql
sed -i 's/,INSERT/);INSERT/g' oa.fact_ypdlb.delete.sql

以上的思路： 因为是没有备份，只能通过binlog的delete语句还原数据

1. 拿到需要恢复的binlog
2. 提取出来相关的delete语句

cat 111.sql | awk '/DELETE FROM/ {
    while(1){
        print $0;
        getline;
        if($0 !~ /^###/){
            break;
        };
    }
}' > 111.txt

3. 处理语句大致为insert into格式
4. 比较有局限性，因为中间可能有不同表。每个表字段数量不同，所以指定处理哪个字段不是很好。所以从这里开始思路有改变
5. 要给每一个insert into语句末尾加上分号(;)，可以匹配INSERT INTO关键字，在上面插入即可

sed -i '/INSERT INTO/i );' 002.txt  
# 第一行和最后一行不对，需要手动修改下。其实第一行无所谓，主要是最后一行少这一部分

6. 语句内的/ / 这一部分是注释，拿掉

sed -i 's#/\* .* \*/#,#g' 002.txt

7. 语句内的开头@1这样的关键是是不能有的，实测拿去执行的话sql显示成功，但数据其实没插入，所以这些关键字拿掉

sed -i 's/@.*=//g' 002.txt

8. 在第六步把字段末尾都替换成了逗号，这是正常的字段分隔符，但是SQL最后一个字段的末尾是不能有的，所以拿掉

sed -i ":label;N;s/,\n);/\n);/g;b label" 002.txt

这里用到了sed匹配换行符，简单搜索了下，sed原理跟换行有些关系，所以默认匹配不到换行，增加: label;N;xxxxx;b label 这一部分即可匹配成功