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:
- Create the new directory
- Stopping the MySQL service
- Copying the files to the new location
- Editing /etc/mysql/my.cnf
- Editing the AppArmor profile
- 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
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”
# * 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
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:
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
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)