I've mapped my MySQL datadir to a different (and bigger) disk with 900GB

[root@web ~]# df -h
    Filesystem      Size  Used Avail Use% Mounted on
    /dev/xvda1       40G   24G   14G  64% /
    tmpfs           7.4G     0  7.4G   0% /dev/shm
    /dev/xvdb1      886G  658G  184G  79% /mnt/cbsvolume1

The /tmp is located in the smaller 40GB disk

[root@web ~]# df -h /tmp
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1       40G   24G   14G  64% /

It appears MySQL is using the /tmp directory and because of that I'm running sometimes out of disk space when trying to perform big MySQL operations.

How can I change the /tmp to also use the bigger disk?

  • It looks to me like your /tmp isn't even located on a disk. – Der Hochstapler Jun 17 '14 at 8:41
  • @OliverSalzburg I have a strong feeling you're right, but not sure what you mean :) Can you elaborate? – Noam Jun 17 '14 at 9:26
  • tmpfs is mounted in volatile memory. Meaning it resides in your RAM, not on your hard drive. That's why the contents are gone after you reboot your machine. That being said, I misread the output you posted in your question. I imagined /tmp to be tmpfs, which isn't the case. – Der Hochstapler Jun 17 '14 at 13:06

As far as I know, the contents of /tmp are deleted on reboot.

However, in your case, you could try changing/adding TMPDIR environment variable in:

  • Did that, df -h /tmp still shows the same thing. How can I verify it 'worked'? – Noam Jun 17 '14 at 9:00
  • Cool. And is this setting temporary till reboot? Can I add this line to some sort of booting script and make it permanent? (btw I also believe /tmp is deleted on reboot, mysql only uses it for temporary tables and such) – Noam Jun 17 '14 at 9:09
  • I actually tried that, but still mysql uses the /tmp from the smaller mount. Also, I don't have a directory /etc/mysql/ might it be in a different place? – Noam Jun 17 '14 at 9:16
  • Read this stackoverflow.com/questions/2482234/… (you can use my.cnf file as well) – Cornelius Jun 17 '14 at 9:19
  • 1
    Yes. I actually see the error I'm getting is: /usr/libexec/mysqld: Can't create/write to file '/mnt/cbsvolume1/newTmpDir/ibvxXVDh' (Errcode: 13) So I guess I need to add premissions to that directory? So I also did chown mysql:mysql /mnt/cbsvolume1/newTmpDir and it seems to be working. Thanks! – Noam Jun 17 '14 at 9:39

Change the TMPDIR environmental variable to a filesystem/directory with more disk space.

Edit the local configuration file /etc/mysql/conf.d/local.cnf. This file contains your local changes - and will not be overwritten if you upgrade mysql. Note the config files may be stored in a different location - use find to locate the default config file my.cnf. Usually local.cnf would be kept in the same directory.

sudo find / -name my.cnf     # look for path of config files

Edit local.cnf e.g. sudo vi /etc/mysql/conf.d/local.cnf

Add these lines to local.cnf:

tmpdir = /your/newpath

Then re-start mysql.

From the MySQL documentation:

B.5.4.4 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 you show best way to change and if should I stop mysql before? – Noam Jun 17 '14 at 8:42
  • See edited answer. You don't need to stop mysql before making the changes. – suspectus Jun 17 '14 at 9:09
  • Thanks. I'm a bit confused as to the difference between this and the export TMPDIR=<path> suggestion. Also, I don't have a /etc/mysql/ directory, might it be located in a different place? – Noam Jun 17 '14 at 9:13
  • I don't recommend using export - unless you plan to start mysql from the same shell that issued the export command. Use find as explained in edited answer. – suspectus Jun 17 '14 at 9:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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