Create/Write Permissions in MySQL

Question

I am experiencing some permission problems with my SELECT ... INTO OUTFILE statement.

When I log into my database and do a simple export command, eg:

mysql> select * from XYZ into outfile '/home/mropa/Photos/Desktop/TEST.txt';

I get the respond:

ERROR 1 (HY000): 
Can't create/write to file '/home/mropa/Photos/Desktop/TEST.txt' 
(Errcode: 13)

However, when I simply write:

mysql> select * from XYZ into outfile 'TEST.txt';
Query OK, 8287 rows affected (0.73 sec)

The file is written into the directory /var/lib/mysql/XYZ. I looked into the file /etc/apparmor.d/usr.sbin.mysqld where mysql seems to define the reading and writting permissions but I don't really know how to modify it.

How can I give myself permission to export a mysql table into any directory I like?

Answer 1

Hi mropa, This sounds like you don't have access to that particular folder. You should add mysql to the group owner of that particular location.

I don't know under what user you are running mysql under, however,

chown mysql:mysql on /home/mropa/Photos/Desktop/logs would mean that mysql user and mysql group is the owner so has permission. You then need to make sure that the permissions include writing but that should be sufficient.

Chown Command

Answer 2

you can create a directory that is writable by the user that is running mysqld (usually "mysql") and write the file there. For intance:

chmod a+w /home/mropa/mysql

Answer 3

See https://wiki.ubuntu.com/DebuggingApparmor for information on debugging/disabling apparmor profiles.

It may not be an apparmor issue in particular. The user that mysqld is running as may not have permissions to write to the folder you specified. Check the permission on the destination directory/file.

Answer 4

The issue is directory permissions. mysqld does not run as current_user. Add the mysqld user to the group that has write permissions on your target directories (convenient, but not as secure) or remember to change your target directory permissions before and after you write the outfile.