在my.cnf中配置MySQL 8.0临时表空间防止磁盘爆满的详细教程

MySQL 8.0 在临时表管理方面进行了重大重构，tmp_table_size 参数被彻底移除。许多从 5.7 版本升级的用户可能尚未注意到这一点。如果旧配置文件中仍保留该参数，MySQL 启动时会静默忽略，既不报错也不发出警告。执行 SELECT @@tmp_table_size 返回 0，或直接提示 Unknown system variable '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;。