I came across a weird problem today. Gladly, the web is full of solutions, but I’m going to post this anyway, just to have it nearby for the next time. I needed to export the results of some query into a CSV file directly from MySQL. I prepared my query, made sure that I can see the correct results and than changed it to export into the file. The query looked something like this:
SELECT id, field1, field2, field3 INTO OUTFILE '/tmp/data.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' FROM data_table WHERE field1 = 0;
I was quite surprised to find myself staring at:
ERROR 1045 (28000): Access denied for user ‘db_user’@’localhost’ (using password: YES)
My database user definitely had full access to the database. I definitely could see the results of the query before the redirect to the file. And I definitely had enough permissions to create files in /tmp directory. And on top of that, I’m sure I used MySQL export functionality a gadzillion times and it always worked without any problems. What’s wrong this time?
A quick search around got me to this Stack Overflow question. Apparently, database user has to be given a FILE privilege, which is global (not per-database). Here is what I did to solve the problem (you’ll need to use MySQL root user of course):
USE mysql; UPDATE user SET File_priv = 'Y' WHERE User = 'db_user'; FLUSH PRIVILEGES;
I think that it worked for me before was because I exported as root, who does have this permission set to ‘Y’.