• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
sharadchhetri

sharadchhetri

Tutorials On Linux, Unix & Open Source

  • Home
  • Linux Commands
  • Resources
    • Learn Linux
  • My WordPress plugins

how to change path of tmpdir in MySQL Server On Ubuntu

December 27, 2014 by Sharad Chhetri

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 –

MySQL TMPDIR

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.
tmpdir-mysql2

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

Share this:

  • Twitter
  • Facebook
  • More
  • Print
  • Email
  • LinkedIn
  • Reddit
  • Tumblr
  • Pinterest
  • Pocket
  • Telegram
  • WhatsApp
  • Mastodon

Related posts:

  1. How to change mysql default data directory in Ubuntu
  2. Find mysql commands history without mysql server login
  3. Change mysql default port number in linux
  4. How To Find Absolute Full Path Of Command On Linux / Unix : which command
  5. How to install MySQL Server 5.7 on Ubuntu 14.04 LTS
  6. Change the default port number of ssh server
  7. Change telnet server port number on CentOS 7 / RHEL 7
  8. How to install mysql server in RedHat and CentOS
  9. Install MySQL Server 5.6 in CentOS 6.x and Red Hat 6.x Linux
  10. Set delay replication to MySQL slave server

Filed Under: Linux Tagged With: mysql

Reader Interactions

Comments

  1. Rudra says

    December 28, 2014 at 7:37 am

    HI Sharad,

    Nice tutorial again. I was trying to understand, you have changed the temporary directory path /run/shm/mysql. Also you have changed its ownership to mysql. But /run/shm basically resides in the system RAM (as per my understanding). If this is true, then if the server reboots, this path will be lost… so don’t we need to put a script in /etc/rc.local to create that temporary directory and change the ownership..??? Or just updating the apparmor is sufficient…????

    • sharad chhetri says

      December 28, 2014 at 3:19 pm

      Thankyou Rudra,

      You raised good points. The /run/shm uses the tmpfs filesystem which means the files/dir in /run/shm will not be present when system restarted. /run/shm or /dev/shm is a shared memory in RAM DISK.

      I have already written about rc.local to be edited. Due to cache, some of the section was not displayed properly when I later updated rc.local section. It is fixed now.

      Regards
      Sharad

Primary Sidebar

Our Social Media Presence

  • Facebook
  • GitHub
  • Twitter

Linux Command

What is Linux Internal And External Command

Linux Basic Commands With Examples For Every Beginner

tr command to convert lines to space , tab and vertical tab

smbpasswd command not found on CentOS 7 and RHEL 7

Solution : semanage command not found

Unix / Linux : How to print duplicate lines from file

More Posts from this Category

You Might Like These Articles!

simplecodesyntax wordpress plugin

SimpleCodeSyntax : My Another WordPress Plugin

Install Nginx

How To Install Nginx On Ubuntu 22.04 LTS

Install Latest Git package in Ubuntu Operating System

How To Always Install Latest Git Package In Ubuntu Operating System

Bash script for installing VirtualBox on Ubuntu 22.04 LTS Desktop

Install VirtualBox On Ubuntu 22.04 LTS Desktop (Bash Script)

libfuse

dlopen(): error loading libfuse.so.2 – Got Error On Ubuntu

Failed to open/create the internal network

VirtualBox Error: Failed to open/create the internal network

Always Useful Tips And Tricks

Password prompt in single user mode is not secure : CentOS/Red Hat

4 Different commands to find system uptime in linux

Non interactive ,without typing password do ssh to Server : By sshpass

Error installing rails

rsync all files,hidden files,symlinks,hardlinks to remotes Linux Server

How to hide php version information in header

Edit the crontab without using crontab -e

Explore 90+ Article On "Linux Tips And Tricks"

Copyright © 2023 ยท
The material in this site cannot be republished either online or offline, without our permission.
Proudly Blogging From Bharat.

  • Contact
  • About Me
  • My WordPress plugins
  • Privacy Policy