9

I was getting the following error when trying to change the data directory in ubuntu server 10.04.

100809 19:33:00 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
100809 19:33:00 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
100809 19:33:00  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.
3
  • 2
    ...and your question is? Note that if you only move the contents of folders, and no files, you will lose all InnoDB table content.
    – bobince
    Aug 10, 2010 at 8:50
  • there is no question/spoon. Just figured this might be interesting to people around here. Good point about the InnoDB table content!
    – Norling Jr.
    Aug 10, 2010 at 8:55
  • 2
    If you want to ask and answer your own question that is fine, but take the time to actually ask a question and provide your answer as an answer.
    – Zoredache
    Aug 10, 2010 at 18:43

1 Answer 1

8

After some general confusion about permissions the OP realized that the problem wasn't that he didn't have permissions and paths rights but that AppArmor was preventing MySQL from reading and writing to the new location.

This is his solution:

First stop MySQL so nothing weird happens while you're fiddling:

$ sudo stop mysql

Then move all the database directories to their new home:

$ sudo mv /var/lib/mysql/<all folders> /new-mysql-dir/

Don't move the files, they will be generated by mysql, just move the folders (which are the databases).

Then politely ask AppArmor to allow mysql to use the new folder:

$ sudo vim /etc/apparmor.d/usr.sbin.mysqld
  >> add lines
     /new-mysql-dir/ r,
     /new-mysql-dir/** rwk,

Then tell mysql that the datadir has moved:

$ sudo vim /etc/mysql/my.cnf 
  >> change the line
     datadir=/var/lib/mysql
  >> to
     datadir=/my-new-db-dir/

NOTE: Depending on your database setup you might need to change innodb-data-home-dir etc. as well.

Then restart AppArmor to read the new settings:

$ sudo /etc/init.d/apparmor restart

And start up MySQL again using the new datadir:

$ sudo start mysql
4
  • 2
    you could keep the database up and running by linking the files to their new location, instead of moving them... unless of course the two datadir locations are on separate partitions.
    – cpbills
    Jun 5, 2011 at 22:06
  • The instruction to move only directories is not accurate in the case you're using innodb. In that case you'll have to move the innodb data and log files aswell (ibdata*, ib_logfile*).
    – hillel
    Mar 13, 2012 at 13:45
  • A note for others as careless as me. You have to have the trailing slashes in the path in the AppArmor config /new-mysql-dir/ r or else it will still be denied. Basic Linux stuff but I can never get the trailing slashes correct.
    – Jeff
    May 24, 2018 at 19:59
  • I was still getting denied by AppArmor. This thread helped make it over that final hurdle.
    – Jeff
    May 24, 2018 at 20:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .