MySQL 學習筆記（十五）：四大日誌體系深度解析與運維實踐

MySQL 四大日誌概覽

二進制日誌 —— 核心功能與操作

啟用配置

[mysqld]
log-bin = /path/to/binlog
expire_logs_days = 10
max_binlog_size = 100M

• log-bin：開啟並指定檔案前綴（如 binlog），實際檔案為 binlog.000001 等。
• expire_logs_days：自動清理天數，0 為不自動清理。
• max_binlog_size：單檔上限（4K~1G），超過自動滾動。

常用管理指令

• SHOW BINARY LOGS;：列出所有 binlog 檔案與大小。
• SHOW BINLOG EVENTS IN 'binlog.000006';：查看特定檔案事件（SQL 語句）。
• mysqlbinlog binlog.000002 > binlog.txt：命令列工具導出完整內容（含時間戳、事務邊界）。
• PURGE MASTER LOGS TO 'binlog.000003';：刪除指定檔案之前的所有 binlog。
• PURGE MASTER LOGS BEFORE '2023-01-01';：刪除指定日期前的 binlog。
• RESET MASTER;：慎用，刪除所有 binlog 並從 000001 重新編號。

時間點恢復（PITR）

mysqlbinlog --stop-datetime="2024-07-02 15:27:48" /path/binlog.000008 | mysql -u user -p

適用場景：誤 DELETE/UPDATE 後，從最後一次備份恢復至事故前一刻。

暫停/恢復記錄

SET sql_log_bin = 0;  -- 當前會話停止寫 binlog
SET sql_log_bin = 1;  -- 恢復

適合大量匯入資料、重建索引等不需記錄的操作。

與 SQL Server 對比

• MySQL binlog ≈ SQL Server LDF（交易記錄），但 binlog 可選啟用，redo/undo 分離存放（ib_logfile*、表空間）。
• 復制架構：Slave 讀取 Master binlog；SQL Server 訂閱端讀取發布端 LDF。

錯誤日誌

配置與查看

[mysqld]
log-error = /var/log/mysql/error.log

• SHOW VARIABLES LIKE 'log_error'; 確認路徑。
• 文字檔，可直接 tail -f 或記事本開啟。
• 記錄：啟動/關閉流程、InnoDB 初始化、異常崩潰堆疊、異常關閉強制回滾。

清理與重建

• 手動刪除 .err 檔後，執行 FLUSH LOGS; 或 mysqladmin flush-logs 重建空檔案（5.5.7+ 僅重開檔案，不重命名）。

通用查詢日誌

啟用

[mysqld]
log = /var/log/mysql/general.log
# 或 5.1+ 動態開啟
SET GLOBAL general_log = ON;
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

特點與建議

• 記錄**所有**語句（含 SELECT），高併發下磁碟 IO 極大，**生產環境強烈建議關閉**。
• 除錯/審計短期開啟，用畢即關並 FLUSH LOGS 切割。

慢查詢日誌

啟用

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1          # 秒，預設 10s
log_queries_not_using_indexes = ON  # 記錄無索引查詢

分析範例

# Time: 240802 00:02:29
# User@Host: root[root] @ localhost [::1]
# Query_time: 7.578125  Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1406908949;
SELECT BENCHMARK(10000000, PASSWORD('newpwd'));

• Query_time 超過閾值即記錄。
• 官方版 long_query_time 最小 1 秒，需微秒級分析可用 Percona microslow-patch 或升級至 MySQL 5.7+（支援微秒）。

生產環境日誌策略建議

總結

• 日誌是資料庫「黑盒子」，善用可將故障恢復時間從小時級壓縮至分鐘級。
• 二進制日誌是高可用架構基石，務必配合 expire_logs_days 防止撐爆磁碟。
• 慢查詢日誌是 SQL 調優最直接依據，定期 pt-query-digest 分析 Top N。
• 通用查詢日誌僅作臨時診斷，**嚴禁長期開啟於生產**。