Home / server / Questions / 473789
Accepted
king_nak
Asked: 2013-01-31 03:47:43 +0800 CST

mysql doesn't start after relocating data dir

  • 772

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?

linux

2 Answers

  1. 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.

    • 12

  2. In case if there is no AppArmor search for selinux because on most of the rpm based linux you will see selinux. Just disable it.

    #setenforce 0
#getenforce

    Getenforce command will give you permssive.

    • 1