3

Recently on one of the prodcution servers we have seen the replication being stopped due to the space issues on the /tmp drive which is having less space allocated.

I would like to move the tmpdir from /tmp[default] to /var/lib/mysql/tmp

Below are what I guess I should do.

  1. stop mysql
  2. create directory called tmp in the location /var/lib/mysql/
  3. add tmpdir = /var/lib/mysql/tmp/
  4. start mysql

Is there anything to be taken care of for the permission level of /var/lib/mysql/tmp/ ?

0

4 Answers 4

6
Below are what I guess I should do.

 1. stop mysql
 2. create directory called `tmp` in the location `/var/lib/mysql/`
 3. add `tmpdir = /var/lib/mysql/tmp/`
 4. start mysql

You can combine 1. to 4. by restarting MySQL after changing.

About 2., you should put the tmpdir outside of the datadir.

Is there anything to be taken care of for the permission level of /var/lib/mysql/tmp/?

chown mysql:mysql /path/to/tmp/dir is enough.

1
  • As relevant as ever. Thanks!
    – wnull
    Aug 23, 2023 at 6:23
1

Visit this blog for detailed steps with apparmor and my.cnf configurations, http://sharadchhetri.com/2014/12/27/change-path-tmpdir-mysql-server-ubuntu/

First check your tmp dir folder

SHOW VARIABLES LIKE 'tmpdir';

Create a backup of my.cnf

sudo cp -pv /etc/mysql/my.cnf /etc/mysql/my.cnf.`date +%F`.bak

Create a new directory to hold the files

sudo mkdir /mysql_tmp_dir

Make mysql user the owner for the directory sudo chown mysql:mysql /mysql_tmp_dir

edit my.cnf

replace,

 sudo vim /etc/mysql/my.cnf
 tmpdir = /tmp 

with

 tmpdir = /mysql_tmp_dir

edit apparmor config for mysqld

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

add lines,

/mysql_tmp_dir r,  
/mysql_tmp_dir rw

restart apparmor and mysql

sudo service apparmor restart
sudo service mysql restart

To check if the changes worked fine,

SHOW VARIABLES LIKE 'tmpdir';

Bingo! And this should display the path for the new tmpdir

2
  • Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference.
    – Jenny D
    Dec 5, 2015 at 17:37
  • Okay jenny, editing the stuff.
    – AnkitM
    Dec 5, 2015 at 17:45
0

This is not recommended. By default, /tmp will be mounted out of tmpfs and mysql will use this for extra large queries etc. Being in tmpfs means IOPS hit RAM instead of your hard drive or SSD. This improves performance and there is no real downside because the data being stored in the tmpdir is suited to volatile storage. If you are maxing out the size of your /tmp, I would recommend you write your queries to be more efficient or enlarge the size of your /tmp through fstab if you have the RAM to spare. If this solution does not work, then feel free to define your own location of tmpdir and point it towards a disk with a larger partition allocation.

1
  • 1
    /tmp is not on a tmpfs on all systems, or even a majority of them. Nov 4, 2013 at 10:50
-2

If you make tmp under /var/lib/mysql, mysqld will think, this is database...

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .