MySQL8 配置文件 my.cnf 重要参数解读原创

# MySQL8 配置文件 my.cnf 重要参数解读

# 1. 改了 my.cnf 重启却不生效，先看 mysqld-auto.cnf

一个在 MySQL 8.0 上很容易踩的现象：my.cnf 里明明写了 32G 的缓冲池，重启后查出来却不是这个值。

# /etc/my.cnf
[mysqld]
innodb_buffer_pool_size = 32G

1
2
3

mysql> SELECT @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS gb;

预期输出（与配置不符）：

+-------+
| gb    |
+-------+
| 8.000 |
+-------+

1
2
3
4
5

根因不在 my.cnf，而在 MySQL 8.0 新增的持久化配置。只要有人执行过一次：

SET PERSIST innodb_buffer_pool_size = 8G;

这个值就被写进了数据目录下的 mysqld-auto.cnf，而它的优先级高于所有 my.cnf。启动时 MySQL 先读配置文件，最后再应用持久化变量，于是后者赢。

确认是不是它干的：

SELECT VARIABLE_NAME, VARIABLE_SOURCE, VARIABLE_PATH
FROM performance_schema.variables_info
WHERE VARIABLE_NAME = 'innodb_buffer_pool_size';

1
2
3

预期输出：

+-------------------------+-----------------+--------------------------------+
| VARIABLE_NAME           | VARIABLE_SOURCE | VARIABLE_PATH                  |
+-------------------------+-----------------+--------------------------------+
| innodb_buffer_pool_size | PERSISTED       | /var/lib/mysql/mysqld-auto.cnf |
+-------------------------+-----------------+--------------------------------+

1
2
3
4
5

VARIABLE_SOURCE 是排查配置问题最有用的一列，取值包括 COMPILED（编译默认值）、GLOBAL（my.cnf）、PERSISTED（mysqld-auto.cnf）、DYNAMIC（运行时 SET GLOBAL）、COMMAND_LINE。清除持久化值：

RESET PERSIST innodb_buffer_pool_size;   -- 清单个
RESET PERSIST;                            -- 清全部

1
2

注意 RESET PERSIST 只删文件里的记录，不会改回当前运行值，要重启或再 SET GLOBAL 一次。

# 2. 为什么值得逐个参数过一遍

MySQL 8.0 的默认配置比 5.7 合理得多——binlog 默认开启、字符集默认 utf8mb4、双 1 默认打开——但它的默认值是按「能在小机器上跑起来」定的，不是按你的硬件定的。三类代价最典型：

内存给少了：innodb_buffer_pool_size 默认 128M。一台 64G 内存的机器如果没人改，热数据全程走磁盘，QPS 差一个数量级。
内存给多了：会话级缓冲区是每连接分配的。sort_buffer_size 设成 16M、max_connections 给到 1000，光排序缓冲的理论上限就是 16G，OOM Killer 会替你做决定。
持久性配错：innodb_flush_log_at_trx_commit 和 sync_binlog 从 1 改成 0 能明显提速，代价是宕机丢最近 1 秒的事务。这个取舍必须是明确决定，不能是抄配置抄来的。

下面按用途分组过一遍，每组只讲会真正影响线上的参数。

# 3. 分组解读：内存、持久性、连接、复制、日志

# 3.1 配置文件的加载顺序

先搞清楚谁覆盖谁，否则调参永远在猜。Linux 上 mysqld 的读取顺序（后读的覆盖先读的）：

/etc/my.cnf
/etc/mysql/my.cnf
SYSCONFDIR/my.cnf
$MYSQL_HOME/my.cnf
--defaults-extra-file=<file>
~/.my.cnf
mysqld-auto.cnf        ← 持久化变量，最后应用，优先级最高

1
2
3
4
5
6
7

用官方工具打印实际生效的文件和参数，比人肉找可靠：

mysqld --verbose --help | head -20
my_print_defaults --mysqld

1
2

# 3.2 内存参数

全局共享——只分配一份，可以放心给大：

参数	默认值	建议
`innodb_buffer_pool_size`	128M	独占机器给物理内存 50%~70%
`innodb_buffer_pool_instances`	1（<1G）/ 8（≥1G）	缓冲池 ≥ 8G 时设 8，减少内部锁竞争
`innodb_buffer_pool_chunk_size`	128M	一般不动，但影响取整（见 4.2）
`table_open_cache`	4000	表多时上调，看 `Opened_tables` 增长速度

会话级——每个连接都可能分配一份，必须保守：

参数	默认值	说明
`sort_buffer_size`	256K	排序用。加大对多数负载无效，反而放大内存占用
`join_buffer_size`	256K	无索引 join 用。真正该做的是加索引
`read_rnd_buffer_size`	256K	同上，默认值够用
`tmp_table_size` / `max_heap_table_size`	16M / 16M	内存临时表上限，两者取小值生效，要改就一起改

MySQL 8.0 的内部临时表默认走 TempTable 引擎，由 temptable_max_ram（默认 1G）统一控制，这是一个全局上限，比 5.7 的按连接分配安全。

一个粗略但够用的内存估算：

峰值内存 ≈ innodb_buffer_pool_size
         + temptable_max_ram
         + max_connections × (sort_buffer_size + join_buffer_size + read_rnd_buffer_size + net_buffer_length)

1
2
3

如果你只想改一个参数，MySQL 8.0 提供了自动挡：

[mysqld]
innodb_dedicated_server = ON

1
2

打开后 MySQL 会根据机器内存自动推导 innodb_buffer_pool_size、innodb_redo_log_capacity、innodb_flush_method。仅在数据库独占该机器时使用，混部会把别的服务挤死。

# 3.3 持久性与崩溃恢复

参数	默认值	说明
`innodb_flush_log_at_trx_commit`	1	1 = 每次提交刷盘，宕机不丢事务
`sync_binlog`	1	1 = 每次提交刷 binlog
`innodb_redo_log_capacity`	100M	8.0.30 起取代 `innodb_log_file_size`
`innodb_flush_method`	fsync	建议 `O_DIRECT`，避免双重缓存
`innodb_doublewrite`	ON	防页断裂，不要关

前两个就是俗称的「双 1」，是数据不丢的底线，取舍细节见MySQL8双1设置保障安全。

innodb_redo_log_capacity 是 8.0.30 的重要变化：它取代了 innodb_log_file_size 和 innodb_log_files_in_group，直接指定 redo 总容量。默认 100M 对写入密集的库明显偏小，会导致频繁刷脏页：

[mysqld]
innodb_redo_log_capacity = 4G

1
2

它是动态参数，可以在线调整：

SET GLOBAL innodb_redo_log_capacity = 4294967296;

# 3.4 连接与线程

参数	默认值	说明
`max_connections`	151	按应用连接池总和 × 1.2 估，不要盲目上万
`thread_cache_size`	自适应	默认 `8 + (max_connections/100)`，一般够用
`back_log`	自适应	瞬时连接风暴时的等待队列
`wait_timeout` / `interactive_timeout`	28800	8 小时太长，建议 600~1800
`max_allowed_packet`	64M	大字段/大批量 insert 报 packet too large 时才调
`skip_name_resolve`	OFF	建议开，见 4.4

# 3.5 字符集

MySQL 8.0 默认已经是 utf8mb4 + utf8mb4_0900_ai_ci，通常不需要改。只有在需要与旧库保持一致的排序行为时才显式指定：

[mysqld]
character_set_server = utf8mb4
collation_server     = utf8mb4_general_ci   # 仅为兼容旧库时使用

1
2
3

字符集选型的取舍见MySQL不同字符集之间的区别和选择。

# 3.6 binlog 与复制

参数	默认值	说明
`log_bin`	ON	8.0 默认开启，不要关
`binlog_format`	ROW	保持 ROW
`binlog_expire_logs_seconds`	2592000（30 天）	取代 `expire_logs_days`
`gtid_mode` / `enforce_gtid_consistency`	OFF	做复制建议双双设为 ON
`server_id`	1	复制拓扑内必须唯一

binlog_expire_logs_seconds 单位是秒，别按天填：

[mysqld]
binlog_expire_logs_seconds = 604800   # 7 天

1
2

# 3.7 日志

[mysqld]
slow_query_log       = ON
slow_query_log_file  = /var/log/mysql/slow.log
long_query_time      = 1
log_error_verbosity  = 2               # 1 错误 / 2 +警告 / 3 +通知

1
2
3
4
5

排查阶段可以把 long_query_time 临时设为 0 记录全部语句，用法见MySQL8设置slowlog记录所有语句。注意这会让慢日志迅速膨胀，用完记得改回来。

# 3.8 一份可以直接抄的模板

以下按独占机器、32G 内存、SSD给出，务必按自己的硬件调整前两组数值：

[mysqld]
# --- 基础 ---
server_id                       = 1
port                            = 3306
datadir                         = /var/lib/mysql
skip_name_resolve               = ON

# --- 内存（按硬件调整）---
innodb_buffer_pool_size         = 20G
innodb_buffer_pool_instances    = 8
table_open_cache                = 4000
tmp_table_size                  = 64M
max_heap_table_size             = 64M

# --- 持久性 ---
innodb_flush_log_at_trx_commit  = 1
sync_binlog                     = 1
innodb_redo_log_capacity        = 4G
innodb_flush_method             = O_DIRECT

# --- IO（SSD）---
innodb_io_capacity              = 2000
innodb_io_capacity_max          = 4000
innodb_flush_neighbors          = 0

# --- 连接 ---
max_connections                 = 500
wait_timeout                    = 1800
interactive_timeout             = 1800
max_allowed_packet              = 64M

# --- binlog ---
binlog_format                   = ROW
binlog_expire_logs_seconds      = 604800
gtid_mode                       = ON
enforce_gtid_consistency        = ON

# --- 日志 ---
slow_query_log                  = ON
long_query_time                 = 1
log_error_verbosity             = 2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

机械硬盘要把 innodb_io_capacity 降到 200 左右、innodb_flush_neighbors 设为 1，SSD 上这两项则相反。

# 3.9 改完必须验证

不要相信「配置文件写了就是生效了」，用 variables_info 逐个核对来源：

SELECT VARIABLE_NAME, VARIABLE_SOURCE
FROM performance_schema.variables_info
WHERE VARIABLE_NAME IN (
  'innodb_buffer_pool_size',
  'innodb_redo_log_capacity',
  'sync_binlog',
  'max_connections'
);

1
2
3
4
5
6
7
8

预期输出（GLOBAL 表示来自 my.cnf）：

+--------------------------+-----------------+
| VARIABLE_NAME            | VARIABLE_SOURCE |
+--------------------------+-----------------+
| innodb_buffer_pool_size  | GLOBAL          |
| innodb_redo_log_capacity | GLOBAL          |
| max_connections          | GLOBAL          |
| sync_binlog              | GLOBAL          |
+--------------------------+-----------------+

1
2
3
4
5
6
7
8

出现 PERSISTED 就说明被 mysqld-auto.cnf 接管了，回到第 1 节处理。

# 4. 五个高频坑

# 4.1 SET GLOBAL 改的值重启就没了

症状：临时调大 innodb_buffer_pool_size 后性能恢复，重启后又变慢。

原因：SET GLOBAL 只改运行时内存中的值，不落盘。

解药：想持久化就用 SET PERSIST（写 mysqld-auto.cnf），想长期固定就写进 my.cnf。两者别混用，否则就是第 1 节那个坑。

# 4.2 缓冲池实际值和配置值对不上

症状：配了 innodb_buffer_pool_size = 5G，查出来是 5.5G 之类的数。

原因：缓冲池大小会被向上取整为 innodb_buffer_pool_chunk_size × innodb_buffer_pool_instances 的整数倍。默认 chunk 128M、8 个 instance，粒度就是 1G。

解药：把目标值设成该粒度的整数倍，或接受取整。别为了凑整去调 chunk_size，它需要重启且影响面更大。

# 4.3 8.0.30 之后 innodb_log_file_size 不再起作用

症状：升级到 8.0.30+ 后，innodb_log_file_size 照旧写在配置里，redo 容量却没变。

原因：8.0.30 引入 innodb_redo_log_capacity，它一旦被设置就完全接管 redo 容量，旧参数被忽略（并已标记弃用）。

解药：新版本统一用 innodb_redo_log_capacity，把旧的两个参数从配置里删掉，避免误导。

# 4.4 连接建立慢、偶发几秒卡顿

症状：应用建连耗时忽长忽短，SQL 本身很快。

原因：MySQL 默认对每个连入 IP 做反向 DNS 解析，DNS 不可达时每次都要等超时。

解药：

[mysqld]
skip_name_resolve = ON

1
2

代价是授权表里不能再用主机名，全部改成 IP 或网段。这是个需要重启的静态参数。

# 4.5 会话缓冲区调大后 OOM

症状：把 sort_buffer_size 从 256K 调到 32M「优化排序」，高并发时 mysqld 被 OOM Killer 杀掉。

原因：这类缓冲是按连接分配的，32M × 并发连接数 迅速吃光内存；而且超过一定大小后 Linux 上的分配方式反而更慢。

解药：保持默认值，真有大排序需求时只在该会话内临时调整：

SET SESSION sort_buffer_size = 32 * 1024 * 1024;

排序慢的正解几乎总是加合适的索引，而不是加缓冲区。

# 5. 可复用要点

先查来源再改值。任何「配了没生效」的问题，第一步都是 performance_schema.variables_info 看 VARIABLE_SOURCE，而不是反复改 my.cnf。
区分全局共享与会话级。全局的（缓冲池）可以给大；会话级的（sort/join buffer）保持默认，需要时只在会话内调。
持久性参数必须是明确决定。双 1 是默认也是底线，改成 0 之前先想清楚能接受丢多少数据。
认版本差异。8.0.30 的 innodb_redo_log_capacity、8.0 的 binlog_expire_logs_seconds，都取代了沿用多年的老参数，抄旧配置最容易在这里翻车。
独占机器可以用 innodb_dedicated_server 兜底，混部环境绝对不要开。

# 6. 延伸阅读

MySQL8双1设置保障安全——innodb_flush_log_at_trx_commit 与 sync_binlog 的取舍细节
MySQL8设置slowlog记录所有语句——排查期记录全量 SQL 的方法
MySQL不同字符集之间的区别和选择——字符集与排序规则选型

{
  "topic": "MySQL 8.0 my.cnf 关键参数解读与调优",
  "mysql_version": "8.0.30+",
  "diagnose_config_source": {
    "sql": "SELECT VARIABLE_NAME, VARIABLE_SOURCE, VARIABLE_PATH FROM performance_schema.variables_info WHERE VARIABLE_NAME = ?",
    "sources": ["COMPILED", "GLOBAL", "PERSISTED", "DYNAMIC", "COMMAND_LINE"],
    "note": "PERSISTED 表示被 datadir/mysqld-auto.cnf 覆盖，优先级高于 my.cnf"
  },
  "clear_persisted": ["RESET PERSIST <var>", "RESET PERSIST"],
  "key_parameters": {
    "memory_global": {
      "innodb_buffer_pool_size": {"default": "128M", "advice": "独占机器物理内存的 50%-70%"},
      "innodb_buffer_pool_instances": {"default": "1 (<1G) / 8 (>=1G)", "advice": "缓冲池 >= 8G 时设 8"},
      "table_open_cache": {"default": 4000},
      "temptable_max_ram": {"default": "1G", "note": "8.0 TempTable 引擎的全局上限"}
    },
    "memory_per_session": {
      "sort_buffer_size": {"default": "256K", "advice": "保持默认，按会话临时调"},
      "join_buffer_size": {"default": "256K", "advice": "保持默认，优先加索引"},
      "tmp_table_size": {"default": "16M", "advice": "与 max_heap_table_size 一起改，取小值生效"}
    },
    "durability": {
      "innodb_flush_log_at_trx_commit": {"default": 1},
      "sync_binlog": {"default": 1},
      "innodb_redo_log_capacity": {"default": "100M", "since": "8.0.30", "replaces": ["innodb_log_file_size", "innodb_log_files_in_group"]},
      "innodb_flush_method": {"default": "fsync", "advice": "O_DIRECT"},
      "innodb_doublewrite": {"default": "ON", "advice": "不要关闭"}
    },
    "connection": {
      "max_connections": {"default": 151},
      "wait_timeout": {"default": 28800, "advice": "600-1800"},
      "skip_name_resolve": {"default": "OFF", "advice": "ON，避免反向 DNS 超时"}
    },
    "replication": {
      "log_bin": {"default": "ON"},
      "binlog_format": {"default": "ROW"},
      "binlog_expire_logs_seconds": {"default": 2592000, "replaces": "expire_logs_days"},
      "gtid_mode": {"advice": "ON"},
      "server_id": {"note": "复制拓扑内必须唯一"}
    }
  },
  "memory_budget_formula": "innodb_buffer_pool_size + temptable_max_ram + max_connections * (sort_buffer_size + join_buffer_size + read_rnd_buffer_size + net_buffer_length)",
  "io_tuning": {
    "ssd": {"innodb_io_capacity": 2000, "innodb_io_capacity_max": 4000, "innodb_flush_neighbors": 0},
    "hdd": {"innodb_io_capacity": 200, "innodb_flush_neighbors": 1}
  },
  "pitfalls": [
    {"symptom": "my.cnf 配置不生效", "cause": "mysqld-auto.cnf 中的持久化变量优先级更高", "fix": "RESET PERSIST 后重启"},
    {"symptom": "缓冲池实际值大于配置值", "cause": "按 chunk_size × instances 向上取整", "fix": "设为该粒度的整数倍"},
    {"symptom": "innodb_log_file_size 不再生效", "cause": "8.0.30 起被 innodb_redo_log_capacity 取代", "fix": "改用新参数并删除旧参数"},
    {"symptom": "建连偶发数秒延迟", "cause": "反向 DNS 解析超时", "fix": "skip_name_resolve = ON，授权改用 IP"},
    {"symptom": "调大 sort_buffer_size 后 OOM", "cause": "会话级缓冲按连接数放大", "fix": "保持默认，按会话临时调整"}
  ],
  "verify": "SELECT VARIABLE_NAME, VARIABLE_SOURCE FROM performance_schema.variables_info WHERE VARIABLE_NAME IN (...)"
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

#MySQL #配置调优

上次更新: 8/7/2026

← MySQL 运维知识地图：从入门配置到高可用排障 MySQL 导出 CSV 中文乱码：字符集链路从头讲一遍→