MySQL误删除 binlog 还原恢复已删除数据实战超详细

硬盘有价，数据无价，数据库执行，谨慎操作！

binlog日志还原不适用于直接删表删库的误操作！

背景

在 2024年9月12日的操作过程中，由于实施SQL拼写错误，导致全表数据被误刷。此问题导致数据丢失，对业务运营产生了严重影响。见下方修复数据文档。从而进一步写一个误删除数据，通过binlog恢复的方案。

误修改恢复见我另一篇文章

生产环境MySQL where条件写错误改全表数据，binlog恢复https://blog.csdn.net/id_is_null/article/details/142253710

恢复流程

1、确定误操作时间

2024年9月14日 10点55-11点05

2、确定目标库表以及执行语句

库：newsee-budget 表：ns_budget_loan_bak

执行语句：delete from ns_budget_loan_bak;

3、导出相关时间binlog数据

4、找到对应语句以及pos区间

5、导出改动区间的sql

6、将binlog导出的sql转换为逆向插入sql

7、执行sql恢复

实战恢复

1、导出相关时间binlog数据

首先去binlog目录找一下，确定误操作时间的数据在哪个binlog文件。

这里可以看出是在 binlog.000925 文件里。

现在将对应时间binlog日志导出：

mysqlbinlog --no-defaults --start-datetime='2024-09-14 10:55:0' --stop-datetime='2024-09-14 11:05:0' --base64-output=decode-rows -d newsee-budget  -vv binlog.000934 > /root/test.sql

2、找到对应语句以及pos区间

如果不会找可以看顶部链接实战修复误改文章

结尾pos 107646361

开始pos 107070608

3、导出误删除区间的sql

这时候我们从binlog里面将这一区间的sql导出来

mysqlbinlog --no-defaults --start-position=107070608 --stop-position=107646361 --base64-output=decode-rows -vv binlog.000934 > /root/test1.sql

可以看到导出来的sql只有刚才那种连续的sql

4、将binlog导出的sql转换为逆向修复sql

可以自行理解意思，以及参考文章顶部另一文章有详细说明

shell sql字段有换行

sed -n '/^###/'p test1.sql > test2.sql
sed 's/### //g' test2.sql > test3.sql
sed 's#/.*##g' test3.sql > test4.sql
sed 's/^DELETE FROM/INSERT INTO/' test4.sql > test5.sql
sed ':a;N;$!ba;s/\nWHERE/ VALUES \(/g' test5.sql > test6.sql
sed 's/@[0-9]*=//g' test6.sql > test7.sql
sed 's/$/,/g' test7.sql > test8.sql
sed 's/VALUES (,/VALUES (/g' test8.sql > test9.sql
sed ':a;N;$!ba;s/,\nINSERT/\);\nINSERT/g' test9.sql > test10.sql
sed '$ s/.$/);/' test10.sql > test11.sql

shell sql一行一个

sed -n '/^###/'p test1.sql > test2.sql
sed 's/### //g' test2.sql > test3.sql
sed 's#/.*##g' test3.sql > test4.sql
sed 's/^DELETE FROM/INSERT INTO/' test4.sql > test5.sql
sed ':a;N;$!ba;s/\nWHERE\n  / VALUES \(/g' test5.sql > test6.sql
sed 's/@[0-9]*=//g' test6.sql > test7.sql
sed 's/$/,/g' test7.sql > test8.sql
sed 's/VALUES (,/VALUES (/g' test8.sql > test9.sql
sed ':a;N;$!ba;s/,\nINSERT/\);\nINSERT/g' test9.sql > test10.sql
sed '$ s/.$/);/' test10.sql > test11.sql
sed ':a;N;$!ba;s/,\n/,/g' test11.sql > test12.sql

通过 python3 的 re

import re

def binlog转换sql(input_file, output_file):
    with open(input_file, 'r', encoding='utf-8') as f:
        lines = f.readlines()

    # 第一步：提取以 ### 开头的行
    lines = [line for line in lines if line.startswith('### ')]

    # 第二步：去掉行首的 ### 
    lines = [line.replace('### ', '', 1) for line in lines]

    # 第三步：去掉行中的 / 开头的部分
    lines = [re.sub(r'/.*', '', line) for line in lines]

    # 第四步：将 DELETE FROM 替换为 INSERT INTO
    lines = [line.replace('DELETE FROM', 'INSERT INTO') for line in lines]

    # 第五步：将换行后的 WHERE 替换为 VALUES (
    text = ''.join(lines)
    text = re.sub(r'\nWHERE', ' VALUES (', text)

    # 第六步：删除 @n= 格式
    text = re.sub(r'@[0-9]+=','', text)

    # 第七步：在每行末尾添加逗号
    lines = text.splitlines()
    lines = [line + ',' if not line.endswith(',') else line for line in lines]

    # 第八步：将 VALUES (, 替换为 VALUES (
    text = '\n'.join(lines)
    text = text.replace('VALUES (,', 'VALUES (')

    # 第九步：将换行逗号替换为 ); 换行 INSERT
    text = re.sub(r',\nINSERT', ');\nINSERT', text)

    # 第十步：将文件最后的字符替换为 );
    if text.endswith(','):
        text = text[:-1] + ');'

    with open(output_file, 'w', encoding='utf-8') as f:
        f.write(text)

input_file = 'test1.sql'    # 原始导出的删除binlog日志
output_file = 'test11.sql'  # 转换插入的sql
binlog转换sql(input_file, output_file)

最后得到 test11.sql 重新将数据insert回去即可