How to change the MySQL tmpdir

Unmanaged dedicated server and Cloud VPS users, who are familiar with making root-level configuration changes, can adjust the MySQL /tmpdir to a new location by following these steps:

1. Login as root to your server via SSH.
2. Open the /etc/my.cnf file in your preferred editor, eg:

nano /etc/my.cnf

3. Locate the [mysqld] section and add the following line beneath it:

tmpdir=/home/mysqltmp

In this example we're using /home/mysqltmp as the new location. Replace with your desired file path here.
4. Save changes (Ctrl + O) and exit from your editor (if using nano press Ctrl + X)
5. Now create the directory specified above. Continuing with our example:

mkdir /home/mysqltmp

chmod 1777 /home/mysqltmp

6. Restart mysql via WHM or use the command:

/etc/init.d/mysql restart

7. Finally re-connect to the server terminal to check that the change has been effective, use this SSH command:

mysqladmin var | grep tmpdir

8. A successful response looks something like:

| slave_load_tmpdir                       | /home/mysqltmp
| tmpdir                                  | /home/mysqltmp