MySQL my.ini/my.cnf 核心配置參數完整解析與效能調校實戰範本

前言

MySQL 服務啟動時讀取 my.ini (Windows) 或 my.cnf (Linux) 配置檔。合理的參數設定能發揮硬體最大效能，錯誤的配置則會導致 OOM、磁碟 IO 瓶頸、複寫中斷等嚴重故障。本文系統分類 80+ 核心參數，提供預設值、建議值、調校原理與生產環境範本，支援 MySQL 5.7/8.0。

一、配置檔結構與讀取順序

# Linux 讀取順序 (後者覆蓋前者)
/etc/my.cnf
/etc/mysql/my.cnf
/usr/etc/my.cnf
~/.my.cnf

# Windows
C:\Windows\my.ini
C:\my.ini
安裝目錄\my.ini

# 查看實際生效值
mysql> SHOW VARIABLES;
mysql> SHOW VARIABLES LIKE 'innodb_%';

二、核心參數分類詳解

■ 目錄與檔案

■ 連線與執行緒

■ 緩衝區與快取 (單連線/全域)

■ InnoDB 核心參數 (效能核心)

■ Binlog 與複寫

■ 安全與其它

三、生產環境配置範本 (MySQL 8.0, 32GB RAM, NVMe SSD, 專用 DB)

[mysqld]
# === 基礎路徑 ===
basedir = /usr/local/mysql
datadir = /data/mysql
tmpdir = /tmp/mysql
socket = /var/run/mysqld/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid
log-error = /var/log/mysql/error.log
lower_case_table_names = 1

# === 連線與執行緒 ===
max_connections = 3000
max_user_connections = 2500
thread_cache_size = 200
back_log = 500
wait_timeout = 300
interactive_timeout = 28800
max_connect_errors = 100000

# === 全域緩衝與快取 ===
table_open_cache = 16000
table_definition_cache = 8000
max_heap_table_size = 256M
tmp_table_size = 256M

# === Session 緩衝 (謹慎調大，總量 = Session數 × 大小) ===
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
join_buffer_size = 4M
binlog_cache_size = 2M

# === InnoDB 核心 ===
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 16
innodb_log_file_size = 4G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 512M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = ON
innodb_io_capacity = 6000
innodb_io_capacity_max = 12000
innodb_read_io_threads = 12
innodb_write_io_threads = 12
innodb_page_cleaners = 8
innodb_max_dirty_pages_pct = 90
innodb_max_dirty_pages_pct_lwm = 10
innodb_lock_wait_timeout = 5
innodb_online_alter_log_max_size = 512M

# === Binlog 與複寫 ===
log_bin = binlog/mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 2592000
max_binlog_size = 1G
sync_binlog = 1
binlog_rows_query_log_events = ON
gtid_mode = ON
enforce_gtid_consistency = ON
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
slave_preserve_commit_order = ON
log_slave_updates = ON

# === 安全與 SQL 模式 ===
skip_name_resolve = ON
skip_host_cache = ON
local_infile = OFF
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
default_authentication_plugin = mysql_native_password

# === 字符集 ===
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci

# === 慢查詢日誌 ===
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON
log_throttle_queries_not_using_indexes = 100  # 限流，避免日誌爆炸

# === 其它優化 ===
open_files_limit = 65535
max_allowed_packet = 64M
net_buffer_length = 16K
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
key_buffer_size = 64M  # 僅 MyISAM 系統表用

四、配置驗證與調優迴路

1. 語法檢查：mysqld --validate-config (8.0+) 或 mysqld --help --verbose 2>&1 | head -20。
2. 啟動測試：systemctl start mysqld，檢查 error.log 無 [ERROR]。
3. 關鍵指標基線：記錄啟動後 SHOW GLOBAL STATUS 核心值。
4. 壓測觀測：sysbench/TPCC 壓測，監控：
   • Buffer Pool 命中率：(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests > 99%
   • 臨時表落盤率：Created_tmp_disk_tables / Created_tmp_tables < 5%
   • 排序落盤率：Sort_merge_passes / Sort_scan < 1%
   • 鎖等待：Innodb_row_lock_waits、Innodb_row_lock_time_avg
5. 微調迴路：根據瓶頸調整單一參數 → 重啟/線上修改 (動態參數) → 壓測驗證 → 固化配置。

五、常見配置錯誤與避坑