macOS下如何修改my.cnf增大MySQL临时表容量解决1114表满错误

一、先确认当前MySQL生效配置 #

你之前查询配置的命令写法错误，@xxx指代的是用户自定义变量，MySQL系统变量需要用以下方式查询：

• 查看临时表相关配置：

SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

• 查看缓冲池配置：

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

• 查看临时文件目录配置：

SHOW VARIABLES LIKE 'tmpdir';
SHOW VARIABLES LIKE 'innodb_temp_data_file_path';

如果查询出来的值和你写在my.cnf里的不一致，说明MySQL确实没有读取你的配置文件。

二、排查配置文件不生效问题 #

1. 确认MySQL读取配置文件的优先级路径
   在终端执行以下命令：

mysql --help | grep "my.cnf"

输出会显示MySQL读取配置的顺序，比如/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf，你需要确认你修改的my.cnf在上述路径列表中，且没有被后面路径的配置文件覆盖。
2. 检查配置文件权限
MySQL会忽略权限过高的配置文件，执行以下命令修改权限（替换为你的my.cnf实际路径）：

chmod 644 /path/to/your/my.cnf

3. 确认Homebrew安装的MySQL配置路径
   从你配置文件的注释看是Homebrew安装的MySQL，默认配置文件路径为/usr/local/etc/my.cnf，确认你修改的是该路径下的文件，而非其他位置的副本。

三、解决临时表满报错问题 #

你当前的配置存在两个核心问题：

1. 参数设置不合理
   你的设备总内存只有16G，同时设置innodb_buffer_pool_size=12G+tmp_table_size=12G会导致内存溢出，合理参数建议：

# 缓冲池设为总内存的50%-60%即可，最多8G
innodb_buffer_pool_size = 8G
# 内存临时表统一设为2G，超过自动落盘
tmp_table_size = 2G
max_heap_table_size = 2G
# 新增磁盘临时表配置，设置最大可扩展到20G
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:20G
# 可选：将临时目录指定到空间更大的分区，先手动创建目录并给MySQL读写权限
# tmpdir = /Users/你的用户名/mysql_tmp

2. 先检查系统临时分区空间
   你报错的临时表存放在/var/tmp目录下，先在终端执行df -h /var/tmp查看该目录所在分区的剩余空间，如果剩余空间不足10G，要么清理磁盘，要么用上面的tmpdir参数指定到其他空间充足的目录。

四、Workbench配置识别问题 #

你在Database > Manage Connections > System Profile指定配置文件路径后，需要重启Workbench并重新连接MySQL实例，即可正常识别配置项。

额外优化建议 #

如果修改配置后仍然报错，建议优化查询逻辑：避免一次性拉取所有3G数据，改为分批分页查询；检查多表关联是否缺少索引，避免全表扫描生成超大临时表。

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