Sunday, May 15, 2011

Moving the Mysql Data Directory? Beware of AppArmor

Recently, my 8 year old linux server I was running mysql on died. My new computer is more than capable of running mysql in the background, but I didn't want to mess with moving everything over. Now I'm forced to. It's extremely easy on Ubuntu to install mysql. However, I run my operating system and all development related applications off of a 64GB SSD. There isn't room, or a need to run it on the SSD. I have a second platter based hard drive as well. It should be fairly straightforward to store MySql data there. Most instructions for doing so will tell you to do the following:
  1. Copy the current data directory to your new partition. (Current directory is usually /var/lib/mysql on Ubuntu)
  2. Make sure it is still owned by the mysql user. 'chown -R mysql:mysql newdatadir'
  3. Change the mysql configuration to point to the new directory. Inside of my.cnf is a value datadir, which is what needs to be changed to the new directory.
Sounds easy, right? It is, for the most part. If you try these steps on ubuntu, it won't work. MySql won't start up. If you're like me, the first thing you'll look is syslog, where you'll find something like:

[1064644.473299] type=1400 audit(1305502269.192:364):
apparmor="DENIED" operation="open" parent=1 profile="/usr/sbin/mysqld"
name="/media/data1/mysql-data/" pid=24578 comm="mysqld" requested_mask="r"
denied_mask="r"fsuid=1001 ouid=1001
Your first instinct is to check the permissions, but they're all fine. So, what's going on? AppArmor is preventing access. If you type 'ls -la /etc/apparmor.d' you'll see the following:
drwxr-xr-x   8 root root  4096 2011-05-15 19:18 ./
drwxr-xr-x 141 root root 12288 2011-05-15 18:20 ../
drwxr-xr-x 3 root root 4096 2011-04-11 18:38 abstractions/
drwxr-xr-x 2 root root 4096 2011-05-15 17:38 cache/
drwxr-xr-x 2 root root 4096 2010-10-07 11:06 disable/
drwxr-xr-x 2 root root 4096 2010-08-06 23:19 force-complain/
-rw-r--r-- 1 root root 986 2010-09-13 03:07 gdm-guest-session
drwxr-xr-x 2 root root 4096 2011-05-15 17:38 local/
-rw-r--r-- 1 root root 2052 2010-08-06 23:18 sbin.dhclient3
drwxr-xr-x 3 root root 4096 2011-04-11 18:38 tunables/
-rw-r--r-- 1 root root 2052 2010-09-27 17:58 usr.bin.evince
-rw-r--r-- 1 root root 4006 2011-04-29 04:00 usr.bin.firefox
-rw-r--r-- 1 root root 4158 2010-10-01 04:58 usr.sbin.cupsd
-rw-r--r-- 1 root root 989 2010-11-10 00:51 usr.sbin.mysqld
-rw-r--r-- 1 root root 1172 2010-08-06 12:45 usr.sbin.tcpdump
If you open up usr.sbin.mysqld, and add the new directory to the list, your problems will be solved. Something like:

 /mysql-data/ r,
/mysql-data/** rwk,

6 comments:

Serge said...

Hey !
I was in the exact same situation than you.
Thank you very much for this piece of information, i'm pretty unaware of apparmor and wasn't aware of this configuration (i usually put all my application datas files on a separate partition, including the mysql datas).

Lucas Ward said...

Glad I could help.

johank said...

I had the same problem and it boiled down to one silly mistake. The directory where I mounted the disk to hold the MySQL data files didn't have the right permissions. Making it group+world readabe did the trick. I realised my error when starting mysqld "by hand" and actually got to see MySQL complain. With apparmor, I got no logs from MySQL...

Unknown said...

I had this problem and wouldn't have been able to solve it without this post, many thanks.

Chris Caldwell said...

would NEVER have found this. thank you thank you.

Sandip Shah said...

Thanks for the instructions.

You may just want to add that one needs to restart the apparmor service before MySQL will load.

SS