DELETE误删：别急，看完这篇文章，实现数据恢复

问题场景：

有时候手动清空了库、表，误删了数据，这个时候你可能要面临被领导批评，敢于承担责任，弥补过错，那么我们如何实现数据恢复呢？此时别懊恼，耐心看完这篇文章，你就可以完美实现数据恢复。

解决思路：

MySQL数据库一般都会有开启binlog日志功能。

打开命令列界面。

第一步:先查看binlog功能是否开启

show variables like '%log_bin%';

 

log_bin为ON说明可以恢复，OFF说明没有开启binlog 。

开启方法：MySQL 中开启二进制日志（Binlog）-优快云博客

第二步：查看mysql数据存储路径，并找到日志文件

show variables like '%datadir%';

 

 第三步：找到mysql安装目录

 通过cmd进入bin目录，找到mysqlbinlog.exe，后续会使用该程序生成binlog日志记录文件

第四步.通过mysqlbinlog导出日志记录，默认是在bin目录下

mysqlbinlog --base64-output=decode-rows -v --database=order --start-datetime="2025-01-22 15:10:14" --stop-datetime="2025-01-22 16:10:00" D:\devsoft\mysql-8.0.20-winx64\data\binlog.000011 > mysqllog.sql

mysqlbinlog 命令的参数说明
--base64-output=decode-rows //数据转换正常的字符，如果不设置这个参数将显示base64的数据
--database=order  //数据库名（一个mysql数据库比较多，指定方便恢复）
--start-datetime="2025-01-22 15:10:00"  //恢复起始时间
--stop-datetime="2025-01-22 16:10:00"  //恢复结束时间
D:\devsoft\mysql-8.0.20-winx64\data\binlog.000011  //第二步：数据恢复的日志文件路径
mysqllog.sql    //恢复以后生成的文件名

dir .*sql

第五步：打开日志文件，查看是否有自己误删的日志记录 

第六步：将日志文件转成insert语句（使用python实现） 

步骤：

1. 获取数据库连接

2. 读取binlog文件

3. 截取delete语句

4. 获取数据库名、表名

5. 获取该表的字段，用于组装sql

6. 转成insert语句

7. 执行sql，也可以复制到navicat执行

8. 检查数据库表是否添加成功

实战代码如下：已实现插入数据库

import re
import pymysql
import chardet

# 数据库配置
MYSQL_SETTINGS = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "passwd": "root",
    "db": "order"
}


# 删除语句转换成insert语句
def delete_to_insert(content, connection):
    # 解析binlog文件
    lines = content.split("\n")
    insert_statements = []
    delete_conditions = []
    in_delete_section = False
    db_name = None
    table_name = None
    for line in lines:
        # 表名
        if "Table_map:" in line:
            db_table_name = extract_db_table_name(line)
            db_name = db_table_name.split(".")[0]
            table_name = db_table_name.split(".")[1]
        if "Delete_rows:" in line:
            in_delete_section = True
        elif in_delete_section and line.startswith("### DELETE FROM"):
            # sql开始
            continue
        elif in_delete_section and line.strip().startswith("### WHERE"):
            continue

        # sql结束
        elif in_delete_section and line.strip().startswith("# at"):
            # 说明当前删除的sql已经获取完毕
            in_delete_section = False

            # 生成sql
            sql_value = extract_columns_value(delete_conditions, connection, db_name, table_name)
            # 保存插入的sql语句
            insert_statements.append(sql_value)
            # 将表名和删除条件清空，准备存储下一轮的删除sql
            table_name = None
            delete_conditions = []
            continue

        # 拼接条件参数
        elif in_delete_section:
            delete_conditions.append(line)
            continue

    return insert_statements


# 查询该表的字段，用于拼接sql语句
def get_table_columns(connection, db_name, table_name):
    with connection.cursor() as cursor:
        cursor.execute(f"SHOW COLUMNS FROM `{db_name}`.`{table_name}`")
        columns = [column[0] for column in cursor.fetchall()]
    return columns


# 拼接sql
def extract_columns_value(delete_conditions, connection_db, db_name, table_name):
    column_values = []
    column_names = get_table_columns(connection_db, db_name, table_name)
    # 遍历where条件
    for part in delete_conditions:
        value = part.split("=")[1].strip()
        if "'" in value:
            value = value
        elif "." in value:
            value = float(value)
        else:
            value = int(value)
        column_values.append(value)
    # 构建 INSERT 语句
    insert_statement = f"INSERT INTO `{db_name}`.`{table_name}` ({', '.join(column_names)}) VALUES ({', '.join([str(val) for val in column_values])});"
    return insert_statement


file_path = 'D:\\devsoft\\mysql-8.0.20-winx64\\bin\\mysqllog.sql'


def main():
    try:
        connection = pymysql.connect(**MYSQL_SETTINGS)
        print("成功连接到 MySQL 数据库！")

        file_data = read_file(file_path)
        # 获取执行sql语句
        insert_statement_sql_list = delete_to_insert(file_data, connection)
        for insert_statement_sql in insert_statement_sql_list:
            # 插入sql语句
            execute_sql(insert_statement_sql, connection)
            print(f"要执行的sql语句：{insert_statement_sql}")

    except pymysql.err.OperationalError as e:
        print(f"连接 MySQL 数据库时出错: {e}")
    finally:
        # 关闭数据库连接
        if connection:
            connection.close()


# 执行sql
def execute_sql(sql, connection):
    try:
        # 创建游标对象
        with connection.cursor() as cursor:
            # 执行 SQL 语句
            cursor.execute(sql)

            # 如果是查询语句，获取查询结果
            if sql.strip().lower().startswith('select'):
                result = cursor.fetchall()
                print("查询结果：")
                for row in result:
                    print(row)
            else:
                # 对于非查询语句，提交事务
                connection.commit()
                print("SQL 语句执行成功，受影响的行数：", cursor.rowcount)

    except pymysql.Error as e:
        print(f"执行 SQL 语句时出现错误: {e}")


# 获取库、表名
def extract_db_table_name(input_string):
    # 查找 Table_map: 的位置
    start_index = input_string.find("Table_map:")
    if start_index == -1:
        return None
    # 截取 Table_map: 后面的部分
    table_map_part = input_string[start_index + len("Table_map:"):]
    # 按空格分割
    parts = table_map_part.replace("`", "").split()
    if len(parts) >= 1:
        # 提取表名
        table_name = parts[0].strip()
        print(f"table_name", table_name)
        return table_name
    return None


# 读取binlog文件
def read_file(file_path_1):
    try:
        with open(file_path_1, 'rb') as file:
            rawdata = file.read()
            result = chardet.detect(rawdata)
            encoding = result['encoding']
            print(f"检测到的编码为: {encoding}")
        with open(file_path_1, 'r', encoding=encoding) as file:
            data = file.read()
            return data
    except FileNotFoundError:
        print(f"文件 {file_path_1} 未找到，请检查文件路径是否正确。")
    except Exception as e:
        print(f"读取文件时发生错误: {e}")


if __name__ == "__main__":
    main()

 

总结:

1. 数据删除谨慎：避免直接使用 DELETE 语句删除数据，建议用字段做删除标记。若必须删除，删除前先备份数据。
2. 定期备份数据库：依据数据库规模合理安排备份频率，规模较小可每小时备份一次，较大则至少每天备份一次。
3. 保持操作细心：即便对 SQL 操作熟练，也需谨慎细心，防止因大意导致操作失误引发额外工作。
4. 开启 Binlog 功能：务必开启 MySQL 的 Binlog 功能，以便在数据出现问题时可进行追溯和恢复。
5. 祝愿大家工作顺顺利利，事业有成