MySQL 配置文件 my.cnf 核心參數深度解析與優化模板

前言

MySQL 的性能很大程度上取決於配置文件 my.cnf (Windows 下為 my.ini) 的參數設置。一份合理的配置能讓資源利用率最大化，避免「內存溢出」或「CPU 閒置」的尷尬局面。本文將對經典配置示例中的核心參數進行分類解析，並提供針對不同內存規格的優化建議。

一、 配置文件結構與作用域

配置文件按區塊分為 [client]、[mysqld]、[mysqldump]、[mysql] 等，其中 [mysqld] 區塊的參數影響服務端核心運行行為，是優化的重點。

二、 核心參數分類詳解

1. 連接與線程管理

2. 緩存與內存分配 (關鍵)

3. InnoDB 核心優化參數 (示例配置中體現)

innodb_data_file_path = ibdata1:500M:autoextend
innodb_data_home_dir = /var/lib/mysql/ibdata
innodb_log_group_home_dir = /var/lib/mysql/iblogs
innodb_flush_log_at_trx_commit = 1  # 1=最安全(ACID), 2/0=高性能但掉電丟數據風險
innodb_lock_wait_timeout = 120
innodb_thread_concurrency = 16     # 0 表示無限制，現代版本建議 0
innodb_file_per_table = 1          # **強烈建議開啟**，獨立表空間便於管理回收空間

4. 日誌與複製

• log-bin：啟用 Binlog，主從複製、數據恢復必備。建議指定路徑 log-bin=/var/log/mysql/mysql-bin。
• expire_logs_days = 7：自動清理舊 Binlog，防止磁盤寫滿。
• slow_query_log + long_query_time=1 (或 0.05 秒級)：必開，性能調優基石。
• server-id：集群環境唯一標識。

5. 安全與網絡

• skip-name-resolve：禁用 DNS 解析，消除連接延遲，**但要求授權表 Host 列必須用 IP**。
• skip-networking：禁用 TCP/IP，僅允許 Unix Socket 連接。**極大增強安全性**，但禁止遠程連接（需應用同機部署）。
• bind-address = 127.0.0.1：僅監聽本地，折中方案。
• max_connect_errors = 10000000：防止主機因連接錯誤被封鎖。

三、 不同內存規格推薦基線配置 (僅供參考，需實測調整)

場景 A：4GB 內存 (入門級/開發測試)

[mysqld]
innodb_buffer_pool_size = 2G
innodb_log_file_size = 256M
key_buffer_size = 64M
max_connections = 200
thread_cache_size = 16
sort_buffer_size = 2M
read_buffer_size = 2M
join_buffer_size = 2M
tmp_table_size = 64M
max_heap_table_size = 64M
query_cache_size = 0  # 建議關閉
slow_query_log = 1
long_query_time = 1
skip-name-resolve
innodb_file_per_table = 1

場景 B：16GB 內存 (生產主庫標配)

[mysqld]
innodb_buffer_pool_size = 10G
innodb_log_file_size = 1G
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 1
innodb_io_capacity = 2000  # SSD 建議 2000-5000
key_buffer_size = 128M
max_connections = 500
thread_cache_size = 64
table_open_cache = 2000
sort_buffer_size = 4M
read_buffer_size = 4M
join_buffer_size = 4M
tmp_table_size = 256M
max_heap_table_size = 256M
slow_query_log = 1
long_query_time = 0.5
log_queries_not_using_indexes = 1
skip-name-resolve
innodb_file_per_table = 1

四、 配置修改後的驗證流程

1. 語法檢查：mysqld --validate-config (8.0+) 或 mysqld --help --verbose 2>&1 | head -20。
2. 重啟服務：systemctl restart mysqld。
3. 觀察錯誤日誌：tail -f /var/log/mysql/error.log。
4. 運行壓測 (sysbench) 或觀察業務指標 (QPS, Latency, 內存增長曲線)。
5. 定期執行 SHOW GLOBAL STATUS 分析 Innodb_buffer_pool_reads, Created_tmp_disk_tables, Threads_created 等關鍵指標微調。

五、 避坑指南

• ❌ 盲目複製網上「高性能配置」不看業務場景 (OLTP vs OLAP)。
• ❌ 將 sort_buffer_size 設為 64M 導致連接數稍增就 OOM。
• ❌ 修改 innodb_log_file_size 直接刪文件重啟導致啟動失敗 (需按流程操作)。
• ✅ 監控 Swap usage，MySQL 進程一旦 Swap 性能雪崩。
• ✅ 使用 mysqltuner.pl 或 MySQLTuner-perl 輔助分析，但**不可盲目執行其建議**。