How to change the MySQL tmp location on CentOS

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.