MySQL 日誌類型、啟用與分析完整指南

前言

MySQL 提供多種日誌，分別記錄錯誤、一般查詢、慢查詢、資料變更與複寫事件。正確啟用並善用分析工具，是問題診斷、效能調優與資安稽核的基石。

一、六大日誌類型對照

二、設定檔啟用範例 (my.cnf)

[mysqld]
# 錯誤日誌
log_error = /var/log/mysql/error.log

# 一般查詢日誌（除錯時暫開）
# general_log = 1
# general_log_file = /var/log/mysql/general.log

# 慢查詢日誌（建議長期開啟）
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1                 # 超過 1 秒
log_queries_not_using_indexes = 1   # 記錄未用索引查詢
log_throttle_queries_not_using_indexes = 60  # 每分鐘最多記錄 60 筆未用索引（防爆量）

# 二進位日誌（主庫/複寫必開）
log_bin = /var/log/mysql/binlog
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 1G
# binlog_do_db = mydb      # 僅記錄指定庫（慎用，複寫過濾建議用 replicate-*-db）
# binlog_ignore_db = test  # 忽略指定庫

修改後 systemctl restart mysqld 生效。

三、動態啟用/關閉（無需重啟）

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;           -- 秒，支援微秒
SET GLOBAL general_log = 1;                 -- 臨時除錯
SET GLOBAL log_queries_not_using_indexes = 1;

四、查看目前日誌狀態

SHOW VARIABLES LIKE '%log%';
SHOW GLOBAL STATUS LIKE '%log%';
-- 確認二進位日誌位置
SHOW MASTER STATUS;
-- 確認從庫中繼日誌位置
SHOW SLAVE STATUS\G

五、二進位日誌分析工具：mysqlbinlog

# 查看單一檔案
mysqlbinlog /var/log/mysql/binlog.000001

# 只看最後 100 行
mysqlbinlog /var/log/mysql/binlog.000001 | tail -100

# 指定時間區間匯出 SQL（用於 PITR）
mysqlbinlog --start-datetime="2024-01-15 10:00:00" \
            --stop-datetime="2024-01-15 11:00:00" \
            /var/log/mysql/binlog.000001 > recover.sql

# 遠端直接套用（謹慎）
mysqlbinlog --read-from-remote-server --host=10.0.0.1 --user=repl --password \
            binlog.000001 | mysql -u root -p

六、慢查詢日誌分析工具：mysqldumpslow / pt-query-digest

# 內建工具：按耗時 Top 20
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log

# 按查詢次數 Top 20
mysqldumpslow -s c -t 20 /var/log/mysql/slow.log

# 按回傳行數 Top 20
mysqldumpslow -s r -t 20 /var/log/mysql/slow.log

# 含特定關鍵字（如 left join）按耗時 Top 10
mysqldumpslow -t 10 -s t -g "left join" /var/log/mysql/slow.log

# Percona Toolkit 專業分析（強烈推薦）
pt-query-digest /var/log/mysql/slow.log --limit 20 --filter '$event->{db} eq "mydb"'

七、慢查詢日誌格式解讀

# Time: 240115 10:17:43
# User@Host: app[app] @ 10.0.0.5 [10.0.0.5]
# Query_time: 12.345  Lock_time: 0.001  Rows_sent: 86345  Rows_examined: 580963
SET timestamp=1705311463;
SELECT * FROM big_table WHERE col1 = 'x' ORDER BY col2 LIMIT 100;

• Query_time：總耗時（秒）。
• Lock_time：等待鎖時間。
• Rows_sent：回傳給客戶端行數。
• Rows_examined：掃描行數（Rows_examined / Rows_sent 越大代表索引利用越差）。

八、日誌輪轉與維護

• 錯誤/一般/慢查詢日誌：配合 logrotate 每日輪轉、保留 7-30 天、壓縮舊檔。
• 二進位日誌：expire_logs_days 自動清理；手動清理請用 PURGE BINARY LOGS TO 'binlog.000010'; 或 PURGE BINARY LOGS BEFORE '2024-01-01';。
• 避免日誌寫滿磁碟導致資料庫掛起，建議掛載獨立分區或磁碟。

總結

「錯誤日誌必開、慢查詢日誌長開、二進位日誌按需開、一般查詢日誌慎開」。善用 mysqlbinlog、mysqldumpslow、pt-query-digest 將日誌轉化為可執行的優化動作。