Wednesday, July 16, 2014

MySQL, Ubuntu:: mysqld does not have the access rights

So today I happen to need to restore a MySQL database from backups so I could recover some tables. While I left he production database running via port 3306, I set up the backup via port 3307.

However, when I attempted to start another version via the 3307 port in a mysql_restore directory but  I ran into some errors....


/usr/bin/mysqld_safe --defaults-file=/etc/my_3307.cnf

[Warning] Can't create test file /var/lib/mysql_restore/localhost.lower-test
[Warning] Can't create test file /var/lib/mysql_restore/localhost.lower-test
Can't find file: './mysql/plugin.frm' (errno: 13)

InnoDB: Completed initialization of buffer pool
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.

# perror 13
OS error code  13:  Permission denied


So I first checked the permissions on the directory, ensured that it was set to 700 and owned by mysql.  I also ensured that the directories inside the data directory was set to 700 and owned by mysql. The rest of the files all set to 600 ( -rw-rw---- ).  Just to be safe I also ensured that the disk was not full.

So the error log shows permission issues but at first glance all permissions are correct. So what it is?

Well, if I was on RHEL, or related branches,   I would think of SELinux. Was it blocking it somehow.
The Ubuntu Apparmor needed to be reviewed in this case.


# cat /etc/apparmor.d/usr.sbin.mysqld
...
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
...


Notice that the default "var/lib/mysql" is being addressed. The restore directory I created was not though. So by adding that and a restart of apparmor I was then able to successfully start MySQL.


# vi /etc/apparmor.d/usr.sbin.mysqld
...
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
  /var/lib/mysql_restore/ r,
  /var/lib/mysql_restore/** rwk,
...
/etc/init.d/apparmor {start|stop|restart|reload|force-reload|status|recache}
/var/lib/mysql_restore# /etc/init.d/apparmor restart
...
/usr/bin/mysqld_safe --defaults-file=/etc/my_3307.cnf
...
# mysql -P 3307 --socket=/var/lib/mysql_restore/mysqld_3307.sock



Additional URLs for review if needed:

No comments:

Post a Comment

@AnotherMySQLDBA