Menu
I have an issue trying to use SELECT INTO OUTFILE and using a directory other than /tmp.
My Linux user is named datam, my MySQL user is lea, and MySQL runs as mysql.
When datam runs mysql -u lea database and tries to do a SELECT INTO OUTFILE with a path of /home/datam/xfers/online/file.csv, I get an error code 13, permission denied. Using /tmp/file.csv works, so I'm fairly confident it is not an issue with permissions within MySQL.
I've added mysql to the datam group and have verified this with:
~$ sudo id mysql
uid=106(mysql) gid=114(mysql) groups=114(mysql),1001(datam)
I have /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 a file.
What do I need to do to allow mysql to write a file from SELECT INTO OUTFILE?
I believe this is not a duplicate of other questions surrounding this subject, because I've looked at them and followed all of their instructions (setting execute on all directories leading up to the one I want, setting GRANT FILE ON, etc).
MySQL user lea grants:
+-----------------------------------------------------------------------------------------------------------+
| Grants for lea@localhost |
+-----------------------------------------------------------------------------------------------------------+
| GRANT FILE ON *.* TO 'lea'@'localhost' IDENTIFIED BY PASSWORD '*9BB439A3A652A9DAD3718215F77A7AA06108A267' |
| GRANT ALL PRIVILEGES ON `database`.* TO 'lea'@'localhost' |
+-----------------------------------------------------------------------------------------------------------+
245
C:\ProgramData\MySQL\MySQL Server 5.6\data\name.csv Show activity on this post. Show activity on this post. If you don't specify an absoulte path but use something like INTO OUTFILE 'output.
The FILE privilege gives you permission to read and write files on the server host using the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. A user who has the FILE privilege can read any file on the server host that is either world-readable or readable by the MySQL server.
The PROCESS privilege controls access to information about threads executing within the server (that is, information about statements being executed by sessions). Thread information available using the SHOW PROCESSLIST statement, the mysqladmin processlist command, the INFORMATION_SCHEMA.
This may be caused by mysql user permissions.
As stated here https://dba.stackexchange.com/questions/17029/cannot-output-mysql-data-to-file
To give yourself FILE privilege, do the following:
service mysql restart --skip-networking --skip-grant-tablesmysql <hit enter> UPDATE mysql.user SET File_priv = 'Y' WHERE user='lea' AND host='localhost';exitservice mysql restartThe linux user can write a file. But the mysql service may be blocked by apparmor.
Check 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 a
sudo /etc/init.d/apparmor reload
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
