MySQL 配置文件 my.cnf / my.ini 核心參數詳解與調優模板 (8.0 版)

配置文件層級與生效順序

# Linux 搜索順序 (後蓋前)
/etc/my.cnf
/etc/mysql/my.cnf
~/.my.cnf

# Windows
my.ini (安裝目錄) / my.cnf (數據目錄)

# 查看實際生效值
mysqld --verbose --help 2>/dev/null | grep -A1 '^datadir'
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

核心參數分類速查表 (生產級建議值參考 16C/64G/SSD 主庫)

1. 基礎/連接/安全

2. 內存/緩衝區 (InnoDB 核心)

3. 查詢緩存 / 排序 / 臨時表 (8.0 查詢緩存已移除)

4. Binlog / 復制 / GTID

5. 其它重要參數

• character_set_server = utf8mb4, collation_server = utf8mb4_0900_ai_ci (8.0 默認)。
• explicit_defaults_for_timestamp = ON (8.0 默認)，TIMESTAMP 無隱式 NOT NULL/DEFAULT。
• lower_case_table_names = 1 (Windows/macOS 強制)，Linux 建議 0 (區分大小寫)，**集群必須一致**。
• max_allowed_packet = 1G (大字段/長 SQL/備份導入)。
• net_read_timeout = 60, net_write_timeout = 60 (防長查詢斷連)。
• slow_query_log = ON, long_query_time = 0.5 (秒), slow_query_log_file = /var/log/mysql/slow.log, log_queries_not_using_indexes = ON (謹慎，量大)。
• log_error = /var/log/mysql/error.log, log_error_verbosity = 2 (8.0)。

調優避坑原則

1. 只改動理解的參數，一次改一個，觀察負載測試指標 (QPS, RT, CPU, IO, Buffer Pool Hit Rate)。
2. 動態參數優先：SET GLOBAL ... 驗證效果，確認後寫入配置文件。
3. 監控先行：Prometheus + Grafana + node_exporter + mysqld_exporter 覆蓋核心指標。
4. 硬件匹配：NVMe 必調 innodb_io_capacity, flush_method；大內存必調 buffer_pool, log_file_size。
5. 版本差異：5.7 vs 8.0 參數名/默認值/移除項 (查詢緩存、密碼插件、時間戳默認) 差異大，**查閱對應版本手冊**。