您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL TRUNCATE 誤操作數據恢復實戰：基於 binlog 定點恢復全流程

編輯：MySQL 綜合教學

場景還原

表 tb_wubx（InnoDB、id PK AUTO_INCREMENT）。
備份基點：mysql-bin.000004 pos 4（僅建表結構）。
T1：正常寫入 3 行（id=1,2,3）。
T2：誤 TRUNCATE TABLE tb_wubx（自增重置為 1）。
T3：新寫入 1 行（id=1，名為「老趙趙」）。
目標：恢復 T1 的 3 行，保留 T3 的 1 行，最終 id=1,2,3,4 連續無衝突。

核心原則

只重放「誤操作前」的 binlog：從備份基點到 TRUNCATE 語句的 stop-position。
重放至臨時庫/表：避免主鍵衝突污染生產庫。
合併「誤操作後」的增量數據：用 INSERT ... SELECT 補齊。
原子切換：RENAME TABLE 秒級完成新舊表交換。

實戰步驟

1. 定位 TRUNCATE 位置

mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000004';
+------------------+------+------------+-----------+-------------+----------------------------------+
| Log_name         | Pos  | Event_type | Server_id | End_log_pos | Info                             |
+------------------+------+------------+-----------+-------------+----------------------------------+
| mysql-bin.000004 | 4    | Format_desc| ...       | 120         | ...                              |
| mysql-bin.000004 | 120  | Query      | ...       | 209         | use `wubx`; TRUNCATE TABLE tb_wubx  <-- 第一次 TRUNCATE (測試殘留)
| ...              | ...  | ...        | ...       | ...         | ...                              |
| mysql-bin.000004 | 633  | Query      | ...       | 722         | use `wubx`; TRUNCATE TABLE tb_wubx  <-- 目標誤操作 TRUNCATE
| ...              | ...  | ...        | ...       | ...         | 後續 INSERT/UPDATE               |
+------------------+------+------------+-----------+-------------+----------------------------------+

目標區間：start-position=4（備份基點） → stop-position=633（誤 TRUNCATE 前）。

2. 建立恢復庫並重放

mysql> CREATE DATABASE re_wubx;
mysql> USE re_wubx;
mysql> CREATE TABLE tb_wubx (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(32)) ENGINE=InnoDB;

shell> mysqlbinlog --rewrite-db='wubx->re_wubx' \
    --start-position=4 --stop-position=633 \
    mysql-bin.000004 | mysql -S /tmp/mysql.sock re_wubx

3. 驗證恢復結果

mysql> SELECT * FROM re_wubx.tb_wubx;
+----+--------------+
| id | name         |
+----+--------------+
| 1  | 張三         |
| 2  | 李四         |
| 3  | 隔壁老王     |
+----+--------------+

4. 合併誤操作後的增量（T3 階段數據）

mysql> INSERT INTO re_wubx.tb_wubx(name) SELECT name FROM wubx.tb_wubx;
Query OK, 1 row affected

5. 原子切換上線

mysql> RENAME TABLE wubx.tb_wubx TO wubx.bak_tb_wubx,
                re_wubx.tb_wubx TO wubx.tb_wubx;

6. 最終驗證

mysql> SELECT * FROM wubx.tb_wubx;
+----+--------------+
| id | name         |
+----+--------------+
| 1  | 張三         |
| 2  | 李四         |
| 3  | 隔壁老王     |
| 4  | 老趙趙       |
+----+--------------+

關鍵參數速查

參數	作用
`--rewrite-db='src->dst'`	庫名重映射，將 binlog 中 `src` 替換為 `dst`
`--start-position`	起始位點（含）
`--stop-position`	結束位點（不含），精確到誤操作語句前
`--base64-output=decode-rows`	ROW 格式 binlog 解碼為可讀 SQL（調試用）
`-v`	顯示行事件詳細內容

避坑提醒

GTID 模式下：改用 --include-gtids / --exclude-gtids 定位事務集合。
MIXED/STATEMENT 格式：TRUNCATE 記錄為 Query 事件，定位較易；ROW 格式需解碼 Table_map+Delete_rows 確認。
主鍵衝突：若誤操作後新數據的 PK 與恢復數據重疊，需在合併階段 INSERT ... ON DUPLICATE KEY UPDATE 或應用層重新分配 ID。
大表恢復：考慮並行 mysqlbinlog | mysql 多線程導入，或用 myloader。