MySQL8修改innodb_tmpdir至大分区创建索引时出现OOM错误

• innodb_tmpdir变量不支持动态修改：MySQL 8.0版本中innodb_tmpdir属于只读全局变量，只能在服务启动时通过配置文件加载，运行时执行SET命令即使能通过SHOW VARIABLES查询到修改后的值，InnoDB引擎实际仍会使用启动时的默认路径。当指定的临时路径不被引擎识别时，创建索引的排序操作会优先尝试使用内存临时空间，你的大表+多字段索引排序所需内存远超过阈值，直接触发OOM报错。
• 目录权限/挂载属性异常：即使后续配置正确，也需要确认mysql系统用户对/mnt/partition/tmp目录有完整的读写执行权限，同时该分区的挂载参数不能包含noexec、nodev、nosuid这类限制MySQL写入临时文件的规则。
• Buffer Pool配置不合理：你当前配置了4G的innodb_buffer_pool_size，但拆分了8个实例，每个实例仅分配512M，低于InnoDB要求的每个实例至少1G的最优阈值，会降低内存利用效率，加剧大操作下的内存占用压力。

1. 编辑my.cnf配置文件，在[mysqld]段下添加/修改以下参数：

innodb_tmpdir = /mnt/partition/tmp
# 调整buffer pool实例数，4G内存建议设为4或直接注释该参数让MySQL自动分配
innodb_buffer_pool_instances = 4

2. 修正临时目录权限，无需设置777这么高的权限，仅开放给mysql用户即可，更安全：

chown mysql:mysql /mnt/partition/tmp
chmod 700 /mnt/partition/tmp

3. 重启MySQL服务让配置生效，之后再执行索引创建命令即可。

内容的提问来源于stack exchange，提问作者bcsta