MYSQL临时表导致根分区爆满问题分析

问题现象

突然接到线上Zabbix告警信息，报MYSQL所在的主机/
分区不足15%，内容如下：

Trigger: app-ali-prod-db1 / 可用空间不足 15%
Trigger status: PROBLEM
Trigger severity: Warning
Trigger URL: 
Item values:
1. Free disk space on / (percentage) (app-ali-prod-db1:vfs.fs.size[/,pfree]): 12.68 %
2. *UNKNOWN* (*UNKNOWN*:*UNKNOWN*): *UNKNOWN*
3. *UNKNOWN* (*UNKNOWN*:*UNKNOWN*): *UNKNOWN*

接收到告警信息，那就登录到服务器看看怎么回事，什么东西占用了/
分区：

[nock@app-ali-prod-db1 nock]# sudo df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        20G   15G  3.6G  87% /
tmpfs           7.8G     0  7.8G   0% /dev/shm
/dev/vdb        394G  124G  251G  34% /data
[nock@app-ali-prod-db1 nock]# sudo df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        20G   19G  511M  98% /
tmpfs           7.8G     0  7.8G   0% /dev/shm
/dev/vdb        394G  124G  251G  34% /data
[nock@app-ali-prod-db1 nock]# sudo df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        20G   19G  359M  99% /
tmpfs           7.8G     0  7.8G   0% /dev/shm
/dev/vdb        394G  124G  251G  34% /data
[nock@app-ali-prod-db1 nock]# sudo df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        20G   19G   96M 100% /
tmpfs           7.8G     0  7.8G   0% /dev/shm
/dev/vdb        394G  124G  251G  34% /data
[nock@app-ali-prod-db1 nock]# sudo df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        20G   19G   67M 100% /
tmpfs           7.8G     0  7.8G   0% /dev/shm
/dev/vdb        394G  124G  251G  34% /data

如上所示/
分区的使用率还在一直变大，那就看看具体哪个分区下文件占用的：

[nock@app-ali-prod-db1 ~]# sudo du -csh /*
6.2M  /bin
90M /boot
124G  /data
160K  /dev
29M /etc
20K /home
234M  /lib
23M /lib64
16K /lost+found
4.0K  /media
4.0K  /mnt
68K /opt
0 /proc
316K  /root
16M /sbin
4.0K  /selinux
4.0K  /srv
513M  /swapfile
0 /sys
320K  /tmp
883M  /usr
218M  /var
126G  总用量

但是根据du
命令统计，不对啊，那是什么占用了呢，只好去看看最近操作了什么。

原因分析

原来是因为最近在做MYSQL表优化的操作，既然是操作MYSQL引起的，那我就自然让我想起了MYSQL临时表了，那我们就先看看MYSQL产生临时表目录，线上怎么设置的:

mysql> show variables like 'tmpdir';
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| tmpdir        | /tmp                 |
+---------------+----------------------+
1 row in set (0.00 sec)

我们再来看看系统情况吧，看看临时文件情况：

[nock@app-ali-prod-db1 ~]# sudo lsof |egrep  'tmp|deleted'
mysqld_sa  9847   root    2u      CHR              136,1         0t0          4 /dev/pts/1 (deleted)
mysqld    11002  mysql    5u      REG              252,1           0     663996 /tmp/ibqhlTQx (deleted)
mysqld    11002  mysql    6u      REG              252,1           0     663997 /tmp/ibw9oKol (deleted)
mysqld    11002  mysql    7u      REG              252,1           0     663998 /tmp/ibdyLBW8 (deleted)
mysqld    11002  mysql    8u      REG              252,1           0     663999 /tmp/ib9Z2RkL (deleted)
mysqld    11002  mysql   12u      REG              252,1           0     664000 /tmp/ibvDAytA (deleted)
mysqld    11002  mysql   60u      REG              252,1  8092909568     661724 /tmp/ibzvPPU1 (deleted)
mysqld    11002  mysql   62u      REG              252,1           0     661734 /tmp/ibam4uXx (deleted)
mysqld    11002  mysql   65u      REG              252,1  4447010816     661735 /tmp/ib9ZmD63 (deleted)
mysqld    11002  mysql   78u      REG              252,1  2729443328     661736 /tmp/ibDgZ9mA (deleted)
mysqld    11002  mysql   88u      REG              252,1  1364197376     661737 /tmp/ibKU9e56 (deleted)

解决问题

原来如此，那只能设置一下tpmdir
参数来改变临时表生成目录了：

[nock@app-ali-prod-db1 ~]# sudo mkdir -pv /data/tmp/mysql
mysql> set global tmpdir='/data/tmp/mysql';
ERROR 1238 (HY000): Variable 'tmpdir' is a read only variable

通过提示可知tmpdir
参数只是一个只读变量不能动态修改指定，那看来只能通过在配置文件my.cnf
的mysqld下添加tmpdir配置了，配置如下：

[mysqld]
tmpdir = /data/tmp/mysql

在配置文件my.cnf
的[mysqld]
下添加tmpdir = /data/tmp/mysql

重载MYSQL生效：

/etc/init.d/mysqld reload

查看效果：

mysql> show variables like 'tmpdir';
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| tmpdir        | /data/tmp/mysql |
+---------------+----------------------+
1 row in set (0.00 sec)

如此看出已经生效，然后线上再也没有出现如此情况，问题得到解决。

总结教训

所以以后大家一定要谨记线上MYSQL一定要设置好tmpdir
参数的配置，不要等到发生问题了再来补救；这里对于MYSQL为什么会生成临时表，什么情况下会生成临时表，后面的文章我们再介绍。