MySQL 日誌詳解:錯誤、慢查詢、一般查詢與二進位日誌
說明 MySQL 8.0 與 8.4 四類日誌的設定和用途、慢查詢分析,以及複寫和時間點復原所需的 binlog 保留策略。
部署後,MySQL 剛重啟完,讀取 API 卻還是很慢。先看錯誤日誌,確認服務何時啟動、是否經過 InnoDB 崩潰復原,再對照同一時段已完成的慢查詢。如果真正要釐清的是部署改動了哪些資料,就要改看二進位日誌。
以下以自行管理的 MySQL 8.0、8.4 為例。各段 [mysqld] 都是啟動設定,變更前請核對執行個體的實際路徑、權限與既有的持久化設定。
要查哪一種 MySQL 日誌?
| 日誌 | 最適合回答的問題 | 一般預設狀態 |
|---|---|---|
| 錯誤日誌 | 為什麼啟動失敗,或崩潰復原期間發生什麼事? | 啟用,輸出位置依啟動設定而定 |
| 慢查詢日誌 | 哪些已完成的語句符合慢查詢記錄條件? | 關閉 |
| 一般查詢日誌 | 短時間內,伺服器收到了哪些連線和 SQL? | 關閉 |
| 二進位日誌 | 複本或時間點復原需要重播哪些已記錄的變更? | 啟用 |
1. 錯誤日誌(Error Log)
[mysqld]
log_error = /var/log/mysql/error.log
用 SHOW GLOBAL VARIABLES LIKE 'log_error'; 確認目前設定。執行個體尚未啟動時,先查服務管理員或容器的啟動設定。範例指定檔案輸出,mysqld 必須有權寫入該目錄。若由標準錯誤輸出,內容可能由 systemd journal 或容器執行環境接收。
使用預設的 log_filter_internal 時,log_error_verbosity 設為 1 會納入錯誤,2 加入警告,3 再加入資訊訊息,抑制清單仍可能過濾其中部分訊息。把關機、啟動與復原訊息串成時間線,才能判斷接下來要查重啟原因,還是查重啟後的查詢表現。 MySQL 錯誤日誌文件(英文)
2. 慢查詢日誌(Slow Query Log)
[mysqld]
log_output = FILE
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
# log_queries_not_using_indexes = 1
# log_slow_admin_statements = 1
MySQL 8.0、8.4 預設關閉慢查詢日誌,long_query_time 預設為 10 秒,範例改為 1 秒。是否寫入還受 min_examined_row_limit 等條件影響。第一個註解選項可納入未使用索引查找資料列的語句,其中有些不到時間門檻。第二個選項可納入符合條件的 ALTER TABLE 等管理語句。先估算增加的日誌量,再決定是否啟用。
log_output 同時影響慢查詢與一般查詢日誌。FILE 寫入檔案,TABLE 分別寫到 mysql.slow_log 與 mysql.general_log,NONE 停止輸出。透過 SET GLOBAL 變更動態變數,重啟後不會自動沿用。已建立的連線也會保留原本的 long_query_time,可用新連線驗證全域變更。需要跨重啟保留時,使用啟動設定,或經核准後對支援的變數執行 SET PERSIST。
檔案中的 Query_time 與 Lock_time 以秒計算,Rows_examined 和 Rows_sent 分別反映掃描和送出的資料列。安裝相應工具後,可以用有讀取權限的日誌副本彙整相似查詢。
mysqldumpslow -l -s t /path/to/copied/slow.log
pt-query-digest /path/to/copied/slow.log
這裡的 mysqldumpslow 依總查詢時間排序,-l 表示不扣除鎖定等待時間。要進一步看各類查詢的統計,可用 pt-query-digest 依語句指紋分組。正式環境的門檻應考慮服務延遲目標、日誌量與磁碟負載。 MySQL 慢查詢日誌(英文) · mysqldumpslow 選項(英文) · Percona Toolkit(英文)
3. 一般查詢日誌(General Log)
[mysqld]
log_output = FILE
general_log = 1
general_log_file = /var/log/mysql/general.log
這份日誌記錄用戶端連線、斷線,以及伺服器收到的語句。排查應用程式實際送了什麼 SQL 時很實用,但要確認語句是否成功或交易是否提交,仍需其他證據。記錄也可能帶有敏感 SQL,需限制存取。
範例會在啟動時開啟。正式環境若只需短時間蒐證,可保留啟動時關閉,由有權限的操作人員先確認原設定、輸出位置和剩餘空間,再執行 SET GLOBAL general_log = 'ON';,結束後還原。改用資料表儲存,也要限制蒐集時段並安排清理。 MySQL 一般查詢日誌(英文) · 輸出位置(英文)
4. 二進位日誌(Binary Log)
[mysqld]
log_bin = /var/log/mysql/mysql-bin
binlog_expire_logs_seconds = 604800
max_binlog_size = 1G
sync_binlog = 1
正常運作的 MySQL 8.x 伺服器預設啟用 binlog,仍應檢查執行個體的 log_bin。Binlog 以事件記錄納入日誌的變更,提供來源伺服器與複本同步。GTID 自動定位可以請求缺少的交易,前提是來源仍能提供所需事件。
時間點復原(PITR)先還原一致的備份,再從備份對應的位置重播到目標時間,需要保留完整的 binlog 序列。可用 mysqlbinlog 檢查日誌副本。重播輸出會對目標資料庫執行變更,先在隔離的執行個體驗證。
範例中的 604800 秒是七天。用平均生成速率乘以保留時間可粗估磁碟空間,再為流量尖峰與大型交易留餘裕。交易會寫在同一個檔案中,可能超過 max_binlog_size 的輪替門檻。sync_binlog=1 會在一組交易提交前同步 binlog,InnoDB 提交的耐久性還取決於 innodb_flush_log_at_trx_commit 及底層儲存。 MySQL 二進位日誌(英文) · GTID 自動定位(英文) · 時間點復原(英文)
5. 其他值得看的記錄
InnoDB 的 redo 支援崩潰復原,undo 支援交易回滾與一致性讀取。Performance Schema 的 events_statements_summary_by_digest 按語句摘要彙整已蒐集的執行統計。有些查詢單次很快,卻因呼叫頻繁而累積大量耗時,適合先從摘要找線索,再用慢日誌檢查已記錄的個別執行。
集中檢查錯誤日誌與慢查詢
DataKit 能讀取檔案時,可用日誌採集分別指定路徑,保留執行個體及服務標籤。先挑一筆已知的多行 SQL 驗證合併結果。標準慢查詢日誌可參考 MySQL 採集設定的 mysql.p,並核對事件時間、query_time、lock_time、rows_examined。錯誤日誌依伺服器實際輸出的文字或 JSON 格式解析。以上文件為簡體中文。
以執行個體和時間篩選錯誤後,再比較同一時段的查詢耗時及掃描列數。保留原始 SQL 並抽取欄位後,仍需另做正規化才能產生語句指紋。遇到需要通知的重複錯誤,可在觀測雲日誌檢測(簡體中文)設定篩選條件、檢測視窗與筆數門檻。錯誤日誌與慢查詢日誌的保留時間,按各自需要回查的期間設定。
常見問題
慢查詢日誌會拖慢 MySQL 嗎?
成本與筆數、輸出方式和儲存設備有關。降低 long_query_time 或納入未使用索引查找的查詢,都可能明顯增加寫入量。先在具代表性的負載下比較日誌量、磁碟 I/O 與查詢延遲。
忘了關閉一般查詢日誌怎麼辦?
檔案或資料表可能快速成長。若排查前是關閉狀態,有權限的操作人員可執行 SET GLOBAL general_log = 'OFF'; 還原。如果也改過啟動設定,要一併還原。TABLE 只是改變儲存位置,仍需設定蒐集時段和清理方式。
binlog 突然佔滿磁碟怎麼處理?
先確認是寫入速率上升、大型交易,還是保留時間造成。縮短保留時間或執行 PURGE BINARY LOGS 前,確認備份復原視窗和所有複本的進度,包括已斷線的複本。來源可能清除離線複本仍需要的日誌。清理時使用伺服器支援的指令,應用程式能接受分開提交時,再考慮拆分大型交易。 MySQL 清除 binlog 的行為(英文)
代管 MySQL 無法讀取檔案,怎麼蒐集?
使用供應商的下載、匯出或訂閱機制,再按匯出格式解析。例如 Amazon RDS for MySQL 用參數群組啟用慢查詢與一般查詢日誌。先查清楚要用的匯出方式需要哪一種輸出,再決定採集路徑。
