Moving MySQL in Ubuntu

Ubuntu 8.04 and Ubuntu 8.10 use AppArmor. And AppArmor watches MySQL.

[2008-10-10]

This has caught me out twice -- the second time because I forgot about the first time.

I got errors like:


InnoDB: Unable to lock ./ibdata1, error: 13

and


[Warning] Can't create test file /usr/local/lib/mysql/myhostname.lower-test

If it's caught you too, I hope I can help.

When I install or upgrade an Ubuntu desktop, I put all the stuff particular to that desktop in a separate partition mounted at /usr/local/. We develop a lot with Apache and MySQL, so I move /var/www to /usr/local/www and /var/lib/mysql to /usr/local/lib/mysql. Here are the steps to make sure MySQL can write to its files.


$ sudo /etc/init.d/mysql stop
$ sudo cp -a /var/lib/mysql /usr/local/lib/mysql

(I copy instead of moving because I like to be able to go back to how things were before. I'm paranoid like that.)


$ sudo vim /etc/mysql/my.cnf

And change the datadir setting to this:


#datadir       = /var/lib/mysql
datadir         = /usr/local/lib/mysql

Save and quit.

Now comes the bit I forgot about. AppArmor makes sure that users referring to themselves as mysql can only mess about in MySQL's directories. It's for your own good. And it's not something you'd need to worry about, unless you moved MySQL's directory. Which is exactly what I've just done, so I have to tell AppArmor.


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

Copy the lines that refer to /var/lib/mysql and change them to /usr/local/lib/mysql, like so:


  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
  /usr/local/lib/mysql/ r,
  /usr/local/lib/mysql/** rwk,

Do the same with /etc/apparmor.d/abstractions/mysql


   /var/lib/mysql/mysql.sock rw,
   /usr/local/lib/mysql/mysql.sock rw,

And you're done. Restart AppArmor, and start MySQL. Check your logs and revel in the lack of error messages.

☙