I need to output some queries as csv files. The directory is in the webroot and I want it to be writable by apache too, so I'm trying to add mysql to the www-data group (on Ubuntu).

I thought this would work, but I've missed something:

Add mysql to www-data:

ubuntu@168:~/viewer/files$ sudo usermod -a -G www-data mysql

Restart mysql:

ubuntu@168:~/viewer/files$ sudo /etc/init.d/mysql stop
 * Stopping MySQL database server mysqld                       [ OK ] 
ubuntu@168:~/viewer/files$ sudo /etc/init.d/mysql start
 * Starting MySQL database server mysqld                       [ OK ]   

Verify permissions:

ubuntu@168:/var/www/html/viewer/files$ pwd
ubuntu@168:/var/www/html/viewer/files$ ls -al
total 640
drwxrwxr-x 2 ubuntu www-data   4096 Jun 10 18:50 .
drwxrwxr-x 3 ubuntu www-data   4096 Jun 10 14:31 ..

Verify group:

ubuntu@168:~$ id mysql
uid=106(mysql) gid=112(mysql) groups=112(mysql),33(www-data)

I've restarted my session - no good. A day later and my sql query still isn't able to write out:

mysql> SELECT id, title FROM recipes WHERE id IN ('R225037', 'R225046', 'R226412') INTO OUTFILE '/var/www/html/viewer/files/ingredient_caps_6-10-14.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
ERROR 1 (HY000): Can't create/write to file '/var/www/html/viewer/files/ingredient_caps_6-10-14.csv' (Errcode: 13)
  • stackoverflow.com/questions/2783313/… might be relevant – Dancrumb Jun 10 '14 at 19:35
  • Double check it's in the group. Post the output of id mysql – Philᵀᴹ Jun 11 '14 at 10:52
  • @Dancrumb: that was totally it. if you want to throw it in an answer, I'll accept it. – doub1ejack Jun 11 '14 at 14:16

On Ubuntu apparmor doesn't let mysql write to directories other than listed in /etc/apparmor.d/usr.sbin.mysqld

apparmor is enabled by default, but on dedicated MySQL servers I don't see much sense in it.

From https://help.ubuntu.com/14.04/serverguide/apparmor.html: "AppArmor can be disabled, and the kernel module unloaded by entering the following:

    sudo service apparmor stop
    sudo update-rc.d -f apparmor remove


| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.