Mysql – ERROR 1 (HY000): Can’t create/write to file (Errcode: 13 – Permission denied)

exportmysql-5.6permissionsUbuntu

I've been struggling for a couple of days with this now and have been trying to use this thread amongst other google searches to resolve it but can't seem to get it to work.

When I run this SELECT query (Ubuntu Server 15.10 & MySQL 5.6)…

SELECT column1 ,column2
FROM table 
INTO OUTFILE '/home/user/mysql_temp/file.csv';

I get this permission error…

ERROR 1 (HY000): Can't create/write to file '/home/mysql_temp/file.csv' (Errcode: 13 - Permission denied)

From my searching, I understand this to be a system permissions issue rather than a MySQL problem but I can't seem to find a solution which fixes it for me.

Checking apparmor shows that mysqld IS in enforce mode…

apparmor module is loaded.
6 profiles are loaded.
6 profiles are in enforce mode.
   /sbin/dhclient
   /usr/lib/NetworkManager/nm-dhcp-client.action
   /usr/lib/NetworkManager/nm-dhcp-helper
   /usr/lib/connman/scripts/dhclient-script
   /usr/sbin/mysqld
   /usr/sbin/tcpdump
0 profiles are in complain mode.
2 processes have profiles defined.
2 processes are in enforce mode.
   /sbin/dhclient (676) 
   /usr/sbin/mysqld (1285) 
0 processes are in complain mode.
0 processes are unconfined but have a profile defined.

So I edited /etc/apparmor.d/usr.sbin.mysqld and added the following lines and then restarted apparmor…

/home/user/mysql_temp/ rw,
/home/user/mysql_temp/* rw,

I also changed the owner:group of mysql_temp to mysql and gave it full permissions…

sudo chown mysql:mysql mysql_temp

…

drwxrwxrwx 2 mysql   mysql       6 Apr  1 22:02 mysql_temp

After this, I still receive the same error.

Have I missed something? Misunderstood some of the instruction? Would be grateful for advice. Thanks.

Best Answer

Not sure if this applies to your situation but I ran into similar problems on RHEL 8.

First gotcha is that systemd was configured to run the MySQL service with the privateTmp option (see https://www.freedesktop.org/software/systemd/man/systemd.exec.html#PrivateTmp=).

This means that when you write to /tmp it actually attempts to write to a directory named something like /tmp/systemd-private-<random>-mariadb.service-<random>

Secondly the directory you write to needs to be owned by mysql (and group mysql).

Lastly the SELinux context needs to be correct as well when SELinux is Enforcing.

Related Question