Change MySQL data file directory (Ubuntu 12.10 64-bit)

Since our MySQL database is stored on an individual hard drive, and the database is growing beyond the size limits of the hard drive, a temporary solution is to move it to a larger drive. Another temporary measure is to clear the binary logs as described in a previous post. I suspect the real solution is to use a distributed database (also see the Wikipedia entry), but I don't think our use case is quite ready for that yet. This post is on how to change the MySQL data file directory.

This is using a MySQL 5.5.29 server installed via the apt package manager on Ubuntu 12.10 64-bit (MySQL server version 5.5.29-0ubuntu0.12.10.1-log)

Credit goes to this post and this forum post for the the information.

If you don't already know where on the hard drive your MySQL database files are stored, identify their location, and then stop the server:

#Enter the mysql shell
$ mysql

> SHOW VARIABLES LIKE 'datadir';

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+

#Exit the shell and shut down the server
> exit
$ sudo service mysql stop

Now we want to copy the database over to the new location and retain the previous permissions. I'm choosing to copy in this case instead of moving in case something goes wrong (although I recommend that you should have additional backups up your database for the event of something like drive failure).

sudo cp -p -r /var/lib/mysql /path/to/new/location

Next we want to tell mysql where to look for the data directory on startup. I prefer to modify the my.cnf configuration file to save having to specify it when starting. My text editor of choice is vim.

sudo vim /etc/mysql/my.cnf

Here you want to change the datadir line under the [mysqld] section

#I'm choosing to comment out the old line in case disaster strikes and I need to get back to start.
#datadir     = /var/lib/mysql
datadir      = /path/to/new/location/mysql

Save those edits. We also need to modify the apparmor profile:

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

Add the following near the end before the closing bracket:

#You should see similar directives for your previous data directory, so you can follow their lead (i.e. /var/lib/mysql/ r, and /var/lib/mysql/** rwk,)
/path/to/new/location/mysql/ r,
/path/to/new/location/mysql/** rwk,

There was a report that having lines for the previous data directory would cause problems. Leaving those lines didn't seem to cause any adverse effects for me, but they may need to be removed or commented out in other cases.
Save the edits and then restart apparmor:

sudo /etc/init.d/apparmor restart

If that went well, you can you can start the mysql server and make sure everything is as expected.

sudo service mysql start