MySQL 8.0/5.7 生產級 my.cnf 全維度配置詳解（InnoDB 專項優化版）

配置文件結構與讀取順序

/etc/my.cnf                 # 全局
/etc/mysql/my.cnf           # Debian/Ubuntu
/usr/etc/my.cnf             # 編譯安裝默認
~/.my.cnf                   # 用戶級（客戶端工具讀取）
# 後讀取的覆蓋先讀取的，mysqld --help --verbose 可見實際生效順序

完整生產級模板（註釋每項用途與調優邏輯）

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set = utf8mb4

[mysql]
no-auto-rehash            # 禁用 Tab 補全，加快啟動
safe-updates              # 防止無 WHERE 的 UPDATE/DELETE（可選）

[mysqld]
# ========== 基礎標識 ==========
server_id = 1                          # 主從/集群唯一標識，必須 >0
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /data/mysql/data             # 獨立掛載點，SSD/NVMe
tmpdir = /data/mysql/tmp               # 臨時表目錄，獨立磁盤
pid_file = /var/run/mysqld/mysqld.pid
log_error = /data/mysql/logs/error.log
log_error_verbosity = 2                # 1=錯誤, 2=警告, 3=信息

# ========== 字符集與排序規則 ==========
character_set_server = utf8mb4
collation_server = utf8mb4_0900_ai_ci  # 8.0 默認，支持表情符、大小寫不敏感、重音不敏感
init_connect = 'SET NAMES utf8mb4'     # 客戶端連接自動執行

# ========== 連接與線程 ==========
max_connections = 2000                 # 應用峰值連接數 + 余量，配合連接池
max_connect_errors = 100000            # 防止誤封禁，配合 FLUSH HOSTS
back_log = 100                         # TCP 監聽隊列，高併發突發調大
thread_cache_size = 64                 # 緩存線程，減少創建銷毀開銷
thread_stack = 256K                    # 線程棧，複雜存儲過程需調大
thread_handling = one-thread-per-connection  # 專用線程模型（默認）

# ========== 緩存與查詢優化 ==========
table_open_cache = 8000                # 表緩存，≈ open_files_limit / 2
table_definition_cache = 4000          # 表定義緩存，大量表必調大
max_allowed_packet = 64M               # 單包最大，大字段/批量 INSERT 需調大
net_buffer_length = 16K                # 通信緩存初始值
sort_buffer_size = 4M                  # 排序緩存（每線程），慎調大防 OOM
read_buffer_size = 2M                  # 連續掃描緩存（每線程）
read_rnd_buffer_size = 4M              # 隨機讀緩存（每線程）
join_buffer_size = 4M                  # JOIN 緩存（每線程），無索引 JOIN 才用
query_cache_type = OFF                 # 5.7 建議關閉，8.0 已移除
query_cache_size = 0

# ========== 臨時表 ==========
tmp_table_size = 256M                  # 內存臨時表上限，超過轉磁盤
max_heap_table_size = 256M             # MEMORY 表/內部臨時表上限，需配套

# ========== InnoDB 核心（性能靈魂） ==========
default_storage_engine = InnoDB
innodb_file_per_table = ON             # 獨立表空間，支持 DROP 回收空間、單表備份
innodb_data_home_dir = /data/mysql/data
innodb_data_file_path = ibdata1:1G:autoextend  # 系統表空間，預分配 1G
innodb_log_group_home_dir = /data/mysql/logs   # Redo log 獨立磁盤
innodb_undo_directory = /data/mysql/undo     # Undo log 獨立磁盤（8.0+）
innodb_undo_tablespaces = 3              # Undo 表空間數，支持在線截斷

# 緩衝池（核心，物理內存 60-75%）
innodb_buffer_pool_size = 12G          # 例：16G 內存機器
innodb_buffer_pool_instances = 8       # 實例數，減少鎖爭用，建議 1-8GB/實例
innodb_buffer_pool_chunk_size = 256M   # 擴容塊大小
innodb_buffer_pool_load_at_startup = ON    # 啟動預熱
innodb_buffer_pool_dump_at_shutdown = ON   # 關閉存儲熱頁

# Redo Log（寫入性能與恢復時間權衡）
innodb_log_file_size = 1G              # 單文件大小，總量 = size × files
innodb_log_files_in_group = 4          # 文件組數，環狀寫入
innodb_log_buffer_size = 64M           # 事務日志緩存，大事務調大
innodb_flush_log_at_trx_commit = 1     # 1=最安全（雙1），0/2=高性能犧牲 1s 數據
innodb_flush_method = O_DIRECT         # 繞過 OS cache，防止雙重緩存
innodb_flush_neighbors = 0             # SSD 關閉鄰近頁刷盤（HDD 設 1）
innodb_flush_sync = ON                 # 刷盤同步（8.0+）

# 並發與鎖
innodb_thread_concurrency = 0          # 0=自動（建議），手動設 2×(CPU核+磁盤)
innodb_read_io_threads = 8             # 讀 I/O 線程
innodb_write_io_threads = 8            # 寫 I/O 線程
innodb_purge_threads = 4               # Purge 線程（8.0+ 默認 4）
innodb_lock_wait_timeout = 30          # 行鎖等待超時（秒）
innodb_deadlock_detect = ON            # 死鎖檢測（高併發可關閉靠 timeout）
innodb_spin_wait_delay = 6             # 自旋等待延遲（微秒級）

# 適應性哈希索引 / 預讀
innodb_adaptive_hash_index = ON        # 熱頁自動建哈希索引
innodb_adaptive_hash_index_parts = 8   # 分區減少鎖爭用
innodb_random_read_ahead = OFF         # 隨機預讀（通常關閉）
innodb_read_ahead_threshold = 56       # 線性預讀閾值

# 統計信息（優化器依賴）
innodb_stats_persistent = ON           # 持久化統計信息
innodb_stats_auto_recalc = ON          # 自動重算（>10% 變更觸發）
innodb_stats_persistent_sample_pages = 20  # 採樣頁數
innodb_stats_transient_sample_pages = 8    # 臨時統計採樣

# 事務與隔離
transaction_isolation = REPEATABLE-READ  # 默認，MVCC 一致性讀
innodb_support_xa = ON                 # XA 分布式事務（關閉可提升性能）

# 檢查點與恢復
innodb_max_dirty_pages_pct = 90        # 髒頁上限，觸發刷盤
innodb_max_dirty_pages_pct_lwm = 10    # 低水位，預刷盤
innodb_lru_scan_depth = 2048           # LRU 掃描深度，影響刷盤效率
innodb_io_capacity = 2000              # IOPS 能力（SSD 2000-5000，NVMe 10000+）
innodb_io_capacity_max = 4000          # 突發 IOPS 上限

# 監控與調試
innodb_monitor_enable = all            # 開啟所有計數器（SHOW ENGINE INNODB STATUS）
innodb_print_all_deadlocks = ON        # 錯誤日志記錄所有死鎖

# ========== Binlog / 複製 / GTID ==========
log_bin = /data/mysql/logs/binlog
binlog_format = ROW                    # 行級，數據一致性最高
binlog_expire_logs_seconds = 604800    # 7 天自動清理（8.0+）
max_binlog_size = 1G
sync_binlog = 1                        # 1=最安全，配合 innodb_flush_log_at_trx_commit=1 實現「雙1」
binlog_checksum = CRC32                # 校驗和
binlog_row_image = FULL                # 全量行映像（FULL/MINIMAL/NOBLOB）
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON                 # 從庫寫入 binlog（鏈式複製/MGR 必開）
master_info_repository = TABLE         # 主庫信息存表（8.0+ 默認）
relay_log_info_repository = TABLE
relay_log_recovery = ON                # 中繼日志自動恢復

# ========== 慢查詢 / 審計 ==========
slow_query_log = ON
slow_query_log_file = /data/mysql/logs/slow.log
long_query_time = 1                    # 秒，核心業務可設 0.5
log_queries_not_using_indexes = ON     # 記錄無索引查詢（噪音大，定期分析）
log_throttle_queries_not_using_indexes = 60  # 限流（每分鐘最多 60 條）
log_slow_admin_statements = ON         # 記錄管理命令
log_slow_slave_statements = ON         # 記錄從庫慢 SQL

# ========== 安全與加固 ==========
local_infile = OFF                     # 禁用 LOAD DATA LOCAL（防任意文件讀取）
skip_show_database                     # 禁止無權限用戶 SHOW DATABASES
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE
ssl_ca = /etc/mysql/ssl/ca.pem
ssl_cert = /etc/mysql/ssl/server-cert.pem
ssl_key = /etc/mysql/ssl/server-key.pem
require_secure_transport = ON          # 強制 SSL 連接（可選）

# ========== 性能 Schema / 監控 ==========
performance_schema = ON
performance_schema_instrument = '%=ON'  # 全開（生產可精簡）
performance_schema_consumer_events_statements_current = ON
performance_schema_consumer_events_waits_current = ON

[mysqldump]
quick
max_allowed_packet = 64M
default-character-set = utf8mb4
single-transaction
master-data = 2
routines
triggers
events
hex-blob

[mysqladmin]
default-character-set = utf8mb4

[mysqld_safe]
open_files_limit = 65535
log_error = /data/mysql/logs/mysqld_safe.log

關鍵參數調優決策樹

版本差異提醒

• 5.7 → 8.0：query_cache 移除、utf8mb4_0900_ai_ci 默認、caching_sha2_password 默認認證插件、innodb_undo_tablespaces 獨立 Undo、binlog_expire_logs_seconds 替代 expire_logs_days、performance_schema 默認 ON、information_schema 視圖化（無 .frm）。
• 參數重命名：log_warnings → log_error_verbosity、tx_isolation → transaction_isolation。

部署前檢查清單

1. 目錄存在且屬主 mysql:mysql：datadir, log_bin dir, slow_log dir, tmpdir, undo dir。
2. ulimit -n 65535、fs.file-max、systemd LimitNOFILE 已生效。
3. 關閉 Swap 或 vm.swappiness=1、transparent_hugepage=never。
4. 防火牆放行 3306、4433 (MGR)、33061 (X Plugin)。
5. SELinux/AppArmor 策略允許 MySQL 讀寫數據目錄。
6. 備份腳本、監控采集（Prometheus mysqld_exporter/PMM）、告警規則已就緒。