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)

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';

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

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;

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.

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";

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 | +1 650 219 4782  | 100 Market Street        | Suite 300    | CA         | USA       | 94080      | NA        |
| 2          | Boston        | +1 215 837 0825  | 1550 Court Place         | Suite 102    | MA         | USA       | 02107      | NA        |
| 3          | NYC           | +1 212 555 3000  | 523 East 53rd Street     | apt. 5A      | NY         | USA       | 10022      | NA        |
| 4          | Paris         | +33 14 723 4404  | 43 Rue Jouffroy D'abbans | NULL         | NULL       | France    | 75017      | EMEA      |
| 5          | Tokyo         | +81 33 224 5000  | 4-1 Kioicho              | NULL         | Chiyoda-Ku | Japan     | 102-8578   | Japan     |
| 6          | Sydney        | +61 2 9264 2451  | 5-11 Wentworth Avenue    | Floor #2     | NULL       | Australia | NSW 2010   | APAC      |
| 7          | London        | +44 20 7877 2041 | 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"|"+1 650 219 4782"|"100 Market Street"|"Suite 300"|"CA"|"USA"|"94080"|"NA"
"2"|"Boston"|"+1 215 837 0825"|"1550 Court Place"|"Suite 102"|"MA"|"USA"|"02107"|"NA"
"3"|"NYC"|"+1 212 555 3000"|"523 East 53rd Street"|"apt. 5A"|"NY"|"USA"|"10022"|"NA"
"4"|"Paris"|"+33 14 723 4404"|"43 Rue Jouffroy D'abbans"|\N|\N|"France"|"75017"|"EMEA"
"5"|"Tokyo"|"+81 33 224 5000"|"4-1 Kioicho"|\N|"Chiyoda-Ku"|"Japan"|"102-8578"|"Japan"
"6"|"Sydney"|"+61 2 9264 2451"|"5-11 Wentworth Avenue"|"Floor #2"|\N|"Australia"|"NSW 2010"|"APAC"
"7"|"London"|"+44 20 7877 2041"|"25 Old Broad Street"|"Level 7"|\N|"UK"|"EC2N 1HN"|"EMEA"