Restore an RDS Instance to a Point in Time using Snapshots

Hands-On Lab

 

Photo of Trent Hayes

Trent Hayes

Training Architect

Length

01:00:00

Difficulty

Intermediate

A critical part of database management is being able to recover your data after corruption or accidental deletion has occurred. In this scenario, you are a systems administrator for a company with a corporate website powered by Wordpress. The developer has just contacted you that some unspecified corruption in the database has occurred and now the site is down and the admin panel cannot be reached. Your task will be to use the point-in-time restore capability of RDS automated snapshots to restore the database and bring the site back up. NOTE: This lab may take up to 15 minutes to fully start due to the number of resources that need to be provisioned.

What are Hands-On Labs?

Hands-On Labs are scenario-based learning environments where learners can practice without consequences. Don't compromise a system or waste money on expensive downloads. Practice real-world skills without the real-world risk, no assembly required.

Restore an RDS Instance to a Point in Time Using Snapshots

Introduction

A critical part of database management is being able to recover your data after corruption or accidental deletion has occurred.

In this lab, we will learn how to create an RDS snapshot and use it to restore an RDS database to a specific point in time.

>Note: Because of the number of resources that must be provisioned, this lab may take up to 15 minutes to spin up.

Log in to the live environment with the credentials provided on the lab instructions page. Make sure you are using us-east-1 as your region throughout the lab.

Create an RDS Snapshot

  1. In the AWS Management Console, navigate to the EC2 service.
  2. Click Load Balancers in the left sidebar.
  3. In the Description tab at the bottom of the page, copy the DNS name to your clipboard.
  4. Paste the DNS name into a new tab in your browser.
  5. Go back to your AWS Management Console tab, and navigate to the RDS service.
  6. Under the Resources header, click DB Instances.
  7. Click the wordpress-database instance.
  8. Scroll down to the Snapshots header, and click Take snapshot.
  9. For Snapshot name, type "wordpress-date".
  10. Click Take Snapshot.

Corrupt the Database

  1. Navigate to the EC2 service, and click 3 Running Instances at the top of the page.
  2. Select the bastion-host.
  3. In the Description tab at the bottom of the page, copy the IPv4 public IP address.
  4. Open your terminal application, and log in to the bastion host via SSH.
    ssh cloud_user@<PRIVATE_IP_ADDRESS>
  5. Type yes at the prompt.
  6. Enter the password for the bastion instance at the prompt.
  7. Go back to the AWS Console and navigate to the RDS service.
  8. Click DB Instances and then click wordpress-database.
  9. Locate the endpoint under the Connect header, and copy it to your clipboard.
  10. Run the following command to connect to the MySQL database running in our private subnet:
    mysql --user=wpuser --password=Password1 --host=<WORDPRESS_DATABASE_ENDPOINT>
  11. Next, we'll tell MySQL to use the wordpress-database instance.
    use wordpressdb;
  12. Then, delete the post table.
    drop table wp_posts;
  13. Go back to our website tab in your browser, and refresh the page. The website should no longer be working properly because we have corrupted the database.

Restore an RDS Database from a Snapshot

  1. Go back to the AWS Management Console, and click Instances in the sidebar.
  2. Select the wordpress-database, then click Instance actions and Restore to point in time.
  3. For Restore time, select Custom.
  4. Enter today's date and a time between when the snapshot was created and when we corrupted the database.
  5. Scroll down to the Settings header, and type "wordpress-recovery" for the DB instance identifier.
  6. Change the Availability zone to us-east-1b.
  7. Click Launch DB Instance.
  8. Once the instance is available, select wordpress-database.
  9. Click Instance actions, then Modify.
  10. Scroll down to the Settings section, and change DB instance identifier to "wordpress-corrupt".
  11. Click Continue.
  12. Select the Apply immediately option, then click Modify DB Instance.
  13. Repeat steps 9-12 for the wordpress-recovery database, but change two things:
    • For DB instance identifier, type "wordpress-database".
    • For Security group, select the DatabaseSecurityGroup option from the dropdown.
  14. Once the wordpress-database is in an available state, go back to our website browser tab and refresh the page. The website should be working properly again, as we have successfully restored the database to a point in time before it was corrupted.

Conclusion

Congratulations, you've successfully completed this lab!