MYSQL into outfile "access denied" - but my user has "ALL" access.. and the folder is CHMOD 777

Question

Any ideas?

SELECT * INTO OUTFILE '/home/myacnt/docs/mysqlCSVtest.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '*'
LINES TERMINATED BY '\n'
FROM tbl_property 
WHERE managerGroupID = {$managerGroupID}

Error:

Access denied for user 'asdfsdf'@'localhost' (using password: YES)

Calisaya

"ALL PRIVILEGES" does not include all privileges. Relevant to this case, it does not include the FILE privilege, which is needed for SELECT INTO OUTFILE statements. I've had several troubles with this mismatch between the English and the MySQL: dba.stackexchange.com/a/96894/53784 – Kerriekerrigan

If you then run into "The MySQL server is running with the --secure-file-priv option so it cannot execute this statement" take a look here: #32737978 – Truncated

Answer 1

Try executing this SQL command:

> grant all privileges 
  on YOUR_DATABASE.* 
  to 'asdfsdf'@'localhost' 
  identified by 'your_password';
> flush privileges; 

It seems that you are having issues with connecting to the database and not writing to the folder you’re mentioning.

Also, make sure you have granted FILE to user 'asdfsdf'@'localhost'.

> GRANT FILE ON *.* TO 'asdfsdf'@'localhost';

Delaunay

Everything looks good with that, even in cpanel EVERYTHING is checked – Calisaya

@Shackrock: check out updated answer. Make sure you've granted FILE to the user. – Delaunay

There are two issues here. 1) GRANT ALL does not do GRANT FILE. 2) GRANT FILE only works with *.* (i.e. globally). Both are bugs in MySQL. – Kennie

FYI, think carefully before doing this is you aren't the db admin. Mine was not pleased to find I'd changed the privileges ("Nooooooo!"). :-) – Fagan

Seems like it also has issues writing to locations in the filesystem other than /var/lib/mysql, which seems contrary to the documentation. Even when the target folder is owned by mysql:mysql and everyone has rwx permissions and/or is called using sudo... (coming from Ubuntu here) – Corker

This worked fro me dev.mysql.com/doc/refman/5.5/en/cannot-create.html starting service with a directory tmpdir=c:\temp – Jenisejenkel

You don't need to use FLUSH PRIVILEGES after GRANT. That's only needed if you modify the privileges table with SQL instead of using GRANT. – Terminate

Didn't work for me until I restarted mysql server (MariaDB) – Mattern

Answer 2

Honestly I didn't bother to deal with the grants and this worked even without the privileges:

echo "select * from employee" | mysql --host=HOST --port=PORT --user=UserName --password=Password DATABASE.SCHEMA > output.txt

Agree

That's because you're just doing a regular SELECT, not a SELECT INTO OUTFILE (a .csv file in this case). – Peacetime

This is a bad way to do it for larger tables because the client buffers the entire result set before it puts anything out to the file. – Angular

MySQL will tab delimit the file using this method, which is perfect. After trying a dozen file permissions, MySQL settings, and various grants, all spanning 5 or 6 different help pages, this worked. Save yourself the headache and use this. – Bourgeoisie

Don't forget to switch to the appropriate codepage before using this method, e.g. "chcp 1252" or "chcp 65001" if there are special characters in the exported data. – Viridissa

Another issue with this is escaping of NULLs. Using INFILE from the output of this command will result in the string literal "NULL" being imported rather than an actual NULL, which is escaped using \N. – Lichfield

I agree with Paul K. Nothing else worked except this answer. Thanks. It inspired my answer here: dba.stackexchange.com/a/245383/18098 – Loewe

This is the most practical answer so far, others are so dependent on privileges that not everyone has control over – Kraemer

Note also that if your data has any newlines in it, the tab-delimited lines get messed up. But if not, then this is the easiest solution! – Lasley

Answer 3

As @fijaaron says,

1. GRANT ALL does not imply GRANT FILE
2. GRANT FILE only works with *.*

So do

GRANT FILE ON *.* TO user;

Coattail

Answer 4

Since cP/WHM took away the ability to modify User privileges as root in PHPMyAdmin, you have to use the command line to:

mysql>  GRANT FILE ON *.* TO 'user'@'localhost';

Step 2 is to allow that user to dump a file in a specific folder. There are a few ways to do this but I ended up putting a folder in :

/home/user/tmp/db

and

chown mysql:mysql /home/user/tmp/db

That allows the mysql user to write the file. As previous posters have said, you can use the MySQL temp folder too, I don't suppose it really matters but you definitely don't want to make it 0777 permission (world-writeable) unless you want the world to see your data. There is a potential problem if you want to rinse-repeat the process as INTO OUTFILE won't work if the file exists. If your files are owned by a different user then just trying to unlink($file) won't work. If you're like me (paranoid about 0777) then you can set your target directory using:

chmod($dir,0777)

just prior to doing the SQL command, then

chmod($dir,0755)

immediately after, followed by unlink(file) to delete the file. This keeps it all running under your web user and no need to invoke the mysql user.

Georgeannageorgeanne

Answer 5

I tried all the solutions but it still wasn't sufficient. After some more digging I eventually found I had also to set the 'file_priv' flag, and restart mysql.

To resume :

Grant the privileges :

> GRANT ALL PRIVILEGES
  ON my_database.* 
  to 'my_user'@'localhost';

> GRANT FILE ON *.* TO my_user;

> FLUSH PRIVILEGES; 

Set the flag :

> UPDATE mysql.user SET File_priv = 'Y' WHERE user='my_user' AND host='localhost';

Finally restart the mysql server:

$ sudo service mysql restart

After that, I could write into the secure_file_priv directory. For me it was /var/lib/mysql-files/, but you can check it with the following command :

> SHOW VARIABLES LIKE "secure_file_priv";

Ritz

Answer 6

For future readers, one easy way is as follows if they wish to export in bulk using bash,

akshay@ideapad:/tmp$ mysql -u someuser -p test -e "select * from offices"
Enter password: 
+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+
| officeCode | city          | phone            | addressLine1             | addressLine2 | state      | country   | postalCode | territory |
+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+
| 1          | San Francisco | [phone]  | 100 Market Street        | Suite 300    | CA         | USA       | 94080      | NA        |
| 2          | Boston        | [phone]  | 1550 Court Place         | Suite 102    | MA         | USA       | 02107      | NA        |
| 3          | NYC           | [phone]  | 523 East 53rd Street     | apt. 5A      | NY         | USA       | 10022      | NA        |
| 4          | Paris         | [phone]  | 43 Rue Jouffroy D'abbans | NULL         | NULL       | France    | 75017      | EMEA      |
| 5          | Tokyo         | [phone]  | 4-1 Kioicho              | NULL         | Chiyoda-Ku | Japan     | 102-8578   | Japan     |
| 6          | Sydney        | [phone]  | 5-11 Wentworth Avenue    | Floor #2     | NULL       | Australia | NSW 2010   | APAC      |
| 7          | London        | [phone] | 25 Old Broad Street      | Level 7      | NULL       | UK        | EC2N 1HN   | EMEA      |
+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+

If you're exporting by non-root user then set permission like below

root@ideapad:/tmp# mysql -u root -p
MariaDB[(none)]> UPDATE mysql.user SET File_priv = 'Y' WHERE user='someuser' AND host='localhost';

Restart or Reload mysqld

akshay@ideapad:/tmp$ sudo su
root@ideapad:/tmp#  systemctl restart mariadb

Sample code snippet

akshay@ideapad:/tmp$ cat test.sh 
#!/usr/bin/env bash

user="someuser"
password="password"
database="test"

mysql -u"$user" -p"$password" "$database" <<EOF
SELECT * 
INTO OUTFILE '/tmp/csvs/offices.csv' 
FIELDS TERMINATED BY '|' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
FROM offices;
EOF

Execute

akshay@ideapad:/tmp$ mkdir -p /tmp/csvs
akshay@ideapad:/tmp$ chmod +x test.sh
akshay@ideapad:/tmp$ ./test.sh 
akshay@ideapad:/tmp$ cat /tmp/csvs/offices.csv 
"1"|"San Francisco"|"[phone]"|"100 Market Street"|"Suite 300"|"CA"|"USA"|"94080"|"NA"
"2"|"Boston"|"[phone]"|"1550 Court Place"|"Suite 102"|"MA"|"USA"|"02107"|"NA"
"3"|"NYC"|"[phone]"|"523 East 53rd Street"|"apt. 5A"|"NY"|"USA"|"10022"|"NA"
"4"|"Paris"|"[phone]"|"43 Rue Jouffroy D'abbans"|\N|\N|"France"|"75017"|"EMEA"
"5"|"Tokyo"|"[phone]"|"4-1 Kioicho"|\N|"Chiyoda-Ku"|"Japan"|"102-8578"|"Japan"
"6"|"Sydney"|"[phone]"|"5-11 Wentworth Avenue"|"Floor #2"|\N|"Australia"|"NSW 2010"|"APAC"
"7"|"London"|"[phone]"|"25 Old Broad Street"|"Level 7"|\N|"UK"|"EC2N 1HN"|"EMEA"

Hurff