116

I am trying to dump the contents of a table to a csv file using a MySQL SELECT INTO OUTFILE statement. If I do:

SELECT column1, column2
INTO OUTFILE 'outfile.csv'
FIELDS TERMINATED BY ','
FROM table_name;

outfile.csv will be created on the server in the same directory this database's files are stored in.

However, when I change my query to:

SELECT column1, column2
INTO OUTFILE '/data/outfile.csv'
FIELDS TERMINATED BY ','
FROM table_name;

I get:

ERROR 1 (HY000): Can't create/write to file '/data/outfile.csv' (Errcode: 13)

Errcode 13 is a permissions error, but I get it even if I change ownership of /data to mysql:mysql and give it 777 permissions. MySQL is running as user "mysql".

Strangely I can create the file in /tmp, just not in any other directory I've tried, even with permissions set such that user mysql should be able to write to the directory.

This is MySQL 5.0.75 running on Ubuntu.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Ryan Olson
  • 2,766
  • 4
  • 29
  • 36
  • 3
    Seeing as the 13 is a system error, this is probably not it, but there is a mySQL setting limiting INTO OUTFILE to a directory: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_secure_file_priv maybe worth a quick look whether it's set to `/tmp`. – Pekka May 06 '10 at 18:04
  • That variable is blank on my installation, which according to that document means my output directories should not be limited. – Ryan Olson May 06 '10 at 18:06

13 Answers13

194

Which particular version of Ubuntu is this and is this Ubuntu Server Edition?

Recent Ubuntu Server Editions (such as 10.04) ship with AppArmor and MySQL's profile might be in enforcing mode by default. You can check this by executing sudo aa-status like so:

# sudo aa-status
5 profiles are loaded.
5 profiles are in enforce mode.
   /usr/lib/connman/scripts/dhclient-script
   /sbin/dhclient3
   /usr/sbin/tcpdump
   /usr/lib/NetworkManager/nm-dhcp-client.action
   /usr/sbin/mysqld
0 profiles are in complain mode.
1 processes have profiles defined.
1 processes are in enforce mode :
   /usr/sbin/mysqld (1089)
0 processes are in complain mode.

If mysqld is included in enforce mode, then it is the one probably denying the write. Entries would also be written in /var/log/messages when AppArmor blocks the writes/accesses. What you can do is edit /etc/apparmor.d/usr.sbin.mysqld and add /data/ and /data/* near the bottom like so:

...  
/usr/sbin/mysqld  {  
    ...  
    /var/log/mysql/ r,  
    /var/log/mysql/* rw,  
    /var/run/mysqld/mysqld.pid w,  
    /var/run/mysqld/mysqld.sock w,  
    **/data/ r,  
    /data/* rw,**  
}

And then make AppArmor reload the profiles.

# sudo /etc/init.d/apparmor reload

WARNING: the change above will allow MySQL to read and write to the /data directory. We hope you've already considered the security implications of this.

Vin-G
  • 4,920
  • 2
  • 21
  • 16
  • This was Ubuntu 9.04, but AppArmor was denying the write. Thanks so much, this solved this for me. – Ryan Olson Jun 07 '10 at 13:47
  • 2
    I hate to point this out, but there is a reason App Armor doesn't allow this. MySQL now has the ability to modify and read anything in the /data folder. Just don't get hacked now. – Ryan Ward Valverde Nov 01 '11 at 03:01
  • 2
    @Serdar, The AppArmor MySQL ruleset distributed with the distro does not allow it *by default*. This makes sense as it is a good baseline of rules for a fresh install. I believe we should be and are allowed to modify the rulesets to fit our needs post-install. It was the original asker's intention to allow MySQL to write to the specific directories. But if it is not readily explicit above, a note to furhter people stumbling on this solution: WARNING: the change above will allow MySQL to read and write to the /data directory. We hope you've already considered the security implications of this. – Vin-G Jan 03 '12 at 01:51
  • Thanks! For Ubuntu 12.04, writing to /dev/shm, I added `/dev/shm/ r, /dev/shm/* rw, /run/shm/ r, /run/shm/* rw, ` – dave1010 May 23 '12 at 16:22
  • 1
    GREAT ANSWER!!! It solved my problem, I was also trying to write in any other directory. Now, I have to research what all this was about! :) Learning about this, I recommend other people to read about apparmor (and hence, the command aa-status): http://en.wikipedia.org/wiki/AppArmor – David L Sep 26 '14 at 16:06
  • That worked for me on ubuntu 14.04 but REMEMBER to chmod the output file with read write to the MySQL user. I made it 0777 as I'm the only one using the machine. – Islam El Hosary Feb 22 '15 at 20:14
  • 1
    In my case this helped: `/your/abs/folder/ r, /your/abs/folder/** rwk, }` don't forget to include the comma at the end! – ACV Oct 24 '16 at 09:40
  • Thanks for saving my life! ^^ – haotang Dec 05 '16 at 06:13
  • 1
    it works to write in /tmp. Use windows instead. – Victor Ionescu May 07 '18 at 12:17
  • This did not work for me, nor did chown nor did running as root. Only creating /tmp/mysql-dump/ then chown -R 777 /tmp/mysql-dump/ and running mysqldump into that folder. – eb80 Sep 04 '18 at 18:19
18

Ubuntu uses AppArmor and that is whats preventing you from accessing /data/. Fedora uses selinux and that would prevent this on a RHEL/Fedora/CentOS machine.

To modify AppArmor to allow MySQL to access /data/ do the follow:

sudo gedit /etc/apparmor.d/usr.sbin.mysqld

add this line anywhere in the list of directories:

/data/ rw,

then do a :

sudo /etc/init.d/apparmor restart

Another option is to disable AppArmor for mysql altogether, this is NOT RECOMMENDED:

sudo mv /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/

Don't forget to restart apparmor:

sudo /etc/init.d/apparmor restart

rook
  • 66,304
  • 38
  • 162
  • 239
  • To actually disable apparmor for mysql I needed to do: https://www.cyberciti.biz/faq/ubuntu-linux-howto-disable-apparmor-commands/ – silver_mx Jun 27 '17 at 09:36
16

I know you said that you tried already setting permissions to 777, but as I have an evidence that for me it was a permission issue I'm posting what I exactly run hoping it can help. Here is my experience:

tmp $ pwd
/Users/username/tmp
tmp $ mkdir bkptest
tmp $ mysqldump -u root -T bkptest bkptest
mysqldump: Got error: 1: Can't create/write to file '/Users/username/tmp/bkptest/people.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'
tmp $ chmod a+rwx bkptest/
tmp $ mysqldump -u root -T bkptest bkptest
tmp $ ls bkptest/
people.sql  people.txt
tmp $
acorello
  • 4,473
  • 4
  • 31
  • 46
  • me@server:/data$ pwd /data me@server:/data$ ls -al total 60 ... drwxrwxrwx 2 mysql mysql 4096 2010-05-06 16:27 dumptest me@server:/data$ mysqldump -u dbuser -p -T dumptest -B db_name --tables test Enter password: mysqldump: Got error: 1: Can't create/write to file '/data/dumptest/test.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE' me@server:/data$ sudo chmod a+rwx dumptest/ me@server:/data$ mysqldump -u dbuser -p -T dumptest -B db_name --tables test Enter password: mysqldump: Got error: 1: (same error) – Ryan Olson May 06 '10 at 20:35
  • Oy, well, didn't realize comments wouldn't format, but double checked a few different ways. First with the target directory owned by mysql:mysql, then with the target directory owned by the user I was running the dump command as, both ways still give me the same permissions error. – Ryan Olson May 06 '10 at 20:36
  • For the record, this worked for me despite having changed the apparmor permissions – Alex Nov 02 '12 at 19:17
  • I tried making modification to apparmor, it didn't worked. Changing permission 'chmod 777' worked for me! – Sudarshan_SMD Jun 22 '15 at 11:34
7

MySQL is getting stupid here. It tries to create files under /tmp/data/.... So what you can do is the following:

mkdir /tmp/data
mount --bind /data /tmp/data

Then try your query. This worked for me after hours of debugging the issue.

vimdude
  • 4,447
  • 1
  • 25
  • 23
  • I like this answer best. It is easy, it works, and it doesn't require you futzing with apparmor. The other way of doing it using pipes does not work well for large exports because of all the buffering that is done. – Chris Seline Nov 04 '14 at 15:19
7

You can do this : 

mysql -u USERNAME --password=PASSWORD --database=DATABASE --execute='SELECT `FIELD`, `FIELD` FROM `TABLE` LIMIT 0, 10000 ' -X > file.xml
Farvardin
  • 5,336
  • 5
  • 33
  • 54
Navrattan Yadav
  • 221
  • 3
  • 5
7

This problem has been bothering me for a long time. I noticed that this discussion does not point out the solution on RHEL/Fecora. I am using RHEL and I do not find the configuration files corresponding to AppArmer on Ubuntu, but I solved my problem by making EVERY directory in the directory PATH readable and accessible by mysql. For example, if you create a directory /tmp, the following two commands make SELECT INTO OUTFILE able to output the .sql AND .sql file

chown mysql:mysql /tmp
chmod a+rx /tmp

If you create a directory in your home directory /home/tom, you must do this for both /home and /home/tom.

fanchyna
  • 2,623
  • 7
  • 36
  • 38
  • 3
    Using /tmp as an example is not a good idea, and you really do not want to change the ownership of the /tmp directory (in most cases). – sastorsl Apr 13 '15 at 10:59
  • Changing the ownership of /tmp is bad, but creating a temp folder inside /tmp and `chown mysql:mysql` solved my problem – Samuel Prevost Jul 31 '20 at 12:52
4

Some things to try:

  • is the secure_file_priv system variable set? If it is, all files must be written to that directory.
  • ensure that the file does not exist - MySQL will only create new files, not overwrite existing ones.
mdma
  • 56,943
  • 12
  • 94
  • 128
  • 1
    I would also go for secure_file_priv. If the file already exists, the error message is different (not errcode 13). – Xavier Maillard Jun 04 '10 at 05:18
  • secure_file_priv is not currently set, so as I understand it that means that I should not be limited as to where I can write files. Am I misunderstanding that and do I need to explicitly set it to something like '/' if I want to be able to write anywhere on the file system? – Ryan Olson Jun 04 '10 at 13:37
  • Also, I am checking that the file does not exist before running the query. – Ryan Olson Jun 04 '10 at 13:37
  • Thanks for the feedback. Based on your findings, I don't think either of these suggestions causes your problem. – mdma Jun 04 '10 at 13:40
3

I have same problem and I fixed this issue by following steps:

  • Operating system : ubuntu 12.04
  • lamp installed
  • suppose your directory to save output file is : /var/www/csv/

Execute following command on terminal and edit this file using gedit editor to add your directory to output file.

sudo gedit /etc/apparmor.d/usr.sbin.mysqld

  • now file would be opened in editor please add your directory there

    /var/www/csv/* rw,

  • likewise I have added in my file, as following given image :

enter image description here

Execute next command to restart services :

sudo /etc/init.d/apparmor restart

For example I execute following query into phpmyadmin query builder to output data in csv file

SELECT colName1, colName2,colName3
INTO OUTFILE '/var/www/csv/OUTFILE.csv'
FIELDS TERMINATED BY ','
FROM tableName;

It successfully done and write all rows with selected columns into OUTPUT.csv file...

Sunny S.M
  • 5,768
  • 1
  • 38
  • 38
2

I just ran into this same problem. My issue was the directory that I was trying to dump into didn't have write permission for the mysqld process. The initial sql dump would write out but the write of the csv/txt file would fail. Looks like the sql dump runs as the current user and the conversion to csv/txt is run as the user that is running mysqld. So the directory needs write permissions for both users.

Matthew McMillan
  • 309
  • 1
  • 6
2

In my case, the solution was to make every directory in the directory path readable and accessible by mysql (chmod a+rx). The directory was still specified by its relative path in the command line.

chmod a+rx /tmp
chmod a+rx /tmp/migration
etc.
Alsciende
  • 26,583
  • 9
  • 51
  • 67
1

You need to provide an absolute path, not a relative path.

Provide the full path to the /data directory you are trying to write to.

Ike Walker
  • 64,401
  • 14
  • 110
  • 109
  • That looks like an absolute path to me. Is it not? – Pekka May 06 '10 at 19:08
  • 2
    Try this as the mysql user to verify that you can create the file outside of mysql: `touch /data/outfile.csv` – Ike Walker May 06 '10 at 19:51
  • 1
    First I couldn't do it because the mysql user's shell was set to /bin/false, so I couldn't log in as mysql. Just to make sure that wasn't contributing to the problem, I set mysql's shell to /bin/bash, su'd to that user and touched a file in /data. The file was created successfully, owned by mysql. – Ryan Olson May 06 '10 at 21:22
  • 3
    You can su to an account even if it's using one of the "disable" shells: `su --shell=/bin/sh nameofaccount` – Marc B May 06 '10 at 21:25
  • Thanks, that fixed my problem. Furthermore, every directory in the path must be +rx for mysql. – Alsciende Feb 10 '12 at 15:19
1

Does Ubuntu use SELinux? Check to see if it's enabled and enforcing. /var/log/audit/audit.log may be helpul (if that's where Ubuntu sticks it -- that's the RHEL/Fedora location).

Charles
  • 50,943
  • 13
  • 104
  • 142
1

I had the same problem on a CentOs 6.7 In my case all permissions were set and still the error occured. The problem was that the SE Linux was in the mode "enforcing".

I switched it to "permissive" using the command sudo setenforce 0

Then everything worked out for me.

Stefan Bicher
  • 96
  • 2
  • As user root, using Centos 7.3, mysql was still giving me Error code 13 every time too! This answer is the ONLY one that is relevant to Centos users. – user96279 Jul 19 '22 at 15:20