生產環境誤執行 DROP TABLE 或 DELETE 無 WHERE,若未開啟 Binlog 幾乎無法恢復。本文記錄真實案例:利用已開啟的 Binlog,透過 mysqlbinlog 導出 SQL、自寫 Java 程式過濾目標表操作、重放恢復 10 萬+ 列資料的完整過程。
[mysqld]
log_bin = /var/lib/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7
驗證:SHOW VARIABLES LIKE 'log_bin'; → ON
read_only=1,防止新資料覆蓋 Binlog 位點。ls -lt /var/lib/mysql/mysql-bin.* 找涵蓋該時間段的檔案。
# 單檔導出
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 ... 註釋)。
原始導出檔包含所有庫表操作,需過濾特定表 (如 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
ROW 格式、足夠保留天數、獨立磁碟。mysqlbinlog + 過濾腳本封裝為內部 CLI 工具,一鍵生成恢復腳本。DROP、TRUNCATE 權限,DDL 走變更管理平台。這次事故在 30 分鐘內完成從發現到資料全量恢復,關鍵在於:Binlog 完整、導出解析工具鏈成熟、團隊演練到位。