Friday, January 6, 2012


There is a very useful functionality in MySQL for exporting data into a file. The syntax is like:

If you use Linux then when you execute this export command with FILE_NAME that is not absolute than the file will be saved in the database folder (e.g. FILE_NAME = '1.csv'); if you specify to use /tmp folder then it will be saved there(e.g. FILE_NAME='/tmp/1.csv').
But if you specify some other directory (e.g. FILE_NAME='/home/anton/mysql/1.csv') then you will get error:
ERROR 1 (HY000): Can't create/write to file '/home/anton/mysql/1.csv' (Errcode: 13)

Even if you will set MySQL user as an owner of the directory and will provide all access to it, this error will appear nevertheless.

The reason lays in the Linux kernel security module AppArmor. This module restricts programs possibilities and we have stumbled upon one of those. Thus the solution is pretty obvious - we need to configure AppArmor. For this we will add at the end (but before the closing brace) of the file /etc/apparmor.d/usr.sbin.mysqld (for Ubuntu) such lines:
/home/anton/mysql/ w,
/home/anton/mysql/* rwk,

Then we need to reload this configuration file with command (for Ubuntu):
sudo /etc/init.d/apparmor reload

Now check that the directory that you want to use (/home/anton/mysql) has 755 rights and mysql us is the owner of it. Also make sure that there is enough free space on the volume.
Repeat export SELECT command