MySQL 二進制日誌點對點恢復完整指南

前言

當 MySQL 啟用二進制日誌後，管理員可利用 mysqlbinlog 工具，將資料從指定時間點（如上次完整備份）恢復至故障發生前一刻，或另一指定時間點。這篇指南將詳細說明如何查詢當前二進制日誌檔案、按時間或位置恢復資料，並提供實戰範例。

確認二進制日誌檔案位置

二進制日誌路徑通常定義於選項檔（my.cnf 或 my.ini）的 log-bin 參數中。若未在選項檔設定，啟動服務時亦可透過命令列指定。查詢目前日誌檔案名稱，可在 MySQL 客戶端執行：

SHOW BINLOG EVENTS \G

或直接在命令列下達：

mysql --user=root -p你的密碼 -e 'SHOW BINLOG EVENTS \G'

按時間點恢復

MySQL 4.1.4 起支援 --start-date 與 --stop-date 參數，格式為 DATETIME。假設今天 2005-04-20 10:00 執行了刪除大表的錯誤 SQL，恢復步驟如下：

1. 先還原前一晚的完整備份。
2. 重放截至 09:59:59 的二進制日誌：

mysqlbinlog --stop-date="2005-04-20 09:59:59" /var/log/mysql/bin.123456 | mysql -u root -p你的密碼

3. 若數小時後才發現錯誤，需補上 10:01 之後的操作：

mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456 | mysql -u root -p你的密碼

結合完整備份與兩次 mysqlbinlog 執行，即可將資料恢復至 10:00 前一秒。執行前請務必檢查日誌確認時間點精確。

按日誌位置恢復

使用 --start-position 與 --stop-position 指定日誌位置號，精度優於時間戳，特別適合同一秒內發生多筆破壞性交易的情境。操作流程：

1. 先匯出可疑時段的日誌至文字檔供人工檢視：

mysqlbinlog --start-date="2005-04-20 09:55:00" --stop-date="2005-04-20 10:05:00" /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

2. 以文字編輯器開啟 /tmp/mysql_restore.sql，尋找不想重複的 SQL 語句，記錄其 log_pos 位置號。
3. 恢復備份檔後，分兩階段重放日誌：

mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 | mysql -u root -p你的密碼
mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 | mysql -u root -p你的密碼

第一行恢復至停止位置為止的所有交易，第二行從指定起始位置重放至日誌末尾。輸出內含 SET TIMESTAMP，恢復後的資料與日誌將保留原始執行時間。

最佳實踐提醒

• 定期驗證備份可用性，並建立恢復演練機制。
• 生產環境建議啟用 --flush-logs 搭配 --lock-tables 建立一致性檢查點。
• 大量資料恢復時，考慮使用 --quick 或 --opt 加速匯出，但需注意鎖表對線上服務的影響。