MySQL 四大日誌體系深度解析：二進制、錯誤、通用查詢、慢查詢

MySQL 四大日誌體系深度解析：二進制、錯誤、通用查詢、慢查詢

MySQL 日誌是資料庫運行狀態的「黑盒記錄儀」，掌握其分類、配置與分析方法，是故障排查、性能調優、數據恢復的核心技能。

日誌類型總覽

注意：日誌功能會消耗 CPU、I/O 與磁盤空間，生產環境建議按需開啟，並配置定期清理/歸檔策略。

一、二進制日誌—— 核心中的核心

啟用配置

# /etc/my.cnf [mysqld] 區塊
log-bin[=/path/to/binlog/prefix]
# 例：log-bin=/var/lib/mysql/mysql-bin
# 重啟後生成：mysql-bin.000001、mysql-bin.index

最佳實踐：將二進制日誌與數據文件放在不同物理磁盤，防單點故障導致數據與日誌同時丟失。

常用管理命令

-- 查看二進制日誌內容
mysqlbinlog mysql-bin.000001

-- 刪除所有二進制日誌（慎用！主從環境會中斷復制）
RESET MASTER;

-- 按編號刪除（刪除指定文件之前的所有日誌）
PURGE MASTER LOGS TO 'mysql-bin.000004';

-- 按時間刪除
PURGE MASTER LOGS BEFORE '2024-01-01 00:00:00';

-- 基於二進制日誌恢復（順序應用）
mysqlbinlog mysql-bin.000001 | mysql -u root -p
mysqlbinlog mysql-bin.000002 | mysql -u root -p

-- 會話級暫停/恢復記錄（不寫入 binlog）
SET SESSION SQL_LOG_BIN = 0;  -- 暫停
SET SESSION SQL_LOG_BIN = 1;  -- 恢復

二、錯誤日誌—— 故障首檢入口

配置與查看

# my.cnf [mysqld]
log-error=/var/log/mysql/error.log
# 未指定則預設為 $datadir/hostname.err

文本格式可直接 tail -f 或 less 查看。

日誌輪轉

-- 方式一：mysqladmin
mysqladmin -u root -p flush-logs
-- 生成新 error.log，舊文件重命名為 error.log-old

-- 方式二：SQL
FLUSH LOGS;

三、通用查詢日誌—— 全量審計

啟用配置

# my.cnf [mysqld]
log[=/var/log/mysql/general.log]
# 未指定文件名則預設 hostname.log

新日誌會直接覆蓋舊文件，亦可用 FLUSH LOGS 觸發輪轉。

四、慢查詢日誌—— 性能調優金鑰

啟用與閾值設置

# my.cnf [mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1          # 超過 1 秒記錄（可設為 0.1 等浮點數）
log_queries_not_using_indexes = 1  -- 記錄未走索引的查詢

分析工具鏈

• mysqldumpslow：官方輕量分析工具

  mysqldumpslow -t 10 /var/log/mysql/slow.log  # Top 10 最慢查詢

• pt-query-digest（Percona Toolkit）：功能全面，支援 slow log、binlog、general log

  pt-query-digest /var/log/mysql/slow.log
  pt-query-digest --type=binlog mysql-bin.000001.sql
  pt-query-digest --type=genlog general.log

基準測試輔助

-- 測試某表達式執行 10000 次耗時
SELECT BENCHMARK(10000, MD5('test'));

五、日誌維護自動化建議

1. 定時清理：配合 expire_logs_days（binlog）或 logrotate（文本日誌）自動過期刪除。
2. 異地備份：關鍵日誌（binlog、slow log）同步至異地存儲。
3. 監控告警：錯誤日誌關鍵字（ERROR、Crash、InnoDB: Error）接入日誌平台告警。

總結

「無日誌，不運維」。合理配置四大日誌、建立分析流程、自動化輪轉清理，是 MySQL 穩定運行的基石。慢查詢日誌配合 pt-query-digest 是 SQL 優化的最高性價比切入點；二進制日誌則是高可用架構（主從、PITR）的命脈。