萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 誤刪表/資料緊急恢復:Binlog 解析與 Java 程式提取實戰

生產環境誤執行 DROP TABLE 或 DELETE 無 WHERE,若未開啟 Binlog 幾乎無法恢復。本文記錄真實案例:利用已開啟的 Binlog,透過 mysqlbinlog 導出 SQL、自寫 Java 程式過濾目標表操作、重放恢復 10 萬+ 列資料的完整過程。

一、前置條件:必須開啟 Binlog


[mysqld]
log_bin = /var/lib/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7

驗證:SHOW VARIABLES LIKE 'log_bin'; → ON

二、緊急響應步驟

  1. 立即停止寫入:應用層切流量、設 read_only=1,防止新資料覆蓋 Binlog 位點。
  2. 確認誤操作時間點:從應用日誌、監控、開發口中獲取大致時間。
  3. 定位 Binlog 檔案:ls -lt /var/lib/mysql/mysql-bin.* 找涵蓋該時間段的檔案。

三、導出 Binlog 為可讀 SQL


# 單檔導出
mysqlbinlog --base64-output=DECODE-ROWS -v /var/lib/mysql/mysql-bin.000015 > /tmp/binlog_000015.sql

# 批次導出多檔 (按時間範圍)
mysqlbinlog --start-datetime="2024-01-15 10:00:00" --stop-datetime="2024-01-15 12:00:00" \
  /var/lib/mysql/mysql-bin.00001[5-9] > /tmp/binlog_recover.sql

--base64-output=DECODE-ROWS -v 將 ROW 格式解碼為可讀的偽 SQL (含 ### INSERT INTO ... 註釋)。

四、自寫 Java 程式精準提取目標表操作

原始導出檔包含所有庫表操作,需過濾特定表 (如 task) 的 INSERT/UPDATE/DELETE,並補全分號。


package com.recovery.binlog;

import java.io.*;
import java.util.*;

public class BinlogExtractor {
    public static void main(String[] args) throws Exception {
        List srcFiles = Arrays.asList(
            "C:/recovery/binlog_000015.sql",
            "C:/recovery/binlog_000016.sql",
            "C:/recovery/binlog_000017.sql"
        );
        String targetTable = "TASK";  // 大寫比對
        String outFile = "C:/recovery/task_recover.sql";

        try (BufferedWriter bw = new BufferedWriter(new FileWriter(outFile))) {
            for (String src : srcFiles) {
                try (BufferedReader br = new BufferedReader(new FileReader(src))) {
                    String line;
                    while ((line = br.readLine()) != null) {
                        String up = line.trim().toUpperCase();
                        // 匹配 INSERT INTO `task` / UPDATE `task` / DELETE FROM `task`
                        if (up.startsWith("INSERT INTO `" + targetTable + "`") ||
                            up.startsWith("UPDATE `" + targetTable + "`") ||
                            up.startsWith("DELETE FROM `" + targetTable + "`")) {
                            // 確保以分號結尾
                            if (!line.trim().endsWith(";")) line += ";";
                            bw.write(line);
                            bw.newLine();
                        }
                    }
                }
            }
        }
        System.out.println("Extracted to " + outFile);
    }
}

關鍵處理:

  • 大小寫不敏感比對表名
  • 補全分號 (原始導出可能換行未帶分號)
  • 對於 INSERT 先補 DELETE 避免主鍵衝突 (視業務決定)

五、恢復驗證


# 1. 建立臨時庫驗證
mysql -u root -p -e "CREATE DATABASE recover_test;"
mysql -u root -p recover_test < task_recover.sql

# 2. 抽樣核對行數、關鍵欄位
mysql -u root -p recover_test -e "SELECT COUNT(*) FROM task;"
mysql -u root -p recover_test -e "SELECT * FROM task WHERE id IN (1001,1002,1003);"

# 3. 確認無誤後導入生產庫 (或重命名表交換)
mysql -u root -p production_db < task_recover.sql

六、經驗總結

  • Binlog 是最後防線:務必開啟 ROW 格式、足夠保留天數、獨立磁碟。
  • 備份不等於恢復:定期演練「從 Binlog 恢復單表」流程,驗證腳本可用性。
  • 工具鏈標準化:將 mysqlbinlog + 過濾腳本封裝為內部 CLI 工具,一鍵生成恢復腳本。
  • 權限最小化:應用賬號無 DROP、TRUNCATE 權限,DDL 走變更管理平台。

這次事故在 30 分鐘內完成從發現到資料全量恢復,關鍵在於:Binlog 完整、導出解析工具鏈成熟、團隊演練到位。

copyright © 萬盛學電腦網 all rights reserved