Whether it’s because your /tmp partition is too small, or because you are trying to gain more speed in some way, switching the MySQL location for all of its temporary files can help you in a few ways. The directions below will allow you to do so, without causing any problems.
Step 1: Copy your existing /etc/my.cnf file to make a backup
cp /etc/my.cnf{,.back-`date +%Y%m%d`}
Step 2: Create your new directory, and set the correct permissions
mkdir /home/mysqltmpdir
chmod 1777 /home/mysqltmpdir
Step 3: Open your /etc/my.cnf file
nano /etc/my.cnf
Step 4: Add below line under the [mysqld] section and save the file
tmpdir=$NEWLOCATION
for example:
tmpdir=/home/mysqltmpdir
If you are using Nano to edit your file:
To save file press: Ctrl + O
To exit from nano editor press: Ctrl + X
Step 5: 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
Congratulations!
You have now changed your MySQL tmpdir. If you have any problems with starting MySQL with the new location set, you can just remove the new line from your /etc/my.cnf, and it should start. If you need to revert to your previous configuration for any reason, you can copy the backup file you made earlier over the top of your existing /etc/my.cnf and you should be set.
