MySQL Binlog 資料恢復與日誌清理實戰指南

前言：Binlog 爆滿磁碟的典型情境

主從複制運行一段時間後，發現磁碟空間暴增，罪魁禍首往往是未設定過期清理的 Binary Log。本文詳述 Binlog 過期設定、手動清理、以及誤操作（如 DROP TABLE) 後的資料恢復實戰流程。

一、Binlog 自動過期設定

檢查現狀

SHOW VARIABLES LIKE 'expire_logs_days';

預設值為 0（永不過期）。

動態調整（立即生效，重啟後失效）

SET GLOBAL expire_logs_days = 8;

永久生效（寫入 my.cnf）

[mysqld]
expire_logs_days = 8

修改後重啟 MySQL。建議配合備份策略設定天數（如全備週期+1天）。

二、手動清理 Binlog

查看現有 Binlog 列表

SHOW BINARY LOGS;

輸出範例：

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 | 150462942 |
| mysql-bin.000002 | 120332942 |
| mysql-bin.000003 | 141462942 |
+------------------+-----------+

清理指定檔案之前的日誌（推薦）

PURGE BINARY LOGS TO 'mysql-bin.000003';

保留 mysql-bin.000003 及之後的檔案。

按時間清理

PURGE BINARY LOGS BEFORE '2024-01-15 10:00:00';
-- 或清理 3 天前
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 3 DAY);

關鍵注意事項（主從環境）

1. 執行前在所有從庫執行 SHOW SLAVE STATUS 確認正在讀取的 Master_Log_File。
2. 目標清理檔案必須早於所有從庫目前讀取的檔案。
3. 建議先備份即將刪除的 Binlog 檔案。
4. 清理過程中無需停止從庫，正常複制不受影響。

三、從 Binlog 恢復誤刪資料（DROP TABLE 實戰）

前提條件

• Binlog 格式為 ROW 或 MIXED（STATEMENT 格式恢復較複雜）。
• 完整保留從建表到誤刪之間的所有 Binlog。
• 未執行 PURGE 或過期清理導致相關 Binlog 遺失。

步驟 1：定位關鍵 Position

# 檢視 Binlog 內容（可導向檔案較易閱讀）
mysqlbinlog /var/lib/mysql/mysql-bin.000001 > /tmp/binlog_000001.sql

# 搜尋 CREATE TABLE 與 DROP TABLE 位置
grep -n "CREATE TABLE.*xxx_db" /tmp/binlog_000001.sql
grep -n "DROP TABLE.*xxx_db" /tmp/binlog_000001.sql

假設找到：CREATE TABLE 後的 position=20，DROP TABLE 前的 position=1000。

步驟 2：擷取並重放 SQL

# 必須從 position 4 開始（Format_description_event），否則會報 Duplicate entry 錯誤
mysqlbinlog --start-position=4 --stop-position=1000 /var/lib/mysql/mysql-bin.000001 | mysql -u root -p

為何從 4 開始？ Binlog 檔案開頭 4 bytes 為 magic number，position 4 為第一個事件（Format_description_event），包含版本資訊，解析器需由此初始化。

步驟 3：按時間區間恢復（替代方案）

mysqlbinlog --start-datetime="2024-01-14 10:20:00" --stop-datetime="2024-01-15 01:25:00" /var/lib/mysql/mysql-bin.000001 | mysql -u root -p

跨多個 Binlog 檔案恢復

# 合併多個檔案按順序重放
mysqlbinlog mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 | mysql -u root -p

四、恢復過程常見錯誤與對策

五、最佳實踐建議

• 啟用 GTID 複制（MySQL 5.6+）：簡化故障轉移與恢復定位。
• Binlog 格式設為 ROW：binlog_format=ROW，確保恢復準確性。
• 定期全量備份 + Binlog 增量備份：使用 mysqldump --master-data=2 或 XtraBackup。
• 監控磁碟空間：設定告警閾值（如 80%），避免寫入阻塞。
• 重要操作前手動備份：FLUSH LOGS 產生新 Binlog 切割點，便於後續精準恢復。

總結

Binlog 是 MySQL 時間機器，善用 expire_logs_days 自動清理、PURGE 手動控制、mysqlbinlog 精準恢復，配合完善備份策略，才能在災難發生時從容應對。切記：沒有測試過的備份，不叫備份。