Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

3 Answers 3

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

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.