MySQL 配置與管理 – 紀錄與監控
簡介
在 MySQL 的運維過程中，紀錄（Logging）與 監控（Monitoring）是確保資料庫穩定、效能最佳化以及問題快速定位的關鍵。
沒有適當的日誌，我們只能靠猜測來找出慢查詢、錯誤或安全漏洞；缺少監控指標，則無法即時掌握 CPU、磁碟 I/O、緩衝池使用率等資源狀態，容易在系統負載突增時發生服務中斷。
本篇文章將從 MySQL 內建的日誌機制說起，說明如何正確設定、讀取與分析，並結合 Performance Schema、sys schema 與外部監控工具（如 Prometheus、Grafana）建立完整的監控體系，讓讀者能在實務環境中即時掌握資料庫健康狀況。
核心概念
1. MySQL 的日誌類型
| 日誌名稱 | 主要用途 | 常見檔案名稱 | 是否預設啟用 |
|---|---|---|---|
| Error Log | 記錄啟動、關閉、錯誤、警告等重大事件 | mysqld.err |
✅ |
| General Query Log | 記錄所有客戶端連線與執行的 SQL 語句 | general.log |
❌ |
| Slow Query Log | 記錄執行時間超過 long_query_time 的查詢 |
slow.log |
❌ |
| Binary Log（binlog） | 記錄所有改變資料的語句，用於主從複寫與 Point‑in‑Time Recovery | mysql-bin.* |
✅（視需求而定） |
| Audit Log（Enterprise） | 記錄安全相關操作，如登入、權限變更 | audit.log |
❌（需額外安裝） |
小技巧：在開發環境可開啟
general_log方便除錯，正式環境則以slow_log為主，避免產生過大檔案。
2. 啟用與設定日誌
以下示範在 my.cnf（或 mysqld.cnf）中設定常用日誌，並說明如何在執行階段動態調整。
-- 1. 啟用慢查詢日誌，設定閾值為 2 秒
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
-- 2. 開啟一般查詢日誌（僅建議短時間使用）
SET GLOBAL general_log = ON;
SET GLOBAL general_log_file = '/var/log/mysql/general.log';
-- 3. 設定錯誤日誌路徑（重啟後生效）
[mysqld]
log_error = /var/log/mysql/error.log
註解：
SET GLOBAL只會影響當前執行的 MySQL 服務，重啟後會回復my.cnf中的設定。slow_query_log_file與general_log_file必須指定 MySQL 執行帳號有寫入權限的路徑。
3. 透過 Performance Schema 觀測即時指標
Performance Schema 提供了細緻的執行階段資訊，以下查詢可快速取得 Top 10 的慢查詢與 I/O 密集型語句。
-- 取得執行時間最長的前 10 筆語句
SELECT
DIGEST_TEXT AS query,
COUNT_STAR AS exec_count,
SUM_TIMER_WAIT/1000000000000 AS total_seconds,
AVG_TIMER_WAIT/1000000000000 AS avg_seconds
FROM performance_schema.events_statements_summary_by_digest
ORDER BY total_seconds DESC
LIMIT 10;
-- 觀測每秒 I/O 次數與等待時間
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
COUNT_READ AS read_cnt,
SUM_TIMER_READ/1000000000000 AS read_sec,
COUNT_WRITE AS write_cnt,
SUM_TIMER_WRITE/1000000000000 AS write_sec
FROM performance_schema.file_summary_by_instance
WHERE OBJECT_SCHEMA NOT IN ('mysql','performance_schema','information_schema')
ORDER BY read_sec DESC
LIMIT 5;
說明：Performance Schema 的時間單位為 皮秒（10⁻¹² 秒），必須除以
1e12才能換算成秒。
4. 使用 sys schema 簡化監控查詢
MySQL 官方提供的 sys schema 封裝了常用的 Performance Schema 查詢，以下示例展示如何直接取得 慢查詢排行。
-- 直接使用 sys schema 的視圖
SELECT *
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 5;
這個視圖已經把 DIGEST_TEXT、執行次數、總延遲、平均延遲等欄位彙整好，對於日常監控非常方便。
5. 整合外部監控系統（Prometheus + Grafana）
MySQL 官方提供的 mysqld_exporter 可將上述指標匯出為 Prometheus 格式，示例 mysqld_exporter 的基本設定：
# 下載並啟動 exporter
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.14.0/mysqld_exporter-0.14.0.linux-amd64.tar.gz
tar -xzf mysqld_exporter-0.14.0.linux-amd64.tar.gz
cd mysqld_exporter-0.14.0.linux-amd64
# 建立只讀帳號供 exporter 使用
mysql -u root -p -e "
CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'StrongPass!';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';
FLUSH PRIVILEGES;
"
# 啟動 exporter（指定帳號密碼）
./mysqld_exporter --collect.global_status --collect.info_schema.innodb_metrics \
--collect.perf_schema.eventsstatements --collect.perf_schema.eventsstatements_summary_by_digest \
--username=exporter --password=StrongPass!
在 Grafana 中匯入官方提供的 MySQL Dashboard，即可即時看到連線數、緩衝池命中率、慢查詢統計等圖表。
常見陷阱與最佳實踐
| 陷阱 | 可能的後果 | 解決方案 / 最佳實踐 |
|---|---|---|
| 過度開啟 general_log | 產生巨量磁碟 I/O，導致寫入延遲甚至磁碟滿 | 僅在除錯時短暫開啟，使用 SET GLOBAL general_log = OFF 關閉 |
| 忽略 binlog 位置 | 主從同步斷掉或無法進行 Point‑in‑Time Recovery | 定期檢查 SHOW MASTER STATUS;，並將 binlog 保留天數設為足夠（expire_logs_days） |
| Performance Schema 關閉 | 失去細部指標，無法定位瓶頸 | 在 my.cnf 中加入 performance_schema=ON，並根據需求調整 performance_schema_consumer_events_statements_current 等參數 |
| 未設定 logrotate | 日誌檔過大，導致備份與檢索困難 | 使用 Linux logrotate 或 MySQL 自帶的 PURGE BINARY LOGS BEFORE 例行清理 |
| 監控指標過多 | Prometheus 抓取頻率過高，造成資源浪費 | 只保留關鍵指標（連線數、緩衝池、慢查詢、I/O），其餘可透過 --no-collector 參數排除 |
實際應用場景
線上交易系統（OLTP）
- 透過 慢查詢日誌 +
sys.statement_analysis定期找出執行時間超過 500ms 的 SELECT，優化索引或改寫 SQL。 - 使用 Performance Schema 監控
innodb_row_lock_time，避免鎖爭用導致交易延遲。
- 透過 慢查詢日誌 +
資料倉儲（OLAP）
- 開啟 general_log 只在批次 ETL 時段，用於追蹤大量 INSERT/UPDATE 的來源。
- 結合 Prometheus 觀測磁碟 I/O（
innodb_data_reads、innodb_data_writes），提前預警磁碟瓶頸。
多租戶 SaaS 平台
- 為每個租戶建立 audit log（Enterprise 版）或自行在應用層記錄登入/權限變更，滿足合規需求。
- 透過 sys.user_summary 監控每個使用者的資源消耗，防止單一租戶過度佔用。
總結
- 日誌是 MySQL 問題排查與安全稽核的根本，Error Log 必須持續保留，Slow Query Log 應根據業務負載設定合適的
long_query_time，而 General Log 僅在短期除錯時使用。 - Performance Schema + sys schema 為即時監控提供了高解析度的指標，配合外部工具（Prometheus、Grafana）可視覺化呈現，讓運維人員在問題發生前即時預警。
- 避免常見陷阱（過度記錄、未設定 logrotate、關閉 Performance Schema），遵循 「只記錄必要、只監控關鍵」 的原則，才能在保障資料庫穩定性的同時，降低資源消耗。
掌握了上述紀錄與監控技巧，你將能在 MySQL 環境中快速定位瓶頸、提升效能，並為未來的擴展與災難復原奠定堅實基礎。祝你在實務操作中得心應手，資料庫永遠跑得穩、跑得快！