起源
MySQL 通过配置文件控制服务器和客户端的运行参数。配置文件决定连接数上限、内存缓冲、日志、复制、安全策略等行为,是数据库稳定运行与性能调优的基础。Linux 下配置文件名称为 my.cnf,Windows 下为 my.ini。
原理
一、配置文件加载顺序
按优先级从高到低,后加载覆盖前加载:
- 命令行启动参数(最高优先级)
--defaults-extra-file指定文件/etc/mysql/my.cnf/etc/my.cnf/usr/local/mysql/my.cnf~/.my.cnf用户私有配置- 编译内置默认参数(最低优先级)
二、配置文件结构分组
| 分组 | 用途 |
|---|---|
| [mysqld] | 服务端核心配置 |
| [mysqld_safe] | 安全启动参数 |
| [mysql] | mysql 客户端命令行配置 |
| [mysqldump] | 备份工具配置 |
| [client] | 所有客户端通用配置 |
| [mysqladmin] | 管理工具配置 |
三、[mysqld] 参数分区设计
[mysqld] 分区按作用划分逻辑,各分区对应不同的优化目标:
- 基础配置:路径、账号、字符集、时区
- 连接会话:并发上限、超时、域名解析
- 内存缓冲:InnoDB 引擎性能核心
- 磁盘 IO 与事务:持久化安全级别
- Binlog 日志:主从复制与数据恢复基础
- 慢查询日志:SQL 性能排查
- 表缓存与文件句柄:吞吐能力
- 主从复制参数:从库运行逻辑
- 安全配置:SQL 模式与安全限制
实现
一、核心服务端配置
- 基础通用配置
user=mysql
port=3306
socket=/tmp/mysql.sock
pid-file=/var/lib/mysql/mysql.pid
basedir=/usr/local/mysql
datadir=/var/lib/mysql
tmpdir=/tmp
default-time_zone='+8:00'
# 字符集统一 utf8mb4
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
# 错误日志
log-error=/var/log/mysql/error.log
- 连接与会话配置
max_connections=1000 # 最大并发连接数
max_connect_errors=1000 # 连接失败阈值,超限锁 IP
wait_timeout=28800 # 空闲连接超时 8 小时
interactive_timeout=28800
connect_timeout=10 # 建立连接超时秒数
back_log=500 # TCP 等待队列长度
skip-name-resolve # 关闭域名解析,提升连接速度
- InnoDB 内存缓冲
innodb_buffer_pool_size=4G # 核心缓冲,占物理内存 50%-70%
innodb_log_buffer_size=64M
innodb_buffer_pool_instances=4 # 缓冲池分片,每片大于等于 1G
innodb_file_per_table=1 # 每张表独立 ibd 文件
innodb_flush_log_at_trx_commit=1 # 1 强事务安全;2 性能提升但可能丢少量数据
innodb_log_file_size=1G # 重做日志大小,单文件 1-4G
innodb_log_files_in_group=2
innodb_flush_method=O_DIRECT
innodb_read_io_threads=8
innodb_write_io_threads=8
innodb_autoinc_lock_mode=2 # 并发自增优化
innodb_sort_buffer_size=2M
- MyISAM 引擎配置
key_buffer_size=512M # MyISAM 索引缓存
sort_buffer_size=2M # 单会话排序缓冲区
read_buffer_size=1M
read_rnd_buffer_size=2M
join_buffer_size=2M
tmp_table_size=64M
max_heap_table_size=64M # 内存临时表上限
- 磁盘 IO 与事务
sync_binlog=1 # binlog 同步策略,1 最安全
transaction-isolation=READ-COMMITTED # 事务隔离级别
- Binlog 二进制日志
log-bin=/var/lib/mysql/binlog
binlog_format=ROW # ROW 行级,数据恢复最可靠
expire_logs_days=7 # 自动清理 7 天前 binlog
sync_binlog=1
binlog_cache_size=4M
max_binlog_size=1G
# 主从过滤(可选)
# binlog-do-db=test
# binlog-ignore-db=mysql
- 慢查询日志
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=1 # 超过 1 秒记录慢 SQL
log_queries_not_using_indexes=1 # 记录无索引 SQL
log_throttle_queries_not_using_indexes=100
- 临时文件、表、文件句柄
open_files_limit=65535
table_open_cache=20000
table_definition_cache=10000
tmp_table_size=64M
max_heap_table_size=64M
- 主从复制参数(从库)
server-id=2 # 集群唯一 ID,主库不能为 0
relay-log=/var/lib/mysql/relay-bin
relay-log-recovery=1
read_only=1 # 普通从库只读,super 权限可写
super_read_only=1
replicate-wild-ignore-table=mysql.% # 忽略 mysql 系统库同步
- 安全相关配置
skip-grant-tables=0 # 0 关闭免密登录,生产禁用
local-infile=0 # 禁止 load local file,防注入
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sql_mode 开启严格模式,拒绝非法数据插入。
二、客户端配置
[client]
port=3306
socket=/tmp/mysql.sock
default-character-set=utf8mb4
[mysql]
no-auto-rehash # 关闭自动补全加速启动
prompt="\\u@\\h:\\d> " # 自定义命令行提示符
三、mysqldump 备份配置
[mysqldump]
quick
max_allowed_packet=1G
single-transaction=1 # innodb 热备不加锁
四、查看当前配置
- 查看所有全局配置
SHOW VARIABLES;
SHOW VARIABLES LIKE '%innodb_buffer%'; -- 模糊查询
- 查看运行状态
SHOW STATUS;
SHOW ENGINE INNODB STATUS; -- innodb 详细运行状态
- 动态修改参数(临时生效,重启失效)
SET GLOBAL max_connections=2000;
动态修改重启失效,永久生效必须修改 my.cnf。
- 查看配置文件加载路径
mysqld --verbose --help | grep -A 1 "Default options"
五、综合生产模板
适配 8C16G 标准业务服务器,内存、连接、日志均衡配置,全量开启慢日志、binlog、事务安全,适合主从架构:
# 客户端通用
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set = utf8mb4
# mysql 命令行客户端
[mysql]
no-auto-rehash
prompt = "\\u@\\h:\\d \\r:\\m:\\s> "
default-character-set = utf8mb4
# 备份工具
[mysqldump]
quick
max_allowed_packet = 1G
single-transaction = 1
routines
triggers
# 服务端核心
[mysqld]
user = mysql
port = 3306
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid
basedir = /usr/local/mysql
datadir = /var/lib/mysql
tmpdir = /tmp
default_time_zone = '+8:00'
# 字符集统一
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
# 错误日志
log-error = /var/log/mysql/mysql-error.log
log_timestamps = SYSTEM
# 连接相关
max_connections = 1500
max_connect_errors = 10000
skip-name-resolve
back_log = 600
connect_timeout = 10
wait_timeout = 28800
interactive_timeout = 28800
# 文件句柄与表缓存
open_files_limit = 65535
table_open_cache = 20000
table_definition_cache = 10000
# 单会话缓冲区(不可过大,并发会占用大量内存)
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
tmp_table_size = 64M
max_heap_table_size = 64M
# MyISAM 兼容旧表
key_buffer_size = 256M
# InnoDB 核心缓冲(8C16G)
innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 2000
innodb_autoinc_lock_mode = 2
innodb_sort_buffer_size = 2M
transaction_isolation = READ-COMMITTED
# Binlog 二进制日志
log_bin = /var/lib/mysql/binlog/bin
binlog_format = ROW
max_binlog_size = 1G
binlog_cache_size = 4M
expire_logs_days = 7
binlog_rows_query_log_events = 1
# 主从 ID(主库 server-id=1,从库改为唯一数字)
server-id = 1
# 从库专属参数(主库注释掉,从库开启)
# relay-log = /var/lib/mysql/relay-bin
# relay_log_recovery = 1
# read_only = 1
# super_read_only = 1
# replicate_wild_ignore_table = mysql.%
# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 100
min_examined_row_limit = 1000
# SQL 严格模式与安全限制
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
local-infile = 0
# mysqld_safe 启动参数
[mysqld_safe]
log-error = /var/log/mysql/mysql-error.log
pid-file = /var/lib/mysql/mysql.pid
配套目录创建命令(CentOS):
mkdir -p /var/log/mysql /var/lib/mysql/binlog
chown mysql:mysql /var/log/mysql /var/lib/mysql/binlog
chmod 750 /var/log/mysql
六、不同内存配置参考
- 2C4G 开发测试环境
innodb_buffer_pool_size=2G
max_connections=500
sort_buffer_size=1M
key_buffer_size=128M
- 4C8G 中小型业务库
innodb_buffer_pool_size = 5G
innodb_buffer_pool_instances = 4
max_connections = 1000
innodb_log_file_size = 1G
key_buffer_size = 128M
- 16C32G 高并发主库
innodb_buffer_pool_size=22G
innodb_buffer_pool_instances=16
max_connections=3000
innodb_log_file_size=4G
七、MySQL 8.0 配置差异
- 默认字符集改为 utf8mb4,无需手动指定
- 默认认证插件
caching_sha2_password,旧客户端连接报错需改为mysql_native_password - 取消
NO_AUTO_CREATE_USERsql_mode - 废弃
query_cache,删除相关参数 - 支持持久化全局变量
SET PERSIST,无需修改配置文件
8.0 额外适配操作:
-- 用户密码兼容旧客户端
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的密码';
-- 持久化配置,不用改 my.cnf 永久生效
SET PERSIST max_connections = 2000;
八、校验配置是否合法
mysqld --defaults-file=/etc/my.cnf --validate-config
注意事项
- innodb_buffer_pool_size 不能超过物理内存 70%,需预留系统、连接缓冲区内存
- sort/join/read buffer 单连接分配,并发上千时设置过大会导致内存溢出
- binlog_format 生产环境使用 ROW,STATEMENT 存在数据不一致风险
- 字符集统一使用 utf8mb4,utf8 不支持 emoji
- 生产环境开启慢查询日志,long_query_time=1
- 主从环境每个实例 server-id 必须唯一
- 修改 my.cnf 后需重启 mysqld 服务,SET GLOBAL 仅临时生效
- 关闭 skip-name-resolve 可减少 DNS 阻塞,提升连接速度
总结
- MySQL 配置通过 my.cnf/my.ini 控制运行参数,加载顺序决定生效配置
- [mysqld] 分区是服务端核心,按基础、连接、内存、IO、日志、复制、安全分区组织
- InnoDB buffer pool 是内存调优核心,与并发连接、日志共同决定整体性能
- 生产配置开启 binlog、慢日志、事务安全,主从环境 server-id 唯一、binlog 用 ROW 格式
- MySQL 8.0 与 5.7 在字符集、认证插件、sql_mode、query_cache 上有差异
- 配置文件校验收敛使用
mysqld --validate-config