MySQL 日誌體系深度解析：錯誤、查詢、慢查詢、事務、二進制五大日誌全覽

日誌類型總覽

配置檔統一啟用範例

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

# 通用查詢日誌（生產慎開，IO 大）
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 = 2
log_queries_not_using_indexes = 1

# 二進制日誌（主從/恢複必開）
log_bin = /var/log/mysql/binlog
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M
sync_binlog = 1

各日誌深度解析

1. 錯誤日誌

• 無法禁用，記錄啟動過程、異常堆棧、事件調度、從庫啟動信息
• 5.5.7+ 版本不再支援 FLUSH LOGS 滾動，需手動 mv + mysqladmin flush-logs

# 查看位置
SHOW VARIABLES LIKE 'log_error';
# 即時追蹤
tail -f /var/log/mysql/error.log

2. 通用查詢日誌

• 記錄所有連接、斷開、SQL 文本（含失敗 SQL）
• 高併發下 IO 壓力極大，僅調試時開啟
• 輸出目標可選：FILE（預設）、TABLE（mysql.general_log）、NONE

SET GLOBAL general_log = 1;
SET GLOBAL log_output = 'TABLE';

3. 慢查詢日誌

• 記錄執行時間 > long_query_time 的 SQL（含鎖等待時間）
• 可記錄未用索引查詢（log_queries_not_using_indexes）
• 對效能影響微乎其微，生產強烈建議開啟

# 動態開啟
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;

# 分析工具
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
pt-query-digest /var/log/mysql/slow.log

4. 事務日誌

• InnoDB 專有：ib_logfile0/1（Redo Log）、Undo Log（系統表空間/獨立表空間）
• WAL（Write-Ahead Logging）機制：修改內存 → 寫 Redo Log（順序 IO） → 後台刷臟頁
• 關鍵參數：innodb_flush_log_at_trx_commit=1（最安全）、innodb_log_file_size、innodb_log_files_in_group

5. 二進制日誌

• 記錄所有數據變更（DDL/DML），不記錄 SELECT
• 三種格式：STATEMENT（SQL 文本）、ROW（行變更）、MIXED（混合，預設）
• 核心參數：sync_binlog=1（每次提交 fsync）、expire_logs_days、max_binlog_size

二進制日誌管理實戰

查看與定位

SHOW BINARY LOGS;
SHOW MASTER STATUS;
SHOW BINLOG EVENTS IN 'binlog.000010' FROM 190 LIMIT 5;

命令行解析工具

# 解析為 SQL
mysqlbinlog binlog.000010 > /tmp/recover.sql

# 指定時間點恢複
mysqlbinlog --start-datetime="2023-01-01 10:00:00" \
            --stop-datetime="2023-01-01 11:00:00" \
            binlog.000010 | mysql -uroot -p

安全清理策略

# 1. 依文件名清理（保留指定文件及之後）
PURGE BINARY LOGS TO 'binlog.000010';

# 2. 依時間清理
PURGE BINARY LOGS BEFORE '2023-01-01 00:00:00';

# 3. 自動過期（配置檔）
expire_logs_days = 7

# ⚠️ 禁用 RESET MASTER（會刪除所有，導致從庫中斷）

日誌分析黃金組合

• 錯誤日誌：故障首查、啟動異常
• 慢查詢日誌：效能調優首選、索引缺失發現
• 二進制日誌：主從同步、誤操作恢複、審計追蹤
• 事務日誌：崩潰恢複、InnoDB 內部監控

參考資源

• 官方文檔：MySQL Server Logs
• 分析工具：mysqldumpslow、pt-query-digest (Percona Toolkit)