您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL Binlog 完整管理指南：查看、清理、時間點恢復與主從同步實戰

編輯：MySQL 綜合教學

MySQL 二進制日誌 (Binlog) 全生命周期管理

Binlog 記錄所有資料變更 (DDL/DML)，是主從複寫、時間點恢復 (PITR)、審計追蹤的基石。掌握 Binlog 管理與恢復技能是 DBA 必修課。

1. 常用查看與管理命令

-- 進入 MySQL 客戶端執行
SHOW BINLOG EVENTS IN 'mysql-bin.000016' LIMIT 10;  -- 查看指定 binlog 事件
SHOW MASTER LOGS;                                    -- 列出所有 binlog 檔案與大小
SHOW BINARY LOGS;                                    -- 同上

-- 刪除 binlog (謹慎操作)
RESET MASTER;                                        -- 清空所有 binlog (重置索引從 000001 開始)
PURGE MASTER LOGS TO 'mysql-bin.000006';             -- 刪除指定檔案之前的所有 binlog
PURGE MASTER LOGS BEFORE '2024-01-15 10:00:00';      -- 刪除指定時間之前的 binlog

-- 自動過期清理 (配置 my.cnf)
[mysqld]
expire_logs_days = 7        -- 保留 7 天，超過自動刪除
max_binlog_size = 500M      -- 單檔最大 500M，超過自動輪轉
binlog_format = ROW         -- 建議 ROW 格式，支援最精確恢復

2. mysqlbinlog 工具離線解析與恢復

# 基本語法
mysqlbinlog [options] log_file > output.sql

# 指定時間範圍導出
mysqlbinlog --start-datetime="2024-01-15 10:00:00" \
            --stop-datetime="2024-01-15 12:00:00" \
            /var/lib/mysql/mysql-bin.000005 > restore.sql

# 指定位置範圍導出 (更精確)
mysqlbinlog --start-position=12345 --stop-position=67890 \
            /var/lib/mysql/mysql-bin.000005 > restore.sql

# 僅導出特定資料庫 (需 binlog_format=ROW 或 MIXED)
mysqlbinlog --database=db_test /var/lib/mysql/mysql-bin.000005 > db_test.sql

3. 時間點恢復 (PITR) 實戰流程

全量備份基準點：mysqldump --single-transaction --master-data=2 --all-databases > full_backup.sql (記錄備份時刻的 Binlog 位置)
故障發生：如誤 DROP TABLE 於 10:30。
恢復全量備份：mysql < full_backup.sql

增量恢復至故障前：

mysqlbinlog --stop-datetime="2024-01-15 10:29:59" \
            /var/lib/mysql/mysql-bin.000005 | mysql -u root -p

跳過故障 SQL，恢復故障後：

mysqlbinlog --start-datetime="2024-01-15 10:31:00" \
            /var/lib/mysql/mysql-bin.000005 | mysql -u root -p

關鍵技巧：先生成 SQL 檔審查 (mysqlbinlog ... > /tmp/review.sql)，確認無誤再管道執行；或用 --one-database 參數僅重放特定庫。

4. Binlog 格式選擇對比

格式	優點	缺點	適用場景
STATEMENT	體積小、相容舊版	非確定性函數 (NOW、UUID) 導致主從不一致	簡單業務、跨版本複寫
ROW	完全記錄行變更、最安全、支援精確恢復	體積大、大批次 UPDATE/DELETE 產生海量日誌	生產環境強烈推薦
MIXED	自動切換：確定性用 STATEMENT，非確定性用 ROW	行為較難預測	折衷方案 (MySQL 5.1+ 預設)

5. 常見問題 FAQ

mysqlbinlog 導出中文亂碼：加 --set-charset=utf8mb4 或確認終端編碼一致。
Binlog 佔滿磁碟：啟用 expire_logs_days、定期 PURGE、避免大事務、考慮將 Binlog 掛載獨立磁碟。
主從同步中斷：SHOW SLAVE STATUS\G 檢查 Last_IO_Error/Last_SQL_Error，常見為網路斷開、SQL 執行錯誤、Binlog 損壞。