一、基礎配置
[mysqld]
# 聲明以下配置屬于MySQL服務器(mysqld)
[mysqld]:配置文件的模塊標識,表示這是 MySQL 服務器的配置段。
二、路徑與基礎設置
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
pid-file=/var/run/mysqld/mysqld.pid
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
datadir:數據庫文件存儲路徑(表數據、索引等)。
socket:Unix 套接字文件路徑(本地客戶端通過此文件連接 MySQL)。
pid-file:MySQL 進程 ID 文件路徑(用于管理 MySQL 服務)。
character-set-server:服務器默認字符集(utf8mb4支持所有 Unicode 字符,包括 emoji)。
collation-server:字符集排序規則(utf8mb4_unicode_ci表示不區分大小寫)。
三、內存配置(最核心)
innodb_buffer_pool_size=5G
innodb_log_buffer_size=64M
key_buffer_size=64M
innodb_buffer_pool_size:InnoDB 存儲引擎的緩存池大小(緩存數據和索引)。
建議:設為服務器內存的 50%-70%(示例中 8GB 內存分配 5GB)。值越大,磁盤 IO 越少,性能越好。
innodb_log_buffer_size:InnoDB 日志緩沖區大小(臨時存儲待寫入磁盤的事務日志)。
建議:寫操作頻繁時調大(如 64M-128M),減少磁盤 IO。
key_buffer_size:MyISAM 引擎的索引緩存(InnoDB 用戶可設小值)。
建議:若全用 InnoDB,設為 32M-64M(MySQL 5.7 + 默認 InnoDB,MyISAM 已淘汰)。
四、連接管理
max_connections=500
wait_timeout=300
interactive_timeout=300
max_user_connections=100
max_connections:允許的最大并發連接數。
建議:根據業務峰值調整(如電商秒殺場景可設 1000)。
注意:每個連接約占 1-4MB 內存,避免設置過大導致內存溢出。
wait_timeout/interactive_timeout:非交互式 / 交互式連接的空閑超時時間(秒)。
建議:設為 300 秒(5 分鐘),避免空閑連接長期占用資源。
max_user_connections:單個用戶的最大連接數。
建議:設為 100-200,防止單個用戶耗盡連接資源。
五、InnoDB 引擎優化(重點)
innodb_flush_log_at_trx_commit=1
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_io_capacity=2000
innodb_read_io_threads=4
innodb_write_io_threads=4
innodb_flush_log_at_trx_commit:事務日志刷新策略(影響安全性與性能)。
1(默認):每次事務提交立即刷盤(最安全,性能略低);
2:提交后寫入操作系統緩存,每秒刷盤(性能好,斷電可能丟 1 秒數據);
0:每秒刷盤(性能最好,風險最高)。
建議:生產環境優先選1,非核心場景可選2。
innodb_file_per_table:是否為每個表創建獨立的.ibd文件。
建議:設為1(開啟),方便單獨管理表空間(如刪除表釋放磁盤空間)。
innodb_flush_method:日志 / 數據文件的刷盤方式。
建議:Linux 設為O_DIRECT(避免雙重緩存),Windows 忽略此參數。
innodb_io_capacity:InnoDB 的 IO 吞吐量上限(影響后臺刷盤效率)。
建議:機械硬盤設 1000,SSD 設 2000-20000(根據實際性能調整)。
innodb_read/write_io_threads:讀寫 IO 線程數(提升并發 IO 能力)。
建議:根據 CPU 核心數調整(如 4-8 核設為 4-8),默認值為 4。
六、查詢優化
slow_query_log=1
long_query_time=1
slow_query_log_file=/var/log/mysql/slow.log
join_buffer_size=512K
sort_buffer_size=2M
slow_query_log:是否開啟慢查詢日志(記錄執行時間超過閾值的 SQL)。
建議:必須開啟(設為1),用于定位需要優化的 SQL。
long_query_time:慢查詢閾值(秒)。
建議:設為 1 秒(敏感業務可設 0.5 秒)。
slow_query_log_file:慢查詢日志存儲路徑。
建議:確保路徑存在且 MySQL 有寫入權限。
join_buffer_size/sort_buffer_size:表連接(JOIN)和排序(ORDER BY)的緩存大小。
建議:不宜過大(每個連接獨立分配),避免內存溢出。
七、其他重要參數
max_allowed_packet=128M
table_open_cache=2048
thread_cache_size=32
log_error=/var/log/mysql/error.log
max_allowed_packet:單個 SQL 語句 / 數據包的最大大小。
建議:若有大字段(如 TEXT/BLOB)或批量插入,設為 128M-1G(避免 “Packet too large” 錯誤)。
table_open_cache:表緩存(緩存打開的表文件描述符)。
建議:根據數據庫表數量調整(如 1000 張表設 2048),減少重復打開表的開銷。
thread_cache_size:線程緩存(復用空閑線程,減少創建 / 銷毀開銷)。
建議:并發高時調大(如 32-64),可通過Threads_created狀態值判斷是否足夠。
log_error:錯誤日志路徑。
建議:必須開啟,用于排查 MySQL 啟動 / 運行錯誤(如崩潰、權限問題)。
八、配置驗證命令
修改配置后,可通過以下命令驗證參數是否生效:
-- 查看所有配置(模糊搜索)
SHOW VARIABLES LIKE '參數名';-- 示例:查看innodb_buffer_pool_size
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';-- 查看MySQL狀態(如連接數、緩存命中率)
SHOW STATUS;
九、注意事項
重啟生效:修改配置文件后需重啟 MySQL 服務(systemctl restart mysqld)。
動態調整:部分參數支持在線修改(無需重啟),例如:
SET GLOBAL max_connections=500;(僅臨時生效,重啟后恢復配置文件值)。
監控與迭代:定期分析慢查詢日志(用pt-query-digest工具),優化高頻低效 SQL。
通過理解每個參數的作用,你可以根據服務器硬件和業務特點靈活調整配置,實現 MySQL 性能的最大化。