Aurora MySQL — Export data to S3

Using SELECT INTO OUTFILE S3 you can query data from an Aurora MySQL DB cluster and save it directly into text files stored in S3 bucket.

1. Create an IAM policy for S3.

{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "VisualEditor0",
"Effect": "Allow",
"Action": [
"s3:DeleteObject",
"s3:GetBucketLocation",
"s3:GetObject",
"s3:ListBucket",
"s3:ListBucketMultipartUploads",
"s3:PutObject"
],
"Resource": [
"arn:aws:s3:::bucket-name",
"arn:aws:s3:::bucket-name/*"
]
},
{
"Sid": "VisualEditor1",
"Effect": "Allow",
"Action": [
"s3:ListAllMyBuckets",
"s3:HeadBucket"
],
"Resource": "*"
}
]
}

2. Create an IAM role, choose RDS, Under Select your use case, choose RDS — Add Role to Database. Attach the above policy to this role.

3. Set either the aurora_select_into_s3_role or aws_default_s3_role DB cluster parameter to the ARN of the new IAM role, created in step 2. If using default DB cluster parameter group, create a new one, change the mentioned parameter and modify the cluster to use it.

4. Associate the role with DB Cluster.

  • Choose the DB cluster → Connectivity & security → Manage IAM roles → Select IAM roles to add to this cluster

5. Confirm the parameter setting and if needed reboot the instance.

mysql> SELECT @@GLOBAL.aurora_select_into_s3_role;
+------------------------------------------------+
| @@GLOBAL.aurora_select_into_s3_role |
+------------------------------------------------+
| arn:aws:iam::xxxxxxxxxxxx:role/mysqllsss3-role |
+------------------------------------------------+
1 row in set (0.09 sec)

6. Configure Aurora MySQL DB cluster to allow outbound connections to Amazon S3. If the DB cluster is in the Private subnet, configure the VPC to have VPC Gateway endpoint for S3 associated with the DB cluster’s route table.

7. Export data to S3. The master user name for a DB cluster is granted the SELECT INTO S3 privilege by default. If you are using other user, grant it before executing the statement.

mysql> SELECT * FROM abcd INTO OUTFILE S3 's3://mysqllsss3/abcd' 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
Query OK, 255 rows affected (0.19 sec)
mysql>

8. Confirm the file in S3.

Reference -

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.SaveIntoS3.html
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.Authorizing.Network.html

Avid learner of technology solutions around databases, big-data, Machine Learning. 5x AWS Certified | 5x Oracle Certified. Connect on Twitter @anandp86

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store