my.cnf 关键参数配置详解
什么是 my.cnf
my.cnf 是 MySQL 的配置文件，位于 /etc/my.cnf、/etc/mysql/my.cnf 或 ~/.my.cnf（按优先级从低到高）。MySQL 启动时读取这些配置，确定内存分配、性能参数、安全设置等。
配置文件结构
[client] # 所有客户端连接
[mysql] # mysql 命令行工具
[mysqld] # MySQL 服务器端（最核心的部分）
[mysqldump] # mysqldump 工具专用
最基本的配置骨架：
[mysqld]
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /var/lib/mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
核心参数分类
1. InnoDB 相关参数
# InnoDB 缓冲池大小（最重要的参数）
innodb_buffer_pool_size = 4G # 通常设为物理内存的 60-80%
# InnoDB 日志文件大小
innodb_log_file_size = 1G # 影响写入性能
# InnoDB 日志缓冲区
innodb_log_buffer_size = 16M # 对于大事务需要调大
# InnoDB 刷新行为
innodb_flush_log_at_trx_commit = 2 # 0/1/2，1 最安全但最慢
# 数据文件管理
innodb_data_file_path = ibdata1:12M:autoextend
innodb_file_per_table = 1 # 每个表独立表空间
# IO 线程数
innodb_read_io_threads = 4
innodb_write_io_threads = 4
2. 连接管理参数
# 最大连接数
max_connections = 500 # 默认 151，需根据实际情况调
# 连接等待时间
wait_timeout = 600 # 非交互式连接超时（秒）
interactive_timeout = 28800 # 交互式连接超时
# 最大连接错误数
max_connect_errors = 10000 # 防止 IP 被锁
# 连接缓存
thread_cache_size = 100 # 减少线程创建开销
3. 查询缓存（MySQL 8.0 已移除）
# MySQL 5.7 及以下
query_cache_type = 0 # 建议禁用
query_cache_size = 0
MySQL 8.0 完全移除了查询缓存，不需要配置。
4. 临时表与文件
# 临时表大小
tmp_table_size = 64M
max_heap_table_size = 64M
# 排序缓冲区
sort_buffer_size = 2M # 每条连接独占，不可过大
join_buffer_size = 2M # 每条连接独占
# 临时文件目录
tmpdir = /tmp # 确保磁盘空间充足
5. 日志参数
# 二进制日志
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
expire_logs_days = 7 # 8.0 使用 binlog_expire_logs_seconds
# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2 # 超过 2 秒的记录
log_queries_not_using_indexes = 1
# 错误日志
log_error = /var/log/mysql/error.log
6. 字符集与排序规则
# MySQL 8.0 推荐配置
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
# MySQL 5.7 兼容配置
[client]
default-character-set = utf8mb4
7. 安全参数
# 只允许本地 root 操作
skip_networking # 或 bind-address = 127.0.0.1
# 禁止 local-infile
local-infile = 0
# 只开启必要的存储引擎
disabled_storage_engines = "MyISAM,ARCHIVE"
# SQL 模式
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ENGINE_SUBSTITUTION
根据服务器配置推荐
小服务器（1-2GB 内存）
innodb_buffer_pool_size = 512M
max_connections = 100
innodb_log_file_size = 128M
tmp_table_size = 16M
sort_buffer_size = 1M
中等服务器（4-8GB 内存）
innodb_buffer_pool_size = 2G
max_connections = 300
innodb_log_file_size = 512M
tmp_table_size = 32M
sort_buffer_size = 2M
大型服务器（16-32GB 内存）
innodb_buffer_pool_size = 12G
max_connections = 500
innodb_log_file_size = 1G
tmp_table_size = 64M
sort_buffer_size = 4M
innodb_buffer_pool_instances = 8
内存型服务器（64GB+）
innodb_buffer_pool_size = 48G
innodb_buffer_pool_instances = 16
max_connections = 1000
innodb_log_file_size = 2G
tmp_table_size = 128M
必须避免的坑
1. 缓冲池设得太大
当 innodb_buffer_pool_size 超过物理内存时，操作系统会使用 SWAP，性能雪崩。经验值：不超过物理内存的 80%。
2. 排序缓冲区设得太大
sort_buffer_size 是每连接独占的。如果有 500 个连接，每个 2M 就是 1GB，每个 256M 就是 128GB。不要盲目改大。
3. 修改后不重启
修改 my.cnf 后必须重启 MySQL 才生效。但部分参数可以动态修改：
SET GLOBAL max_connections = 500;
动态修改的参数重启后丢失，需要同时更新 my.cnf。
4. 多实例混用配置
如果同一台机器跑多个 MySQL 实例，注意各自的端口、socket、datadir 不能冲突。
常用诊断命令
# 查看当前配置
mysql -e "SHOW VARIABLES;"
mysql -e "SHOW GLOBAL VARIABLES LIKE 'innodb_buffer%';"
# 查看当前运行状态
mysql -e "SHOW GLOBAL STATUS;"
# 查看 InnoDB 引擎状态
mysql -e "SHOW ENGINE INNODB STATUS\\G"
# 验证配置是否生效
mysqladmin variables
# 查看 my.cnf 读取顺序
mysql --help | grep "Default options"
面试常问题
Q：max_connections 设置太大有什么风险？
A：每个连接占用内存（排序缓冲区、线程栈等），连接太多会导致内存耗尽，操作系统开始 SWAP。同时，过多连接导致上下文切换频繁，反而降低吞吐。
Q：修改 my.cnf 后是否需要重启？
A：需要重启。但部分参数（如 max_connections、innodb_buffer_pool_size）在 MySQL 5.7+ 支持动态修改，使用 SET GLOBAL 可以即生效。不过 my.cnf 中的改动不会同步到运行时，重启后才会读取。
Q：生产环境里 my.cnf 应该怎么管理？
A：建议用配置管理工具（Ansible/Puppet）统一管理，通过 CI/CD 部署。写个测试脚本验证配置加载后的参数值是否符合预期。日志配置文件的变更记录。
暂无评论内容