Grant database user folder access

Question

I'm trying to create a csv export of data from mysql using the following query:

SELECT * INTO OUTFILE '/tmp/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM getfreepellets WHERE 1

And I get the following error:

#1045 - Access denied for user '[username]'@'localhost' (using password: YES)

(removed username but it's the right one)

How would I go about granting access to this user to create a file on the server?

Edit:

I changed the first line to be my exact home path, and received the same error.

Answer 1

You may want to GRANT your user the FILE privilege:

The FILE privilege gives you permission to read and write files on the server host using the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function.

A user who has the FILE privilege can read any file on the server host that is either world-readable or readable by the MySQL server. (This implies the user can read any file in any database directory, because the server can access any of those files.) The FILE privilege also enables the user to create new files in any directory where the MySQL server has write access. As a security measure, the server will not overwrite existing files.

To grant the FILE privilege, log-in as root and execute:

GRANT FILE ON *.* TO 'your_user'@'localhost';