以下為 **生產環境高併發實例 (16C/32G/SSD)** 參考模板，**需根據實際硬體/業務調整**，切勿直接照搬。
[client]
port = 3306
socket = /data/mysql/mysql.sock
default-character-set = utf8mb4
[mysql]
no-auto-rehash # 禁用自動補全，啟動更快
# prompt = "mysql [\d]> " # 自定義提示符basedir = /usr/local/mysql
datadir = /data/mysql
tmpdir = /data/mysql/tmp
pid-file = /data/mysql/mysql.pid
socket = /data/mysql/mysql.sock
port = 3306
server-id = 1001 # 主從唯一
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
default_storage_engine = InnoDB
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONmax_connections = 3000 # 最大連接數
max_user_connections = 500 # 單用戶限制
thread_cache_size = 100 # 線程緩存 (1G→8, 2G→16, 4G→32, >4G→64-100)
back_log = 512 # 監聽隊列 (受限於 OS somaxconn)
skip_name_resolve = ON # 禁用 DNS 反解，加速連接
# skip_networking = ON # 僅本機 Unix Socket (極高安全/性能)
interactive_timeout = 7200 # 交互式超時
wait_timeout = 7200 # 非交互超時 (連接池需 < 此值)# query_cache_type = 0 # 5.7 建議關閉
# query_cache_size = 0
# query_cache_limit = 1M
# 8.0 完全移除，改用 Redis/應用級緩存sort_buffer_size = 4M # 排序緩存 (連接級，慎設過大)
read_buffer_size = 2M # 順序掃描緩存
read_rnd_buffer_size = 4M # 隨機讀緩存
join_buffer_size = 4M # Join 緩存 (無索引 Join 時)
# 以上 4 個參數 **連接級分配**，最大併發 × 大小 = 總內存風險，建議 2-8Mtable_open_cache = 4000 # 表緩存 (配合 open_files_limit)
table_definition_cache = 2000 # 表定義緩存 (5.6+)
open_files_limit = 65535 # 文件描述符上限 (系統 ulimit -n 需同步)innodb_buffer_pool_size = 20G # **核心**：專用機 60-80% 內存 (32G機→20-24G)
innodb_buffer_pool_instances = 16 # 實例數 (1G/實例，減少鎖競爭)
innodb_file_per_table = ON # 獨立表空間 (必開)
innodb_flush_method = O_DIRECT # 繞過 OS Cache，直寫磁碟 (SSD/NVMe 必開)
innodb_log_file_size = 2G # Redo Log 單文件 (總 4G，大事務/高寫入需大)
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M # Log Buffer
innodb_flush_log_at_trx_commit = 1 # **1=最安全** (每提交刷盤), 2=每秒刷盤, 0=OS刷盤
innodb_doublewrite = ON # 雙寫緩衝 (防頁損壞，NVMe 可考慮 OFF 提速)
innodb_read_io_threads = 8 # 讀線程
innodb_write_io_threads = 8 # 寫線程
innodb_io_capacity = 2000 # IOPS 能力 (SSD 5000+, NVMe 20000+)
innodb_io_capacity_max = 4000 # 最大 IOPS
innodb_page_cleaners = 4 # 頁清理線程 (5.7+)
innodb_adaptive_hash_index = ON # 自適應哈希索引 (熱點頁加速)
innodb_lock_wait_timeout = 50 # 鎖等待超時
innodb_deadlock_detect = ON # 死鎖檢測 (高併發可 OFF 改應用層重試)log_bin = mysql-bin
binlog_format = ROW # ROW 格式 (主從一致性最佳)
binlog_expire_logs_seconds = 604800 # 7 天自動清理 (8.0+)
max_binlog_size = 1G
binlog_cache_size = 4M
max_binlog_cache_size = 2G
sync_binlog = 1 # 1=每提交同步 (最安全), 0=OS決定, N=每N次同步
gtid_mode = ON # GTID 模式 (主從切換簡單)
enforce_gtid_consistency = ONslow_query_log = ON
slow_query_log_file = /data/mysql/log/slow.log
long_query_time = 1 # >1s 記錄
log_queries_not_using_indexes = ON # 無索引查詢也記
log_slow_admin_statements = ON # 管理語句也記
log_throttle_queries_not_using_indexes = 100 # 限流 (5.7+)key_buffer_size = 128M # 僅 MyISAM 索引緩存
myisam_sort_buffer_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
delay_key_write = ON # 延遲鍵寫入 (風險：崩潰索引損壞)[mysqldump]
quick
max_allowed_packet = 256M
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 65535
log-error = /data/mysql/log/error.log「Buffer Pool 最大、Redo Log 大、Binlog ROW、Flush=1、O_DIRECT、獨立表空間、慢查詢開、連接池配對。」
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads'; -- 物理讀次數，應極低
