MySQL 配置與管理 – my.cnf 基本參數
簡介
在 MySQL 的日常運維與效能調校中，my.cnf（或 my.ini）是最核心的設定檔。它決定了資料庫的啟動方式、資源使用上限、日誌行為以及許多安全相關的選項。即使是小型開發環境，適當的 my.cnf 設定也能提升穩定性與效能；在大型線上服務中，錯誤的參數甚至可能造成服務中斷或資源浪費。
本篇文章針對 my.cnf 的基本參數 進行說明，從檔案結構、常見設定項目，到實務上的最佳實踐與常見陷阱，提供 初學者到中級開發者 都能直接套用的範例與操作步驟。透過本篇的學習，你將能夠：
- 正確定位與編輯 my.cnf 檔案。
- 調整記憶體、連線數與日誌相關的關鍵參數。
- 依照不同的使用情境（開發、測試、正式環境）制定適合的配置。
核心概念
1. my.cnf 的檔案結構與搜尋順序
MySQL 在啟動時會依序搜尋多個位置的設定檔，常見的搜尋路徑如下（以 Linux 為例）：
| 順序 | 路徑 | 說明 |
|---|---|---|
| 1 | /etc/my.cnf |
系統全域設定，最常見的預設位置 |
| 2 | /etc/mysql/my.cnf |
部分發行版（如 Ubuntu）使用的路徑 |
| 3 | /usr/local/mysql/etc/my.cnf |
手動編譯或二進位安裝時的預設路徑 |
| 4 | ~/.my.cnf |
使用者層級設定，僅對當前使用者有效 |
| 5 | --defaults-file 參數指定的路徑 |
最高優先權，可在啟動指令中明確指定 |
小技巧：使用
mysqld --verbose --help | grep -A 1 "Default options"可以快速查看當前 MySQL 讀取的設定檔順序。
2. 基本的段落（Section）與語法
my.cnf 采用 INI 風格的語法，由 段落（Section） 與 鍵值對（key=value） 組成。常見的段落包括：
| 段落 | 目的 |
|---|---|
[client] |
客戶端工具（如 mysql、mysqldump）共用的連線參數 |
[mysqld] |
伺服器端（mysqld）本身的設定 |
[mysqld_safe] |
mysqld_safe 包裝腳本的相關參數 |
[mysqldump] |
mysqldump 工具的預設行為 |
[mysql] |
mysql 命令列介面的預設參數 |
範例：
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld]
port = 3306
datadir = /var/lib/mysql
注意：同一鍵值在不同段落中可以有不同的設定，MySQL 會根據使用情境選擇對應段落。
3. 常見的基礎參數說明
以下列出在 [mysqld] 段落中最常調整的參數，並說明其意義與影響。
| 參數 | 類型 | 說明 | 建議值（依環境） |
|---|---|---|---|
port |
整數 | MySQL 監聽的 TCP 端口 | 3306（預設） |
datadir |
路徑 | 資料庫檔案所在目錄 | 視磁碟布局而定，建議放在高速磁碟 |
socket |
路徑 | 本機 Unix socket 檔案 | /var/run/mysqld/mysqld.sock |
max_connections |
整數 | 同時允許的最大連線數 | 151（預設），根據流量調整 |
innodb_buffer_pool_size |
記憶體 | InnoDB Buffer Pool 大小，決定快取資料量 | 70% 以上的系統記憶體（若僅跑 MySQL） |
innodb_log_file_size |
記憶體 | InnoDB redo log 檔案大小 | 256M~1G，視寫入量調整 |
query_cache_type / query_cache_size |
整數/布林 | Query Cache（MySQL 5.7 以前） | 8.0 後已移除，若使用舊版慎用 |
log_error |
路徑 | 錯誤日誌檔案位置 | /var/log/mysql/error.log |
slow_query_log |
布林 | 是否啟用慢查詢日誌 | ON（建議正式環境開啟） |
slow_query_log_file |
路徑 | 慢查詢日誌檔案位置 | /var/log/mysql/slow.log |
long_query_time |
浮點數 | 判定為慢查詢的最小執行時間（秒） | 2.0（可根據需求調整） |
程式碼範例
以下提供 5 個實務上常用的 my.cnf 設定範例，每個範例都附有簡短說明與註解。
範例 1：最小化測試環境設定
# my.cnf - 測試環境（單機、低資源）
[mysqld]
port = 3306
datadir = /opt/mysql/data
socket = /opt/mysql/mysql.sock
# 只允許少量同時連線，避免測試機資源被耗盡
max_connections = 30
# 記憶體設定：僅佔用 256MB
innodb_buffer_pool_size = 128M
innodb_log_file_size = 64M
# 關閉 Query Cache（MySQL 5.7 以前）
query_cache_type = 0
query_cache_size = 0
# 啟用慢查詢日誌，方便除錯
slow_query_log = ON
slow_query_log_file = /opt/mysql/logs/slow.log
long_query_time = 1
說明：此設定適合於本機開發或 CI 測試環境，資源需求極低，且保留了除錯所需的慢查詢日誌。
範例 2：中等規模的生產環境（單節點）
# my.cnf - 生產環境（單節點、8GB RAM）
[mysqld]
port = 3306
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
# 連線數上限根據預估流量調整
max_connections = 500
# InnoDB 設定（占用 70% 記憶體）
innodb_buffer_pool_size = 5G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1 # 強一致性
# 啟用慢查詢日誌，記錄超過 2 秒的查詢
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
# 錯誤日誌與一般查詢日誌
log_error = /var/log/mysql/error.log
general_log = OFF
說明：此範例針對 8GB 記憶體的單節點伺服器做了 70% 記憶體分配給 InnoDB Buffer Pool，同時保留嚴格的事務提交設定，適合大多數中小型網站。
範例 3：高可用叢集（主從）環境的共用設定
# my.cnf - 主從叢集共用設定
[mysqld]
port = 3306
datadir = /data/mysql
socket = /data/mysql/mysql.sock
log_bin = mysql-bin # 開啟二進位日誌，支援複寫
binlog_format = ROW # 建議使用 ROW 模式
server_id = 1 # 每台機器需唯一，主庫為 1
# 複寫相關參數
relay_log = relay-bin
log_slave_updates = ON
# 記憶體與連線設定
max_connections = 400
innodb_buffer_pool_size = 4G
innodb_log_file_size = 256M
# 監控用日誌
slow_query_log = ON
slow_query_log_file = /data/mysql/slow.log
long_query_time = 1
說明：在主從環境中，binary log 必須開啟，且
server_id必須唯一。此範例提供了基礎的複寫設定，適合作為所有節點的基礎檔案，只需在各節點調整server_id。
範例 4：使用 mysqld_safe 包裝腳本的額外參數
# my.cnf - mysqld_safe 設定
[mysqld_safe]
log_error = /var/log/mysql/mysqld_safe_error.log
nice = 0 # 以正常優先權執行，若系統負載高可調高 nice
說明：
mysqld_safe是 MySQL 的啟動腳本，提供自動重啟與錯誤日誌功能。若想讓 MySQL 在資源競爭激烈的環境中降低 CPU 優先權，可調整nice值。
範例 5：客戶端工具（mysql、mysqldump）共用設定
# my.cnf - 客戶端共用設定
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
default-character-set = utf8mb4
# 讓所有客戶端預設使用壓縮連線，減少網路流量（需伺服器支援）
compress = true
[mysql]
# mysql 互動式介面預設顯示列寬
pager = less -SFX
[mysqldump]
quick
max_allowed_packet = 64M
說明：將常用的連線資訊與字元編碼放在
[client]，可以讓所有 MySQL 客戶端自動套用，減少每次執行指令時必須手動加參數的麻煩。
常見陷阱與最佳實踐
| 陷阱 | 可能的後果 | 解決方式 / 最佳實踐 |
|---|---|---|
| 忘記重啟服務 | 設定變更未生效，仍使用舊參數 | 編輯完 my.cnf 後，務必執行 systemctl restart mysql 或 service mysqld restart |
innodb_buffer_pool_size 設定過大 |
系統記憶體被耗盡，導致 OOM (Out‑Of‑Memory) | 建議：保留至少 20% 記憶體給 OS 與其他服務，使用 free -m 觀察實際可用量 |
max_connections 設定過高 |
每個連線會佔用一定的記憶體，總量可能超過實際可用 | 計算方式：max_connections * (thread_stack + per‑thread buffers)，確保不會超過可用記憶體 |
| 忽略慢查詢日誌 | 無法定位效能瓶頸 | 在正式環境 必須 開啟 slow_query_log，並設定合理的 long_query_time |
| 使用舊版 Query Cache | 在高併發寫入場景下會造成鎖爭用，降低效能 | MySQL 5.7 之後已廢除，若使用 5.6 以下，僅在讀取密集且資料變更少的情況下使用 |
未設定 log_error 路徑 |
錯誤訊息寫入預設位置，可能被輪替或無法即時取得 | 明確指定 log_error，並確保目錄權限正確（chmod 750、chown mysql:mysql） |
server_id 重複（主從） |
複寫失敗、產生循環複寫 | 每台 MySQL 伺服器必須有唯一的 server_id，建議使用 1~2^32-1 範圍內的正整數 |
未對 my.cnf 設定檔加密或限制權限 |
敏感資訊（如 user、password）外洩 |
設定檔案權限建議為 600（僅 MySQL 用戶可讀寫），或使用 !includedir 把密碼放在受保護的子目錄 |
最佳實踐小結
- 分層管理：使用
/etc/my.cnf作為全域設定，將環境特有的參數放在/etc/mysql/conf.d/或/etc/mysql/mysql.conf.d/中，便於版本控制與維護。 - 使用
!includedir：讓設定檔結構化，方便在不同環境（dev、staging、prod）僅替換子目錄內的檔案。 - 定期檢視日誌：利用
logrotate讓錯誤日誌與慢查詢日誌自動輪替，避免磁碟被寫滿。 - 自動化測試：在 CI pipeline 中加入
mysqld --help --verbose檢查實際載入的參數，確保每次部署的設定正確。 - 效能監控：結合
performance_schema、sysschema 與Prometheus/Grafana，即時觀測innodb_buffer_pool_reads、threads_connected等指標，快速發現設定瓶頸。
實際應用場景
| 場景 | 目標 | 建議的 my.cnf 調整 |
|---|---|---|
| 本機開發 | 快速啟動、低資源占用 | max_connections=50、innodb_buffer_pool_size=128M、關閉 binary log |
| 測試環境（CI） | 每次測試自動建立、保留慢查詢日誌以 debug | 使用 --initialize-insecure 建立資料庫，slow_query_log=ON、long_query_time=0.5 |
| 小型電商網站（單節點） | 高併發讀寫、資料一致性 | innodb_flush_log_at_trx_commit=1、innodb_buffer_pool_size=70% RAM、max_connections=300 |
| 大型 SaaS 平台（多節點） | 主從複寫、容錯、線上備份 | log_bin、server_id 唯一、relay_log、expire_logs_days=7、sync_binlog=1 |
| 資料倉儲（OLAP） | 大批量讀取、長查詢 | innodb_buffer_pool_size 設定較大、read_rnd_buffer_size、sort_buffer_size 調高、slow_query_log 設為 ON 並將 long_query_time 調至 5 秒 |
案例說明：假設你在一家中型電商公司，已經在單節點 MySQL 上部署了商品與訂單資料。根據每日高峰期的流量觀測，連線峰值約 350，且大部分查詢集中於商品查詢。此時，你可以將
max_connections調整至 400，將innodb_buffer_pool_size設為 5GB（假設伺服器有 8GB RAM），同時開啟slow_query_log並把long_query_time設為 2 秒，配合pt-query-digest定期分析慢查詢，持續優化索引與查詢語句。
總結
- my.cnf 是 MySQL 運維的核心入口，掌握其段落結構與關鍵參數，才能在不同環境中快速調校。
- 記憶體、連線數與日誌 是最常被調整的三大類別，合理配置可避免資源枯竭與效能瓶頸。
- 實務上，透過分層設定、
!includedir、日誌輪替與自動化測試，能讓配置管理更安全、可追蹤。 - 常見陷阱 如過大
innodb_buffer_pool_size、錯誤的max_connections設定、忽略慢查詢日誌，都可能導致服務不穩或難以排錯。 - 最佳實踐 包括：保留系統記憶體、開啟慢查詢日誌、使用唯一
server_id、設定檔權限、結合監控工具。
只要依照本文提供的範例與檢查清單，從開發環境到正式上線的每一步，都能確保 MySQL 的配置既安全又高效。祝你在未來的資料庫管理與調校路上，能以 my.cnf 為利器，快速解決問題、提升服務品質！