您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 日誌分析實戰：錯誤日誌、慢查詢日誌、二進制日誌深度解讀與工具鏈

編輯：MySQL 綜合教學

MySQL 三大核心日誌體系

日誌類型	用途	預設位置	關鍵參數
錯誤日誌	服務啟動/關閉、崩潰堆疊、啟動失敗原因	`/var/log/mysqld.log` 或 `hostname.err`	`log_error`
慢查詢日誌	超過閾值的 SQL、未使用索引的查詢	`/var/log/slow.log`	`slow_query_log`, `long_query_time`, `log_queries_not_using_indexes`
二進制日誌	主從複制、時間點恢復 (PITR)、審計追蹤	`/var/lib/mysql/mysql-bin.*`	`log_bin`, `binlog_format` (ROW/STATEMENT/MIXED), `expire_logs_days`

錯誤日誌：故障診斷第一現場

典型錯誤模式識別

# 啟動失敗：資料目錄權限
[ERROR] InnoDB: Operating system error number 13 in a file operation.
[ERROR] InnoDB: Cannot open datafile './ibdata1' (errno: 13)

# 崩潰恢復：Redo Log 重放
[Note] InnoDB: Starting crash recovery.
[Note] InnoDB: Applying a batch of 15 redo log records...

# 複制中斷：從庫 SQL 執行錯誤
[ERROR] Slave SQL: Could not execute Write_rows event on table db.tbl; Duplicate entry '1001' for key 'PRIMARY', Error_code: 1062

關鍵檢查點

搜尋 [ERROR]、[Warning]、Aborted connection
檢查 InnoDB: Crash recovery 耗時，評估 innodb_log_file_size 是否過大
確認 mysqld: ready for connections 正常啟動完成

慢查詢日誌：效能調優金礦

開啟與配置最佳實踐

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5          -- 0.5 秒閾值，生產建議 1~2 秒
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1  -- 記錄管理命令
log_slow_slave_statements = 1  -- 記錄從庫慢 SQL

日誌格式解讀 (標準格式)

# Time: 2024-01-15T10:30:45.123456Z
# User@Host: app_user[app_user] @ 10.0.1.5 [10.0.1.5]  Id: 12345
# Query_time: 2.345678  Lock_time: 0.000123  Rows_sent: 100  Rows_examined: 1000000
# Rows_affected: 0  Bytes_sent: 15234
SET timestamp=1705314645;
SELECT * FROM orders WHERE user_id = 12345 AND status = 1 ORDER BY created_at DESC LIMIT 20;

核心指標判讀

Query_time：總耗時，超過 long_query_time 才記錄
Rows_examined：掃描行數，遠大於 Rows_sent 表示索引失效或全表掃描
Lock_time：鎖等待時間，高值提示熱點行爭用
Rows_affected：影響行數，DML 關注

分析工具鏈

工具	特點	適用場景
pt-query-digest (Percona Toolkit)	聚合分析、Top N、執行計畫關聯、報表輸出	生產例行分析、歷史趨勢
mysqldumpslow	官方內建、輕量、按查詢模板聚合	快速現場排查
MySQL Workbench	圖形化、視覺化執行計畫	單條 SQL 深度調優
ELK/EFK + Filebeat	集中化、實時告警、儀表板	大規模集群、DevOps 整合

pt-query-digest 實戰指令

# 分析最近 1 小時慢查詢，輸出前 20 條
pt-query-digest --since '1h ago' --limit 20 /var/log/mysql/slow.log

# 按照影響行數排序，找出寫入熱點
pt-query-digest --order-by Rows_affected:cnt /var/log/mysql/slow.log

二進制日誌：複制與災備核心

格式選擇：ROW vs STATEMENT vs MIXED

ROW (推薦，5.7+ 預設)： 記錄行級變更，複制最安全，支援所有非確定性函數，體積較大
STATEMENT： 記錄 SQL 文本，體積小，但 UUID()、NOW()、RAND() 等非確定性函數會導致主從不一致
MIXED： 混合模式，自動判斷，兼容性折中

關鍵管理操作

-- 查看當前 Binlog
SHOW MASTER STATUS;

-- 查看所有 Binlog 列表
SHOW BINARY LOGS;

-- 解析 Binlog 內容 (指定位置/時間範圍)
mysqlbinlog --base64-output=decode-rows -v \
  --start-datetime="2024-01-15 10:00:00" \
  --stop-datetime="2024-01-15 11:00:00" \
  /var/lib/mysql/mysql-bin.000014

-- 時間點恢復 (PITR) 範例
mysqlbinlog --start-datetime="2024-01-15 10:00:00" \
  --stop-datetime="2024-01-15 10:30:00" \
  /var/lib/mysql/mysql-bin.000014 | mysql -u root -p

清理策略

-- 自動過期 (建議 7~30 天)
expire_logs_days = 14

-- 手動清理 (保留從庫最舊需求的 Binlog)
PURGE BINARY LOGS TO 'mysql-bin.000010';
-- 或
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';