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.
splattne
  • 28,508
  • 20
  • 98
  • 148
Norling
  • 121
  • 1
  • 1
  • 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 '10 at 08:50
  • there is no question/spoon. Just figured this might be interesting to people around here. Good point about the InnoDB table content! –  Aug 10 '10 at 08: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 '10 at 18:43

1 Answers1

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
splattne
  • 28,508
  • 20
  • 98
  • 148
  • 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 05 '11 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 '12 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 '18 at 19:59
  • I was still getting denied by AppArmor. [This thread](https://askubuntu.com/questions/916009/mysql-wont-start-because-of-apparmor) helped make it over that final hurdle. – Jeff May 24 '18 at 20:08