I recently had the need to extract some data from a MySQL database running in production and while some methods are brutally simple and some a little more involved, all of them come with a few caveats.

Database dump

The mysqldump command can be used to dump a database to disk. Note that in this case the entire database (structure and data) is dumped as a single sql file containing the requisite commands which can be used to re-create the database. The following is an example of a script that can be used to create a database backup:

#!/bin/sh

DBHOST=${1}
DBPORT=${2}
DBUSER=${3}
DBNAME=${4}
POSTFIX=${5}
DATE=`date +"%Y%m%d-%H%m"`
SQLFILE=~/db-backups/$DBNAME-${DATE}${POSTFIX}.sql

mysqldump --no-tablespaces --add-drop-table --host=$DBHOST --port=$DBPORT --user=$DBUSER -p $DBNAME > $SQLFILE
gzip -f $SQLFILE  

As can be noted, running the script above should prompt you for the password. Finally, note that is mostly an option for smaller databases that can fit within a reasonable file size.

SELECT ... INTO OUTFILE

The following command is a nice clean way to extract data from a table into a csv file:

(SELECT 'notificationId','text','status')
UNION
(SELECT notificationId, text, status 
FROM Notification
INTO OUTFILE '/tmp/notification.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ',' 
ESCAPED BY '"' 
LINES TERMINATED BY '\n');

Running the above command gave a mysql ERROR 1045 (28000): access denied which was solved by granting the File privilege to the user in question: sudo mysql and then GRANT FILE on *.* to exampleuser;. You might need to exit your mysql shell and log back in for the privilege to take effect.

Running the command a second time gave a different error: ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement. The issue here is that the secure-file-priv option does not allow mysql to read/write from outside of a particular directory. This directory can be found by running the command: SHOW variables LIKE 'secure_file_priv';. In this case it was /var/lib/mysql-files. Thus changing the above command to the following resulted in the csv being created:

(SELECT 'notificationId','text','status')
UNION
(SELECT notificationId, text, status 
FROM Notification
INTO OUTFILE '/var/lib/mysql-files/notification.csv'
FIELDS ENCLOSED BY '"' 
TERMINATED BY ',' 
ESCAPED BY '"' 
LINES TERMINATED BY '\n');

Finally, note that /var/lib/mysql-files is user protected and any files created belong to the mysql user as well.

One issue with the above command is that the target file must not already exist. A simple way to solve this problem is to add a timestamp. This can be achieved via a prepared statement:

SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s');

SET @FOLDER = '/var/lib/mysql-files/';
SET @PREFIX = 'notifications';
SET @EXT    = '.csv';

SET @CMD = CONCAT("SELECT * FROM notifications INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,
            "' FIELDS ENCLOSED BY '\"' TERMINATED BY ',' ESCAPED BY '\"'",
            "  LINES TERMINATED BY '\r\n';");

PREPARE statement FROM @CMD;

EXECUTE statement;

Finally, in case you have null values it is advisable to convert them to something useful as follows:

(SELECT 'notificationId','text','status')
UNION
(SELECT notificationId, text, IFNULL(status, "unknown") 
FROM Notification
INTO OUTFILE '/var/lib/mysql-files/notification.csv'
FIELDS ENCLOSED BY '"' 
TERMINATED BY ',' 
ESCAPED BY '"' 
LINES TERMINATED BY '\n');

In my case, I needed to extract data from a production instance where I did not have easy access via a db tool like MySQL workbench. If you do have access via a tool, you're probably better off extracting data using the tool as it will be more robust and provide more features :).

HackerNews submission / discussion