Exporting table from Amazon RDS into a csv file

Question

I have a mysql database running in Amazon RDS, and I want to know how to export an entire table to csv format. I currently use mysql server on Windows to query the Amazon database, but when I try to run an export I get an error, probably because there's no dedicated file server for amazon RDS. Is there any solution to this?

Answer 1

Presumably you are trying to export from an Amazon RDS database via a SELECT ... INTO OUTFILE query, which yields this indeed commonly encountered issue, see e.g. export database to CSV. The respective AWS team response confirms your assumption of lacking server access preventing an export like so, and suggests an alternative approach as well via exporting your data in CSV format by selecting the data in the mysql command line client and piping the output to reformat the data as CSV, like so:

mysql -u username -p --database=dbname --host=rdshostname --port=rdsport --batch 
  -e "select * from yourtable" 
  | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > yourlocalfilename

User fpalero provides an alternative and supposedly simpler approach, if you know and specify the fields upfront:

mysql -uroot -ppassword --database=dbtest 
  -e "select concat(field1,',',field2,',',field3) FROM tabletest" > tabletest.csv

Good luck!