MySQL Binlog 自動清理與主從安全清理完整指南：expire_logs_days、PURGE 實戰與復制模式解析

一、 為什麼要清理 Binlog

• 佔用磁盤空間導致實例掛起
• 主從復制時，從庫延遲需保留對應 binlog，盲目刪除導致復制中斷
• 備份恢復、時間點恢復（PITR）依賴完整 binlog 鏈

二、 自動清理配置（推薦）

# /etc/my.cnf [mysqld] 區塊
expire_logs_days = 7          # 自動刪除 7 天前 binlog（默認 0 從不刪除）
max_binlog_size = 100M        # 單文件最大 100M，防止單文件過大
binlog_format = ROW           # 推薦 ROW 格式，主從一致性最強
# log_bin = mysql-bin         # 開啟 binlog（通常默認開）

修改後 systemctl restart mysqld 生效。動態調整（無需重啟）：

SET GLOBAL expire_logs_days = 7;
SET GLOBAL max_binlog_size = 104857600;

三、 手動清理命令：PURGE BINARY LOGS

-- 按文件名清理（刪除指定文件之前的所有）
PURGE BINARY LOGS TO 'mysql-bin.000058';
-- 按時間清理（刪除指定時間之前的所有）
PURGE BINARY LOGS BEFORE '2024-01-15 00:00:00';
-- 相對時間清理（3 天前）
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 3 DAY);

MASTER / BINARY 同義，PURGE MASTER LOGS ... 效果相同。

四、 主從環境安全清理標準流程（必背）

1. 從庫檢查：SHOW SLAVE STATUS\G → 記錄 Master_Log_File / Relay_Master_Log_File，多從庫取最舊文件
2. 主庫確認：SHOW BINARY LOGS 列出所有文件
3. 執行清理：PURGE BINARY LOGS TO '最舊從庫正在讀取的文件名';
4. 驗證：再次 SHOW BINARY LOGS 確認文件列表，從庫 SHOW SLAVE STATUS 確認復制正常

⚠️ 絕不 直接刪除文件系統層面的 mysql-bin.* 文件，會導致索引文件 mysql-bin.index 不一致，主庫拒絕啟動。

五、 復制模式對比與 Binlog 格式選擇

動態切換：SET SESSION/GLOBAL binlog_format = ROW|STATEMENT|MIXED;

六、 RBR 模式解決主鍵衝突實例

語句：INSERT INTO db_allot_ids SELECT * FROM db_allot_ids

• STATEMENT：binlog 記錄原 SQL，從庫重放因主鍵衝突報錯
• ROW：binlog 記錄每行具體 (id, ...) 值，從庫逐行應用，自動跳過/處理衝突

七、 監控與告警建議

• 磁盤使用率 > 80% 告警
• Binlog 積壓大小（主從延遲換算）超過 expire_logs_days 保留量告警
• 定期 SHOW BINARY LOGS 對比文件數量與預期

八、 總結

Binlog 清理鐵律：配置 expire_logs_days 自動清理 + 主從環境按最舊從庫位置 PURGE TO + 絕不手動 rm 文件。生產環境強烈建議 binlog_format=ROW 或 MIXED，配合 GTID 復制（5.6+）構建高可用架構。將清理策略納入運行手冊，定期演練 PITR 恢復，確保數據安全無死角。