MySQL 誤刪資料緊急回滾實戰：binlog2sql 從 Binlog 生成反向 SQL

前言：沒有備份怎麼辦？Binlog 是最後防線

線上環境 DELETE FROM t WHERE 1=1 忘加條件、UPDATE 寫錯 WHERE 導致全表覆蓋，這時若開啟了 binlog_format=ROW，Binlog 記錄了每行的前後映像，配合開源工具 binlog2sql 可在分鐘級生成精確的回滾 SQL，挽回損失。本文實戰演示全流程。

前置條件

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1000M
binlog_format = ROW          # 關鍵：行級模式才記錄每行變更

重啟 MySQL 生效。未開 Binlog 或非 ROW 格式，無法行級回滾。

安裝 binlog2sql

git clone https://github.com/danfengcao/binlog2sql.git
cd binlog2sql
pip install -r requirements.txt   # 需 Python 2.7/3.4+、mysqlclient

實戰場景：誤刪表 f 全部資料

-- 原始資料
SELECT * FROM f;
+-----+-----+---------------------+
| uid | did | updateTime          |
+-----+-----+---------------------+
| 1   | 18  | 2016-12-06 12:28:18 |
| 2   | 19  | 2016-12-06 12:55:56 |
| 3   | 20  | 2016-12-07 14:00:58 |
| 4   | 21  | 2016-12-07 14:01:00 |
+-----+-----+---------------------+

-- 誤操作
DELETE FROM f;   -- 4 rows affected
SELECT * FROM f; -- Empty set

回滾步驟

1. 定位 Binlog 位置：

   mysql> SHOW MASTER LOGS;
   +------------------+-----------+
   | Log_name         | File_size |
   +------------------+-----------+
   | mysql-bin.000001 | 12262268  |
   | mysql-bin.000002 | 132776    |  <-- 最新
   +------------------+-----------+
   
   # 解析最新 Binlog，找 DELETE 事件位置
   python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t f --start-file='mysql-bin.000002'
   

   輸出顯示 4 條 DELETE，位置 start 4 end 314。
2. 生成回滾 SQL（-B 參數反向）：

   python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t f \
     --start-file='mysql-bin.000002' --start-pos=4 --end-pos=314 -B
   

   輸出：

   INSERT INTO `test`.`f`(`did`, `updateTime`, `uid`) VALUES (21, '2016-12-07 14:01:00', 4);
   INSERT INTO `test`.`f`(`did`, `updateTime`, `uid`) VALUES (20, '2016-12-07 14:00:58', 3);
   INSERT INTO `test`.`f`(`did`, `updateTime`, `uid`) VALUES (19, '2016-12-06 12:55:56', 2);
   INSERT INTO `test`.`f`(`did`, `updateTime`, `uid`) VALUES (18, '2016-12-06 12:28:18', 1);
   

3. 驗證 SQL 正確後直接管道執行回滾：

   python binlog2sql.py ... -B | mysql -h127.0.0.1 -P3306 -uadmin -p'admin'
   

4. 確認資料恢復：

   mysql> SELECT * FROM f;
   +-----+-----+---------------------+
   | uid | did | updateTime          |
   +-----+-----+---------------------+
   | 1   | 18  | 2016-12-06 12:28:18 |
   | 2   | 19  | 2016-12-06 12:55:56 |
   | 3   | 20  | 2016-12-07 14:00:58 |
   | 4   | 21  | 2016-12-07 14:01:00 |
   +-----+-----+---------------------+
   

常見問題與限制

避坑建議

1. 核心庫強制開啟 ROW 模式 Binlog，保留至少 48 小時。
2. 生產環境禁用 DELETE/UPDATE 無 WHERE：sql_safe_updates=1、或用 pt-kill 攔截。
3. 定期演練 binlog2sql 回滾流程，確保工具版本兼容、權限足夠、網路通暢。
4. DDL 變更前必備份，或使用 gh-ost/pt-online-schema-change 線上變更可回滾。

Binlog + binlog2sql 是 DML 誤操作的「後悔藥」，但前提是你預先配置好 Binlog 並熟悉工具用法。把這套流程寫進運維手冊，關鍵時刻能救命。