MySQL临时目录相关问题处理

MySQL临时目录空间不足会导致很多操作问题。比如主从复制出现错误而停止,查询排序无法完成等。一般来说,我们可以通过扩大临时目录所在文件系统的大小来避免问题。

MySQL的临时目录在哪里

默认情况下,MySQL根据环境变量的设置作为其临时目录。Linux中为TMPDIR,Windows中为TMPDIR, TEMP, TMP。如果未能找到,则使用系统的常见临时目录。Linux中为/tmp, /var/tmp, /usr/tmp等;在Windows中c:\Windows\Temp等。

此外,还可以在mysqld启动参数中指定临时目录。

在Linux中,

[mysqld]
tmpdir=/tmp:/var/tmp:/var/mysql/tmp

在Windows中,

[mysqld]
tmpdir=c:\\Windows\\Temp;c:\\MySQL\\Temp

请注意上面的例子中,tmpdir所设置的临时目录是可以有多个的,以路径分隔符区分。此外,该变量不能动态设置,改动以后需要重启才能生效。

在主从复制架构中,从服务器临时目录设置注意事项

从服务器在复制临时表以及LOAD DATA INFILE语句时,会在从服务器上创建临时文件。这些临时文件在复制完毕之前不能被删除。在复制未完成以前,如果从服务器重启后这些临时文件被删除了,那么接下来的复制操作会失败。

因此,从服务器的临时目录不要设置为tmpfs,或其他会被重启自动清除的临时目录。 此外,MySQL提供了专门的参数来设置从服务器临时文件目录 --slave-load-tmpdir。如果不设置该参数,则默认使用tmpdir的规则来确定临时目录。

哪些操作会使用到临时目录

会创建临时文件的操作就会使用到临时目录。

  1. 排序相关操作, order by, group by。排序操作一般会使用一个或者两个临时文件。所需的最大磁盘为:
( 待排序行数 + 行指针 ) * 匹配行数 * 2
  1. 某些查询语句,会创建可见的临时文件,以SQL_开头。
  2. 某些情况下的ALTER TABLE语句。该语句一般使用表所在的目录来创建复制表;但如果使用了在线DDL特性,InnoDB会使用临时文件。
  3. 主从架构中的从服务器复制临时表以及LOAD DATA INFILE操作。
  4. 临时表。临时表可能是在内存中,也可能是在临时文件中。系统变量 tmp_table_size和max_heap_table_size中较小的一个决定了内存中临时表的大小。对于超过这一设置的临时表,会被转移到临时文件中。系统变量Created_tmp_disk_tables记录了临时文件中的临时表,而Create_tmp_tables则记录了所有临时表的数量,其差值反应了内存中临时表数量。

如何查看MySQL临时文件

MySQL临时文件一般是不可见的。比如通过常用的查看命令ls等是不会被列出的。在Linux中,打开一个临时文件,然后unlink它。当前进程仍然可以访问该文件,进程退出时文件会被删除。这种文件可以通过命令 lsof +L1 列出.

比如在某台MySQL机器以root用户运行该命令

shell> sudo lsof +L1
COMMAND     PID  USER   FD   TYPE DEVICE SIZE/OFF NLINK    NODE NAME
rpc.idmap  1876  root  txt    REG  253,0    33344     0 2366451 /usr/sbin/rpc.idmapd (deleted)
mysqld    19332 mysql    5u   REG  253,0        0     0 1572871 /tmp/ibLin0AL (deleted)
mysqld    19332 mysql    6u   REG  253,0      199     0 1572882 /tmp/ibYznl55 (deleted)
mysqld    19332 mysql    7u   REG  253,0        0     0 1572911 /tmp/ibDRLGzq (deleted)
mysqld    19332 mysql    8u   REG  253,0        0     0 1572915 /tmp/ibAsFgI5 (deleted)
mysqld    19332 mysql   12u   REG  253,0        0     0 1573014 /tmp/ibcb4Kfr (deleted)

参考案例

  1. 排序操作导致临时目录空间耗尽 http://blog.chinaunix.net/uid-21879027-id-3504785.html
  2. CSDN博主刀刀所总结的MySQL临时目录相关文章,http://blog.csdn.net/zyz511919766/article/details/38084515
  3. 使用mysqlbinlog工具时遇到的临时文件问题 http://www.2cto.com/database/201202/119998.html
  4. 在51cto网站上能找到一篇写于2007年的文章,与本文内容绝大部分相同。 http://database.51cto.com/art/200711/60960.htm