一次误删数据库,通过日志恢复数据的经历

清理数据的时候,因为有两张表名称相近,不小心就把task表中的数据清空了,

mysql数据库使用的是docker 的容器,二进制日志一直开启着

该命令查到了 删除的相关操作

docker exec mysql mysqlbinlog --verbose /var/lib/mysql/binlog.000011 | grep -A 10 -B 10 "DELETE.*sys_tasks"

 

 

该命令把部分日志文件生成到recovery.sql  文件中
docker exec mysql mysqlbinlog --start-datetime="2025-07-24 1:00:00" --stop-datetime="2025-07-24 9:30:00" --database=uav --verbose /var/lib/mysql/binlog.000011 > recovery.sql

内容是这样的

### DELETE FROM `uav`.`sys_tasks`
### WHERE
###   @1=14
###   @2='2025-06-07 12:46:02.253694'
###   @3='2025-06-07 12:48:26.904406'
###   @4='0001'
###   @5='第一架次'
###   @6=1
###   @7=2
###   @8=NULL
###   @9=6
###   @10='2025-06-07 12:46:04.000000'
###   @11='2025-06-07 12:48:26.000000'
###   @12=1
###   @13='2025-06-07'
###   @14=1
###   @15=1
###   @16=NULL
###   @17='手动'
然后写了一个python程序把delete 操作写成insert 语句,再运行,数据完美的回复了
 
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
处理大型SQL文件,将DELETE语句转换为INSERT语句
"""

import re
import os
from datetime import datetime

def parse_delete_statement(delete_text):
    """解析DELETE语句,提取参数值"""
    # 匹配参数模式 @1=value,但需要处理包含空格的时间戳
    param_pattern = r'@(\d+)=([^@\n]+?)(?=\s*###|$)'
    params = {}
   
    matches = re.findall(param_pattern, delete_text)
    for param_num, param_value in matches:
        param_value = param_value.strip()
       
        # 处理字符串值(去掉引号)
        if param_value.startswith("'") and param_value.endswith("'"):
            param_value = param_value[1:-1]
        # 处理NULL值
        elif param_value.upper() == 'NULL':
            param_value = None
        # 处理数字值(整数)
        elif param_value.isdigit():
            param_value = int(param_value)
        # 处理浮点数
        elif param_value.replace('.', '').replace('-', '').isdigit():
            param_value = float(param_value)
        # 其他情况保持为字符串(包括时间戳)
           
        params[int(param_num)] = param_value
   
    return params

def generate_insert_statement(params):
    """根据参数生成INSERT语句"""
    # 字段映射(根据之前提供的表结构)
    field_mapping = {
        1: 'id',
        2: 'created_at',
        3: 'updated_at',
        4: 'task_flight',
        5: 'task_name',
        6: 'task_flight_num',
        7: 'task_state',
        8: 'task_desc',
        9: 'user_id',
        10: 'start_time',
        11: 'end_time',
        12: 'region_id',
        13: 'plan_date',
        14: 'device_id',
        15: 'project_id',
        16: 'uid',
        17: 'task_type'
    }
   
    # 构建字段列表和值列表
    fields = []
    values = []
   
    for param_num in sorted(params.keys()):
        if param_num in field_mapping:
            field_name = field_mapping[param_num]
            field_value = params[param_num]
           
            fields.append(f'`{field_name}`')
           
            if field_value is None:
                values.append('NULL')
            elif isinstance(field_value, str):
                # 转义单引号
                field_value = field_value.replace("'", "''")
                values.append(f"'{field_value}'")
            else:
                values.append(str(field_value))
   
    # 生成INSERT语句
    insert_sql = f"""INSERT INTO `uav`.`sys_tasks` (
    {', '.join(fields)}
) VALUES (
    {', '.join(values)}
);"""
   
    return insert_sql

def process_sql_file(input_file, output_file, max_records=None):
    """处理SQL文件"""
    print(f"开始处理文件: {input_file}")
    print(f"输出文件: {output_file}")
   
    # 读取输入文件
    with open(input_file, 'r', encoding='utf-8') as f:
        content = f.read()
   
    # 分割DELETE语句
    delete_statements = re.split(r'### DELETE FROM', content)
   
    # 过滤掉空语句和注释
    delete_statements = [stmt.strip() for stmt in delete_statements if stmt.strip() and not stmt.startswith('--')]
   
    print(f"找到 {len(delete_statements)} 条DELETE语句")
   
    # 限制处理数量
    if max_records:
        delete_statements = delete_statements[:max_records]
        print(f"限制处理前 {max_records} 条记录")
   
    # 生成INSERT语句
    insert_statements = []
    success_count = 0
    error_count = 0
   
    for i, delete_stmt in enumerate(delete_statements, 1):
        try:
            # 解析DELETE语句
            params = parse_delete_statement(delete_stmt)
           
            if params:
                # 生成INSERT语句
                insert_sql = generate_insert_statement(params)
                insert_statements.append(insert_sql)
                success_count += 1
               
                if i % 100 == 0:
                    print(f"已处理 {i} 条记录...")
            else:
                error_count += 1
                print(f"警告: 第 {i} 条记录解析失败")
               
        except Exception as e:
            error_count += 1
            print(f"错误: 处理第 {i} 条记录时出错: {e}")
   
    # 写入输出文件
    with open(output_file, 'w', encoding='utf-8') as f:
        f.write("-- 自动生成的INSERT语句\n")
        f.write(f"-- 源文件: {input_file}\n")
        f.write(f"-- 生成时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n")
        f.write(f"-- 成功转换: {success_count} 条记录\n")
        f.write(f"-- 转换失败: {error_count} 条记录\n\n")
       
        for i, insert_sql in enumerate(insert_statements, 1):
            f.write(f"-- 记录 {i}\n")
            f.write(insert_sql)
            f.write("\n\n")
   
    print(f"\n处理完成!")
    print(f"成功转换: {success_count} 条记录")
    print(f"转换失败: {error_count} 条记录")
    print(f"输出文件: {output_file}")

def main():
    """主函数"""
    input_file = "a.sql"
    output_file = "large_insert_statements.sql"
   
    # 检查输入文件是否存在
    if not os.path.exists(input_file):
        print(f"错误: 输入文件 {input_file} 不存在")
        return
   
    # 询问用户是否要限制处理数量
    print("检测到大型SQL文件,建议分批处理")
    print("1. 处理前100条记录(推荐用于测试)")
    print("2. 处理前1000条记录")
    print("3. 处理所有记录(可能需要较长时间)")
   
    choice = input("请选择处理方式 (1/2/3): ").strip()
   
    max_records = None
    if choice == "1":
        max_records = 100
        output_file = "insert_statements_100.sql"
    elif choice == "2":
        max_records = 1000
        output_file = "insert_statements_1000.sql"
    elif choice == "3":
        output_file = "insert_statements_all.sql"
    else:
        print("无效选择,默认处理前100条记录")
        max_records = 100
        output_file = "insert_statements_100.sql"
   
    # 处理文件
    process_sql_file(input_file, output_file, max_records)

if __name__ == "__main__":
    main()
posted @ 2025-07-24 10:40  会飞的小虫子  阅读(17)  评论(0)    收藏  举报