Binlog 核心價值

• 時間點恢復 (PITR)：配合全量備份，恢復到任意秒
• 主從複寫：Slave 讀取 Binlog 重放實現同步
• 審計/追蹤：記錄所有 DDL/DML（不含 SELECT）

一、開啟與配置

編輯 my.cnf ([mysqld] 區)：

log_bin = /var/log/mysql/mysql-bin.log
server_id = 1                    # 必須唯一
binlog_format = ROW              # 推薦 ROW 格式
max_binlog_size = 200M           # 單檔上限，達到自動輪轉
expire_logs_days = 7             # 自動清理 7 天前舊檔
sync_binlog = 1                  # 每次提交刷盤，最安全（犧牲些許寫入效能）

重啟 MySQL 或執行 FLUSH LOGS 生效。

二、查看 Binlog

1. 列出所有 Binlog 檔案

SHOW BINARY LOGS;

2. 查看當前寫入位置

SHOW MASTER STATUS;

3. 解析 Binlog 內容 (mysqlbinlog)

# 查看指定資料庫操作
mysqlbinlog -d test_db /var/log/mysql/mysql-bin.000002

# 指定位置範圍
mysqlbinlog --start-position=870 --stop-position=1274 /var/log/mysql/mysql-bin.000004

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

# 導出為 SQL 檔供審計/恢復
mysqlbinlog /var/log/mysql/mysql-bin.000004 > /tmp/binlog.sql

三、清理策略

命令	效果	場景
RESET MASTER	刪除所有 Binlog，索引歸零	初始化新主庫、測試環境重置
PURGE MASTER LOGS TO 'mysql-bin.000010'	刪除指定檔案之前的所有 Binlog	手動清理舊檔
PURGE MASTER LOGS BEFORE '2024-01-10 00:00:00'	刪除指定時間之前的所有 Binlog	按時間清理
expire_logs_days = 7	自動清理超過 N 天的 Binlog	生產環境必開

四、資料恢復實戰

1. 完全恢復（全量備份 + 全部 Binlog）

mysqlbinlog /var/log/mysql/mysql-bin.000001 | mysql -u root -p

2. 時間點恢復 (PITR) — 誤刪表場景

假設 20:00 誤執行 DROP TABLE users：

# 1. 先恢復全量備份 (至 19:59)
mysqlbinlog --stop-datetime="2024-01-15 19:59:59" /var/log/mysql/mysql-bin.000001 | mysql -u root -p

# 2. 跳過誤操作，繼續恢復後續 Binlog
mysqlbinlog --start-datetime="2024-01-15 20:01:00" /var/log/mysql/mysql-bin.000001 | mysql -u root -p

3. 位置點恢復 — 更精確

用 mysqlbinlog 找到 DROP TABLE 的 start-position (889107) 與 end-position (889189)：

# 恢復至誤操作前
mysqlbinlog --stop-position=889107 /var/log/mysql/mysql-bin.000001 | mysql -u root -p

# 跳過誤操作，恢復之後
mysqlbinlog --start-position=889189 /var/log/mysql/mysql-bin.000001 | mysql -u root -p

4. 兜底：導出 SQL 手動編輯恢復

mysqlbinlog mysql-bin.000001 > /tmp/recover.sql
# 用編輯器刪除/修正錯誤語句，再執行
mysql -u root -p < /tmp/recover.sql

五、避坑指南

• 大事務導致單檔超過 max_binlog_size：事務不會跨檔，該檔案會超大，需監控檔案大小。
• sync_binlog=1 可能影響寫入延遲，SSD 環境可接受；HDD 可設 100-1000 平衡效能與安全。
• Binlog 只記錄變更，不記錄 SELECT，不可用於讀流量分析。
• 從庫 relay_log 同理需配置 expire_logs_days 或 relay_log_purge=ON。