How can I get around MySQL Errcode 13 with SELECT INTO OUTFILE?

Question

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.

Answer 1

Ubuntu Server Editions like 10.04 ship with AppArmor and MySQL's profile can be in enforcing mode by default. You can check that by executing sudo aa-status like this:

# 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 the entries can be written in /var/log/messages when AppArmor blocks the writes/accesses. 

You can edit /etc/apparmor.d/usr.sbin.mysqld and add /data/ and /data/* near the bottom like this:

...  

/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 you can make AppArmor reload the profiles.

# sudo /etc/init.d/apparmor reload

WARNING: The above change will allow MySQL to read and write to the /data directory.