How to configure MySQL to use specific temporary directory to create tmp tables?

Created:

2016-11-16 13:18:04 UTC

Modified:

2017-04-24 11:40:04 UTC

0

Was this article helpful?


Have more questions?

Submit a request

How to configure MySQL to use specific temporary directory to create tmp tables?

Applicable to:

  • Plesk 12.5 for Linux

Symptoms

How to configure MySQL to use specific temporary directory to create 'tmp' tables?

Resolution

  1. Create the 'tmp' directory:

    # mkdir -p /var/mysqltmp
  2. Set permissions:

    # chown mysql:mysql /var/mysqltmp
  3. Determine mysql user id:

    # id mysql

4.Edit /etc/fstab : add the following line, replacing mysql user id 105 and group id 114 with specific ones:

    tmpfs /var/mysqltmp tmpfs rw,gid=105,uid=114,size=10G,nr_inodes=10k,mode=0700 0 0
  1. Mount the new 'tmpfs' partition:

    # mount -a
  2. Change MySQL configuration adding the following line:

    # vi /etc/my.cnf

    tmpdir = /var/mysqltmp
  3. Restart MySQL service:

    # /etc/init.d/mysqld restart

Additional information

MySQLd tmp_dir option

Have more questions? Submit a request
Please sign in to leave a comment.