In this post we will see the resolution of MySQL “permission denied” error due to movement of database to a different partition on CentOS.<\/p>\n\n\n\n
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!<\/p>\n\n\n\n
ERROR 1018 (HY000): Can't read dir of '.\/sakila\/' (errno: 13 - Permission denied)<\/pre>\n\n\n\nThis is what happens when he try accessing the tables from sakila database:<\/p>\n\n\n\n
mysql>use sakila;\nReading table information for completion of table and column names\nYou can turn off this feature to get a quicker startup with -A\n\nDatabase changed\nmysql> show tables;\nERROR 1018 (HY000): Can't read dir of '.\/sakila\/' (errno: 13 - Permission denied)\n<\/pre>\n\n\n\n\n\n\n\nWe reviewed the ‘sakila’ database, it was well owned by mysql user and group with proper permissions.<\/p>\n\n\n\n
[root@localhost mysql]# ls -lrth | grep sakila\nlrwxrwxrwx. 1 mysql mysql 15 Jul 6 07:51 sakila -> \/database\/sakila\n<\/pre>\n\n\n\nNote 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.<\/p>\n\n\n\n
Usually such error is caused by wrong permission<\/span> due to which MySQL isn’t able to read the specifics. Correcting the file\/folder permissions can fix them quickly!<\/p>\n\n\n\n
$] chown mysql:mysql \/database -R # Making sure \/database and everything under is mysql owned.\n$] chown mysql:mysql \/var\/lib\/mysql -R # Making sure mysql $] datadir and everything under is mysql owned.\n<\/pre>\n\n\n\nWe can actually connect as mysql user and confirm that the table files are readable by mysql!<\/p>\n\n\n\n
[root@localhost mysql]# su - mysql\n[mysql@localhost ~] cd \/var\/lib\/mysql\/sakila\n[mysql@localhost sakila]# cat actor.frm\n...\n<\/pre>\n\n\n\nThough ours was not the permission issue and without softlink it was working fine!<\/p>\n\n\n\n
Solution to the Permission Denied Error<\/strong><\/h2>\n\n\n\n
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!<\/p>\n\n\n\n
We are on CentOS and we have something called SELinux<\/strong>!<\/p>\n\n\n\n
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).<\/em>\n- Wikipedia\n<\/pre>\n\n\n\nSELinux logs at \/var\/log\/audit\/audit.log<\/strong> and following is a snippet from audit log:<\/p>\n\n\n\n
type=AVC msg=audit(1436192732.305:119): avc: denied { read } for pid=7063 comm=\"mysqld\" name=\"sakila\"<\/strong><\/span> dev=sda3 ino=655362 scontext=unconfined_u:system_r:mysqld_t:s0 tcontext=unconfined_u:object_r:default_t:s0 tclass=dir\n<\/pre>\n\n\n\nThis confirms that SELinux is obstructing MySQL to read from location other than datadir and fixing that will make MySQL happy.<\/p>\n\n\n\n
Command ‘sestatus’<\/b> can show the SELinux Status:<\/p>\n\n\n\n
[root@localhost mysql]# sestatus\nSELinux status: enabled\nSELinuxfs mount: \/selinux\nCurrent mode: enforcing\nMode from config file: enforcing<\/strong><\/span>\nPolicy version: 24\nPolicy from config file: targeted\n<\/pre>\n\n\n\nHere we can note SELinux is enabled and the mode is enforcing.<\/p>\n\n\n\n
Here comes 2 more commands: setenforce & getenforce.<\/b><\/p>\n\n\n\n
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.<\/p>\n\n\n\n
To make it persistent over reboots, we’ll need to change a configuration file, \/etc\/sysconfig\/selinux:<\/p>\n\n\n\n
[root@localhost mysql]# cat \/etc\/sysconfig\/selinux\n# This file controls the state of SELinux on the system.\n# SELINUX= can take one of these three values:\n# enforcing - SELinux security policy is enforced.\n# permissive - SELinux prints warnings instead of enforcing.\n# disabled - No SELinux policy is loaded.\n#SELINUX=enforcing # Removed this line to disable SELinux completely.\nSELINUX=disabled<\/span>\n# SELINUXTYPE= can take one of these two values:\n# targeted - Targeted processes are protected,\n# mls - Multi Level Security protection.\nSELINUXTYPE=targeted\n<\/pre>\n\n\n\nWe added “SELINUX=disabled” to disable SELinux completely<\/span>. (You might want to keep it “permissive<\/span>” or even better, manage SELinux policies.)<\/p>\n\n\n\n
Upon disabling SELinux the issue went away and ‘sakila’ database tables were accessible.<\/p>\n\n\n\n
mysql> use sakila\nReading table information for completion of table and column names\nYou can turn off this feature to get a quicker startup with -A\n\nDatabase changed\nmysql> show tables;\n+----------------------------+\n| Tables_in_sakila |\n+----------------------------+\n| actor |\n| actor_info |\n| address |\n| category |\n| city |\n| country |\n| customer |\n| customer_list |\n| film |\n| film_actor |\n| film_category |\n| film_list |\n| film_text |\n| inventory |\n| language |\n| nicer_but_slower_film_list |\n| payment |\n| rental |\n| sales_by_film_category |\n| sales_by_store |\n| staff |\n| staff_list |\n| store |\n+----------------------------+\n23 rows in set (0.00 sec)\n<\/pre>\n\n\n\nJust to note that \/etc\/sysconfig\/selinux<\/strong> configuration file for SELinux is actually pointing to \/etc\/selinux\/config.<\/p>\n\n\n\n
[root@localhost mysql]# ls -l \/etc\/sysconfig\/selinux\nlrwxrwxrwx. 1 root root 17 Oct 8 2014 \/etc\/sysconfig\/selinux -> ..\/selinux\/config\n<\/pre>\n\n\n\nLater I found plenty of posts just talking about the same issue! So we walked a common path, but not without learning!<\/p>\n\n\n\n