MySQL 生產環境配置優化：my.cnf 核心參數深度解析與最佳實踐

前言

my.cnf 是 MySQL 性能的「畫龍點睛」之筆。本文基於實戰經驗總結的核心配置模板，適用於讀寫混合、InnoDB 為主、單機 16G~64G 內存的典型業務場景。參數需根據實際硬件、業務模式調整，切勿盲目複製。

完整配置模板（註釋版）

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set = utf8mb4

[mysql]
no-auto-rehash
default-character-set = utf8mb4
socket = /var/lib/mysql/mysql.sock

[mysqld]
# === 基礎設置 ===
user = mysql
port = 3306
socket = /var/lib/mysql/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
default-storage-engine = INNODB
default-time-zone = '+8:00'
skip-name-resolve
skip-external-locking

# === 連接與安全 ===
max_connections = 2000
max_connect_errors = 100000
max_allowed_packet = 64M
wait_timeout = 86400
interactive_timeout = 86400

# === 緩存與內存（核心）===
# InnoDB 緩存池：物理內存 60~70%，單實例
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_chunk_size = 128M

# 表緩存
table_open_cache = 4000
table_definition_cache = 2000

# 線程緩存
thread_cache_size = 100
thread_stack = 512K

# 查詢緩存（5.7 建議關閉，8.0 已移除）
query_cache_type = 0
query_cache_size = 0

# 會話級緩存（謹慎設置，防止 OOM）
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 1M
binlog_cache_size = 4M

# === InnoDB 核心優化 ===
# 事務日志
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1  # 核心參數，見下文詳解
innodb_flush_method = O_DIRECT

# 刷新策略
innodb_max_dirty_pages_pct = 75
innodb_max_dirty_pages_pct_lwm = 10
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_neighbors = 0  # SSD 設為 0

# 鎖與並發
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 0  # 0 表示不限制
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_purge_threads = 4

# 表空間
innodb_file_per_table = 1
innodb_data_file_path = ibdata1:1G:autoextend

# 雙寫緩衝
innodb_doublewrite = ON
innodb_change_buffer_max_size = 25

# === Binlog 與復制 ===
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = MINIMAL
server_id = 1
gtid_mode = ON
enforce_gtid_consistency = ON
sync_binlog = 1
binlog_expire_logs_seconds = 604800  # 7 天
max_binlog_size = 1G

# === 慢查詢與審計 ===
slow_query_log = 1
slow_query_log_file = /data/logs/slow.log
long_query_time = 1
log_queries_not_using_indexes = 0
log_slow_admin_statements = 1
log_slow_slave_statements = 1

# === 其他 ===
tmp_table_size = 64M
max_heap_table_size = 64M
open_files_limit = 65535
explicit_defaults_for_timestamp = 1

五大核心參數深度解析

1. innodb_flush_log_at_trx_commit —— 數據安全與性能的平衡點

實戰建議：主庫核心業務堅持 1；從庫、報表庫可設 2；配合電池備援 RAID 卡（BBWC/FBWC）可大幅減輕 fsync 開銷。

2. innodb_buffer_pool_size 與 innodb_buffer_pool_instances

• Buffer Pool 承載數據頁、索引頁、鎖信息、自適應哈希索引等
• 命中率公式：(1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%，目標 > 99%
• 實例數建議：Buffer Pool > 1GB 時啟用多實例，每實例 ≥ 1GB，通常設為 CPU 核心數或 8（較大者）
• 避免 Swap：vm.swappiness = 1，預留 10~20% 內存給 OS

3. binlog_format = ROW + binlog_row_image = MINIMAL

• ROW：記錄行變更，避免 STATEMENT 模式下函數、觸發器導致主從不一致
• MINIMAL：僅記錄修改前後的差異列，大幅減小 binlog 體積（對比 FULL 可減 30~50%）
• 需 MySQL 5.6+，主從版本一致

4. innodb_lock_wait_timeout 與 transaction_isolation

• innodb_lock_wait_timeout = 50（默認 50s）：OLTP 核心鏈路建議降至 5~10s，快速失效防止連鎖阻塞；批處理任務可單獨 SET SESSION 調大
• transaction_isolation = REPEATABLE-READ：InnoDB 默認，配合 MVCC 解決幻讀；分布式事務必須 SERIALIZABLE

5. sync_binlog = 1

• 每提交 fsync binlog，配合 innodb_flush_log_at_trx_commit = 1 實現雙 1，保證主從零數據丟失
• 性能壓力大時，可考慮 sync_binlog = 100 等非 1 值，但需評估風險

配置生效與驗證

# 修改配置後
systemctl restart mysqld

# 關鍵參數驗證
mysql -e "SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';"
mysql -e "SHOW VARIABLES LIKE 'sync_binlog';"
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"

避坑指南

• 不要在配置文件中混用 MB/GB 大小寫，統一大寫
• innodb_log_file_size 修改需停庫、刪除舊 ib_logfile* 後啟動
• 開啟 GTID 後不可輕易關閉，主從拓撲變更需謹慎規劃
• 定期 mysqldump --no-data 導出表結構備份，防 DDL 意外