Menu
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 a solution to this?
394
You can query data from an RDS for PostgreSQL DB instance and export it directly into files stored in an Amazon S3 bucket. To do this, you use the aws_s3 PostgreSQL extension that Amazon RDS provides. The upload to S3 uses server-side encryption by default.
To export data to an Amazon S3 file, give the RDS for PostgreSQL DB instance permission to access the Amazon S3 bucket that the export will use for storage. Doing this includes the following steps: Create an IAM policy that provides access to an Amazon S3 bucket that you want to export to.
AWS Region (optional) – The AWS Region where the Amazon S3 bucket is located. If you don't specify an AWS Region value, then Amazon RDS saves your files into Amazon S3 in the same AWS Region as the exporting DB instance .
To export data from the Aurora table to the S3 bucket, use the SELECT INTO OUTFILE S3 The following statement exports the entire table to the S3 bucket. If you’re trying this feature out for the first time, consider using the LIMIT clause for a larger table. See the following code: The following code shows the exported file in the S3 bucket.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
