SELECT INTO OUTFILE allowed, but user can write to directory
I have a problem using SELECT INTO OUTFILE
and using a directory other than /tmp
.
My Linux user has a name datam
, my user is MySQL lea
, and MySQL works like mysql
.
When it datam
launches mysql -u lea database
and tries to execute SELECT INTO OUTFILE
with path /home/datam/xfers/online/file.csv
, I get error code 13, permission denied. Usage /tmp/file.csv
works, so I'm pretty sure this isn't a permissions issue in MySQL.
I added mysql
to the group datam
and tested it with:
~$ sudo id mysql uid=106(mysql) gid=114(mysql) groups=114(mysql),1001(datam)
I have it /home/datam/
set as 775 recursively.
If I do sudo -u mysql /bin/bash
and go to /home/datam/xfers/online/
and do touch file
, it writes the file.
What do I need to do so that I mysql
can write the file from SELECT INTO OUTFILE
?
I believe this is not a duplicate of other questions related to this topic because I have looked at them and followed their instructions (installation is done in all directories leading up to what I want, setting GRANT FILE ON, etc.) ).
MySQL user lea
provides:
+-----------------------------------------------------------------------------------------------------------+
| Grants for lea@localhost |
+-----------------------------------------------------------------------------------------------------------+
| GRANT FILE ON *.* TO 'lea'@'localhost' IDENTIFIED BY PASSWORD '*9BB439A3A652A9DAD3718215F77A7AA06108A267' |
| GRANT ALL PRIVILEGES ON `database`.* TO 'lea'@'localhost' |
+-----------------------------------------------------------------------------------------------------------+
source to share
It could be caused by mysql user permissions.
As stated here https://dba.stackexchange.com/questions/17029/cannot-output-mysql-data-to-file
To grant yourself the FILE privilege, do the following:
-
service mysql restart --skip-networking --skip-grant-tables
-
mysql <hit enter>
-
UPDATE mysql.user SET File_priv = 'Y' WHERE user='lea' AND host='localhost';
-
exit
-
service mysql restart
The linux user can write the file. But the mysql service might be blocked by apparmor.
Check out this file /etc/apparmor.d/usr.sbin.mysqld
.
Add your project folder there:
/usr/sbin/mysqld {
[...]
/home/datam/xfers/online/ r,
/home/datam/xfers/online/* rw
[...]
}
Finally, do
sudo /etc/init.d/apparmor reload
source to share