Changing the tmp folder of mysql

Changing the tmp folder of mysql

You should edit your my.cnf

tmpdir = /whatewer/you/want

and after that restart mysql

P.S. Dont forget give write permissions to /whatewer/you/want for mysql user

Here is an example to move the mysqld tmpdir from /tmp to /run/mysqld which already exists on Ubuntu 13.04 and is a tmpfs (memory/ram):

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

Add:

[mysqld]
tmpdir = /run/mysqld

Then:

sudo service mysql restart

Verify:

SHOW VARIABLES LIKE tmpdir;

==================================================================

If you get an error on MySQL restart, you may have AppArmor enabled:

sudo vim /etc/apparmor.d/local/usr.sbin.mysqld

Add:

# Site-specific additions and overrides for usr.sbin.mysqld.
# For more details, please see /etc/apparmor.d/local/README.
/run/mysqld/ r,
/run/mysqld/** rwk,

Then:

sudo service apparmor reload 

sources: http://2bits.com/articles/reduce-your-servers-resource-usage-moving-mysql-temporary-directory-ram-disk.html, https://blogs.oracle.com/jsmyth/entry/apparmor_and_mysql

Changing the tmp folder of mysql

This is answered in the documentation:

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.

On Windows, Netware and OS2, MySQL checks in order the values of the
TMPDIR, TEMP, and TMP environment variables. For the first one found
to be set, MySQL uses it and does not check those remaining. If none
of TMPDIR, TEMP, or TMP are set, MySQL uses the Windows system
default, which is usually C:windowstemp.