什麼是二進制日誌

記錄所有**修改資料**的 SQL (INSERT/UPDATE/DELETE/DDL 等)，不含 SELECT。支援：

• 時間點恢復 (PITR)：恢復到任意秒
• 主從複寫：從庫重放 binlog 同步資料
• 審計/追蹤：誰在何時改了什麼資料

啟用配置 (my.cnf)

[mysqld]
log-bin = /var/log/mysql/mysql-bin
binlog_format = ROW          # ROW/STATEMENT/MIXED，推薦 ROW
max_binlog_size = 500M       # 單檔上限，自動分檔
expire_logs_days = 7         # 自動清理天數
binlog_expire_logs_seconds = 604800  # 8.0+ 秒級精度
sync_binlog = 1              # 每次提交刷盤，最安全 (效能稍損)
binlog_cache_size = 4M       # 事務緩衝
max_binlog_cache_size = 1G

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

查看與管理

查看當前 binlog

SHOW BINARY LOGS;
SHOW MASTER STATUS;  -- 當前寫入檔案與位置

mysqlbinlog 工具查看內容

# 查看單檔
mysqlbinlog /var/log/mysql/mysql-bin.000003

# 指定時間範圍
mysqlbinlog --start-datetime="2024-01-15 10:00:00" --stop-datetime="2024-01-15 11:00:00" \
  /var/log/mysql/mysql-bin.000003

# 指定位置範圍
mysqlbinlog --start-position=1234 --stop-position=5678 /var/log/mysql/mysql-bin.000003

# 解碼 ROW 格式 (需 -v -v -v)
mysqlbinlog -v -v -v /var/log/mysql/mysql-bin.000003 | less

# 過濾特定表/用戶
mysqlbinlog /var/log/mysql/mysql-bin.000003 | grep -i 'update.*users'

時間點恢復 (PITR) 實戰

場景：誤執行 DELETE FROM orders WHERE id > 1000 於 2024-01-15 14:30:00，需恢復到 14:29:59。

步驟

1. 確認備份基準點：上次全量備份時間 (如 02:00)
2. 還原全量備份：

   gunzip < backup_20240115_0200.sql.gz | mysql -u root -p

3. 重放 binlog 到誤操作前：

   mysqlbinlog --stop-datetime="2024-01-15 14:29:59" \
     /var/log/mysql/mysql-bin.00000* | mysql -u root -p

4. 驗證資料：確認 orders 表資料正確

手動刷新/切換 binlog

FLUSH LOGS;                    -- 產生新 binlog 檔案
FLUSH BINARY LOGS;             -- 同義
RESET MASTER;                  -- 危險！清空所有 binlog、重置 index，僅測試環境用

mysqldump 備份配合 binlog

# 導出時同步刷新 binlog，確保備份點與 binlog 位置對應
mysqldump --flush-logs --master-data=2 --single-transaction -u root -p mydb > backup.sql
-- --master-data=2: 註釋形式輸出 CHANGE MASTER TO 位置資訊

效能影響與調優

• 官方預估開啟 binlog 效能損耗 **~1%**，完全可接受
• sync_binlog=1 最安全但 IOPS 要求高，SSD 必須；HDD 可設 100-1000 妥協
• binlog_format=ROW 體積較大但複寫最安全，避免非確定性函數問題
• 將 binlog 放在**獨立磁碟/分區** (如 NVMe)，隔離 IO 爭用

常見坑點

• default-character-set 在 [client] 區段會導致 mysqlbinlog 報錯 unknown variable → 移至 [mysqld] 或註釋
• 大事務超過 max_binlog_cache_size 會報錯，需調大
• DDL (ALTER TABLE) 會強制刷新 binlog，長時間鎖表