Dumping Data, Saving Passwords, and OUTFILE Syntax with MySQL

This is a side-post cut out of “Using R to Automatically Generate Pie Charts“. I enjoyed talking about the architecture behind making the graphs, but the specific hurdles I hit with MySQL were overwhelming and detrimental to the overall point: simple automated graph generation. I hit three hurdles getting MySQL to work with my automated pie graphs:

1) Dumping a table with the column names on the top row.

2) Having the root user perform these tasks without placing the MySQL password in the cronjob.

3) Dumping the data to the same location through a cronjob.

Dumping the table with column names:

Dumping a CSV file is easy to do, and can be accomplished using a simple MySQL query or by using the mysqldump utility. I figured I’d start with the mysqldump utility since I didn’t want to store my password in a cronjob (which was fixed later.) I wanted the column names to be on the first row in order to have R know the table information.  I had a line working with mysqldump that would dump the data to CSV, but couldn’t get the column names included in the first line. Mysqldump will not dump a CSV file with the column names in the first row.

I ended up using a cronned MySQL query that dumps a CSV file. There are many different examples of this syntax online, but fewer for dumping the columns as well (specifics shortened up:)

mysql -u root -e “SELECT ‘id’,’title’,’url’,’section’ UNION SELECT id,title,url,section INTO OUTFILE ‘/tmp/result.csv’ FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘\”‘ ESCAPED BY ‘\\\\’ LINES TERMINATED BY ‘\n’ FROM db.table;”

The part before the “UNION SELECT” will specify the first row that gets sent to the file; these are your headers. I have a small amount of information to dump to the file, so I hard-coded the column names.

Saving the user password to a configuration file

Having the correct SELECT query didn’t help, because I didn’t want to put my password in the plaintext of the cronjob. StackOverflow came to the rescue, and a few minutes later I had a configuration file for the MySQL user:

http://serverfault.com/questions/56341/mysqldump-prompting-for-password-in-shellscript/56345#56345

User Zoredache explains the reason behind doing this perfectly by saing, “…having it in the command line [would allow] anyone who can run ps find the password for your server.” After creating this file, a simple “chmod 600” ensured that the contents of the file would remain secured.

Dumping the data to the same location nightly and OUTFILE syntax

I thought I’d change the path to something other than /tmp before realizing the MySQL needs permission to write to the directory. After getting the query correct, I ran it. My intentions were to have this run each night (or week, or month) and have the previous file overwritten. The OUTFILE syntax doesn’t allow for the overwriting of files though:

The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. file_namecannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed. (http://dev.mysql.com/doc/refman/5.0/en/select.html)

This one was simple: I added a cronjob that would “rm” the old CSV file once the R script finished.

Finish the rest of the post at: Using R to Automatically Generate Pie Charts