In this post we will see the resolution of MySQL “permission denied” error due to movement of database to a different partition on CentOS.
ERROR 1018 (HY000): Can’t read dir of ‘./sakila/’ (errno: 13 – Permission denied)
Recently, a friend was stuck on following issue and he was not able to access the tables under ‘sakila’ database. Though he was able to read all other databases!
ERROR 1018 (HY000): Can't read dir of './sakila/' (errno: 13 - Permission denied)
This is what happens when he try accessing the tables from sakila database:
mysql>use sakila; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; ERROR 1018 (HY000): Can't read dir of './sakila/' (errno: 13 - Permission denied)
We reviewed the ‘sakila’ database, it was well owned by mysql user and group with proper permissions.
[root@localhost mysql]# ls -lrth | grep sakila lrwxrwxrwx. 1 mysql mysql 15 Jul 6 07:51 sakila -> /database/sakila
Note that “sakila” is pointing (/soft-linked) to a different partition which was the recent change he made! We confirmed that the partition and sakila database / datafiles were again having correct permissions too.
Usually such error is caused by wrong permission due to which MySQL isn’t able to read the specifics. Correcting the file/folder permissions can fix them quickly!
$] chown mysql:mysql /database -R # Making sure /database and everything under is mysql owned. $] chown mysql:mysql /var/lib/mysql -R # Making sure mysql $] datadir and everything under is mysql owned.
We can actually connect as mysql user and confirm that the table files are readable by mysql!
[root@localhost mysql]# su - mysql [mysql@localhost ~] cd /var/lib/mysql/sakila [mysql@localhost sakila]# cat actor.frm ...
Though ours was not the permission issue and without softlink it was working fine!
Solution to the Permission Denied Error
Considering, all but sakila database tables were generating “permission denied” errors and mysql error log remained silent about this, we’re surely dealing with some “external” power!
We are on CentOS and we have something called SELinux!
Security-Enhanced Linux (SELinux) is a Linux kernel security module that provides a mechanism for supporting access control security policies, including ... mandatory access controls (MAC). - Wikipedia
SELinux logs at /var/log/audit/audit.log and following is a snippet from audit log:
type=AVC msg=audit(1436192732.305:119): avc: denied { read } for pid=7063 comm="mysqld" name="sakila" dev=sda3 ino=655362 scontext=unconfined_u:system_r:mysqld_t:s0 tcontext=unconfined_u:object_r:default_t:s0 tclass=dir
This confirms that SELinux is obstructing MySQL to read from location other than datadir and fixing that will make MySQL happy.
Command ‘sestatus’ can show the SELinux Status:
[root@localhost mysql]# sestatus
SELinux status: enabled
SELinuxfs mount: /selinux
Current mode: enforcing
Mode from config file: enforcing
Policy version: 24
Policy from config file: targeted
Here we can note SELinux is enabled and the mode is enforcing.
Here comes 2 more commands: setenforce & getenforce.
The “setenforce 0” command switches off SELinux enforcing, and getenforce shows you the current status. Though setenforce is a “temporary” solution and it will auto-reset to configuration option upon system reboot.
To make it persistent over reboots, we’ll need to change a configuration file, /etc/sysconfig/selinux:
[root@localhost mysql]# cat /etc/sysconfig/selinux
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
#SELINUX=enforcing # Removed this line to disable SELinux completely.
SELINUX=disabled
# SELINUXTYPE= can take one of these two values:
# targeted - Targeted processes are protected,
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
We added “SELINUX=disabled” to disable SELinux completely. (You might want to keep it “permissive” or even better, manage SELinux policies.)
Upon disabling SELinux the issue went away and ‘sakila’ database tables were accessible.
mysql> use sakila Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------------------+ | Tables_in_sakila | +----------------------------+ | actor | | actor_info | | address | | category | | city | | country | | customer | | customer_list | | film | | film_actor | | film_category | | film_list | | film_text | | inventory | | language | | nicer_but_slower_film_list | | payment | | rental | | sales_by_film_category | | sales_by_store | | staff | | staff_list | | store | +----------------------------+ 23 rows in set (0.00 sec)
Just to note that /etc/sysconfig/selinux configuration file for SELinux is actually pointing to /etc/selinux/config.
[root@localhost mysql]# ls -l /etc/sysconfig/selinux lrwxrwxrwx. 1 root root 17 Oct 8 2014 /etc/sysconfig/selinux -> ../selinux/config
Later I found plenty of posts just talking about the same issue! So we walked a common path, but not without learning!
On CentOS, Redhat or Fedora we have SELinux while on Ubuntu, Debian or SuSE you should find AppArmor! Refer a good post from Jeremy @Oracle here.
Hope this helps (or refreshes).