Moving the MySQL Data Directory on Ubuntu

If you've ever setup a web-app on a Linux server, chances are that you've setup a MySQL server at least once. On the off chance that you haven't, we'll do a quick crash course now:

apt-get install mysql-server

It might seem like I'm being a bit sarcastic. I am, though not by much. I suspect that most of the instances that I've seem running have had no configuration past what comes stock. For a small site, this might be acceptable. For anything of any substance, however, we need a bit more thought regarding our configuration. To that end, most of the articles I've seen provide recommendations about increasing the key_buffer, the query_cache_size, or even to swapping the underlying engine between MyISAM or InnoDB. Some of the posts go as far as to suggest rewriting the schema for the data ¹. While all of these options are likely to provide some speed increase, that list isn't quite complete: we can also move the data directory to a separate drive.

Unless extra attention was paid to the OS setup, /var/lib/mysql would likely be on the same physical drive as the OS and the swap partition. Thus, I/O for our database likely has contention with every other application on our system. Simply reducing the competing I/O requests should give us an increase in performance. ²

At this point, I'm going to assume that a second drive is already installed on the system and that it is mounted at /mnt/data. We'll start by opening the MySQL configuration file for editing.

sudo vim /etc/mysql/my.cnf

Find the line reading datadir = /var/lib/mysql and change it to be a subfolder of our external drive: datadir = /mnt/data/mysql. Now we'll need to update AppArmor so that it knows to allow the mysqld process to access the new location.

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

Replace references to /var/lib with /mnt/data.

At this point we need to stop the database, move the data from the old data-store, and start the database again.

sudo service mysql stop
sudo mv /var/lib/mysql /mnt/data
sudo service mysql start

All of our applications should now continue working the way they were, so no more configuration should be needed.

Update (July 2, 2014)

The line “Replace references to /var/lib with /mnt/data.” original read “This file should have four references to /var/lib/mysql. Change them all to /mnt/data/mysql.”

Footnote