AWSAWS Aurora

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

Screen Shot 2020-04-12 at 4.13.29 PM
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.
Screen Shot 2020-04-12 at 4.36.47 PM
 
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

Leave a Reply