Export Data from RDS MySQL to CSV File

A customer wanted to know how to export data from RDS MySQL table into CSV format. There are few different ways to export data. In the above script, I am showing how to install Percona Toolkit and use pt-archiver to export data in CSV format.

Here are other ways I could think:

• mysql command can be used to output data to file. But it is limited to tab delimited. If you want a comma-separated output, you can use the CONCAT function in your SQL query to manually format your output as CSV. Here's an example:

mysql --host=$DB_INSTANCE_NAME --user=$USER_NAME -p $DB_NAME -e \
   "SELECT CONCAT(productCode, ',', productName, ',', productLine, ',', productScale, ',', productVendor, ',', productDescription, ',', quantityInStock, ',', buyPrice) FROM $TABLE_NAME WHERE productCode BETWEEN 'S10_1949' AND 'S24_2000'" > ./output.csv

• mysqldump - This option will not export data in CSV format for Cloud Managed databases

• Output to s3 - You can use SELECT INTO OUTFILE S3 clause to write data in CSV format to s3 bucket. This requires IAM roles with specific permissions. For example:

SELECT isoId, mktDate, nodeId, peakTypeId, hour, lmp, mcc, mlc 
  FROM historic_da_prices_hourly 
 WHERE mktDate BETWEEN '2023-01-02' AND '2023-01-02' AND isoId = 7
 INTO OUTFILE S3 's3://aurora-select-into-s3-pdx/historic_da_prices_hourly'
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'

• AWS Data Pipeline - You can use Data Pipeline to automate the movement of data from Amazon Aurora MySQL to Amazon S3.

• AWS Glue - A bit complicated and requires few lines of code but you can get data in parquet format in s3.

As time permits, I'll put instructions on how to export in different ways.

#rds #mysql #export #csv #percona-toolkit #pt-archiver