Export Amazon RDS DB Snapshot to S3

Launch this lab in the IP Lab Portal, then follow the steps below in the AWS console. Open IP Lab Portal

​

Overview

​

Lab Details

1. This lab walks you through the steps to export the RDS DB snapshot to S3.
2. In this lab, you will practice creating an Amazon RDS DB Instance, Amazon S3 Bucket, AWS KMS Key.
3. Duration: 2 hours
4. AWS Region: US East (N. Virginia) us-east-1

​

Introduction

1. All types of RDS Backups can be exported to S3 whether they are Automated Backups, Manual Backups or those created by AWS Backup service.
2. Steps to export to s3:
   • We have to create an Amazon S3 Bucket with the required IAM permissions and create a KMS key for server-side encryption (SSE).
   • The Snapshot can be exported either via Console or CLI commands.
3. The Export runs in the background. It does not affect any kind of database performance.
4. The data which is exported to S3 is always in the Apache Parquet format. Parquet format is 2 times faster to export and consumes up to 6 times less storage in Amazon S3 compared to test formats.
5. The exported data can be analyzed by other AWS services like Amazon Sagemaker, Amazon EMR, and Amazon Athena.

​

Architecture Diagram

​

Use cases

1. Disaster Recovery
2. Data migration
3. Perform queries on the exported snapshot using AWS services like Amazon Sagemaker, Amazon EMR, and Amazon Athena.

​

Task Details

1. Sign in to AWS Management Console.
2. Create an Amazon S3 Bucket.
3. Create an AWS KMS Key.
4. Create a Security group for RDS DB Instance.
5. Create an Amazon RDS DB Instance.
6. Take a snapshot from an existing DB Instance.
7. Create a sample database and a table.
8. Export the Snapshot to S3.
9. Deleting AWS resources

​

Pre-requisites

1. For testing this lab, it is necessary to download the MySql GUI Tool, To download it, go to the Download MySQL Workbench page. Based on your OS, select the respective option under Generally Available (GA) Releases. Download and Install.

​

Launching Lab Environment

1. To launch the lab environment, Click on the Start Lab button.
2. Please wait until the cloud environment is provisioned. It will take less than a minute to provision.
3. Once the Lab is started, you will be provided with IAM user name, Password, Access Key, and Secret Access Key.

Note : You can only start one lab at any given time

​

Lab guide

​

Lab Steps

​

Task 1: Sign in to AWS Management Console

1. Click on the Open Console button, and you will get redirected to AWS Console in a new browser tab.
2. On the AWS sign-in page,
   • Leave the Account ID as default. Never edit/remove the 12 digit Account ID present in the AWS Console. otherwise, you cannot proceed with the lab.
   • Now copy your User Name and Password in the Lab Console to the IAM Username and Password in AWS Console and click on the Sign in button.
3. Once Signed In to the AWS Management Console, Make the default AWS Region as US East (N. Virginia) us-east-1.

​

Task 2: Create an Amazon S3 Bucket

1. Navigate to the Services menu at the top. Click on S3 under the Storage section.
2. On the S3 Page, click on Create bucket and fill in the bucket details.
   • Bucket type : General purpose
   • Bucket name: Enter export-snapshot-demo
     • Note: S3 bucket name is globally unique, choose a name that is available.
3. Object ownership: Select ACLs disabled (recommended) option.
4. Leave all the other settings as default.
5. Click Create bucket button.

​

Task 3: Create an KMS Key

1. Navigate to the Services menu on the top. Click on Key Management Service under the Security, Identity, & Compliance section.
2. In the KMS console, navigate to Customer managed keys and click on Create Key.

Note: Ignore, if any other customer managed keys present in the account. It will take some time to delete the keys.

3. Select the Key Type as Symmetric and click on Next.
4. Under the Add Labels, give the following details:
   • Alias: Enter kmskey_1 (Change the name, if there is a key present with the same name)
   • Description: Enter Used for RDS Snapshot exports to S3 of the “database-demo” DB Instance
   • Click on Next.

     Note : Ignore if any error message appear like below.

     
5. Leave Define key administrative permissions as default and click on Next.
6. Leave Define key usage permissions as default and click on Next.
7. Leave Edit key policy as default and click on Next.
8. Click on the Finish button.

   Note : Ignore if any error message appear like below.

   Click on the created KMS key. Copy the ARN and keep it for future reference.

​

Task 4: Create a Security group for RDS DB Instance

1. In this task, you  are going to create a Security group for RDS with a 3306 port number enabled
2. Navigate to EC2 by clicking on the Services menu, under the Compute section.
3. On the left panel menu, select the security group under the Network & Security section.
4. Click on the  Create Security group button.
5. In the security group page,
   • Security group name: Enter RDS_sg
   • Description: Enter Security group for RDS
   • VPC: Select Default VPC

• Click on the Add Rule button under Inbound rules.
  • Type: Select MYSQL/Aurora
  • Source: Select Anywhere-IPv4
  • In the textbox add 0.0.0.0/0
• Leave the outbound rules as it is.

6.Leave everything as default and click on the Create security group button.

​

Task 5: Create an Amazon RDS DB Instance

1. Navigate to the Services menu at the top left corner and click on Aurora and RDS present under the Database section.
2. RDS dashboard is displayed.
3. Click on the Create database button and you are navigated to the page where you will provide all the required details to create a MySQL database.
4. On the page, click on the option Standard create a method for our lab requirement.
5. In the Engine options, select MySQL engine type.
6. Edition: Leave it as default
7. Under Templates, select Sandbox option.
8. Under Settings, provide the following details.

• DB instance identifier: Enter demo-db
• Master username: Enter whizlabs
• Master password: Enter Whizlabs123
• Confirm password: Enter Whizlabs123

Note - Make sure the master and confirm passwords should be the same. Make a note of the username and password for later use.

9. Under DB instance class, select Burstable classes (including t classes) and select db.t3.micro
10. Under Storage,
    • Storage type: General Purpose (SSD)
    • Allocated storage: 20
    • Uncheck Enable storage autoscaling.
11. Leave the Availability and durability as default.
12. Under Connectivity:
    • Network & Security
      • Virtual Private Cloud (VPC): default VPC
      • Subnet group: default
      • Public accessibility: Choose Yes (Important)
      • VPC security groups: Select Choose existing Remove the default one and select RDS_sg instead.

• Availability zone: default No Preference.

13. Leave Database Authentication as default.
14. Expand the Additional configuration.

15. In the displayed layout provide the following values under Database options.

• Initial database name: Enter demodb
• Leave DB parameter group and Option group as default.
• Under Backup, uncheck Enable automated backups.
• Leave other settings as default.
• Uncheck Deletion protection.

16. Click on Create database button to create the database.  This process does take time between 5-10 minutes.
17. Once the database is created the status changes to Available.
18. Click on the database identifier demo-db and copy the database endpoint.

​

Task 6: Connect to RDS Database using the MySQL Workbench

In this example, we will connect to a database on a MySQL DB instance using MySQL monitor commands. One GUI-based application you can use to connect is MySQL workbench, which you have already downloaded and installed based on instructions in the prerequisite section.

1. To connect to a database on a DB instance using MySQL monitor, find the endpoint (DNS name) and port number for your DB Instance.
   • Navigate to Databases and click on the created demo-db.
   • Under Connectivity & security section, copy and note the endpoint and port.
     • Endpoint: demo-db.c7owzvjtjkvz.us-east-1.rds.amazonaws.com
     • Port: 3306
     • You need both the endpoint and the port number to connect to the DB instance.
2. Open MySQL Workbench. Click on the MYSQL Connection plus icon.
   • Connection Name    : Enter a sample name MyDBConnection.
   • Host Name: Enter the endpoint: demo-db.c03iqoooef9u.us-east-1.rds.amazonaws.com
   • Port: 3306
   • Username: Enter whizlabs
   • Password: Click on Store in Vault option and enter a password - Whizlabs123. Click on Ok.

• Click on Test Connection to make sure that you are able to connect to the database properly.

• Click on OK and OK again to save the connection.

3. A database connection will be created in MySQL Workbench.

4. Double-click on it to open the database. Enter the database password if prompted.
5. After successfully connecting and opening the database, you can create tables and perform various queries over the connected database.

6. Navigate to the Schemas tab to see databases available to start doing database operations.

​

Task 7: Create a sample database and a table

1. In the SQL Editor, let us create a sample database and a table for demo purposes.

Note: After executing every command, it is necessary to clear the editor and proceed to the next command.

2. For executing the command press the icon as shown below in the image.

3. To see the changes after every execution, click on the Refresh button, right to the SCHEMAS.

   Note: Please copy-paste the SQL queries carefully, double-check, and remove extra whitespaces.

4. Create a database:

CREATE DATABASE SchoolDB; 6.You can see the created database with the following command: show databases; 7.Switch to the database named SchoolDB. use SchoolDB; 8.Create a sample table consisting of Subjects. CREATE TABLE IF NOT EXISTS subjects ( subject_id INT AUTO_INCREMENT, subject_name VARCHAR(255) NOT NULL, teacher VARCHAR(255), lesson TEXT, PRIMARY KEY (subject_id) ) ENGINE=INNODB;

• Enter

show tables;

• to see the table you just created.
• Insert some details into the table executing one after the other:

INSERT INTO subjects(subject_name, teacher, lesson) VALUES (‘English’, ‘John Taylor’, ‘Chapter one’); INSERT INTO subjects(subject_name, teacher, lesson) VALUES (‘Science’, ‘Mary Smith’, ‘Chapter four’); INSERT INTO subjects(subject_name, teacher, lesson) VALUES (‘Maths’, ‘Ted Miller’, ‘Chapter ten’); INSERT INTO subjects(subject_name, teacher, lesson) VALUES (‘Arts’, ‘Suzan Carpenter’, ‘Chapter seven’);

• Let’s check the items we added to the table:

select * from subjects; 9.We have created a sample database and table. 10.Close the database connection by clicking on the x symbol.

​

Task 8: Take a Snapshot from the existing DB Instance

1. Let us take a snapshot of the database.
2. Select the created DB Instance and click on Actions.
3. Click Take snapshot from the options.

4. Give a name to the snapshot, demo-db-snapshot  (give a unique name for validation report purposes) and click on the Take snapshot button.
5. The snapshot creation takes 3-5 minutes. Refresh after some time, the snapshot creation status will be Available.

​

Task 9: Export the DB snapshot to S3

1. Let us export the snapshot to S3.
2. Select the created snapshot and click on the Actions.
3. Click on Export to Amazon S3 from the options.
4. Under Settings, give a name to identify the export:
   • Export Identifier: Enter snap-export-s3
   • Leave the Exported data as default.
   • Under the S3 destination, select the created S3 from the drop-down.
   • Under IAM Role, choose to Select an existing role from the drop-down named as WhizRole.

     Note: Do not select any other IAM role present instead of WhizRole.

5. Select the created KMS key from the drop-down or enter the ARN that we noted while creating the key.

Note: There may be other keys present, select the one you created in previous steps.

6. Review all the settings and click on Export to Amazon S3.
7. This may take upto 20-25 minutes as it is exporting the whole database. You can see a Complete status once the export is done.

Note: You will be seeing other exports also in the Export to Amazon S3 tab. The reason is, we cannot delete them. Ignore the other exports done, if you see them.

​

Task 10: Check the exported data in Amazon S3

1. Once the export is completed, navigate to the created S3 Bucket.
2. You will be able to see a folder with the name of the export identifier.
3. When clicked on that, you can see 2 JSON files.
   • The First JSON file is the final report of the export task.
   • The Second JSON file gives us information about the individual table including overall size and the data type mappings.
4. You can see the SchoolDB database we created earlier.
5. In this way, we can export the snapshots to S3.

Do you know?

Cross-Region Snapshot Exports: By default, you can export an RDS DB snapshot to an S3 bucket in the same AWS Region. However, if you need to export the snapshot to an S3 bucket in a different AWS Region, you can leverage the AWS Data Pipeline service. It allows you to create a pipeline that copies the snapshot to a different region.

​

Task 11: Delete AWS Resources

Deleting the DB Instance

1. Click on Databases present to the left of the screen.
2. Select the DB Instance and click on Actions.
3. Click on the Delete option.
4. Uncheck the Create final snapshot option.
5. Check the Acknowledge box.
6. Confirm the deletion by entering delete me and click on delete.

7. The status changes to deleting and the DB Instance gets deleted.
8. You can proceed to further steps even if it is in a deleting state.

Deleting the Snapshot

1. Click on the Snapshots on the left of your screen. Under Manual snapshots, select the unencrypted snapshot and click on Actions.
2. Click on the Delete snapshot option.
3. Confirm by clicking on the Delete button.

​

Completion and Conclusion

1. You have created an S3 Bucket to store the backup of exports.
2. You have created a Customer managed KMS key.
3. You have created an Amazon RDS DB Instance.
4. You have taken the snapshot of the DB Instance.
5. You have exported the snapshot to Amazon S3.
6. You have checked the exported data in the S3 Bucket.

​

End Lab

1. Sign out of AWS Management Console.
2. You have successfully completed the lab.
3. Once you have completed the steps, click on End Lab from your IP Lab Portal and wait till the process gets completed.

​

What gets checked

When you press Check my work, the platform verifies each of these:

• Create Private S3 bucket — Check whether a private S3 bucket is created or not
• Launch RDS instance — Check whether an RDS Instance is created or not
• Create RDS Snapshot — Check whether an RDS Snapshot is created or not.
• check s3 object — Check whether an object is uploaded to the S3 bucket.

​

Related help

• FAQs and Troubleshooting