Moving /tmp to a different mounted drive

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:

/etc/mysql/conf.d/local.cnf

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.