7

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?

Improve this question
0

5 Answers 5

Reset to default
7

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

@dnagirl i usually start mysql with the command mysql -u mropa -p so the user is 'mropa' which should have writing permissions. can you elaborate your answer a little bit more, i don't totally comprehend it. thanks.
@mropa: you are not starting the mysqld process when you do mysql -u mropa -p. You are only logging into it. When you write to outfile, it is not your login user that is writing, it is the mysqld process. Imagine you login to your shell as mropa and then you login into mysql as mydb_user. Neither mropa nor mydb_user is doing the writing when you write to outfile from mysql. The writer is the owner of the mysqld process.
Do you know how could we grant file permission to the mysqld? I am having the same issue as @mropa
3

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
Improve this answer

Comments

1

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

Improve this answer

Comments

1

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.

Improve this answer

Comments

0

mysql usually runs under user mysqld. permissions may be solved directly with apparmor. see this answer

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.