MySQL 5.5.49 為 5.5 系列最後版本，提供 5 種預設配置檔，按記憶體容量分級：
| 配置檔 | 適用記憶體 | 場景 |
|---|---|---|
| my-small.cnf | ≤ 64 MB | 極小型系統，偶爾使用 MySQL |
| my-medium.cnf | 32~128 MB | 中型系統，MySQL 為重要角色 |
| my-large.cnf | 512 MB | 大型系統，主跑 MySQL |
| my-huge.cnf | 1~2 GB | 巨型系統，主跑 MySQL |
| my-innodb-heavy-4G.cnf | 4 GB | InnoDB 專用、支援 ACID、少連線重查詢 |
註：MySQL 5.6+ 已移除預設配置檔選擇，改由 DBA 依實際硬體自行調優。
port = 3306
socket = /tmp/mysql.sock
# default-character-set = utf8 # 建議加上back_log = 50 # 監聽隊列長度，高併發建議調大
max_connections = 100 # 最大連線數，生產環境常調至 1000+
max_connect_errors = 10 # 主機錯誤上限，建議調大防誤封
thread_cache_size = 8 # 執行緒快取，減少建立銷毀開銷
thread_concurrency = 8 # 僅 Solaris 有效，Linux 忽略read_buffer_size = 2M # 循序掃描緩衝
read_rnd_buffer_size = 16M # 隨機讀取/排序緩衝
sort_buffer_size = 8M # ORDER BY/GROUP BY 排序緩衝
join_buffer_size = 8M # 全表連接緩衝（無索引 JOIN）
key_buffer_size = 32M # MyISAM 索引塊快取（建議 ≤ 實體記憶體 30%）query_cache_size = 64M
query_cache_limit = 2Mdefault-storage-engine = MYISAM # 建議改為 INNODB
innodb_buffer_pool_size = 2G # 核心！專用 DB 建議設實體記憶體 60~80%
innodb_additional_mem_pool_size = 16M
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M # 單檔大小，總量建議 25~100% buffer_pool
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 1 # 1=最安全(ACID) 2=折中 0=最高效但可能丟數據
innodb_lock_wait_timeout = 120
innodb_thread_concurrency = 16 # 0=無限制，建議 2×CPU核心數log-bin = mysql-bin # 啟用二進制日誌（主從複制、時間點恢復必備）
binlog_format = mixed # 混合模式：Statement + Row
slow_query_log # 啟用慢查詢日誌
long_query_time = 2 # 慢查詢閾值(秒)，生產建議 1~5
server-id = 1 # 複制架構唯一 ID適用場景：日 IP 100~200 萬，日 PV 200~500 萬
| 參數 | 預設值 | 優化值 | 說明 |
|---|---|---|---|
| max_connections | 100 | 3000 | 支撐高併發 |
| table_open_cache | 2048 | 4096 | 減少開表開銷 |
| innodb_buffer_pool_size | 2G | 6G | 核心記憶體，設物理 60~70% |
| innodb_flush_log_at_trx_commit | 1 | 2 | 遊戲/日誌類建議 2；金融/核心交易保持 1 |
| innodb_log_file_size | 256M | 512M | 減少 checkpoint 壓力 |
| innodb_log_buffer_size | 8M | 16M | 大事務緩衝 |
| query_cache_size | 64M | 128M | 唯讀多場景有效 |
| key_buffer_size | 32M | 128M | MyISAM 臨時表仍需 |
| open-files-limit | 8192 | 10240 | 配合 table_open_cache |
[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8
[mysqld]
port = 3306
socket = /tmp/mysql.sock
character-set-server = utf8
back_log = 512
max_connections = 3000
max_connect_errors = 30
table_open_cache = 4096
max_allowed_packet = 32M
max_heap_table_size = 128M
read_buffer_size = 8M
read_rnd_buffer_size = 64M
sort_buffer_size = 16M
join_buffer_size = 16M
thread_cache_size = 16
thread_concurrency = 8
thread_stack = 512K
query_cache_size = 128M
query_cache_limit = 4M
ft_min_word_len = 8
default-storage-engine = INNODB
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin = mysql-bin
binlog_cache_size = 4M
binlog_format = mixed
log_warnings
slow_query_log
long_query_time = 10
server-id = 1
key_buffer_size = 128M
bulk_insert_buffer_size = 256M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size = 6G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 32M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 2048M
sort_buffer_size = 2048M
read_buffer = 32M
write_buffer = 32M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 10240SHOW STATUS; -- 狀態變數
SHOW VARIABLES; -- 系統變數
SHOW ENGINES; -- 引擎支援
SHOW ENGINE INNODB STATUS; -- InnoDB 詳細狀態總結：配置優化必須基於實際業務負載、硬體資源、資料特徵動態調整，切勿生搬硬套。建議建立基準測試（sysbench），迭代調優，監控關鍵指標（QPS、延遲、緩存命中率、磁碟 I/O）。
