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

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

Solution 2:

Honestly I didnt 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

Solution 3:

As @fijaaron says,

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

So do

GRANT FILE ON *.* TO user;

Solution 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.

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