Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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/ ?

share|improve this question

migrated from stackoverflow.com Sep 25 '12 at 5:44

This question came from our site for professional and enthusiast programmers.

    
this question is out of scope of stackoverflow, would be better be asked on serverfault. – spencer7593 Aug 30 '12 at 21:33
    
sure spencer...... – Uday Aug 30 '12 at 21:50
    
There are thousands of mysql questions on stackoverflow. everybody feel it like they can get answers for them over here so they posted. please do negative marking if it is disturbing the purpose of the site. not if u have free time. – Uday Aug 30 '12 at 22:04
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.

share|improve this answer

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

share|improve this answer
    
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 '15 at 17:37
    
Okay jenny, editing the stuff. – AnkitM Dec 5 '15 at 17:45
    
Thanks AnkitM, turned a downvote into an upvote! – Jenny D Dec 5 '15 at 19:29

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.

share|improve this answer
    
/tmp is not on a tmpfs on all systems, or even a majority of them. – Michael Hampton Nov 4 '13 at 10:50

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.