How to Export Amazon RDS MySQL Results to CSV

Over the past couple of months, I have been tasked with moving our server stack over from Digital Ocean VPS hosting to AWS. What used to be just one single Digital Ocean VPS is now an RDS instance, EC2, Elasticache, and S3 with Cloudfront.

Previously, it was possible to make CSV exports directly from the MySQL server using INTO OUTFILE in the query. This worked fine because the MySQL process had access to the server’s filesystem from which I would download the newly created CSV file. Using this processs is not possible using RDS’ MySQL and, instead, the following steps should be used instead:

1. Ensure you have the correct security group rules to allow access to your RDS instance.
2. Run the following command, replacing the values with your own:
   
   mysql your_database --user=your_username --password=your_password --host=your-host.eu-west-2.rds.amazonaws.com --batch -e "SELECT * FROM table" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > ~/output.csv
   
3. You will find the CSV file created at the location provided.
   Note: NULL column values will represented be literal “NULL” strings in the CSV file. If you would like to have them represented by something else, such as empty string “”, then this should be done in the SELECT query before output to the CSV file.
   
   For example, if name was NULLable then SELECT name FROM yourtable would turn into SELECT IFNULL(name, '') FROM yourtable.

Use SQL from a File

If you would like to redirect a file from stdin to your query (so you do not need to have the SQL written out in the CLI) then you can remove the -e flag and use the < redirection operator to provide the input stream to the script.

For example, your command might then look like this…

mysql your_database --user=your_username --password=your_password --host=your-host.eu-west-2.rds.amazonaws.com --batch < ~/input.sql | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > ~/output.csv