如何在my.cnf中配置MySQL 8.0的临时表空间以防止磁盘爆满

MySQL 8.0 已彻底移除tmp_table_size参数，只修改它无效；真正生效的是max_heap_table_size与internal_tmp_mem_storage_engine双控机制，且需配合temptable_max_ram和innodb_temp_data_file_path协同配置。

为什么只改tmp_table_size没用？

MySQL 8.0 已彻底移除 tmp_table_size 参数。如果你从 5.7 升级上来，配置文件里还留着它，mysqld 启动时会静默忽略，不报错也不警告。SELECT @@tmp_table_size 会返回 0 或直接报错 Unknown system variable 'tmp_table_size'。

真正起作用的是：

• max_heap_table_size：控制 MEMORY 表和内存临时表上限，也约束 TEMPTABLE 引擎的内存使用
• internal_tmp_mem_storage_engine：MySQL 8.0 默认为 TempTable（比 MEMORY 更省内存、支持 BLOB），不建议改回 MEMORY
• temptable_max_ram：TEMPTABLE 引擎总内存池上限，默认是物理内存的 3%（上限 4 GiB），不支持动态修改

temptable_max_ram 必须写死在配置文件里

这个参数决定 TEMPTABLE 引擎能用多少内存做临时计算。默认值对报表类查询极易卡死——超限后直接写入 ibtmp1，撑爆根分区。

实操要点：

• 查当前值：SELECT @@temptable_max_ram（单位字节，比如 2147483648 = 2 GiB）
• 在 /etc/my.cnf 的 [mysqld] 段添加：temptable_max_ram = 4G（注意：MySQL 8.0.23+ 才完全支持 G 单位；旧版本建议写 4294967296）
• 若物理内存 ≥ 128 GiB，可设为 6G，但别超物理内存 15%，否则可能触发系统 OOM
• 重启前务必执行：SET GLOBAL innodb_fast_shutdown = 0;，再 SHUTDOWN;，确保旧 ibtmp1 可被安全重建

innodb_temp_data_file_path 不是优化手段，是保命底线

它不是用来“缩小”或“优化”ibtmp1 的，而是防止它无节制增长撑爆磁盘的硬性上限控制。一旦达到 max:，MySQL 报错退出，而不是继续写坏磁盘。

正确写法（必须严格）：

• innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:20G
• 含义：起始 12MB，按需扩展，上限 20GB
• 错误写法举例：ibtmp1:20G（没 autoextend，启动就占满 20G）；/var/lib/mysql/ibtmp1:12M:autoextend:max:20G（路径不能带目录）；ibtmp1:12M:autoextend:max:20g（单位必须大写 G）
• 该参数是只读变量，改完必须重启 MySQL 才生效

别忘了同步设置 max_heap_table_size 和 tmpdir

哪怕你已经调了 temptable_max_ram，老式 MEMORY 临时表逻辑依然存在——比如某些 GROUP BY、ORDER BY 场景仍会走 MEMORY 引擎，这时就又回到“双参数取小值”的老问题。

操作建议：

• 在 [mysqld] 段显式设置：max_heap_table_size = 256M，且确保与 tmp_table_size（虽已废弃，但部分工具仍读取）值一致（如也设为 256M）
• 通过 SELECT @@tmpdir 查实际路径，再用 lsof +D /path/to/tmpdir 排除非 MySQL 进程干扰
• Docker 部署时，建议挂载独立宿主机目录到容器内 /tmp，并用 --tmpdir=/tmp 显式指定，避免占用系统盘

最易被忽略的点：所有这些参数都依赖重启生效，而重启前必须确认没有长事务——否则回滚可能拖长停机时间，甚至卡住释放空间。查长事务用：SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 300;。