MySQL No Space Error

背景

[Err] 3 - Error writing file '/tmp/MYfd=36' (OS errno 28 - No space left on device)
临时接到开发人员反馈，mysql 在执行 sql 语句的时候出现如上错误 从错误日志上来看，是说 /tmp 目录的空间不足，于是在服务器查看 /tmp 目录磁盘空间，果然不足。

参数

mysql 在执行 sql 语句时，会将数据保存在临时表中用于排序，那么临时表时占用磁盘空间的，在 mysql 中通过 tmpdir 参数指定临时空间所在地磁盘位置默认为 /tmp 目录，所以上面的错误会报 /tmp 下的磁盘空间不足。下面是 mysql 官方对 tempdir 参数对解释:

The path of the directory to use for creating temporary files. It might be useful if your default /tmp directory resides on a partition
that is too small to hold temporary tables. This option accepts several paths that are used in round-robin fashion. Paths should be
separated by colon characters (:) on Unix and semicolon characters (;) on Windows. If the MySQL server is acting as a replication slave,
you should not set –tmpdir to point to a directory on a memory-based file system or to a directory that is cleared when the server host
restarts. For more information about the storage location of temporary files, see Section B.5.3.5, “Where MySQL Stores Temporary Files”.
A replication slave needs some of its temporary files to survive a machine restart so that it can replicate temporary tables or LOAD DATA
INFILE operations. If files in the temporary file directory are lost when the server restarts, replication fails.

解决

先登陆 mysql 查看以下 tmpdir 参数的配置

$ mysql -uroot -p
mysql> show variables like 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |
+---------------+-------+

修改 my.cnf 配置，添加 tmpdir 参数到 /db/tmp 目录下

$ vim /etc/my.cnf
tmpdir = /db/tmp

重启 mysql 验证

$ service msyqld restart
$ mysql -uroot -p
mysql> show variables like 'tmpdir';
+---------------+----------+
| Variable_name |   Value  |
+---------------+----------+
| tmpdir        |  /db/tmp |
+---------------+----------+

---