Changing the data directory for MySQL in Ubuntu

There have been several occasions where I wanted to change my MySQL data directory. First it was to run multiple instances of MySQL, which I gave up on. Then I bought an external drive to save my development environment, including my source code and the MySQL data directory, so I could develop from any machine at work or home. Both times I kept running into problems getting such a seemingly simple configuration change to work properly.

I thought it would be rather easy. One way to do it is by changing the MySQL configuration in /etc/mysql/my.cnf, but I wanted to make it easier for other developers to use the local MySQL database. So instead I created a directory for MySQL data on my external drive, then issued the following commands to create a symbolic link from /var/lib/mysql to that location:

cd /var/lib
sudo mv mysql mysql2
sudo ln -s /mnt/jbarton/mysql /var/lib/mysql

Of course, I was greeted with several nasty error messages any time I tried to start MySQL or initialize the new data directory (either directly or through the symbolic link). Here are some of the complaints I found in the logs:

[Warning] Can't create test file /srv/mysql/hostname.lower-test

kernel: [ 82.391726] audit(1210139515.249:2): type=1503 operation="inode_create" requested_mask="w::" denied_mask="w::" name="/var/lib/mysql2/hostname-linux.lower-test" pid=5888 profile="/usr/sbin/mysqld" namespace="default"

InnoDB: Error: unable to create temporary file; errno: 13

kernel: [ 82.508111] audit(1210139515.369:5): type=1503 operation="inode_mknod" requested_mask="w::" denied_mask="w::" name="/var/run/mysqld/mysqld.sock" pid=5888 profile="/usr/sbin/mysqld" namespace="default"

[ERROR] Can't start server : Bind on unix socket: Permission denied

[ERROR] Do you already have another mysqld server running on socket: /var/run/mysqld/mysqld.sock ?

[ERROR] Aborting

[Note] /usr/sbin/mysqld: Shutdown complete

mysqld_safe[5892]: ended

After several failed searches on Google to figure out the problem, I finally found the solution. The culprit is AppArmor, a security framework now default in Ubuntu that is supposed to be easy to use. I would say that it is a big pain in the ass, and not very user-friendly. There was nothing in my syslog around the nasty MySQL and kernel error messages that even hinted at AppArmor.

Once you know AppArmor is the problem, the fix is very easy. You simply edit /etc/apparmor.d/usr.sbin.mysqld. Underneath the two lines authorizing the default MySQL data directories, add two more with your custom directory. Make sure you have a trailing / on the directory name, otherwise it will not work (I had this problem at first). After this change, restart AppArmor:

sudo invoke-rc.d apparmor restart

Now you should be able to set up the new directory as a MySQL data directory and initialize it using mysql_install_db. For more information on this topic, check out the following links:

AppArmor - Ubuntu Wiki
The Brainwrecked Tech had the same problem
Ubuntu bug #201799
A thread on the Ubuntu forums with the solution