MySQL datadir on different partition

This writeup will walk you through installing MySQL with the data directory on a separate partition. Although a new install is pretty straightforward, we ran into some quirks when trying to move the data directory on an existing installation. For this tutorial, I’ll be using an otherwise-fresh Ubuntu 14.04 install with MySQL already installed.

The default MySQL data directory (where the database files are stored) is in /var/lib/mysql. I’ll be moving this to a disk mounted at /mnt/SAN for the purpose of freeing up disk space on the VM. (I’m not going to discuss the benefits and drawbacks of doing so, as that’s beyond the scope of this article. I assume that if you’re here, you’ve already determined a need to mount the data directory on another filesystem.)

There are a couple of steps involved in this:

  1. Create the new directory
  2. Stopping the MySQL service
  3. Copying the files to the new location
  4. Editing /etc/mysql/my.cnf
  5. Editing the AppArmor profile
  6. Reloading the AppArmor profile and restarting MySQL

The new data directory will be located at /mnt/SAN/mysql, which will have to be created. When creating this directory, ensure it’s owned by the mysql group and user, and set permissions to 700.

sudo mkdir -p /mnt/SAN/mysql
sudo chown mysql:mysql /mnt/SAN/mysql
sudo chmod 700 /mnt/SAN/mysql

Next, stop the MySQL service:

sudo service mysql stop

or

sudo /etc/init.d/mysql stop

Once you’ve set up the new data directory on your mounted partition, copy the files over:

cp -dpR /var/lib/mysql/* /mnt/SAN/mysql/

The -dpR flags do the following:

-d prevents symlinks from being followed
-p preserves ownership, timestamps and permissions
-R copies recursively

Once the files have copied, ensure the permissions match those of the original data directory (/var/lib/mysql/). Make sure the new mysql directory has the correct ownership and permissions as well!

At this point, a directory listing of /mnt/SAN/mysql should match /var/lib/mysql exactly.

Now, we’ll edit the MySQL config file, located at /etc/mysql/my.cnf. I recommend backing this file up first!

sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.bak
sudo emacs /etc/mysql/my.cnf

Look for the “datadir” param, which should be set to the default value of “/var/lib/mysql”

[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

Change this value to your new mysql data directory (/mnt/SAN/mysql) and save the file.

If you try to start the MySQL service now, it’ll likely fail because AppArmor sees it accessing a directory it’s not supposed to. Dmesg will show errors like this:

init: mysql main process ended, respawning
 init: mysql post-start process (14005) terminated with status 1
 apparmor="STATUS" operation="profile_replace" profile="unconfined" name="/usr/sbin/mysqld" pid=14020 comm="apparmor_parser"
 init: mysql main process (14032) terminated with status 1
 init: mysql respawning too fast, stopped

In order to correct this, we’ll have to tell AppArmor to allow mysql to read/write to the new data directory. Open up the MySQL AppArmor profile:

sudo emacs /etc/apparmor.d/usr.sbin.mysql

Comment out the lines pertaining to the old data directory, and add the new data directory to the AppArmor profile:

...
#/var/lib/mysql/ r,
#/var/lib/mysql/** rwk,
/mnt/SAN/mysql/ r,
/mnt/SAN/mysql/** rwk,
...

Once this is done, reload the AppArmor profile:

sudo apparmor_parser -r /etc/apparmor.d/usr.sbin.mysql

If all the permissions are correct, the mysql service should now start:
sudo service mysql start

or

sudo /etc/init.d/mysql start

If you’re still running into issues, make sure that:

  • The new data directory has the correct permissions
  • The AppArmor profile is correct
  • You’ve started the mysql service (mysqld)
MySQL datadir on different partition

Leave a Reply