Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Changing the tmp folder of mysql

Tags:

mysql

tmp

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?

like image 404
Maxim Dsouza Avatar asked Aug 16 '12 15:08

Maxim Dsouza


People also ask

Where does MySQL store temp files?

5 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 .

Can I delete tmp folder?

~TMP is safe to delete and all other similar Temp files and folders even the %temp% folder itself. Just keep in mind that is a temp file/folder is a big one, if is most probably a Windows Update or a system restore temp file that is created after you perform a major update from your PC.


2 Answers

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

like image 138
CyberDem0n Avatar answered Oct 07 '22 21:10

CyberDem0n


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

like image 43
Elijah Lynn Avatar answered Oct 07 '22 21:10

Elijah Lynn