This tutorial explains how you can change or move MySQL /tmp directory to a new location on a server / VPS. Login as root to your server via SSH.
Step 1: Open /etc/my.cnf file for editing
nano /etc/my.cnf
Step 2: Add below line under the [mysqld] section and save the file.
tmpdir=/home/mysqltmp
To save file press: Ctrl + O
To exit from nano editor press: Ctrl + X
This will set new location of MySQL tmp to /home/mysqltmp. You may change to any different location.
Step 3: Create new directory
mkdir /home/mysqltmp chmod 1777 /home/mysqltmp
Step 4: Restart MySQL
/etc/init.d/mysql restart
Step 5: Check new location
mysqladmin var | grep tmpdir
This should show following return.
| slave_load_tmpdir | /home/mysqltmp | tmpdir | /home/mysqltmp
Done!!
