how to change path of tmpdir in MySQL Server On Ubuntu

The post will explain , how to change the tmpdir in MySQL Server on Ubuntu. In the temporary directory for MySQL, generally temporary tables are stored. We generally change its path to make our mysql server performance better.

Practical Tested On : It will works for other MySQL and Ubuntu version also.Our testing inventory is given below.

MySQL Version : MySQL Server 5.6,5.1
Ubuntu : Ubuntu 12.04,14.04

IMPORTANT NOTE : This method do not apply for MySQL SLAVE server (replication). In slave, slave-load-tmpdir variable is used

If TMPDIR is not set, MySQL uses the system default, which is usually /tmp, /var/tmp, or /usr/tmp. Now here questions comes, see below section –

How to find default tmpdir path in MySQL server

As we stated in above brief introductory section, MYSQL uses default TMPDIR path as /tmp, /var/tmp, or /usr/tmp . Now we have to make sure, which TMPDIR path bydefault using by MySQL Server.
To find the default TMPDIR path for MySQL , login into MySQL Server. And run below given comman in MySQL prompt.

Be ready to give your MySQL root password.

mysql -u root -p

Run the query inside mysql prompt

SHOW VARIABLES LIKE 'tmpdir';

Above query will show the defualt TMPDIR path in MySQL.Here is the screenshot –

Change the TMPDIR path in MySQL

Now change the path of TMPDIR in MySQL. In Ubuntu, by default AppArmor will not allow you to change the TMPDIR path for MySQL hence we have to work on AppArmor also. The service called apparmor available and running on Ubuntu systems.

Edit the my.cnf file on Ubuntu system

First take backup of my.cnf file. In case any problem occur , we can revert back to original settings.

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

We will use the custom path inside /run/shm equivalent to /dev/shm in Ubuntu.
(run command ls -l /run/shm, the output will show symlink with /dev/shm)
You can use your own custom path.

Create directory inside /run/shm

sudo mkdir -p /run/shm/mysql

Change owner and group to mysql (id mysql command help to verify)

sudo chown mysql:mysql /run/shm/mysql

Now we will set the value TMPDIR in my.cnf file. Use your file editor and edit the my.cnf file.

sudo vi /etc/mysql/my.cnf

Search for tmpdir in my.cnf file and set the path of tmpdir. If tmpdir parameter do not exist in my.cnf file, you can set tmpdir path in new line inside [mysqld] section.

[mysqld]
..

tmpdir		= /run/shm/mysql

..

Edit AppArmor :

Now we will allow new tmpdir path for mysql from apparmor. Give the path of new tmpdir as you set in my.cnf in /etc/apparmor.d/usr.sbin.mysqld file.

Edit file /etc/apparmor.d/usr.sbin.mysqld

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

/run/shm/mysql/ r,
/run/shm/mysql/* rw

Note: It should be within { .. } means you can set above the line having curly brackets close }

Edit the rc.local file only if you are using /run/shm for tmpdir
Follow this step only in case you are using /run/shm for tmpdir. If you are using disk drive for tmpdir, you can skip this section.

tmpfs is temporary in the sense that no files will be created on the hard drive. The /run/shm or /dev/shm are shared memory in the form of a RAM disk. Hence, when the system reboot, the tmpdir set in /run/shm will be gone whereas the MySQL service will also be stopped.

To solve this, we will edit rc.local file, write commands of create directory and change group ownership of the directory. Below is the mehtod –

vi /etc/rc.local

mkdir -p /run/shm/mysql
chown mysql:mysql /run/shm/mysql

Restart the apparmor service.

sudo service apparmor restart

Now restart the MySQL service

sudo service mysql restart

Verify the tmpdir path

Login into mysql server

mysql -u root -p

Run same query as we used in starting.

SHOW VARIABLES LIKE 'tmpdir';

Here is our screenshot.

TIP for MySQL optimization :

Using different tmpdir path helps in controlling the use of Disk I/O rate. You will observe mysql better performance and may be some good changes in CPU and memory usage by MySQL process in your system.

NOTE: To spread the load effectively, the tmpdir paths should be located on different physical disks, not different partitions of the same disk.

How to set multiple TMPDIR in mysql

To set multiple tmpdir, we have to follow almost all of the process same.

1. Create new directory for tmpdir
2. Change owner and group of new directory to mysql
3. Allowing new directory in apparmor and restart the apparmor service.
4. Editing the /etc/mysql/my.cnf file and set tmpdir parameter : Only difference is setting tmpdir in my.cnf file. We have to use : (colon) when we add new path in tmpdir parameter.

See below example. Here, we created new directory /opt/mysql_tmppath

tmpdir		= /run/shm/mysql:/opt/mysql_tmppath

Restart the mysql service at the end.

sudo service mysql restart