记录一次数据恢复，mysql8

SHOW VARIABLES LIKE 'log_bin_basename';

Variable_name Value
log_bin_basename E:\environment\mysql-8.0.41-winx64\data\binlog

SHOW MASTER STATUS;

File Position Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Set
binlog.000046 1889087

保存日志，（E:\environment\mysql-8.0.41-winx64\data\binlog.000046  这个是日志的路径）
mysqlbinlog --no-defaults --start-datetime="2025-11-03 16:40:00" --stop-datetime="2025-11-03 17:10:00" --base64-output=DECODE-ROWS -v "E:\environment\mysql-8.0.41-winx64\data\binlog.000046" > "E:\recovery_data1.sql"

import re

def generate_recovery_sql(binlog_file_path, output_file_path):
    with open(binlog_file_path, 'r', encoding='utf-16') as f_in:
        binlog_content = f_in.read()

    # Regex to find DELETE statements and capture the SET_VAR lines that contain the column values
    # We look for '### DELETE FROM `mall_wxyoupin_com`.`yoshop_upload_file`' followed by '### WHERE' and then '### SET_VAR' lines
    delete_pattern = re.compile(
        r'### DELETE FROM `mall_wxyoupin_com`\.`yoshop_upload_file`\n'
        r'### WHERE\n'
        r'(.*?)(?=\n### @|\n# at |$)',
        re.DOTALL
    )

    # Column mapping based on the table structure and @ variable indices
    column_mapping = {
        1: 'file_id',
        2: 'group_id',
        3: 'channel',
        4: 'storage',
        5: 'domain',
        6: 'file_type',
        7: 'file_name',
        8: 'file_path',
        9: 'file_size',
        10: 'file_ext',
        11: 'cover',
        12: 'uploader_id',
        13: 'is_recycle',
        14: 'is_delete',
        15: 'store_id',
        16: 'create_time',
        17: 'update_time',
    }

    recovery_statements = []

    for match in delete_pattern.finditer(binlog_content):
        where_clause_block = match.group(1)
        
        # Extract all @ variables and their values from the WHERE clause block
        set_var_matches = re.findall(r'###   @(\d+)=([\s\S]*?)(?=\n###   @|\n)', where_clause_block)
        
        row_values = {}
        for var_index_str, var_value_str in set_var_matches:
            var_index = int(var_index_str)
            # Clean up the value string: remove leading/trailing spaces and quotes if present
            var_value = var_value_str.strip()
            if var_value.startswith("''") and var_value.endswith("''"):
                var_value = var_value[1:-1] # Remove outer single quotes
            elif var_value.startswith("'") and var_value.endswith("'"):
                var_value = var_value[1:-1] # Remove outer single quotes
            
            row_values[var_index] = var_value

        # Construct the INSERT statement
        columns = []
        values = []
        for i in range(1, len(column_mapping) + 1):
            if i in row_values:
                columns.append(f'`{column_mapping[i]}`')
                # Handle string values by quoting them, numeric values directly
                if isinstance(row_values[i], str) and not row_values[i].isdigit():
                    values.append(f"'{row_values[i].replace("'", "''")}'") # Escape single quotes within string
                else:
                    values.append(str(row_values[i]))
            else:
                # If a column is not found in the DELETE statement, it might be NULL or have a default value.
                # For recovery, it's safer to explicitly set it to NULL or its default if known.
                # For simplicity, we'll assume all columns are present in the DELETE's WHERE clause for now.
                # If not, this part needs more sophisticated handling based on schema defaults.
                pass # This case should ideally not happen if all columns are in the WHERE clause

        if columns and values:
            insert_statement = f"INSERT INTO `mall_wxyoupin_com`.`yoshop_upload_file` ({', '.join(columns)}) VALUES ({', '.join(values)});"
            recovery_statements.append(insert_statement)

    with open(output_file_path, 'w', encoding='utf-8') as f_out:
        for statement in recovery_statements:
            f_out.write(statement + '\\n')

    return len(recovery_statements)

if __name__ == "__main__":
    binlog_file = 'd:/phpstudy_pro/WWW/yp/yoshop2.0/数据库修改记录/圈子/recovery_data1.sql'
    output_sql_file = 'd:/phpstudy_pro/WWW/yp/yoshop2.0/数据库修改记录/圈子/recovery_yoshop_upload_file.sql'
    
    print(f"Starting recovery SQL generation from {binlog_file}...")
    num_statements = generate_recovery_sql(binlog_file, output_sql_file)
    print(f"Generated {num_statements} INSERT statements into {output_sql_file}")
    print("Recovery SQL generation complete. Please review the generated SQL file before executing it.")

 

python d:/phpstudy_pro/WWW/yp/yoshop2.0/generate_recovery_sql.py 执行会得到 recovery_yoshop_upload_file.sql