MySQL 四大日志體系詳解：錯誤、查詢、二進制、慢查詢與實戰管理指南

MySQL 四大日志體系詳解：錯誤、查詢、二進制、慢查詢與實戰管理指南

MySQL 日志是窺探資料庫內部行為的關鍵窗口，主要分四類：錯誤日志、通用查詢日志、二進制日志、慢查詢日志。本文系統梳理各日志功能、啟用配置、查看分析、輪轉清理與實戰建議。

一、二進制日志 —— 核心：複制與時間點恢復

1. 啟用與關鍵參數

編輯 my.cnf [mysqld] 區塊：

[mysqld]
log-bin[=path/[filename]]      # 啟用並指定路徑/前綴，預設 datadir/hostname-bin
expire_logs_days=10            # 自動清理天數（0=不自動清理）
max_binlog_size=100M           # 單檔上限，超過自動滾動（4KB~1GB）
sync_binlog=1                  # 每次提交強制 fsync，最安全但影響效能
binlog_format=MIXED            # ROW/STATEMENT/MIXED，建議 MIXED

修改後重啟 MySQL。會產生 hostname-bin.000001、hostname-bin.index（索引清單）。

2. 查看與管理

• SHOW BINARY LOGS; 列出所有 Binlog 檔案與大小
• SHOW MASTER STATUS; 當前寫入檔案與位置
• mysqlbinlog binlog.000001 > /tmp/binlog.txt 解析為可讀 SQL
• SHOW BINLOG EVENTS IN 'binlog.000006'; SQL 層面查看事件

3. 安全刪除

RESET MASTER;                    -- 刪除全部，重新從 000001 開始
PURGE MASTER LOGS TO 'binlog.000003';   -- 刪除指定檔案之前
PURGE MASTER LOGS BEFORE '2023-01-01';  -- 刪除指定日期之前

4. 時間點恢復 (PITR)

# 恢復至指定時間
mysqlbinlog --stop-datetime="2023-07-01 12:00:00" /var/lib/mysql/binlog.000008 | mysql -u root -p

# 恢復指定位置區間
mysqlbinlog --start-position=12345 --stop-position=67890 /var/lib/mysql/binlog.000008 | mysql -u root -p

5. 暫停/恢復記錄（無需重啟）

SET SESSION sql_log_bin=0;  -- 當前會話不記錄 Binlog
SET SESSION sql_log_bin=1;  -- 恢復記錄

二、錯誤日志 —— 必開：啟動、關閉、嚴重錯誤

配置

[mysqld]
log-error[=path/[filename]]   # 預設 datadir/hostname.err

查看

SHOW VARIABLES LIKE 'log_error';  -- 確認路徑
# 直接用文本編輯器開啟 .err 檔案

記錄範例：InnoDB 初始化、Buffer Pool 大小、異常關閉警告、插件狀態等。

清理與重建

• MySQL 5.5.7+：FLUSH LOGS 僅重新開啟檔案，不重命名
• 手動刪除 .err → mysqladmin -u root -p flush-logs 或 FLUSH LOGS; 重建

三、通用查詢日志 —— 慎開：所有客戶端操作

配置

[mysqld]
log[=path/[filename]]   # 預設 datadir/hostname.log

特點

• 記錄連線、查詢、更新、事務等所有操作
• 高併發下磁碟 I/O 巨大，嚴重拖慢效能
• 僅建議開發/測試環境短期開啟排查問題

清理

同錯誤日志：刪除檔案 → FLUSH LOGS;

四、慢查詢日志 —— 必開：效能調優基石

配置

[mysqld]
slow_query_log=1
slow_query_log_file=path/filename   # 預設 datadir/hostname-slow.log
long_query_time=1                   # 閾值秒數，支援微秒（如 0.1）
log_queries_not_using_indexes=1     # 記錄未用索引查詢

查看與分析

• 文本編輯器直接開啟
• mysqldumpslow -s t -t 10 slow.log 統計 Top 10 耗時查詢
• Percona Toolkit pt-query-digest 深度分析

突破 1 秒限制

官方版本 long_query_time 最小 1 秒（舊版），需使用 Percona Server 或打 microslow-patch 達到毫秒級。

五、生產環境日誌開啟策略建議

磁碟空間緊張時，可僅高峰期開啟慢查詢日誌，捕獲問題後關閉。

六、事務日誌寫入流程補充

Redo Log (ib_logfile) 保證持久性，Undo Log 保證原子性與 MVCC，Binlog 服務複制與 PITR。三者協同構成 MySQL 事務可靠性基石。

七、總結

掌握四大日誌的功能邊界、配置參數、分析工具與清理策略，是 DBA 從「會用」進階到「會診」的必經之路。建議：錯誤+二進制+慢查詢三開，通用關閉；定期 PURGE BINLOG 與 logrotate 管理磁碟空間。