背景

數據誤刪是一個比較嚴重的場景

1.典型誤操作場景

場景1：DELETE FROM orders WHERE status=0 → 漏寫AND create_time>=‘2025-06-20’
場景2：DROP TABLE customer → 誤執行于生產環境

認識 binlog

1.binlog 的核心作用

• 記錄所有 DDL/DML 操作（不含 SELECT）
• ROW格式binlog記錄數據行的完整鏡像（例：DELETE操作保存被刪行的所有字段值)

2.三種格式對比

• ROW格式：可解析具體數據變更（如DELETE的行數據），是數據恢復的前提。
• STATEMENT格式：僅記錄SQL語句（如DELETE FROM user），無法還原誤刪的具體數據。
• MIXED格式：混合模式，可能無法保證所有操作記錄完整數據3。

3.binlog恢復原理與前提條件

binlog為什么能恢復數據？

• ROW格式記錄物理變更：保存每行數據的修改前/后鏡像（DELETE記錄完整被刪行數據）。
• 事務連續性：通過start position和end position精準定位事務邊界。
• 與Undo Log的區別：binlog持久化到磁盤，不受事務提交影響。

4.無法恢復的場景

• binlog未開啟或格式為STATEMENT（僅記錄SQL語句，無原始數據）
• TRUNCATE TABLE操作（直接清空物理文件，不記錄行數據）
• binlog已被自動清理（expire_logs_days過期）或手動PURGE
• 大事務未提交時服務器崩潰（事務不完整）
• …

5.如何開啟 binlog

• 修改 my.cnf 配置（代碼示例）：

[mysqld]
server_id=1 
log_bin=mysql-bin 
binlog_format=ROW  # 必須為ROW格式才能解析具體數據 
expire_logs_days=7  # 自動清理周期 

• 驗證是否開啟：SHOW VARIABLES LIKE '%log_bin%';

恢復步驟

1. 確定操作時間與特征

• 通過業務日志/監控系統確認誤刪時間（如 2025-06-16 18:30:00）

• 提取SQL特征（如 delete from employee_performance where department = ‘研發’）

• 查看最近執行的刪除語句（需開啟general_log）

SELECT * FROM mysql.general_log  
WHERE argument LIKE '%DELETE FROM your_table%' 
ORDER BY event_time DESC LIMIT 1;

2. 列出時間范圍內的所有binlog文件

# Linux/Mac（需替換實際路徑）
ls -l /var/lib/mysql/mysql-bin.0*
# 解析binlog索引文件（通常為mysql-bin.index ）
cat /var/lib/mysql/mysql-bin.index # Windows（PowerShell）
dir
# 解析binlog索引文件（通常為mysql-bin.index ）
cat .\mysql-binlog.index# mysql
SHOW BINARY LOGS;  -- 列出所有binlog文件

3. 解析binlog找到誤操作事件

舉例

• 誤刪表：employee_performance
• 誤刪表所屬庫：test_demo
• 誤刪大致時間段：2025-06-17 23:00:00-2025-06-17 23:30:00
• 誤刪數據所屬binlog文件名稱：mysql-binlog.000002 (根據誤刪時間段推導)
• 誤刪數據量：124514行（根據delete sql執行結果得出）

從binlog提取SQL
使用mysqlbinlog解析binlog：

# 查找2025-06-18 23:00:00-2025-06-18 23:30:00間的binlog
#并輸出到delete_sql.sql
mysqlbinlog --no-defaults --user=root -p -d test_demo --start-datetime="2025-06-17 23:00:00" --stop-datetime="2025-06-17 23:30:00" --base64-output=decode-rows -vv ../data/mysql-binlog.000002 > delete_sql.sql

輸出文件內容示例

注意事項：

• –start/stop-datetime（時間范圍）依賴系統時鐘同步），受服務器時間漂移影響。
• 導出的文件并不一定是一個完整的事務(可能漏掉邊界事務
  ) 觀察此批delete from 末尾是否含有commit標志
  

數據恢復

在上一步我們已經導出了delete_sql.sql文件，接下來需要根據這個文件內容進行
過濾并重建數據

1.數據轉換（java)

反轉操作邏輯（將DELETE轉為INSERT）
轉換代碼如下：

        Path outputPath = Paths.get(outputFilePath);// 步驟一: 讀取輸入文件的所有行，使用 UTF-8 編碼，并替換無法解析的字符。List<String> lines;try (BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(inputFilePath),StandardCharsets.UTF_8.newDecoder().onMalformedInput(CodingErrorAction.REPLACE).onUnmappableCharacter(CodingErrorAction.REPLACE)))) {lines = reader.lines().collect(Collectors.toList());}// 步驟二、三、四: 過濾并處理有效的 binlog 行記錄。List<String> processedLines = lines.stream()// 僅保留以 "### " 開頭的行，這些是我們要處理的 binlog DML 語句。.filter(line -> line.startsWith("### "))// 去掉行首的 "### " 標識。.map(line -> line.substring(4))// 使用正則表達式移除從 "/*" 開始到行尾的注釋。.map(line -> line.replaceAll("(?s)/\\*.*", ""))// 將 "DELETE FROM" 替換為 "INSERT INTO"，為逆向生成 INSERT 語句做準備。.map(line -> line.replace("DELETE FROM", "INSERT INTO")).collect(Collectors.toList());// 步驟五: 將所有處理過的行用換行符連接成一個單獨的字符串，并將 "WHERE" 替換為 " VALUES ("。String text = String.join("\n", processedLines);text = text.replace("\nWHERE", " VALUES (");// 步驟六: 移除 binlog 中的字段位置標識，例如 "@1=", "@2=" 等。text = text.replaceAll("@[0-9]+=", "");// 步驟七: 對每一行進行整理，并在行尾添加逗號，為構建 VALUES 子句做準備。text = Stream.of(text.split("\n"))// 移除每行首尾的空白字符。.map(String::trim)// 過濾掉處理后可能產生的空行。.filter(line -> !line.isEmpty())// 如果行尾沒有逗號，則添加一個，確保 VALUES 子句中的值都以逗號分隔。.map(line -> line.endsWith(",") ? line : line + ",").collect(Collectors.joining("\n"));// 步驟八: 修正由于前面步驟可能產生的 "VALUES (," 寫法，將其規范為 "VALUES ("。text = text.replace("VALUES (,", "VALUES (");// 步驟九: 將多個 INSERT 語句正確地分隔開。將前一個 INSERT 的值與后一個 INSERT 語句用 ");" 連接。text = text.replace(",\nINSERT", ");\nINSERT");// 步驟十: 閉合最后一個 INSERT 語句。如果字符串以逗號結尾，則將其替換為 ");"。if (text.endsWith(",")) {text = text.substring(0, text.length() - 1) + ");";}// 將最終處理好的字符串以 UTF-8 編碼寫入到指定的輸出文件中。Files.write(outputPath, text.getBytes(StandardCharsets.UTF_8));

詳細轉換代碼見此項目（含測試用例)

mysql-binlog2sql

---

2.數據校驗

運行完成后可以看到輸入文件已經轉換完成

條數也是對的 一條數據10行 1245140/10 = 1245410行和上面的模擬數據一致。

3.數據導入

重新執行轉換的sql文件導入即可

SOURCE /tmp/restore.sql; 

番外

binlog 文件分割機制

當通過 binlog 恢復誤刪數據時，binlog 文件的大小限制和跨文件存儲問題是影響恢復完整性的關鍵因素。

文件大小限制
MySQL 默認通過 max_binlog_size 控制單個 binlog 文件大小（默認 1GB）

大事務跨文件存儲
若單個事務（如大表 DELETE）產生的日志量超過當前要落入的binlog文件 的max_binlog_size，該事務會跨多個 binlog 文件存儲。

示例：一個 5GB 的事務可能分布在 mysql-bin.000001～mysql-bin.000005 中。

文件命名規則
Binlog 按順序生成：mysql-bin.000001 → mysql-bin.000002 → …

新文件在以下情況創建：

• 當前文件 ≥ max_binlog_size
• 執行 FLUSH LOGS 或重啟 MySQL。

確認binlog是否開啟（Value=ON）

SHOW VARIABLES LIKE 'log_bin';  

查看MySQL的binlog模式是否為ROW

show global variables like "binlog%";

查詢ROW格式需為FULL（記錄完整行數據）

SHOW VARIABLES LIKE 'binlog_row_image'; 

檢查binlog過期時間（確保日志未被自動清理）MySQL 8.0+默認30天

SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

常用解析binlog文件參數詳解

-no-defaults:

–start-datetime：指定開始時間（過濾早于該時間的日志） 示例： --start-datetime=“2025-06-15 14:00:00”

–stop-datetime： 指定結束時間（過濾晚于該時間的日志） 示例： --stop-datetime=“2025-06-15 15:00:00”

–read-from-remote-server： 從遠程MySQL服務器讀取binlog（需配合–host）
示例：–read-from-remote-server --host=192.168.1.100

–user --password ：指定連接MySQL的用戶名和密碼（遠程解析時必填）示例： --user=root --password=your_password

-base64-output=decode-rows：解析ROW格式數據

-d ：指定庫

注意事項

時間格式必須精確
日期時間需用引號包裹，格式為 “YYYY-MM-DD HH:MM:SS”（如 “2025-06-15 14:00:00”）。
權限要求
本地解析：需操作系統用戶有binlog文件讀取權限。
遠程解析：MySQL用戶需有REPLICATION CLIENT權限[]。
大事務處理
若事務過大導致mysqlbinlog內存溢出，可分段解析

-start/stop-position --start/stop-datetime 區別詳解

1. --start/stop-position（物理位置）

原理
直接指向binlog事件的物理存儲位置（如 # at 54321），通過解析binlog文件的固定偏移量定位。
特點
? 100%精確到具體事務
? 不受時間同步問題影響
? 需先通過其他方式獲取位置號

2. --start/stop-datetime（時間范圍）

原理
根據binlog事件頭的時間戳字段過濾（如 #250618 10:00:00），依賴系統時鐘同步。
特點
? 無需提前知道位置號
? 最大可能有1秒誤差（可能漏掉邊界事務）
? 受服務器時間漂移影響