7

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?

king_nak
  • 193
  • 1
  • 1
  • 6
  • As the error message already suggests: I quote " 130130 9:59:23 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it ". So you have to run `mysql_upgrade` – Valentin Bajrami Jan 30 '13 at 11:59
  • Did you copy the files while mysql process is running? – Khaled Jan 30 '13 at 12:05
  • Have you verified the directory permissions? Make it even with `/var/lib/mysql`. And how exactly have you copied those files? – fboaventura Jan 30 '13 at 12:43
  • @val0x00ff This is caused by the previous error `Can't find file: './mysql/plugin.frm' (errno: 13)`. However, this file does exist and is readable! – king_nak Jan 30 '13 at 12:59
  • @fboaventura I used `cp -p -r /var/lib/mysql/* /web/dbs/mysql/`. MySQL was not running at that time – king_nak Jan 30 '13 at 13:00
  • What are the permissions/owner on /web & /web/dbs – USD Matt Jan 30 '13 at 13:06
  • @USDMatt I would further and ask also for permissions/owner on `/web/dbs/mysql`. – fboaventura Jan 30 '13 at 13:11
  • The question suggests that the owner/group of /var/dbs/mysql is mysql:mysql although it's not 100% clear (he could be referring to just the contents of that folder). Mode 777 has also been tried on this directory so I don't see that folder being the culprit. I'm more interested in whether the higher directories have had their 'other' permissions locked down (I assume they are not owned by the mysql user). It's either that or MySQL is not using /etc/mysql/my.cnf and the old database files have been removed. – USD Matt Jan 30 '13 at 13:22
  • @king_nak You should check if paths in your my.cnf are pointing correctly to the newly created directory. Check for the sock files etc. – Valentin Bajrami Jan 30 '13 at 13:26
  • Also, check if the datadir is being given as a command line option. On my BSD systems, the datadir is handled by default using /etc/rc.conf and passed as an option in the rc scripts. The MySQL website doesn't seem to say whether command line options override my.cnf, but if they do you may find the startup script is overriding your setting. (If you haven't deleted the old directory yet I would expect MySQL to start though, just using the original set of data files) – USD Matt Jan 30 '13 at 13:34
  • 1
    Looking up the test file error on the net, are you running a Linux system with AppArmor? If so, you may need to edit /etc/apparmor.d/usr.sbin.mysqld and add entries for the new path. Apparently you may need to reload the AppArmor profile. Not sure exactly how to do that, may be easiest to reboot. – USD Matt Jan 30 '13 at 15:41
  • @USD Matt I didn't think about AppArmor... Simply solved my problem. Make it an answer and I will accept it... Thanks! – king_nak Jan 30 '13 at 18:40

2 Answers2

12

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.

USD Matt
  • 5,321
  • 14
  • 23
  • More info on AppArmor can be found here: https://help.ubuntu.com/10.04/serverguide/apparmor.html – thealexbaron Sep 18 '14 at 23:00
  • The initial mysql datadir contains an `mysql` folder inside it, in my case I had to add the new path to `datadir` but also the `mysql` folder inside it, otherwise the startup log would complain about not finding `./mysql/host.frm` – adrianTNT Jun 11 '19 at 09:21
1

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.

Zelocox
  • 11
  • 1
  • Disabling SELinux is not a great solution. However, explaining how to allow the required access under SELinux would be a great addition to this question. – Paul Gear Mar 27 '18 at 22:05