基線配置模板 (MySQL 5.5, 專用 DB 服務器, 16GB 內存, SSD, 核心業務 InnoDB)
[mysqld]
# 基礎標識
server-id = 1
port = 3306
datadir = /data/mysql
socket = /tmp/mysql.sock
pid-file = /data/mysql/mysqld.pid
user = mysql
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
skip-name-resolve # 禁用 DNS 反解，加速連接
explicit_defaults_for_timestamp = ON
# 連接層
max_connections = 2000
max_connect_errors = 100000
max_user_connections = 1800
back_log = 100
wait_timeout = 28800
interactive_timeout = 28800
# InnoDB 核心 (最關鍵)
innodb_buffer_pool_size = 10G # 物理內存 60~80%
innodb_buffer_pool_instances = 8 # 1GB/實例，減少鎖競爭
innodb_log_file_size = 512M # 大日誌減少 checkpoint 頻率
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1 # 完全 ACID，HA 架構必須
innodb_flush_method = O_DIRECT # 繞過 OS cache，避免雙重緩存
innodb_file_per_table = ON # 獨立表空間
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 75
innodb_io_capacity = 4000 # SSD IOPS 能力
innodb_io_capacity_max = 8000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_purge_threads = 4
innodb_thread_concurrency = 0 # 0=不限制，由 OS 調度
# 日誌與備份
log-bin = /data/mysql/binlog/mysql-bin
binlog_format = ROW # 主從一致性最佳
expire_logs_days = 7
max_binlog_size = 500M
sync_binlog = 1 # HA 必須，配合 innodb_flush_log_at_trx_commit=1
slow_query_log = 1
slow_query_log_file = /data/mysql/log/slow.log
long_query_time = 0.5 # 記錄 >500ms 慢查詢
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
# 緩存與臨時表 (MyISAM 少量兼容)
key_buffer_size = 32M
query_cache_size = 0 # 5.5 可開，但生產建議關
query_cache_type = 0
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
join_buffer_size = 2M
thread_stack = 256K
# 安全與兼容
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
default_storage_engine = InnoDB
[client]
default-character-set = utf8mb4
socket = /tmp/mysql.sock
[mysql]
no-auto-rehash
default-character-set = utf8mb4
核心參數解讀
| 參數 | 建議值 | 調優邏輯 |
|---|
innodb_buffer_pool_size | 物理內存 60~80% | InnoDB 數據/索引/鎖/自適應哈希/鎖信息全部在這，越大命中率越高 |
innodb_log_file_size | 256M~2G | 大日誌減少刷盤頻率，但崩潰恢復時間變長，5.5 單文件 4GB 上限 |
innodb_flush_log_at_trx_commit | 1 | 0=每秒刷盤(丟1秒)；2=提交寫OS cache(掛電丟1秒)；1=提交fsync(最安全) |
sync_binlog | 1 | 每事務同步 binlog 到磁碟，主從不丟事務 |
binlog_format=ROW | ROW | Statement 有非確定函數風險，Mixed 兼容但複雜，Row 最安全 |
skip-name-resolve | ON | 跳過 DNS 反解，避免連接卡頓，授權需用 IP |
max_connections | 2000~5000 | 配合連接池 maxPoolSize，防止「Too many connections」 |
tmp_table_size/max_heap_table_size | 64M | 臨時表超限轉 MyISAM 磁碟表，適當調大減少轉換 |
long_query_time | 0.5~1 | 微秒級精度，捕捉潛在慢查詢 |
調優避坑指南
- **不要照搬模板**，必須根據「內存大小、CPU 核心、存儲類型(SSD/HDD)、業務讀寫比、併發量」二次調整。
- 修改
innodb_log_file_size 需停庫、刪除舊 ib_logfile*、啟動 (5.5 無在線調整)。
- 調大
max_connections 同步增大 open_files_limit (系統 ulimit -n 及 /etc/security/limits.conf)。
- 監控
Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests (目標 < 1%)、Innodb_log_waits (應為 0)、Threads_connected 峰值。
- 5.5 版本無
innodb_online_alter_log_max_size、innodb_buffer_pool_dump_at_shutdown 等新特性，大表 DDL 鎖表久，規劃維護窗口。
