MySQL my.ini 優化模板：2GB 內存多站點高併發抗壓配置

MySQL 2GB 內存高併發優化配置模板 (my.ini / my.cnf)

針對 2GB 內存、多站點部署、高併發讀寫場景，給出生產級參數配置與解釋。注意：參數需根據實際硬件（CPU 核心、磁盤類型）、業務讀寫比、數據量動態調整，不可直接照搬。

核心配置參數

[mysqld]
# === 基礎 ===
port = 3306
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
default-storage-engine = InnoDB
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

# === 連接層 ===
max_connections = 1024          # 最大連接數，配合應用連接池
max_connect_errors = 1000000
back_log = 500                  # 監聽隊列，防突發連接堵塞
wait_timeout = 7200             # 空閒連接超時 (2h)
interactive_timeout = 7200
skip-name-resolve               # 禁用 DNS 解析，加速連接

# === InnoDB 核心 (佔內存 60-70%) ===
innodb_buffer_pool_size = 1G    # 核心：數據+索引緩存，物理內存 50-70%
innodb_buffer_pool_instances = 8 # 實例數，減少鎖競爭 (1GB+ 建議 8)
innodb_log_file_size = 256M     # Redo log 大小，大事務/高寫入需調大
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M    # 事務日誌緩存
innodb_flush_log_at_trx_commit = 1  # 1=最安全 2=高性能(秒級丟失) 0=最高性能
innodb_file_per_table = ON      # 獨立表空間，便於管理
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_thread_concurrency = 0   # 0=無限制，由 OS 調度

# === 緩存與排序 (按連接分配，總量=連接數×單值) ===
sort_buffer_size = 2M           # 排序緩存 (ORDER BY/GROUP BY)
read_buffer_size = 2M           # 順序掃描緩存
read_rnd_buffer_size = 8M       # 隨機讀緩存 (範圍排序)
join_buffer_size = 2M           # JOIN 緩存 (無索引 JOIN)
thread_stack = 256K

# === Query Cache (5.7 可用，8.0 移除) ===
query_cache_type = 0            # 生產建議關閉，改用 Redis
query_cache_size = 0

# === 表緩存 ===
table_open_cache = 2048         # 表緩存數量，配合 open_files_limit
table_definition_cache = 1024

# === 臨時表 ===
tmp_table_size = 64M
max_heap_table_size = 64M       # 內存臨時表上限，超過轉磁盤

# === 二進制日誌 (主從/恢復必需) ===
log_bin = mysql-bin
binlog_format = ROW             # ROW 最安全，MIXED 兼容
expire_logs_days = 7
max_binlog_size = 100M

# === 慢查詢 ===
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1             # >1s 記錄
log_queries_not_using_indexes = 1

# === 其他 ===
key_buffer_size = 32M           # MyISAM 索引緩存 (系統表仍用)
myisam_sort_buffer_size = 64M
thread_cache_size = 100         # 線程緩存，減少創建銷毀

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4
no-auto-rehash

關鍵參數調優邏輯

驗證命令

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';