Export data to a CSV file using only the MySQL command prompt

Recently our marketing department has been running a series of promotions as contests. The system is setup to receive entries, but for the present time we will not be building an administration interface. The entries will instead be extracted into a CSV file manually when the contest deadline has passed. Up until this point I was unaware that MySQL could perform the task sans server-side language at the command prompt.

For those of you who rely on a tool such as phpMyAdmin, you might find this to be just as quick. If you have root access, or the proper system administration privileges, then it will be rather painless. This method also tends to be less brutish and less tedious than a complete mysqldump.

MySQL:

SELECT tableColumnName1, tableColumnName2 
INTO OUTFILE '/path/to/file/data.csv' 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
FROM tableName;

Access Denied

Even if you have been granted the SELECT privilege, you will still need to be granted the FILE privilege, or else access will be denied. Some architects will only grant this privilege to the root user. Another reason that you might receive an access denied message, is because the directory where you are trying to save the CSV file is not writable.

In order to prevent a user from accidentally overwriting an important system file (like etc/passwd), you will be unable to save over a file that already exists on the server.

• Posted by Brian
• September 19th, 2007