MySQL Binlog 配置全攻略：啟用、格式、清理、恢復與主從復制核心參數詳解

MySQL 二進制日志 完整配置與運維指南

基礎概念

• Binlog 記錄所有數據變更 (DDL/DML)，不記錄 SELECT/無實際變化的 UPDATE
• MySQL 5.0+ 僅支持二進制格式 (性能/解析優於文本)
• 核心用途：主從復制、時間點恢復 (PITR)、審計追蹤

啟用與基礎配置

# /etc/my.cnf
[mysqld]
log-bin = mysql-bin                    # 啟用，指定基礎文件名
binlog_format = ROW                    # 格式：ROW/STATEMENT/MIXED (推薦 ROW)
max_binlog_size = 512M                 # 單文件上限 (1G 硬限制)
expire_logs_days = 7                   # 自動清理天數
binlog_cache_size = 4M                 # 事務緩存 (每會話)
max_binlog_cache_size = 512M           # 事務最大緩存
sync_binlog = 1                        # 關鍵：每提交 1 次 fsync (最安全)
# sync_binlog = 0 由 OS 決定 (性能最好，風險最大)
# sync_binlog = N 每 N 次提交 fsync

Binlog 格式深度對比

生產強烈建議 ROW 格式

文件管理與查看

# 查看當前 Binlog 列表
SHOW BINARY LOGS;
SHOW MASTER LOGS;

# 查看當前寫入文件
SHOW MASTER STATUS;

# 命令行查看文件內容
mysqlbinlog mysql-bin.000001 | more
mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000001  # 解析 ROW 格式

# 指定時間範圍導出
mysqlbinlog --start-datetime="2024-01-01 00:00:00" \
            --stop-datetime="2024-01-01 01:00:00" \
            mysql-bin.000001 > /tmp/binlog_1h.sql

基於 Binlog 的時間點恢復 (PITR)

# 1. 最近全量備份恢復
mysql -uroot -p db_name < full_backup.sql

# 2. 依次應用備份後的 Binlog (按序號順序)
mysqlbinlog mysql-bin.000002 | mysql -uroot -p db_name
mysqlbinlog mysql-bin.000003 | mysql -uroot -p db_name
# ... 直到目標時間點

# 3. 或指定位點恢復
mysqlbinlog --stop-position=12345 mysql-bin.000003 | mysql -uroot -p db_name

清理策略

# 按文件名清理 (保留指定文件及之後)
PURGE BINARY LOGS TO 'mysql-bin.000010';

# 按時間清理 (保留指定時間及之後)
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';

# 重置所有 (危險！僅用於全新主庫初始化)
RESET MASTER;

主從復制關鍵過濾參數 (慎用！)

SHOW GLOBAL STATUS LIKE 'Binlog%';
-- Binlog_cache_use: 使用內存緩存次數
-- Binlog_cache_disk_use: 寫臨時文件次數 (調大 binlog_cache_size)
-- Binlog_stmt_cache_use / _disk_use: STATEMENT 格式專用

最佳實踐清單

1. 格式：binlog_format = ROW
2. 持久化：sync_binlog = 1 (配合電池備援 RAID 卡寫緩存)
3. 保留：expire_logs_days = 7 (配合備份週期)
4. 監控：Binlog_cache_disk_use 增長 → 擴大 binlog_cache_size
5. 備份：Binlog 文件納入異地備份策略