利用 Binary Log 實現時間點恢復，可精確回溯到誤操作前一秒。本文完整記錄 Windows 環境下開啟 binlog、導出 SQL、定位/定時還原的操作流程。

1. 開啟 Binary Log

# my.ini [mysqld] 區段
log-bin = mysql-bin
# 或指定目錄
log-bin = "D:/mysql/data/binlog/mysql-bin"

重啟 MySQL 後，資料目錄將產生 mysql-bin.000001、mysql-bin.index。

2. 常用查看指令

-- 當前 binlog 狀態
SHOW MASTER STATUS;

-- 列出所有 binlog
SHOW BINARY LOGS;

-- 查看 binlog 事件（含 position）
SHOW BINLOG EVENTS [IN 'mysql-bin.000001'] [FROM 100] [LIMIT 20];

3. mysqlbinlog 導出 SQL 檔案

按 Position 範圍導出

# 語法
mysqlbinlog --start-position=START --stop-position=STOP binlog_file > output.sql

# 範例
mysqlbinlog --start-position=4 --stop-position=239 \
  "D:/web/mysql/data/mysql-bin.000001" > test1.sql

按時間範圍導出

mysqlbinlog --start-datetime="2023-03-16 13:00:00" \
            --stop-datetime="2023-03-16 14:00:00" \
            "D:/web/mysql/data/mysql-bin.000001" > test1.sql

4. 還原執行

方法一：導出 SQL 再 SOURCE（可審查）

mysql -u root -p < test1.sql
# 或在 mysql client
SOURCE test1.sql;

方法二：管道直接還原（生產環境慎用）

# 按時間
mysqlbinlog --start-datetime="2023-03-16 13:00:00" \
            --stop-datetime="2023-03-16 14:00:00" \
            "D:/web/mysql/data/mysql-bin.000001" | mysql -u root -p

# 按 Position
mysqlbinlog --start-position=3696 --stop-position=4241 \
            "D:/web/mysql/data/mysql-bin.000001" | mysql -h localhost -u root -p

5. 實戰避坑

• AUTO_INCREMENT 衝突：還原含自增主鍵的 INSERT 會報 Duplicate entry '1' for key 'PRIMARY'
  • 解決：導入前 ALTER TABLE t AUTO_INCREMENT = N 或刪除目標表重建
  • 或使用 --insert-ignore 參數跳過衝突
• 部分還原順序：必須按 binlog 先後順序執行，否則外鍵/觸發器報錯
• 大檔案分割：split -l 10000 huge.sql part_ 分批導入
• GTID 模式：MySQL 5.6+ 建議啟用 GTID，配合 mysqlbinlog --include-gtids 更精確

6. 生產環境 PITR 標準流程

1. 定期全量備份（mysqldump --master-data=2 --single-transaction） 2. Binlog 連續保留（expire_logs_days=7） 3. 事故發生 → 找到最近全量備份 → 套用備份 → 用 binlog 從備份位置重放至事故前一秒 4. 驗證資料一致性 → 切換流量

掌握 binlog 時間點恢復，是 DBA 從「備份者」進階為「資料守護者」的關鍵技能。