MySQL 利用 Binlog 回滾誤執行 DELETE 操作實戰指南

生產環境誤執行 DELETE 無 WHERE 或條件錯誤，若開啟 binlog_format=ROW，可透過 mysqlbinlog 解析 Binlog、反轉為 INSERT 陳述式實現資料恢復。本文提供完整操作流程與 Python 自動化腳本。

前置條件

• binlog_format = ROW（唯有 ROW 格式記錄完整列前/後映像）。
• binlog_row_image = FULL（預設，記錄所有欄位）。
• Binlog 檔案未被 PURGE 或自動過期清理。

操作步驟

1. 解析目標時間區間 Binlog

mysqlbinlog --no-defaults \
  --start-datetime="2024-01-15 14:30:00" \
  --stop-datetime="2024-01-15 14:35:00" \
  -vv mysql-bin.000001 > /tmp/binlog_raw.txt

-vv 將 ROW 事件轉為可讀的偽 SQL 格式。

2. 分析 ROW 格式 DELETE 事件結構

### DELETE FROM `test`.`me_info`
### WHERE
###   @1=2165974  /* INT meta=0 nullable=0 is_null=0 */
###   @2='1984:03:17'  /* DATE meta=0 nullable=1 is_null=0 */
###   @3=NULL  /* DATE meta=765 nullable=1 is_null=1 */
###   @4='2012-10-25 00:00:00'  /* DATETIME meta=0 nullable=0 is_null=0 */
###   @5=''  /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
... (共 36 個欄位)

3. 關鍵轉換規則

4. Python 自動化轉換腳本 (restore.py)

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sys
import re

def parse_binlog(file_path, col_count):
    target_col = '@' + str(col_count)
    with open(file_path, 'r', encoding='utf-8', errors='ignore') as f:
        for line in f:
            line = line.rstrip('\n')
            if not line.startswith('###'):
                continue
            parts = line.split(' ', 3)
            if len(parts) < 4:
                continue
            # DELETE -> INSERT INTO
            if parts[1] == 'DELETE' and parts[2] == 'FROM':
                parts[1] = 'INSERT'
                parts[2] = 'INTO'
            # WHERE -> VALUES (
            if parts[1].strip() == 'WHERE':
                parts[1] = 'VALUES ('
            # 處理欄位值
            raw = parts[3].strip()
            if '@' in raw:
                col_name, val = raw.split('=', 1)
                val = val.split('/*')[0].strip()  # 移除註解
                # 字串類型加引號、逸出單引號
                if val.startswith("'") and val.endswith("'"):
                    val = val.replace("'", "''")
                    val = "'" + val + "'"
                # 負數處理
                elif re.match(r'-\d+\s+\(\d+\)', val):
                    val = val.split()[0]
                # NULL 直接保留
                elif val == 'NULL':
                    pass
                # 數值直接保留
                # 判斷是否為最後一欄
                if col_name == target_col:
                    val += ');'
                else:
                    val += ','
                parts[3] = val
            print(' '.join(parts[1:]))

if __name__ == '__main__':
    if len(sys.argv) != 3:
        print(f"Usage: {sys.argv[0]} binlog.txt column_count")
        sys.exit(1)
    parse_binlog(sys.argv[1], int(sys.argv[2]))

5. 執行轉換並恢復

# 假設表有 36 個欄位
python3 restore.py /tmp/binlog_raw.txt 36 > /tmp/restore.sql

# 檢查生成的 SQL
head -20 /tmp/restore.sql
# INSERT INTO `test`.`me_info` VALUES (2123269, '1990:11:12', NULL, 2, '', 0, '', -1, 0, 340800, 1, 0, '', ..., 1, NULL);

# 執行恢復
mysql -u root -p test < /tmp/restore.sql

進階提示

• 大量 DELETE：Binlog 檔案極大時，建議用 awk/sed 分塊處理，避免記憶體溢位。
• 混合 DML：同時間區間若有 UPDATE/INSERT，需過濾只處理 DELETE 事件（腳本已處理）。
• GTID 模式：MySQL 5.6+ 啟用 GTID 時，恢復後需 SET GTID_PURGED 避免主從複寫衝突。
• 預防勝於治療：生產環境強制 --i-am-a-dummy（safe-updates）模式，或使用 pt-archiver 歸檔替代直接刪除。

ROW vs STATEMENT 格式選擇

ROW 格式佔用空間大、但支援精準恢復；STATEMENT 格式體積小、但無法恢復非確定性 SQL（如 UUID()、NOW()）導致的資料差異。核心業務庫建議 binlog_format=ROW + expire_logs_days=7 + 定期備份 Binlog 至異地存儲。