MySQL TRUNCATE TABLE 誤操作後的資料恢復實戰：Binlog 定點恢復全流程

真實線上場景：TRUNCATE TABLE 導致 7 萬行資料遺失，停止操作時又有 1 萬行新寫入。本文以簡化場景演示完整恢復流程：利用備份起點 Binlog 位置，恢復到 TRUNCATE 發生前的狀態，再合併 TRUNCATE 後的新增資料。

測試環境與表結構

• Percona Server 5.6.16，Binlog 格式 MIXED，未啟用 GTID。
• 表 tb_wubx：id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(32)。

事件時間線

1. t1 程式寫入：INSERT ... VALUES('張三'),('李四'),('隔壁老王') → id 1,2,3
2. t2 誤操作：TRUNCATE TABLE tb_wubx → 資料清空，id 重置為 1
3. t3 程式繼續寫入：INSERT ... VALUES('老趙') → id 1；UPDATE ... SET name='老趙趙' WHERE id=1

現狀與目標

恢復步驟

1. 確認備份起點與 Binlog 分佈

mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000004 | 1144      |
+------------------+-----------+

備份位置：mysql-bin.000004, pos=4（建表語句後）。

2. 解析 Binlog 找到 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  <-- 第一次     |
| ...              | ...  | Write_rows  | ...       | ...         | t1 寫入                                            |
| ...              | 633  | Query       | ...       | 722         | use `wubx`; TRUNCATE TABLE tb_wubx  <-- 誤操作     |
| ...              | ...  | Write_rows  | ...       | ...         | t3 寫入                                            |
+------------------+------+-------------+-----------+-------------+----------------------------------------------------+

目標區間：pos 4 → 633（第一次 TRUNCATE 到誤操作 TRUNCATE 之間的寫入）。

3. 建立恢復庫並重放 Binlog

# 建立恢復庫 re_wubx 及同結構表 tb_wubx（略）
mysqlbinlog --rewrite-db='wubx->re_wubx' \
  --start-position=4 --stop-position=633 \
  mysql-bin.000004 | mysql -S /tmp/mysql.sock re_wubx

4. 驗證恢復結果

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

5. 合併 TRUNCATE 後的新增資料

INSERT INTO re_wubx.tb_wubx(name) SELECT name FROM wubx.tb_wubx;
RENAME TABLE wubx.tb_wubx TO wubx.bak_tb_wubx;
RENAME TABLE re_wubx.tb_wubx TO wubx.tb_wubx;

6. 最終結果

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

關鍵點總結

• 必須有備份起點 Binlog 位置或完整 Binlog 鏈，否則無法恢復。
• 恢復區間截止於誤操作 TRUNCATE 之前，避免主鍵衝突（TRUNCATE 重置 AUTO_INCREMENT）。
• 使用 --rewrite-db 將原庫重導向恢復庫，隔離環境。
• TRUNCATE 後的新增資料需單獨合併（INSERT ... SELECT + RENAME TABLE 原子切換）。
• 大表恢復前調大 max_allowed_packet。

此流程適用於 MIXED/ROW 格式，STATEMENT 格式需謹慎驗證。

1. 上一篇文章： Linux 產生隨機密碼指令集：/dev/urandom 與 UUID 兩種方法
2. 下一篇文章： MySQL 修改資料庫名稱變通方案：RENAME DATABASE 移除後的最佳實踐

MySQL 資料庫相關知識

MySQL 利用自我複製快速讓資料表資料成倍增長

前言 在效能測試、分頁功能驗證或大量資料模擬時，逐筆 INS

MySQL 外部安全：網絡訪問控制與權限表詳解

MySQL 網絡訪問控制：權限表結構與管理 MySQL 通過

MySQL 單引號導致索引失效：性能相差 100 倍實測

字符類型主鍵查詢時，條件值加不加單引號決定是否走索引。實測

Linux 異構網路環境下 MySQL 資料庫備份策略：本地、DVD 與網路備份實戰

針對中小型 Linux 網站（RHEL + PHP + My