一個mysql /tmp目錄爆滿問題的處理

突然收到zabbix告警，說mysql服務器的/目錄磁盤空間不足。

登錄到服務器，看了下發現100GB的根目錄，居然使用了差不多90GB。這臺服務器上只跑了一個MySQL，應該不是日志未清理等其它原因造成的。

（說明：下面的幾張截圖是后期截的，當時已經有部分SQL跑完，釋放掉部分磁盤空間了）

lsof |grep deleted 發現如下：

可以看到這個臨時文件差不多有40GB。

show processlist; 如下：

上圖看的話，沒有涉及到寫binlog的操作，但是由于單純的select并不會造成/tmp目錄爆滿的情況，所以猜測他這個同一個事務里面之前還有涉及到寫binlog的操作（update、delete等）。

官方的說明：

https://dev.mysql.com/doc/refman/5.6/en/binary-log.html

When a thread that handles the transaction starts, it allocates a buffer of binlog_cache_size to buffer statements. If a statement is bigger than this, the thread opens a temporary file to store the transaction. The temporary file is deleted when the thread ends.

The Binlog_cache_use status variable shows the number of transactions that used this buffer (and possibly a temporary file) for storing statements. TheBinlog_cache_disk_use status variable shows how many of those transactions actually had to use a temporary file. These two variables can be used for tuning binlog_cache_size to a large enough value that avoids the use of temporary files.

The max_binlog_cache_size system variable (default 4GB, which is also the maximum) can be used to restrict the total size used to cache a multiple-statement transaction. If a transaction is larger than this many bytes, it fails and rolls back. The minimum value is 4096.

If you are using the binary log and row based logging, concurrent inserts are converted to normal inserts for CREATE ... SELECT or INSERT ... SELECTstatements. This is done to ensure that you can re-create an exact copy of your tables by applying the log during a backup operation. If you are using statement-based logging, the original statement is written to the log.

當事務開始時，它將緩沖區語句分配一個binlog_cache_size大小的緩沖區（我這里設置的是16777216bytes，即16MB）。 如果一個語句大于此，線程將打開一個臨時文件來存儲事務(默認是存放在/tmp/目錄下）。 當線程結束時，臨時文件會自動被刪除。

上面就是因為事務里面的臨時文件超過16MB了，被放到/tmp目錄下了，但是這個臨時文件實在太大了，導致磁盤空間不足告警了。

解決方法：

等上面的查詢結束后，我們先關閉mysqld。（條件能允許的話，當然是讓查詢自己結束。如果直接kill掉的話，估計回滾也要話挺長時間的）

然后調整mysql的tmpdir到其他更大的磁盤去。

mkdir /bdata/mysql_tmp

chown mysql.mysql /bdata/mysql_tmp -R

chown 1777 -R /bdata/mysql_tmp -R

vim /etc/my.cnf 

[mysqld]

tmpdir = /bdata/mysql_tmp

然后啟動mysql即可

再次執行lsof|grep deleted 可以看到臨時文件的路徑已經改到了/bdata/mysql_tmp目錄下了。