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)
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';
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
As @fijaaron says,
GRANT ALL
does not imply GRANT FILE
GRANT FILE
only works with *.*
So do
GRANT FILE ON *.* TO user;
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.