配置檔結構與讀取順序
MySQL 啟動時按順序讀取:
- /etc/my.cnf(全域)
- /etc/mysql/my.cnf(Debian/Ubuntu)
- ~/.my.cnf(使用者)
- --defaults-extra-file 指定檔案
後讀取的覆蓋先讀取的同名選項。區塊以 [section] 區分,常見區塊:[mysqld]、[client]、[mysqldump]、[mysql]。
核心配置分類速查
📁 目錄與檔案
| 選項 | 說明 | 典型值 |
basedir | 安裝根目錄 | /usr/local/mysql |
datadir | 資料檔案目錄 | /var/lib/mysql |
tmpdir | 臨時檔案目錄 | /tmp |
pid-file | 進程 ID 檔案 | /var/run/mysqld/mysqld.pid |
socket | Unix Socket 檔案 | /var/run/mysqld/mysqld.sock |
character-sets-dir | 字符集檔案目錄 | /usr/share/mysql/charsets |
lower_case_table_names | 表名大小寫敏感度 (0/1/2) | Linux=0, Win=1 |
🌐 語言與字符集
| 選項 | 說明 | 建議值 |
character-set-server | 預設字符集 | utf8mb4 |
collation-server | 預設排序規則 | utf8mb4_unicode_ci |
language | 錯誤訊息語言 | english |
🔗 通訊、網路與安全
| 選項 | 說明 | 建議值 |
port | TCP 連接埠 | 3306 |
bind-address | 綁定 IP(0.0.0.0 監聽所有) | 127.0.0.1 (內網) / 具體 IP |
skip-networking | 禁用 TCP/IP,僅 Socket | 本機專用時啟用 |
skip-name-resolve | 不解析主機名,僅用 IP,加速連線 | 啟用 |
skip-host-cache | 禁用主機名快取 | 視情況 |
max_connections | 最大連線數 | 500-2000 (視記憶體) |
max_connect_errors | 連線錯誤閾值,超過封鎖主機 | 100000 |
wait_timeout | 非互動連線逾時(秒) | 600 |
interactive_timeout | 互動連線逾時(秒) | 600 |
ssl-ca/ssl-cert/ssl-key | SSL 憑證路徑 | 啟用 SSL 時設定 |
require_secure_transport | 強制加密連線 | ON (8.0+) |
🧠 記憶體、緩衝區與快取
| 選項 | 說明 | 建議值 |
key_buffer_size | MyISAM 索引緩衝區 | 實體記憶體 25-30% |
innodb_buffer_pool_size | InnoDB 核心緩衝池(最關鍵) | 實體記憶體 60-80% |
innodb_buffer_pool_instances | 緩衝池實例數,減少鎖競爭 | 1-8 (每實例 >1GB) |
innodb_log_file_size | Redo log 大小 | 256M-2G |
innodb_log_buffer_size | Log 寫入緩衝 | 16M-64M |
innodb_flush_log_at_trx_commit | 事務提交刷盤策略 (1=最安全, 2/0=高效能) | 1 (生產) / 2 (可接受秒級丟失) |
query_cache_type | 查詢快取模式 (0=關, 1=開, 2=按需) | 0 (5.7+ 已移除) |
query_cache_size | 查詢快取大小 | 0 (不建議開啟) |
table_open_cache | 資料表快取數量 | 2000-10000 |
table_definition_cache | 表定義快取 | 1400+ |
thread_cache_size | 執行緒快取 | 100-500 |
sort_buffer_size | 排序緩衝 (每連線) | 2M-8M |
read_buffer_size | 順序讀取緩衝 (每連線) | 128K-2M |
read_rnd_buffer_size | 隨機讀取緩衝 (每連線) | 256K-8M |
join_buffer_size | JOIN 緩衝 (每連線) | 256K-2M |
tmp_table_size | 記憶體臨時表大小上限 | 64M-256M |
max_heap_table_size | MEMORY 表大小上限 | 同 tmp_table_size |
bulk_insert_buffer_size | 批次插入緩衝 (MyISAM) | 8M-32M |
📝 日誌系統
| 選項 | 說明 | 建議 |
log_error | 錯誤日誌路徑 | /var/log/mysql/error.log |
log_bin | 啟用二進制日誌 (主從/備份必須) | mysql-bin |
binlog_format | 日誌格式:ROW/STATEMENT/MIXED | ROW (最安全,主從一致) |
expire_logs_days | Binlog 保留天數 | 7-30 |
max_binlog_size | 單個 Binlog 最大值 | 100M-1G |
sync_binlog | 每 N 次提交同步磁碟 (1=最安全) | 1 (生產) |
slow_query_log | 啟用慢查詢日誌 | ON |
slow_query_log_file | 慢查詢日誌路徑 | /var/log/mysql/slow.log |
long_query_time | 慢查詢閾值(秒) | 1-2 |
log_queries_not_using_indexes | 記錄未用索引查詢 | ON (配合慢查詢) |
log_slow_admin_statements | 記錄管理命令慢查詢 | ON |
🔄 主從複製
| 選項 | 角色 | 說明 |
server-id | 主/從 | 唯一正整數 (1-2^32-1) |
log_bin | 主 | 啟用二進制日誌 |
binlog_do_db | 主 | 只記錄指定庫 |
binlog_ignore_db | 主 | 忽略指定庫 |
log_slave_updates | 從 | 從庫記錄重放的 SQL,支援鏈式複製 |
master-host/user/password/port | 從 | 連線主庫參數 (5.6+ 建議用 CHANGE MASTER TO) |
read_only | 從 | 1=只讀,防誤寫入 |
relay_log | 從 | 中繼日誌檔案前綴 |
relay_log_purge | 從 | 自動清理已套用中繼日誌 |
replicate_do/ignore_db/table | 從 | 複製過濾規則 |
🛡️ InnoDB 核心參數 (5.0+ 關鍵)
innodb_file_per_table=ON:獨立表空間,支援縮表、可攜式表空間
innodb_flush_method=O_DIRECT:避免雙重緩衝,直接 I/O
innodb_lock_wait_timeout=50:鎖等待逾時(秒)
innodb_deadlock_detect=ON:死鎖偵測
innodb_strict_mode=ON:嚴格模式,錯誤而非警告
配置驗證與熱修改
# 語法檢查
mysqld --help --verbose 2>&1 | head -20
# 查看當前運行值
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL VARIABLES LIKE 'max_connections';
# 部分參數支援動態修改 (無需重啟)
SET GLOBAL max_connections = 1000;
SET GLOBAL slow_query_log = ON;
# 持久化至配置檔 (8.0+)
SET PERSIST max_connections = 1000;
版本演進提醒
- MySQL 5.7:移除 Query Cache、預設
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES...
- MySQL 8.0:
SET PERSIST 持久化配置、資料字典原子化、角色權限、CTE/窗口函數
- MariaDB 10.x:相容 MySQL 但參數有差異,請參考對應文件