Don’t you hate it when you know you solved a problem three years ago but you can’t remember what the solution was? I ran into that recently. I was trying to make mysql generate a csv directly from query results. I remembered that you can do it, and I had an example of the correct syntax to use in my old code, and I had a query that did it just fine when running in the mysql CLI as root. I had to piece together the answer from several different sources; and none of them told the whole story. So here is the full story, along with all the stumbling blocks that I know of.
Here is the general format for the query that you can find in the mysql documentation. You write your query as usual and you insert the “INTO OUTFILE….” immediately after the list of columns you wish to select.
SELECT .... INTO OUTFILE '/tmp/out.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM ..... WHERE ..... etc.
Potential Stumbling Block #1: Wherever on the file system you decide to write the file to, the mysql user (usually called mysql) must have write permissions to that place, because the file will be owned by mysql. In my example I am writing to /tmp, which is usually writable by anyone.
Potential Stumbling Block #2: If you are going to embed one of these queries into PHP code, a few extra escapes are necessary to get what you want. In PHP I ended up doing the following: (Note: I omitted all the particular details of my query.)
$q = "SELECT ..... INTO OUTFILE '/tmp/out.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY " . "'\\\"'" . " LINES TERMINATED BY '\\n' FROM ..... WHERE .....";
Potential Stumbling Block #3 Do not get the mysql system user confused with the mysql user who connects to your particular database. My db server runs several applications, each with its own database and mysql user, to keep the different users from tromping all over eachother’s databases. It is customary to GRANT ALL privileges on the database to a user whom the application uses in connecting to it. But ALL does not include the rights to create files on the file system. That is set as a GLOBAL attribute, which makes sense when you think about it: either a mysql user can create files on the filesystem or he can’t. Here is a discussion about it. You would issue a command like this:
GRANT FILE on *.* to 'dorkage'@'localhost';
and to look at who has this permission, do
use mysql; select Host, User, File_priv from user;
Stumbling Block #4: I include code to explicitly delete the file if it previously existed. If you don’t do this, you might find yourself using last week’s copy and never getting an updated one.
Stumbling Block #5Be very very careful who you give FILE permissions to on your server. Most of the places I surfed that told you how to do it didn’t even mention the security issues involved with this at all. If I were hosting for other people I wouldn’t let them have FILE permission. If they wanted to create CSV’s I’d provide a special place where certain vetted queries could run as me. Huge security concern. Keep your eyes open on this one or hackers may be able to DoS your server up with a bunch of useless files.