MySQL 二進制日誌點對點恢復：基於時間與位置的精準還原

前提條件

• MySQL 已啟用二進制日誌（log-bin）
• 擁有 mysqlbinlog 工具（MySQL 內建）
• 知曉最後一次完整備份時間點

一、確認當前二進制日誌檔案

# 方法 1：MySQL 客戶端
mysql> SHOW BINLOG EVENTS\G

# 方法 2：命令列
mysql --user=root -p -e 'SHOW BINLOG EVENTS\G'

二、按時間點恢復

適用 MySQL 4.1.4+，使用 --start-date、--stop-date（格式：YYYY-MM-DD HH:MM:SS）。

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

# 1. 先恢復昨晚完整備份
# 2. 重放 09:59:59 前的 binlog
mysqlbinlog --stop-date="2005-04-20 09:59:59" /var/log/mysql/bin.123456 | mysql -u root -p

若錯誤發生數小時後才發現，需恢復後續正常操作

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

組合：完整備份 + 兩段 binlog 重放 = 恢復至誤操作前一秒。

三、按日誌位置恢復（更精準）

使用 --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

用文字編輯器開啟 /tmp/mysql_restore.sql，搜尋錯誤 SQL（如 DROP TABLE），記錄其 log_pos 值（如 368312）。

2. 執行精準恢復

# 恢復至錯誤事務之前
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

注意：mysqlbinlog 輸出包含 SET TIMESTAMP，恢復後的資料與日誌會保留原始執行時間。

四、生產環境操作建議

1. 恢復前務必備份當前狀態，防止操作失誤擴大損失
2. 在測試環境先演練恢復流程，驗證資料一致性
3. 記錄每次恢復的 binlog 檔案、位置、時間範圍，留存審計軌跡
4. 考慮使用 GTID（MySQL 5.6+）簡化位置管理