起源

MySQL 通过配置文件控制服务器和客户端的运行参数。配置文件决定连接数上限、内存缓冲、日志、复制、安全策略等行为,是数据库稳定运行与性能调优的基础。Linux 下配置文件名称为 my.cnf,Windows 下为 my.ini。

原理

一、配置文件加载顺序

按优先级从高到低,后加载覆盖前加载:

  1. 命令行启动参数(最高优先级)
  2. --defaults-extra-file 指定文件
  3. /etc/mysql/my.cnf
  4. /etc/my.cnf
  5. /usr/local/mysql/my.cnf
  6. ~/.my.cnf 用户私有配置
  7. 编译内置默认参数(最低优先级)

二、配置文件结构分组

分组 用途
[mysqld] 服务端核心配置
[mysqld_safe] 安全启动参数
[mysql] mysql 客户端命令行配置
[mysqldump] 备份工具配置
[client] 所有客户端通用配置
[mysqladmin] 管理工具配置

三、[mysqld] 参数分区设计

[mysqld] 分区按作用划分逻辑,各分区对应不同的优化目标:

  1. 基础配置:路径、账号、字符集、时区
  2. 连接会话:并发上限、超时、域名解析
  3. 内存缓冲:InnoDB 引擎性能核心
  4. 磁盘 IO 与事务:持久化安全级别
  5. Binlog 日志:主从复制与数据恢复基础
  6. 慢查询日志:SQL 性能排查
  7. 表缓存与文件句柄:吞吐能力
  8. 主从复制参数:从库运行逻辑
  9. 安全配置:SQL 模式与安全限制

实现

一、核心服务端配置

  1. 基础通用配置
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
  1. 连接与会话配置
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             # 关闭域名解析,提升连接速度
  1. 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
  1. 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       # 内存临时表上限
  1. 磁盘 IO 与事务
sync_binlog=1                 # binlog 同步策略,1 最安全
transaction-isolation=READ-COMMITTED # 事务隔离级别
  1. 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
  1. 慢查询日志
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
  1. 临时文件、表、文件句柄
open_files_limit=65535
table_open_cache=20000
table_definition_cache=10000
tmp_table_size=64M
max_heap_table_size=64M
  1. 主从复制参数(从库)
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 系统库同步
  1. 安全相关配置
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 热备不加锁

四、查看当前配置

  1. 查看所有全局配置
SHOW VARIABLES;
SHOW VARIABLES LIKE '%innodb_buffer%'; -- 模糊查询
  1. 查看运行状态
SHOW STATUS;
SHOW ENGINE INNODB STATUS; -- innodb 详细运行状态
  1. 动态修改参数(临时生效,重启失效)
SET GLOBAL max_connections=2000;

动态修改重启失效,永久生效必须修改 my.cnf。

  1. 查看配置文件加载路径
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

六、不同内存配置参考

  1. 2C4G 开发测试环境
innodb_buffer_pool_size=2G
max_connections=500
sort_buffer_size=1M
key_buffer_size=128M
  1. 4C8G 中小型业务库
innodb_buffer_pool_size = 5G
innodb_buffer_pool_instances = 4
max_connections = 1000
innodb_log_file_size = 1G
key_buffer_size = 128M
  1. 16C32G 高并发主库
innodb_buffer_pool_size=22G
innodb_buffer_pool_instances=16
max_connections=3000
innodb_log_file_size=4G

七、MySQL 8.0 配置差异

  1. 默认字符集改为 utf8mb4,无需手动指定
  2. 默认认证插件 caching_sha2_password,旧客户端连接报错需改为 mysql_native_password
  3. 取消 NO_AUTO_CREATE_USER sql_mode
  4. 废弃 query_cache,删除相关参数
  5. 支持持久化全局变量 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

注意事项

  1. innodb_buffer_pool_size 不能超过物理内存 70%,需预留系统、连接缓冲区内存
  2. sort/join/read buffer 单连接分配,并发上千时设置过大会导致内存溢出
  3. binlog_format 生产环境使用 ROW,STATEMENT 存在数据不一致风险
  4. 字符集统一使用 utf8mb4,utf8 不支持 emoji
  5. 生产环境开启慢查询日志,long_query_time=1
  6. 主从环境每个实例 server-id 必须唯一
  7. 修改 my.cnf 后需重启 mysqld 服务,SET GLOBAL 仅临时生效
  8. 关闭 skip-name-resolve 可减少 DNS 阻塞,提升连接速度

总结

  1. MySQL 配置通过 my.cnf/my.ini 控制运行参数,加载顺序决定生效配置
  2. [mysqld] 分区是服务端核心,按基础、连接、内存、IO、日志、复制、安全分区组织
  3. InnoDB buffer pool 是内存调优核心,与并发连接、日志共同决定整体性能
  4. 生产配置开启 binlog、慢日志、事务安全,主从环境 server-id 唯一、binlog 用 ROW 格式
  5. MySQL 8.0 与 5.7 在字符集、认证插件、sql_mode、query_cache 上有差异
  6. 配置文件校验收敛使用 mysqld --validate-config