一次误删数据库,通过日志恢复数据的经历
清理数据的时候,因为有两张表名称相近,不小心就把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()