MySQL 核心配置參數詳解：my.cnf 完整調優指南
MySQL 安裝後提供多種預設配置模板（my-huge.cnf, my-large.cnf, my-medium.cnf, my-small.cnf, my-innodb-heavy-4G.cnf），生產環境需根據硬體資源與業務負載進行深度調優。本文系統整理核心參數分類、作用與建議值。
配置檔結構與預設模板選擇
| 模板檔案 | 適用場景 | 記憶體 |
|---|
| my-huge.cnf | 高端專用伺服器 | 1-2 GB，主要跑 MySQL |
| my-innodb-heavy-4G.cnf | 純 InnoDB、大查詢低流量 | 4 GB |
| my-large.cnf | 中型專用伺服器 | ~512 MB |
| my-medium.cnf | 低端伺服器 | <128 MB |
| my-small.cnf | 最小配置/嵌入式 | <64 MB |
核心參數分類詳解
1. 基礎路徑與核心設定
[mysqld]
basedir = /usr/local/mysql # 安裝根目錄
datadir = /data/mysql # 資料目錄
socket = /tmp/mysql.sock # Unix Socket 路徑
pid-file = /data/mysql/mysql.pid # PID 檔案
port = 3306 # TCP 連接埠
user = mysql # 運行用戶
default-storage-engine = InnoDB # 預設引擎
character-set-server = utf8mb4 # 預設字元集
collation-server = utf8mb4_unicode_ci
lower_case_table_names = 1 # 表名大小寫不敏感(1=不敏感)
2. 連線與網路安全
max_connections = 1000 # 最大連線數
max_connect_errors = 100000 # 連線錯誤阈值
connect_timeout = 10 # 連線超時(秒)
wait_timeout = 28800 # 非互動連線空閒超時(8小時)
interactive_timeout = 28800 # 互動連線空閒超時
skip-name-resolve # 禁用 DNS 反解析(加速連線)
skip-host-cache # 禁用主機名快取
bind-address = 0.0.0.0 # 監聽所有介面(或指定內網IP)
max_allowed_packet = 64M # 最大封包大小
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
3. 記憶體與快取（InnoDB 關鍵）
# InnoDB 核心：建議實體記憶體 60-80%
innodb_buffer_pool_size = 4G # 核心參數！資料+索引快取
innodb_buffer_pool_instances = 8 # 實例數(>1GB時建議8)
innodb_log_buffer_size = 64M # 事務日誌快取
innodb_log_file_size = 512M # Redo Log 檔案大小
innodb_log_files_in_group = 2 # Redo Log 群組檔案數
innodb_flush_log_at_trx_commit = 1 # 1=最安全, 2=折衷, 0=最高效
innodb_flush_method = O_DIRECT # 繞過檔案系統快取
innodb_file_per_table = ON # 獨立表空間
innodb_open_files = 4000 # 最大開啟檔案數
innodb_max_dirty_pages_pct = 75 # 髒頁比例觸發刷盤
# 查詢快取(5.7+已棄用, 8.0移除)
query_cache_type = 0
query_cache_size = 0
# 連線級快取
thread_cache_size = 64 # 執行緒快取
table_open_cache = 4000 # 表快取
table_definition_cache = 2000 # 表定義快取
# 會話級緩衝區(按連線分配, 謹慎設大)
sort_buffer_size = 4M # 排序緩衝
read_buffer_size = 2M # 循序讀取緩衝
read_rnd_buffer_size = 8M # 隨機讀取緩衝
join_buffer_size = 4M # Join 緩衝(無索引時)
binlog_cache_size = 4M # Binlog 事務緩衝
4. 日誌與複寫
# 二進位日誌(主從複寫/時間點恢復必備)
log-bin = mysql-bin
binlog_format = ROW # ROW/STATEMENT/MIXED
expire_logs_days = 7 # 自動清理天數
max_binlog_size = 1G # 單檔最大值
sync_binlog = 1 # 每次提交同步到磁碟(最安全)
binlog_rows_query_log_events = ON # 記錄原始查詢(ROW格式輔助)
# 慢查詢日誌
slow_query_log = ON
slow_query_log_file = /data/logs/slow.log
long_query_time = 1 # 超過1秒記錄
log_queries_not_using_indexes = ON # 記錄未用索引查詢
# 錯誤日誌
log-error = /data/logs/error.log
# 中繼日誌(從庫)
relay_log = relay-bin
relay_log_recovery = ON # 啟動時自動修復中繼日誌
read_only = ON # 從庫唯讀
5. InnoDB 進階調優
innodb_thread_concurrency = 0 # 0=不限制(建議)
innodb_read_io_threads = 16 # 讀 IO 執行緒
innodb_write_io_threads = 16 # 寫 IO 執行緒
innodb_io_capacity = 2000 # SSD IOPS 能力
innodb_io_capacity_max = 4000 # 爆發 IOPS
innodb_purge_threads = 4 # Purge 執行緒
innodb_lru_scan_depth = 2000 # LRU 掃描深度
innodb_lock_wait_timeout = 50 # 鎖等待超時
innodb_deadlock_detect = ON # 死鎖檢測
innodb_online_alter_log_max_size = 128M # DDL 線上操作日誌上限
6. 安全與稽核
local_infile = OFF # 禁用 LOCAL INFILE
skip-show-database # 隱藏 SHOW DATABASES
safe-user-create = ON # 建立用戶需 INSERT 權限
secure-auth = ON # 禁用舊式密碼認證
不同負載場景建議值快速參考
| 參數 | OLTP 高併發 | OLAP 大查詢 | 混合負載 |
|---|
| innodb_buffer_pool_size | 70-80% RAM | 50-60% RAM | 60-70% RAM |
| max_connections | 2000-5000 | 200-500 | 1000-2000 |
| innodb_flush_log_at_trx_commit | 1 | 2 | 1 |
| sync_binlog | 1 | 0/100 | 1 |
| sort_buffer_size | 2-4M | 8-16M | 4M |
| join_buffer_size | 2-4M | 8-16M | 4M |
配置驗證與生效
# 語法檢查
mysqld --verbose --help 2>/dev/null | head -20
# 線上修改(部分需重啟)
SET GLOBAL innodb_buffer_pool_size = 4294967296; # 5.7+ 支援線上調整
SET GLOBAL max_connections = 2000;
# 查看當前值
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
避坑指南
- ❌ 盲目複製網路配置——必須根據實體記憶體、CPU 核心、磁碟型別(SSD/HDD)、業務型別計算
- ❌ 會話級緩衝區設過大——`sort_buffer_size` × `max_connections` 可能耗盡記憶體
- ❌ 忽略 `innodb_flush_log_at_trx_commit=1` + `sync_binlog=1` 的 IO 壓力——SSD 必備
- ✅ 建立配置版本控管，變更前備份，變更後觀察 `SHOW GLOBAL STATUS` 關鍵指標
