Linux – thesql doesn’t start after relocating data dir

file-permissionslinuxmysql5.1startup

I have a web-server where the default installation of mysql places all its database files in /var/lib/mysql. The partition where /var is mounted has only 2GB of space, so after running in space problems, I decided to relocate mysql's data directory.

My naive approach was to copy the /var/lib/mysql directory completely to /web/dbs/mysql, and change /etc/mysql/my.cnf so that it reads

datadir = /web/dbs/mysql

However, after restarting, I get the following errors in the mysql error log, and the server won't start up.

130130  9:59:23 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
130130  9:59:23 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
130130  9:59:23  InnoDB: Initializing buffer pool, size = 8.0M
130130  9:59:23  InnoDB: Completed initialization of buffer pool
130130  9:59:23  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: 'open'.
InnoDB: Cannot continue operation.

All files and directories belong to mysql:mysql. For testing, I even changed the access rights for /web/dbs/mysql are rwxrwxrwx for now.

And yes, /web/dbs/mysql/mysql/plugin.frm does exist.

What could be the problem here? Am I missing something? Is there a more verbose output log available?

UPDATE:
Some more information:
I retried everything with the following commands:

  • I shut down the server stop mysql
  • I removed the old data rm -r /web/dbs/mysql
  • I copied using cp -p -r /var/lib/mysql/ /web/dbs/
  • I set the datadir in my.cnf to datadir = /web/dbs/mysql
  • I restarted the server. Same error

Privileges:

drwxr-xr-x  4 mysql mysql 34   2013-01-30 15:55 /web/dbs
drwx------ 19 mysql mysql 4096 2013-01-30 15:44 /web/dbs/mysql
drwx------  2 mysql mysql 4096 2012-10-11 11:25 /web/dbs/mysql/mysql
-rw-rw----  1 mysql mysql 8586 2012-08-14 19:15 /web/dbs/mysql/mysql/plugin.frm

When I reset the datadir to datadir = /var/lib/mysql, the server starts without problems.

Tried the following:

root:/# su - mysql
mysql:~$ /usr/sbin/mysqld --verbose
130130 16:01:05 [Warning] Can't create test file /web/dbs/mysql/s15800994.lower-test
130130 16:01:05 [Warning] Can't create test file /web/dbs/mysql/s15800994.lower-test

mysql:~$ touch /web/dbs/mysql/s15800994.lower-test
mysql:~$ ls -l /web/dbs/mysql/s15800994.lower-test
-rw-r--r-- 1 mysql mysql 0 2013-01-30 16:01 /web/dbs/mysql/s15800994.lower-test

So the data directory is set correclty. The mysql user has write access, but the mysql process can't create the files.

What could be wrong?

Best Answer

Adding solution we came to in comments as a full answer for completeness...

Testing with su/sudo showed that while mysqld complained of permission errors, the mysql user could indeed successfully write to the folder, making it clear this was not a file system permission problem. (A useful first step if faced with a similar problem)

Some distributions of Linux (if not all?) now come with AppArmor which limits the files/folders that executables are allowed to access.

The solution in this case was to simply add the new path to the /etc/apparmor.d/usr.sbin.mysqld policy file.

Related Topic