Blog/ How to change location where MySQL stores data on disk

By neerav.mehta Fri, 09/12/2014 - 19:27 Comments

If you install MySQL via apt-get on Ubuntu, it comes with pre-defined setting. One of these settings is datadir, which specific where MySQL stores all its data on disk. By default, it is set to /var/lib/mysql. Sometimes we need to change this location. As an example, we want to store MySQL data in /data/mysql. Here are the steps to accomplish that:

1) Stop MySQL server.

sudo service mysql stop

2) Create the new /data directory.

sudo mkdir /data

3) Move the existing MySQL directory to /data.

sudo mv /var/lib/mysql /data/

4) Open /etc/mysql/my.cnf file and change the value of datadir ftom /var/lib/mysql to /data/mysql.

5) If you have AppArmor installed, then there are two more steps. AppArmor is a Mandatory Access Control (MAC) utility to confine programs to a limited set of resources. So even if you change MySQL to use /data/mysql directory, AppArmor will not allow MySQL to use it. We need to change AppArmor settings. Open /etc/apparmor.d/usr.sbin.mysqld and remove the following lines:

  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,

Add the following lines instead.

  /data/mysql/ r,
  /data/mysql/** rwk,

6) Restart AppArmor.

sudo service apparmor restart

7) Restart MySQL

sudo service mysql restart

Now MySQL should use the new directory /data/mysql to store all of its data.

Ready to get Started?