MySQL mysqlbinlog 工具實戰：基於位置與時間點的二進制日誌恢復完整指南

MySQL mysqlbinlog 工具實戰：基於位置與時間點的二進制日誌恢復

核心概念

• 全備 + 增量日誌：每日全量備份 + 每小時 binlog 備份
• 恢復目標：恢復至任意 binlog 位置或時間點 (Point-in-Time Recovery, PITR)
• 關鍵工具：mysqlbinlog - 解析並應用二進制日誌

Server 端查看 Binlog

RESET MASTER;                    -- 清空現有日誌 (謹慎使用)
SHOW BINARY LOGS;                -- 列出日誌檔案
SHOW BINLOG EVENTS;              -- 查看第一個日誌事件
SHOW BINLOG EVENTS IN 'bin.000002' FROM 120 LIMIT 2,3;  -- 進階查看

mysqlbinlog 核心參數

恢復實戰指令

單一檔案直接管道恢復

mysqlbinlog bin.000001 | mysql -u root -p
# 或先導出 SQL 再執行
mysqlbinlog bin.000001 > /tmp/bin.000001.sql
mysql> SOURCE /tmp/bin.000001.sql;

多檔案同時處理 (避免臨時表丟失)

# 正確：單次調用處理多檔
mysqlbinlog bin.000001 bin.000002 bin.000003 \
  --start-position=120 --stop-datetime='2024-01-15 10:00:00' \
  | mysql -u root -p

# 通配符批次 (bash)
mysqlbinlog bin.00000[1-3] --start-position=120 | mysql -u root -p

關鍵：多檔案必須單次調用處理，否則連線中斷導致臨時表丟失報錯。--start-position 僅套用第一個檔，--stop-position 僅套用最後一個檔。

時間點恢復範例

# 恢復至特定時間
mysqlbinlog bin.000001 --stop-datetime='2024-01-15 14:30:00' | mysql -u root -p

# 恢復特定時間區間
mysqlbinlog bin.000001 \
  --start-datetime='2024-01-15 10:00:00' \
  --stop-datetime='2024-01-15 14:30:00' | mysql -u root -p

位置恢復範例

# 從特定位置恢復
mysqlbinlog bin.000001 --start-position=238 | mysql -u root -p

# 位置區間恢復
mysqlbinlog bin.000001 --start-position=238 --stop-position=484 | mysql -u root -p

完整 PITR 流程

1. 確認最後一次全備時間與對應 binlog 位置
2. 恢復全備資料
3. 應用全備後至目標時間點的所有 binlog (使用 --start-position 或 --start-datetime)
4. 驗證資料一致性

進階技巧

• mysqlbinlog --base64-output=decode-rows -v：深度解析 Row 事件
• 結合 grep 過濾特定表/操作：mysqlbinlog bin.000001 | grep -A5 'table_name.*users'
• 大量日誌建議先導出 SQL 檔案審查再執行