配置檔結構與讀取順序
# MySQL 讀取順序（後者覆蓋前者）
/etc/my.cnf # 全域
/etc/mysql/my.cnf # Debian/Ubuntu 習慣
/usr/local/mysql/etc/my.cnf # 編譯安裝預設
~/.my.cnf # 使用者級
# 命令列 --defaults-file=... 最高優先級
核心區段對照
| 區段 | 用途 | 典型服務 |
|---|
| [mysqld] | Server 端核心參數 | mysqld, mysqld_safe |
| [mysqld_safe] | 啟動腳本參數 | mysqld_safe |
| [client] | 客戶端預設連線參數 | mysql, mysqldump, mysqladmin |
| [mysql] | mysql 命令列專用 | mysql |
| [mysqldump] | mysqldump 專用 | mysqldump |
| [myisamchk]/[mysqlchk] | 維護工具參數 | myisamchk, mysqlchk |
InnoDB 核心參數深度解析（生產必調）
| 參數 | 建議值 | 作用與調優邏輯 |
|---|
| innodb_buffer_pool_size | 物理記憶體 60-80% | 核心快取：資料頁、索引頁、鎖、自適應雜湊、鎖資訊。越大命中率越高，避免磁碟 IO。32位系統受 2-3.5GB 限制。 |
| innodb_log_file_size | 1-4 GB | Redo Log 單檔大小。大 = 檢查點頻率低、恢復時間長；小 = 頻繁檢查點、IO 壓力大。總大小 = log_file_size × log_files_in_group (建議 2-3)。 |
| innodb_flush_log_at_trx_commit | 1 (最安全) / 2 (高效能) | 1 = 每次 commit fsync，零資料遺失；2 = commit 寫 OS cache，每秒 fsync，掉電丟 1 秒資料。生產核心業務建議 1。 |
| innodb_file_per_table | ON | 獨立表空間 .ibd，支援 Transportable Tablespace、單表備份、避免 ibdata1 膨脹無法縮減。 |
| innodb_thread_concurrency | 0 (無限制) 或 2×CPU核心 | InnoDB 內部並發控制。0 交給 OS 調度，現代版本建議 0。 |
| innodb_read_io_threads / write_io_threads | 4-16 (視 CPU 核心) | 背景 IO 執行緒，SSD 可調大提高預讀/刷新並行度。 |
| innodb_io_capacity | SSD: 2000-5000 / HDD: 200-500 | 告知 InnoDB 底層 IO 能力，影響刷新頁面、合併插入緩衝、清除速率。 |
| innodb_max_dirty_pages_pct | 75-90 | 髒頁比例上限，觸發積極刷新。避免檢查點風暴。 |
| innodb_flush_method | O_DIRECT | 繞過 OS cache，避免雙重快取浪費記憶體。需檔案系統支援。 |
| innodb_lock_wait_timeout | 50-100 | 鎖等待超時，避免長事務阻塞大面積連線。 |
複寫與雙寫安全參數
[mysqld]
# Master
log_bin = mysql-bin
binlog_format = ROW
sync_binlog = 1
binlog_expire_logs_seconds = 604800 # 7 天
gtid_mode = ON
enforce_gtid_consistency = ON
# Slave
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 1
skip_slave_start = 1
relay_log_recovery = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE
連線、快取、暫存表參數
| 參數 | 建議 | 說明 |
|---|
| max_connections | 2000-5000 | 配合 thread_cache_size、open_files_limit、應用連線池大小 |
| thread_cache_size | 100-200 | 減少建立/銷毀執行緒開銷 |
| table_open_cache | 4000-10000 | 避免 "Table cache full"，配合 open_files_limit |
| query_cache_type / query_cache_size | 0 / 0 | 5.7+ 已移除；高併發鎖競爭劇烈，改用 Redis |
| tmp_table_size / max_heap_table_size | 64M-256M | 記憶體暫存表上限，超過轉 MyISAM 落盤，導致效能崩塌 |
| sort_buffer_size / join_buffer_size | 2M-8M | 每連線分配，設太大易 OOM；複雜排序/Join 可臨時 SET SESSION 調大 |
| binlog_cache_size | 1M-4M | 事務期間 Binlog 記憶體快取，大事務可調大 |
MyISAM 專屬參數（若仍有 MyISAM 表）
key_buffer_size = 256M # 索引塊快取，MyISAM 核心
read_buffer_size = 2M # 全表掃描緩衝
read_rnd_buffer_size = 16M # 排序後隨機讀取緩衝
bulk_insert_buffer_size = 64M # 批次插入優化
myisam_sort_buffer_size = 128M # 索引重建排序緩衝
myisam_max_sort_file_size = 10G # 重建索引最大暫存檔
myisam_repair_threads = 1 # 並行修復執行緒 (多核可調大)
慢查詢與審計日誌
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5 # 秒，建議 0.5-1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
# 錯誤日誌
log_error = /var/log/mysql/error.log
# 一般查詢日誌（除錯時開啟，生產關閉）
general_log = 0
general_log_file = /var/log/mysql/general.log
客戶端預設區段（避免每次輸入參數）
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set = utf8mb4
[mysql]
no-auto-rehash # 關閉自動補全，啟動更快
safe-updates # 強制 WHERE 條件 UPDATE/DELETE
[mysqldump]
quick # 不緩存結果直接寫檔，大表必加
max_allowed_packet = 64M
驗證配置生效
# 檢查語法
mysqld --help --verbose 2>&1 | head -20
# 或
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
# 運行時動態調整（部分需重啟）
SET GLOBAL innodb_buffer_pool_size = 4294967296; # 5.7+ 支援線上調整
配置檔不是複製貼上即可，需依據硬體、負載特徵、業務 SLA 反覆壓測調整。建立「配置版本控制 → 變更審核 → 灰度發布 → 效能基準線比對」流程，讓每次調優可追溯、可回滾、可量化。
