【MySQL深入详解】第10篇：MySQL配置原理——从配置文件到动态变量

xyghehehehe
7 阅读7分钟

开篇引入

"我的服务器有32G内存，12核CPU，帮我配一下MySQL。"

每次听到这种需求，我都想说：没有放之四海而皆准的配置文件。MySQL的配置要基于工作负载、数据量、业务需求来定制，而不是简单地按硬件比例放大。

《高性能MySQL》第5章开篇就强调了这一点。配置不是"调优"，而是对MySQL行为的精确控制。这篇文章帮你建立正确的配置观念。

MySQL配置的基本原理

配置从哪来

MySQL读取配置的地方有两个：

1. 命令行参数

mysqld --port=3306 --datadir=/var/lib/mysql

2. 配置文件

# 类Unix系统查找顺序
/etc/my.cnf
/etc/mysql/my.cnf
$MYSQL_HOME/my.cnf
~/.my.cnf

最佳实践：把所有配置写进文件，而不是命令行。命令行容易被遗忘，文件更可控。

配置文件的格式

[mysqld]
# MySQL服务器配置
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid

# InnoDB配置
innodb_buffer_pool_size = 128G
innodb_log_file_size = 4G

[client]
# 客户端配置
port = 3306
socket = /var/lib/mysql/mysql.sock

注意：

  • 配置项可以用下划线或横线：max_connections = max-connections
  • 建议统一风格，我偏好下划线
  • 放在正确的section里，[mysqld]是服务器配置，[client]是客户端

变量作用域

MySQL变量有三种作用域：

全局变量（Global）：整个服务器生效

-- 查看全局配置
SHOW GLOBAL VARIABLES LIKE 'max_connections';

-- 修改全局配置（需要SUPER权限）
SET GLOBAL max_connections = 1000;

会话变量（Session）：只影响当前连接

-- 查看会话配置
SHOW SESSION VARIABLES LIKE 'sort_buffer_size';

-- 修改会话配置
SET SESSION sort_buffer_size = 1048576;

实例变量：全局+会话都有，修改会话不影响全局

-- 全局值 vs 会话值
SHOW VARIABLES;              -- 默认显示会话值
SHOW GLOBAL VARIABLES;       -- 显示全局值

-- 两者的关系
SET GLOBAL sort_buffer_size = 2097152;  -- 设置全局默认值
SET sort_buffer_size = 1048576;         -- 只改当前会话

动态修改的陷阱

动态修改配置后，如果MySQL重启，配置会恢复到配置文件中的值。

-- 动态修改（重启失效）
SET GLOBAL max_connections = 2000;

-- 持久化（MySQL 8.0+）
SET PERSIST max_connections = 2000;
-- 等价于：
-- 1. 立即应用
-- 2. 写入 mysqld-auto.cnf

-- 查看持久化的配置
SELECT * FROM performance_schema.persisted_variables;

动态修改的副作用

有些变量修改会立即生效，有些则不会：

变量何时生效行为说明
table_open_cache下次打开表时检查缓存大小
thread_cache_size下次关闭连接时可能不会立即减少内存
sort_buffer_size立即分配建议会话级别设置
read_buffer_size查询需要时整块分配

MySQL 8.0的智能配置

innodb_dedicated_server

MySQL 8.0引入了这个选项，让MySQL自动根据硬件配置参数：

[mysqld]
# 开启自动配置（推荐用于云环境）
innodb_dedicated_server = ON

开启后，MySQL会自动设置：

  • innodb_buffer_pool_size
  • innodb_log_file_size
  • innodb_log_files_in_group
  • innodb_flush_method

计算规则（大约）：

内存 ≤ 1G:   buffer_pool = 128M
内存 1-4G:   buffer_pool = 物理内存 * 0.5
内存 4-64G:  buffer_pool = 物理内存 * 0.75
内存 > 64G:  buffer_pool = 物理内存 * 0.5

注意：自动配置只是起点，生产环境应该根据实际工作负载调整。

配置文件推荐模板

最小配置示例

[mysqld]
# 基本设置
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

# InnoDB（根据硬件调整）
innodb_buffer_pool_size = 64G
innodb_log_file_size = 4G
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1

# 连接
max_connections = 2000
thread_cache_size = 64

# 查询优化
sort_buffer_size = 2M
read_buffer_size = 2M
join_buffer_size = 2M

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

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

什么不该做

1. 不要迷信"调优比率"

网上流传的"缓冲池命中率要>95%"之类的说法是误导。

-- 缓冲池命中率低，不一定是缓存太小
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- 命中率 = read_requests / (read_requests + reads)

命中率低可能是因为工作负载本身就难以缓存，跟缓存大小无关。

2. 不要用调优脚本

那些"一键优化MySQL"的脚本：

  • 假设所有服务器都一样
  • 可能设置危险的值
  • 让你不理解真正的问题

3. 不要盲目照搬别人的配置

"我看到某博客说sort_buffer_size设成8M最好"
"某论坛说innodb_buffer_pool_size要设成物理内存的80%"

这些建议可能对别人的场景有效，但不一定适合你。

4. 不要相信内存公式

MySQL启动时会输出一个内存公式，告诉你"最坏情况下会占用多少内存"。

这个公式是错的。它忽略了：

  • 线程栈
  • Prepared语句
  • InnoDB内部结构
  • 操作系统开销

正确的配置思路

第一步：理解工作负载

-- 查看最常见的查询类型
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY COUNT_STAR DESC
LIMIT 20;

第二步：设置基本参数

-- 根据数据量设置Buffer Pool
-- 如果数据有100GB，Buffer Pool至少要有100GB
-- 留30%给系统和其他开销

第三步：设置安全参数

[mysqld]
# 防止数据损坏
innodb_checksum_algorithm = crc32

# 防止连接耗尽
max_connections = 2000
wait_timeout = 600
interactive_timeout = 600

# 防止SQL注入（应用层也要做）
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO

第四步：监控和调优

# 使用状态变量调整配置
mysqladmin extended-status -r -i 1 | grep -E 'Threads_running|Questions|Innodb_buffer_pool_wait_free'

常用配置项速查

连接相关

[mysqld]
max_connections = 2000          # 最大连接数
thread_cache_size = 64           # 线程缓存
wait_timeout = 600              # 空闲连接超时
max_connect_errors = 100000      # 允许的错误连接数

内存相关

[mysqld]
innodb_buffer_pool_size = 128G  # InnoDB缓冲池
key_buffer_size = 32M           # MyISAM索引缓存
sort_buffer_size = 2M            # 排序缓存（会话级调整更佳）
join_buffer_size = 2M            # JOIN缓存
read_buffer_size = 2M           # 顺序读缓存
tmp_table_size = 256M           # 临时表大小
max_heap_table_size = 256M      # 内存表大小

InnoDB相关

[mysqld]
innodb_buffer_pool_size = 128G
innodb_log_file_size = 4G
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT

日志相关

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

配置变更管理

使用版本控制

# 配置文件放到Git中
git add /etc/my.cnf
git commit -m "更新max_connections到2000"

变更流程

1. 在测试环境验证
   ↓
2. 评估影响（内存、连接数、IO）
   ↓
3. 准备回滚方案
   ↓
4. 变更生产配置
   ↓
5. 验证效果
   ↓
6. 监控SLO

记录变更

-- 记录配置变更历史
CREATE TABLE config_changes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    variable_name VARCHAR(64),
    old_value VARCHAR(255),
    new_value VARCHAR(255),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    changed_by VARCHAR(64),
    reason TEXT
);

小结

  1. 没有万能配置：配置要基于工作负载，而不是硬件规格
  2. 理解变量作用域：全局变量vs会话变量，动态修改vs持久化
  3. innodb_dedicated_server：云环境可以考虑使用
  4. 不要迷信调优比率：那些经验法则可能是误导
  5. 不要用调优脚本：手动配置更可控
  6. 配置变更要记录：用Git管理配置文件
  7. 先优化查询和索引：配置调优的收益远小于查询优化
  8. 监控SLO：配置改变后要确认客户体验没有下降

MySQL的配置哲学是：保持简单，只改需要的。默认设置经过了大量测试，大多数情况下是安全的。在有明确理由之前，不要随意修改。

上一篇：【第09篇】文件系统与磁盘IO——让MySQL数据写入飞起来

下一篇：【第11篇】内存与IO配置——InnoDB核心参数调优

延伸阅读

  • 《高性能MySQL》第5章 优化服务器设置
  • MySQL 8.0 Reference Manual: Server System Variables
  • MySQL Server Variables: dev.mysql.com/doc/refman/…
avatar
文章
阅读
粉丝