前置條件：必須啟用 Binlog

在 my.ini/my.cnf 的 [mysqld] 區段加入：

log-bin = mysql-bin
# 可選：指定日誌目錄
# log-bin = /data/mysql/binlog/mysql-bin

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

常用 Binlog 管理指令

-- 查看當前 binlog 狀態
SHOW MASTER STATUS;

-- 列出所有 binlog 檔案
SHOW BINARY LOGS;

-- 查看特定 binlog 事件（含位置、時間、SQL）
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

mysqlbinlog 工具：核心恢復利器

位於 MySQL bin/ 目錄下，將二進制日誌轉為可讀 SQL 或直接管線導入。

1. 按位置範圍導出 SQL 檔案

mysqlbinlog --start-position=3696 --stop-position=4241 \
  /data/mysql/data/mysql-bin.000001 > recover.sql

再用 SOURCE recover.sql; 或 mysql -u root -p db < recover.sql 執行。

2. 按時間範圍導出（精確到秒）

mysqlbinlog --start-datetime="2024-01-15 10:00:00" \
  --stop-datetime="2024-01-15 11:00:00" \
  /data/mysql/data/mysql-bin.000001 > recover.sql

3. 直接管線還原（無中間檔案）

# 按時間
mysqlbinlog --start-datetime="2024-01-15 10:00:00" \
  --stop-datetime="2024-01-15 11:00:00" \
  /data/mysql/data/mysql-bin.000001 | mysql -uroot -p

# 按位置
mysqlbinlog --start-position=3696 --stop-position=4241 \
  /data/mysql/data/mysql-bin.000001 | mysql -uroot -p

實戰恢復流程範例

1. 確認誤操作時間點：如 14:30 誤 DROP TABLE。
2. 找到對應 binlog：SHOW BINARY LOGS; 找涵蓋該時間的檔案。
3. 定位具體位置：SHOW BINLOG EVENTS 找 DROP TABLE 前一個事件的 end_log_pos。
4. 執行還原：從最後一次完整備份後的位置，恢復到誤操作前的位置。

常見錯誤與應對

• Duplicate entry '1' for key 'PRIMARY'：重放含 AUTO_INCREMENT 的 INSERT 衝突。解決：略過該段、或臨時移除自增屬性、或用 REPLACE INTO 邏輯。
• 編碼亂碼：加上 --default-character-set=utf8 參數。
• 權限不足：需 SUPER 或 REPLICATION SLAVE 權限。

備份策略建議：Binlog + 定期全量備份

PITR (Point-In-Time Recovery) 架構：

每日 02:00 完整備份 (mysqldump/xtrabackup)
        ↓
Binlog 持續記錄所有變更
        ↓
災難發生 → 還原最近全量備份 → 重放 Binlog 到故障前一秒

配合 expire_logs_days=7 避免磁碟爆滿，sync_binlog=1 保證不丟交易（效能折中可設 100~1000）。

總結

Binlog 是 MySQL 資料安全的最後防線。熟練掌握 mysqlbinlog 定位、導出、管線還原技巧，配合定期全量備份，即可實現秒級 RPO（恢復點目標）的企業級災備能力。