Changing SQL database location outside of /var/lib/mysql results in permission error

[mobodo]

I'm trying to change the datadir for MySQL, but always end up with permission errors no matter that directory I use, if it is different than /var/lib/mysql. The most simple case of the problem is the following:

rm -rf /var/lib/mysql
mysql_install_db --no-defaults --datadir=/var/lib/mysql
-> works

rm -rf /var/lib/mysql-abc
mysql_install_db --no-defaults --datadir=/var/lib/mysql-abc
-> fails with
080504 19:59:52 [Warning] Can't create test file /var/lib/mysql-abc/maya.lower-test
ERROR: 1005 Can't create table 'db' (errno: 13)

rm -rf /var/lib/mysql/subdir
mysql_install_db --no-defaults --datadir=/var/lib/mysql/subdir
-> works

The fact that I can change the datadir to a subdir but not to another directory suggests that mysql performs a chroot /var/lib/mysql at some point - but I don't know where/why

For these cases, I have removed the /etc/mysql/my.conf

I am root when doing all this, and I have just installed mysql with "apt-get install mysql-client mysql-server"

Anyone has an idea?

[JacobSteelsmith]

Looks like /var/lib/mysql is owned by the mysql user and the group is set to mysql as well.

$ sudo chown -R mysql /var/lib/mysql-abc
$ sudo chgrp -R mysql /var/lib/mysql-abc
$ sudo chmod 755 /var/lib/mysql-abc

[mobodo]

If you look in the commands I gave above, you'll notice that I rm -rf the directory before I create the database, /var/lib/mysql-abc and /var/lib/mysql do not exist prior to the mysql_install_db command - so permissions can't be an issue here.

[vik4]

I have much the same issue. I have /var/lib symlinked to a different disk, and it creates the same issues:

$ ls -ld /var/lib/mysql
drwxr-xr-x 3 mysql mysql 4096 2008-05-07 16:09 /var/lib/mysql

The errors from syslog are:

May  7 16:01:29 vcke-gl-linux mysqld_safe[5927]: started
May  7 16:01:29 vcke-gl-linux kernel: [   77.275620] audit(1210140089.689:2): type=1503 operation="inode_create" requested_mask="w::" denied_mask="w::" name="/mnt/fastdata/var/lib/mysql/vcke-gl-linux.lower-test" pid=5929 profile="/usr/sbin/mysqld" namespace="default"
May  7 16:01:29 vcke-gl-linux mysqld[5931]: 080507 16:01:29 [Warning] Can't create test file /var/lib/mysql/vcke-gl-linux.lower-test
May  7 16:01:29 vcke-gl-linux kernel: [   77.277266] audit(1210140089.689:3): type=1503 operation="inode_create" requested_mask="w::" denied_mask="w::" name="/mnt/fastdata/var/lib/mysql/vcke-gl-linux.lower-test" pid=5929 profile="/usr/sbin/mysqld" namespace="default"
May  7 16:01:29 vcke-gl-linux mysqld[5931]: 080507 16:01:29 [Warning] Can't create test file /var/lib/mysql/vcke-gl-linux.lower-test
May  7 16:01:29 vcke-gl-linux kernel: [   77.398269] audit(1210140089.809:4): type=1503 operation="inode_permission" requested_mask="rw::" denied_mask="rw::" name="/mnt/fastdata/var/lib/mysql/ibdata1" pid=5929 profile="/usr/sbin/mysqld" namespace="default"
May  7 16:01:29 vcke-gl-linux mysqld[5931]: 080507 16:01:29  InnoDB: Operating system error number 13 in a file operation.
May  7 16:01:29 vcke-gl-linux mysqld[5931]: InnoDB: The error means mysqld does not have the access rights to
May  7 16:01:29 vcke-gl-linux mysqld[5931]: InnoDB: the directory.
May  7 16:01:29 vcke-gl-linux mysqld[5931]: InnoDB: File name ./ibdata1
May  7 16:01:29 vcke-gl-linux mysqld[5931]: InnoDB: File operation call: 'open'.
May  7 16:01:29 vcke-gl-linux mysqld[5931]: InnoDB: Cannot continue operation.
May  7 16:01:29 vcke-gl-linux mysqld_safe[5937]: ended

Could it be that mysqld is running as another user?

I couldn't successfully su to mysql to try it from the shell.

vik

[nijaba]

Hello,

There is now an apparmor profile enabled by default for mysql that will prevent mysql to access non standard area of the file system. Putting the mysql profile in complain mode using "sudo aa-complain" as described in the server guide should show you this in the logs.

To fix this, you will need to add /mnt/fastdata/var/lib/mysql (or whatever non standard path you are using) to the list of authorized paths in the apparmor mysql profile you will find in /etc/apparmor.d/.

Hope this helps,
Nick

[excalq]

I'm running Ubuntu 10.04, MySQL 5.0.83.
I was following this great tutorial for installing multiple instances of mysql on the same server, and ran into this issue.

I was able to resolve it by running:

vim /etc/apparmor.d/usr.sbin.mysqld

And then edited the block of mysql paths to appear as follows:

  /etc/mysql/*.pem r,
  /etc/mysql/conf.d/ r,
  /etc/mysql/conf.d/* r,
  /etc/mysql/my.cnf r,
  /etc/mysql2/*.pem r,
  /etc/mysql2/conf.d/ r,
  /etc/mysql2/conf.d/* r,
  /etc/mysql2/my.cnf r,
  /usr/sbin/mysqld mr,
  /usr/share/mysql/** r,
  /usr/share/mysql2/** r,
  /var/log/mysql.log rw,
  /var/log/mysql.err rw,
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
  /var/log/mysql/ r,
  /var/log/mysql/* rw,
  /var/log/mysql2.log rw,
  /var/log/mysql2.err rw,
  /var/lib/mysql2/ r,
  /var/lib/mysql2/** rwk,
  /var/log/mysql2/ r,
  /var/log/mysql2/* rw,
  /var/run/mysqld/mysqld*.pid w,
  /var/run/mysqld/mysqld*.sock w,

[oldos2er]

Old thread closed.