MySQL 二進制日誌時間點恢復（PITR）實戰：mysqlbinlog 精準還原指定時刻資料

前置條件

• 啟用二進制日誌：log-bin=mysql-bin（my.cnf）。
• 確認當前 Binlog 檔案：SHOW BINLOG EVENTS\G 或 mysql --user=root -p -e 'SHOW BINLOG EVENTS\G'。

方法一：按時間點恢復（--start-date/--stop-date）

場景：今天 10:00 誤刪大表，需恢復至 09:59:59。

# 1. 還原前一晚完整備份
mysql -u root -p < full_backup_20240119.sql

# 2. 重放 Binlog 至誤操作前一秒
mysqlbinlog --stop-date="2024-01-20 09:59:59" /var/log/mysql/mysql-bin.000123 | mysql -u root -p

# 3. 若誤操作後還有正常業務，續接重放
mysqlbinlog --start-date="2024-01-20 10:01:00" /var/log/mysql/mysql-bin.000123 | mysql -u root -p

缺點：秒級精度，同秒內多筆交易無法精準切割。

方法二：按位置恢復（--start-position/--stop-position）——推薦

1. 導出可疑時段 Binlog 為文字檔分析：

   mysqlbinlog --start-date="2024-01-20 09:55:00" --stop-date="2024-01-20 10:05:00" \
     /var/log/mysql/mysql-bin.000123 > /tmp/review.sql
   

2. 用編輯器開啟 /tmp/review.sql，搜尋誤刪語句（DROP TABLE、DELETE FROM），記下其 # at xxx 位置號。
3. 精準恢復：

   # 重放至誤操作前一個位置
   mysqlbinlog --stop-position=123456 /var/log/mysql/mysql-bin.000123 | mysql -u root -p
   
   # 跳過誤操作，從誤操作後位置續放
   mysqlbinlog --start-position=123789 /var/log/mysql/mysql-bin.000123 | mysql -u root -p
   

優點：位置號對應每個事件，精準至單一交易，不受同秒多交易影響。

多 Binlog 檔案處理

# 依序處理多個 Binlog 檔案
mysqlbinlog mysql-bin.000123 mysql-bin.000124 mysql-bin.000125 | mysql -u root -p

GTID 模式下的簡化恢復（MySQL 5.6+）

• 啟用 gtid_mode=ON、enforce_gtid_consistency=ON。
• 恢復時無需指定檔案位置，直接用 SET GTID_PURGED 或 mysqlbinlog --skip-gtids 配合 SOURCE_POSITION_WAIT()。
• 工具 mysqlbinlog --include-gtids=gtid_set 可直接提取特定 GTID 區間。

實戰檢核清單

1. 確認備份時間點 < Binlog 起始位置（無縫銜接）。
2. Binlog 格式建議 ROW 或 MIXED，避免 STATEMENT 非確定性問題。
3. 恢復前在測試環境驗證腳本正確性。
4. 恢復過程監控 Seconds_Behind_Master（若為 Slave）或錯誤日誌。
5. 恢復完成後 ANALYZE TABLE 更新統計資訊。

避坑提醒

• mysqlbinlog 輸出包含 SET TIMESTAMP=...，會影響 NOW()、SYSDATE() 行為，確保應用層不依賴此類函數做關鍵邏輯。
• 大 Binlog 管線傳輸可能超過 max_allowed_packet，需調大或分檔處理。
• DROP DATABASE/TRUNCATE TABLE 等 DDL 也會記錄在 Binlog，恢復時會重執行，謹慎確認範圍。