I’ve mapped my MySQL datadir to a different (and bigger) disk with 900GB
[root@web ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/xvda1 40G 24G 14G 64% / tmpfs 7.4G 0 7.4G 0% /dev/shm /dev/xvdb1 886G 658G 184G 79% /mnt/cbsvolume1
The /tmp is located in the smaller 40GB disk
[root@web ~]# df -h /tmp Filesystem Size Used Avail Use% Mounted on /dev/xvda1 40G 24G 14G 64% /
It appears MySQL is using the /tmp directory and because of that I’m running sometimes out of disk space when trying to perform big MySQL operations.
How can I change the
/tmp to also use the bigger disk?
As far as I know, the contents of
/tmp are deleted on reboot.
However, in your case, you could try changing/adding
TMPDIR environment variable in:
Change the TMPDIR environmental variable to a filesystem/directory with more disk space.
Edit the local configuration file
/etc/mysql/conf.d/local.cnf. This file contains your local changes – and will not be overwritten if you upgrade mysql. Note the config files may be stored in a different location – use find to locate the default config file my.cnf. Usually local.cnf would be kept in the same directory.
sudo find / -name my.cnf # look for path of config files
Edit local.cnf e.g.
sudo vi /etc/mysql/conf.d/local.cnf
Add these lines to local.cnf:
[mysqld] tmpdir = /your/newpath
Then re-start mysql.
From the MySQL documentation:
B.5.4.4 Where MySQL Stores Temporary Files
On Unix, MySQL uses the value of the TMPDIR environment variable as
the path name of the directory in which to store temporary files. If
TMPDIR is not set, MySQL uses the system default, which is usually
/tmp, /var/tmp, or /usr/tmp.