Changing the tmp folder of mysql

Question

Our Mysql queries use temporary tables which creates temporary files in the process. Currently the files are written to /tmp. How exactly can the path of the temp folder to which mysql writes to be changed?

Dyarchy

Answer 1

You should edit your my.cnf

tmpdir = /whatewer/you/want

and after that restart mysql

P.S. Don't forget give write permissions to /whatewer/you/want for mysql user

Nugatory

Answer 2

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

Bondwoman

Answer 3

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:\windows\temp.

Bucher

The other answers are much better since they explain how to set tmpdir in the MySQL configuration file instead of relying upon environment variables. – Rumormonger

Answer 4

if you dont have apparmor or selinux issues, but still get errorcode 13's:

mysql must be able to access the full path. I.e. all folders must be mysql accessible, not just the one you intend in pointing to.

example, you try using this in your mysql configuration: tmp = /some/folder/on/disk

# will work, as user root:
mkdir -p /some/folder/on/disk
chown -R mysql:mysql /some

# will not work, also as user root:
mkdir -p /some/folder/on/disk
chown -R mysql:mysql /some/folder/on/disk

Prather

Answer 5

This maybe helpful for MySql with AppArmor

stop mysql :

sudo /etc/init.d/mysql stop

Create directory called /somewhere/tmp

Edit Config:

sudo vim /etc/mysql/my.cnf # or perhaps sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

change

tmpdir = /somewhere/tmp/

Then

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

Add

# Allow data dir access
  /somewhere/ r,
  /somewhere/** rwk,

sudo chown -R root:root /somewhere

sudo chmod -R 1777 /somewhere

Restart

 sudo /etc/init.d/apparmor reload

 sudo /etc/init.d/mysql restart

Andra

Answer 6

You can also set the TMPDIR environment variable.

In some situations (Docker in my case) it's more convenient to set an environment variable than to update a config file.

Gittle

Answer 7

Works for 5.7 on centos 8

mkdir /tmp/1 /tmp/1

semanage fcontext -a -t mysqld_db_t "/tmp/1(/.*)?"
                   
restorecon -Rv /tmp/1

semanage fcontext -a -t mysqld_db_t "/tmp/2(/.*)?"

restorecon -Rv /tmp/2

to my.cnf tmpdir=/tmp/1:/tmp/2

sudo service mysql restart

Orbiculate

Answer 8

If you are a MariaDB user, all this above apply, by don't forget to unlock the "home" protection by doing this.

touch /etc/systemd/system/mariadb.service.d/override.conf
nano /etc/systemd/system/mariadb.service.d/override.conf

Inside override.conf put this content and save.

[Service]
ProtectHome=false

Then run the following commands :

systemctl daemon-reload
/scripts/restartsrv_mysql

After restarting mysql, the variables can be checked by :

mysqladmin variables|grep tmp

Colombi