MySQL Binlog 自動清理與手動刪除最佳實踐：expire_logs_days、PURGE MASTER LOGS 與三種複寫模式深度解析

為什麼要清理 Binlog？

主庫繁忙寫入下，Binlog 增長極快。未清理將佔滿磁碟導致寫入阻塞、甚至主庫掛起。Row 格式（RBR）更佔空間。

自動清理配置

持久化配置（my.cnf）

[mysqld]
expire_logs_days = 10  -- 保留 10 天，超過自動刪除

動態調整（無需重啟）

SHOW BINARY LOGS;
SHOW VARIABLES LIKE '%log%';
SET GLOBAL expire_logs_days = 10;

手動刪除指令

-- 刪除 10 天前的所有 Binlog
PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
-- 等價寫法
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';

-- 查看剩餘
SHOW MASTER LOGS;

MASTER = BINARY 同義詞。刪除前**務必確認從庫已應用至該位置**（SHOW SLAVE STATUS\G 對比 Relay_Master_Log_File）。

三種複寫模式深度對比

MBR 自動切換 RBR 的觸發條件

• 更新 NDB 表
• 語句含 UUID()
• ≥2 個 AUTO_INCREMENT 表同時更新
• INSERT DELAYED
• 使用 UDF
• 視圖定義需 RBR（如含 UUID()）

動態切換 Binlog 格式

-- 會話級
SET SESSION binlog_format = 'ROW|STATEMENT|MIXED';

-- 全域級（需 SUPER，影響新會話）
SET GLOBAL binlog_format = 'MIXED';

-- 持久化
# my.cnf
binlog_format = MIXED

受限場景（存儲程序/觸發器中、啟用 NDB、會話用 RBR 且開啟臨時表）無法動態切換。

系統庫 mysql 表變更的特殊規則

• DML (INSERT/UPDATE/DELETE) → 遵循 binlog_format
• 管理語句 (GRANT/REVOKE/SET PASSWORD) → **強制 SBR**

RBR 解決主鍵衝突實例

INSERT INTO db_allot_ids SELECT * FROM db_allot_ids;

-- SBR Binlog：記錄 SQL 文本 → 從庫重新執行 → 主鍵衝突
-- RBR Binlog：記錄每行前後映像 → 從庫直接應用 → 無衝突

生產環境建議

1. 預設 MBR，兼顧體積與準確性
2. expire_logs_days = 7-14，配合備份策略
3. 重要遷移/大批量操作前，SET SESSION binlog_format='STATEMENT' 減少 Binlog 體積
4. 監控 Max_binlog_size 觸發 rotate 頻率，異常暴增報警
5. 從庫啟用 relay_log_purge=ON、relay_log_recovery=ON