Creating and Configuring Secure AWS RDS Instances with Read Replicas and Backup Solutions
Fernando Medina Corey
In this live AWS environment, you will learn how to create an RDS database, then successfully implement a read replica and backups for that database. You will learn how to access that database and verify that it is working properly, and discover how to make sure your RDS instance can failover to a read replica if it were to go down. By the end of this environment, you should feel comfortable creating and securing relational databases with backup solutions. Here is the link to several of the SQL commands you may find useful for some of the videos - https://github.com/linuxacademy/csa-a-2018/blob/master/relational-database-service/sql_commands.md
Creating and Configuring Secure AWS RDS Instances with Read Replicas and Backup Solutions
In this lab, we are going to create and configure an RDS database with some backup solutions. We'll start by constructing a VPC, an internet gateway, route tables, and security groups. Next, we'll create some subnets that we'll put different things in, like a bastion host and some EC2 instances that we will use to connect to our RDS instances. Finally, we will place our primary RDS instance and our RDS replica in private subnets. We will then configure some backup and failover solutions for these resources.
Sign in to the AWS console with the username
cloud_user and the password provided on the lab instructions page.
Creating the Beginnings of Your VPC
The first step is to create the different resources we'll need for this lab. In the AWS Management Console page, open the VPC, RDS, and EC2 services in new browser tabs. If you don't see them under the Recently visited services header, you can also find them by opening the Services dropdown menu and searching for them there. Once you've opened these three services, you can close your original AWS Management Console tab.
Navigate to the VPC Management Console and click on Your VPCs in the left sidebar. Click the Create VPC button at the top of the page. In the Create VPC menu, type "AppVPC" for the Name tag and "10.0.0.0/16" for the IPv4 CIDR block. Leave the rest of the default options as they are, and click Yes, Create.
The next step is to create an internet gateway that will allow traffic to come in from the open internet and connect to the bastion host EC2 instance. Click on Internet Gateways in the left sidebar, and click the Create Internet Gateway button at the top of the page. For the Name tag, type "Lab-IG", then click Create. Once the internet gateway has been created, click the Close button to exit the success message.
Right now, our internet gateway's state is detached, so the next step is to attach it to the VPC we created earlier. Click the Actions button, and select Attach to VPC from the dropdown menu. Click into the VPC field, and select AppVPC from the dropdown menu. (If you don't see AppVPC as an option in this menu, try refreshing the console.) Click Attach. The State field should now say attached.
Next, we're going to create three subnets that we'll be using throughout this lab. Click Subnets in the left sidebar, and then click the Create subnet button at the top of the page. For the Name tag, type "public1". Click into the VPC field, and select our VPC from the dropdown menu. Next, click into the Availability Zone field, and select the us-east-1a option. For the IPv4 CIDR block, type "10.0.1.0/24". Click Create, and then close out of the success message. You should now see an entry called public1 in the subnet list.
Now let's create our second subnet. Click the Create subnet button at the top of the page. This time, type "private2" for the Name tag. Click into the VPC field, and select our VPC from the dropdown menu. Next, click into the Availability Zone field, and select the us-east-1a option. For the IPv4 CIDR block, type "10.0.2.0/24". Click Create, and then close out of the success message. You should now see an entry called private2 in the subnet list.
Finally, let's create our third subnet. Name this subnet "private3". Click into the VPC field, and select our VPC from the dropdown menu. This time, select us-east-1b for the Availability Zone. For the IPv4 CIDR block, type "10.0.3.0/24". Click Create, and then close out of the success message. You should now see an entry called private3 in the subnet list.
Now, let's create our security group. Navigate to your EC2 Management Console browser tab, and click Security Groups in the left sidebar under the Network & Security header. You'll notice there is a default security group already on this page. However, it's best practice for us to create our own security group and customize it for our needs so we can control traffic on our terms.
Click the Create Security Group button at the top of the page. In the Create Security Group menu, type "Bastion-SG" for the Security group name. Enter the same name for the Description. Click into the VPC menu, and select AppVPC. Next, click the Add Rule button on the left side of the menu. Click into the Type field, and select SSH from the dropdown menu. This will populate the Protocol field with "TCP" and the Port Range with "22". Click into the Source menu, and select the My IP option to automatically populate this field with your IP address. (Note: This option only works if your IP address stays consistent throughout the lab. If you're completing this lab on a corporate network or using more than one computer, you may need to choose the Anywhere option instead.) Next, click Create. You should now see our Bastion-SG security group in the list, as well as the inbound rule that we set up for port 22. By default, the outbound rule is set to allow all traffic, so we don't need to make any modifications.
Creating a Secure RDS Instance
Now we're ready to create our RDS database. Navigate to the RDS portion of the AWS console. Now, before we create an RDS instance, we first need to create a subnet group. Click Subnet groups in the left sidebar, then click the orange Create DB Subnet Group button. Under Name, enter "rds-private-subnetgroup", and then copy and paste the same text into the Description field. Under VPC, select AppVPC.
Next, we need to add specific subnets to our subnet group. Under Availability zone, select us-east-1b. The Subnet field should automatically populate with one of the private subnets (the only one that we assigned to this availability zone), so we don't need to change anything here. Click Add subnet.
Now let's add another subnet. Under Availability zone, select us-east-1a. This time, the Subnet field will not automatically populate. This is because there are two subnets assigned to this availability zone, so we have to tell AWS which one we want assigned to this subnet group. We need to make sure we select the correct subnet name here. Navigate to the Subnets list in the VPC Management Console, and check the name of the private2 subnet. It should look something like this:
subnet-38ea4b16. Next, go back to the RDS console and select the subnet-38ea4b16 (10.0.2.0/24) option. Click Add subnet. You should now have two private subnets listed under Subnets in this subnet group. If everything is configured correctly, these should be the subnets with CIDR blocks 10.0.2.0/24 and 10.0.3.0/24. If everything looks right, click Create.
Now let's create an instance. Click Databases in the left sidebar, then choose Create Database. On the Select engine page, under Engine options, select Amazon Aurora. Then click Next at the bottom of the screen. On the Configuration page, choose Provisioned for the Capacity Type. Next, change DB instance class from the default to db.t2.small — 1 vCPU, 2GiB RAM. Then, under Multi-AZ deployment, select No. Now scroll down to the Settings section. Under DB instance identifier, type "rdslab-instance", and under Master username, type "rds_user". Finally, enter a password under Master password (best practice is to choose a strong password and save it in a password manager). Click the Next button at the bottom of the screen.
On the Configure advanced settings page, change the Availability zone to us-east1b. Next, enter "labclusterid" for the DB cluster identifier and "labrdsdb" for the Database name. The Database port should be set to 3306. Optionally, you can change the Backup settings to greater than the default 1 day if you'd like. In the Monitoring section, change the Enhanced monitoring setting to Disable enhanced monitoring, as we won't be needing it for this lab. (Note: If enhanced monitoring is left on, you'll experience permissions errors with this lab, so make sure this feature is disabled.) Leave the rest of the settings on this page set to their defaults. Click the Create database button at the bottom of the screen, then click View DB instance details. It may take a few minutes to spin up.
While we're waiting for it to spin up, let's create our Bastion host. Navigate to your EC2 Management Console tab, click Instances in the menu on the left, and click the Launch Instance button at the top of the screen. Under Choose an Amazon Machine Image (AMI), select Amazon Linux 2 AMI (HVM), SSD Volume Type - ami-b70554c8. Leave the Instance Type set to the default t2.micro option. Click Next: Configure Instance Details. Next, click into the Subnet field, and select the public1 subnet of the us-east-1a availability zone. For Auto-assign Public IP, choose Enable. Then click Next: Add Storage, followed by Next: Add Tags and Next: Configure Security Groups.
On the Configure Security Group page, choose Select an existing security group under Assign a security group. Select the Bastion-SG group by clicking the checkbox on the left. Next, click Review and Launch, followed by Launch. On the Select an existing key pair or create a new key pair pop-up, change Choose an existing key pair to Create a new key pair. For Key pair name, type "labkeyfile". Then click Download Key Pair. It should show up in your Downloads folder. Finally, click the Launch Instance button.
Click View Instances. Once the Instance State changes from pending to running, make sure it is selected, and then click the Connect button at the top of the page. Next, open your terminal application. In the Connect To Your Instance menu, copy the command listed under Step 3:
chmod 400 labkeyfile.pem
Paste this command into your terminal application and press Enter to run it. This should grant the lab key file the appropriate permissions so that we won't run into issues with AWS when we try to use it.
The next step is to copy the SSH connection command, paste it into the terminal, and press Enter to sign in to the instance. The command should look something like this:
ssh -i "labkeyfile.pem" email@example.com
However, you'll see that it's not actually allowing us to connect. This is because there's one more thing we need to set up in our lab environment: route tables. We won't be able to connect to the instance without setting up route tables for our VPC. Let's fix that now.
Navigate to your VPC Management Console browser tab, and click Route Tables. We're going to create two route tables. Click the Create Route Table button at the top of the screen. In the Create Route Table pop-up, enter "RT-Public" for the Name tag. Make sure the VPC is set to AppVPC, then click the Yes, Create button. Make sure to select the checkbox next to the newly created route table. Next, click the Routes tab at the bottom of the screen, and click the Edit button. Click Add another route and type "0.0.0.0/0". Click into the Target field, and the internet gateway we created earlier should auto-populate as an option. If it doesn't, make sure you have created the internet gateway, then try typing in "igw", and it should pop up for you to click on and populate the field. Next, click Save.
Now click the Subnet Associations tab, and click Edit. Click the checkbox next to the public1 subnet to select it, then click Save. This will associate our RT-Public route table with our public1 subnet.
Once you see the Save successful message, check back on your terminal application. You should see a message that says, "Are you sure you want to continue connecting (yes/no)?" Type
yes. This will add the fingerprint of the EC2 instance to our list of trusted fingerprints and allow us to connect to the host.
Now let's create our second route table to manage the traffic in the private portion of our VPC. In the VPC Management Console, click the Create Route Table button again. For the Name tag, enter "RT-Private". Once again, make sure the VPC is set to AppVPC, then click Yes, Create. This time, we don't need to make any changes to the Routes section; by default, the routes for our private route table will only have the local destination and target. Make sure the RT-Private route table is selected, click the Subnet Associations tab, and check the checkboxes next to the private2 and private3 subnets to select them. Then click Save.
Next, let's install MySQL on our EC2 instance. Enter the following command in your terminal:
sudo yum install -y mysql
This will allow us to use the MySQL command line client to connect to the RDS instance.
Navigate to the RDS Management Console. The Status should now be available.
> Note: You may have to refresh the page to see the DB instance status change to available.
Connecting to and Testing our RDS Instance and Read Replica and Creating Snapshots
Creating an RDS Replica
We'll start by logging in to our RDS instance. In the RDS Management Console tab, click the Databases tab, then click the rdslab-instance link. Scroll down the page to the Connect header, and copy the endpoint for our RDS instance. It should look like this:
Go back to the terminal, and enter the following command, replacing
<ENDPOINT> with the endpoint of your database instance:
mysql -h &lt;ENDPOINT&gt; -P 3306 -u rds_user -p
Press Enter to run the command. You'll notice that nothing happens. This is because we haven't yet set up the security group for our RDS instance.
Switch to your EC2 Management Console tab, and select Security Groups under Network & Security in the left sidebar. Select the security group called rds-launch-wizard by checking the checkbox to the left, then click the Inbound tab at the bottom of the screen. Next, click Edit. In the Edit inbound rules menu, under Source, enter "sg-" (or click it in the dropdown if it auto-populates there) to select the IP of the Bastion host. This will overwrite the previous IP, which was most likely set to your personal IP address by default.
Now go back to the terminal, and press the up arrow key twice to paste the previous command we used again. Next, copy and paste the flags and options we set before (
-P 3306 -u rds_user -p), then press Enter to run the entire command string. You should see a prompt to enter your password. Enter your password, and press Enter. We now have a MySQL client in our terminal, which means we've successfully connected to the RDS database.
Now let's try adding some data to it. The first step is to create a database called "ratings". In your terminal, type
CREATE DATABASE ratings;, and press Enter.
Next, we need to tell MySQL to actually use the database we just created.
Then, check to make sure the previous command was successful by entering the following function:
SELECT DATABASE ();
You should see a table that indicates the database currently in use is "ratings". Now let's check to see if there are any tables in our database.
This command returns an empty set, which tells us that there are currently no tables in the database. Let's fix that now.
We're going to create a table that has the following attributes:
- Author = Variable Character Field (Varchar) of 20
- Rating = Between 1 and 10
- Timerated = Date and time
Enter the following command:
CREATE TABLE ratings (author VARCHAR (20), rating TINYINT, timerated DATETIME);
SHOW TABLES; command again and press Enter. This time, we can see that we have a table called "ratings". Run the
DESCRIBE ratings; command to check the details of the table we just created. If everything is working as expected, you should see all of the attributes we just assigned to the table.
Now let's add some data to this table.
INSERT INTO ratings ( author, rating, timerated ) VALUES ( "Fernando Medina Corey", 10, "2018-06-26 10:06:47"), ( "Treva Williams", 10, "2018-06-23 10:06:27");
The output of this command indicates that we successfully added data to our table. Next, enter the following command:
SELECT * FROM ratings;
This returns our table and lists the author, rating, and time we inserted.
The next step is to create an Aurora read replica. Go back to your RDS browser tab, click Databases, and select the rdslab-instance. Next, click Instance actions in the top-right of the page and Create aurora read replica. For the Availability zone, select us-east-1a. For Publicly accessible, choose No. Leave the DB instance class set to the db.t2.small option. For DB instance identifier, enter "rdslab-instance-rr-id". Leave the rest of the options set to their defaults, and then click Add reader.
Once our read replica is finished creating, we should be able to sign in to it and see our ratings table. On the Databases page, select the read replica we just created. Scroll down the page to the Connect header, and select the endpoint. Copy it to your clipboard.
In your terminal window, exit the MySQL instance by pressing Ctrl+C. Sign in to the EC2 instance with the following command:
mysql -h &lt;READ_REPLICA_ENDPOINT&gt; -P 3306 -u rds_user -p
Enter your password at the prompt. We are now signed in to the read replica.
Run the following command:
Next, let's tell MySQL to select our
USE ratings; SELECT DATABASE ();
Now what if we wanted to insert a third author into our table? Currently, our read replica is read-only, meaning we can only retrieve data from it; we can't add or modify anything. But we can change this.
Go back to the Databases page of the RDS console. Select rdslab-instance, then click Instance actions and Delete. Type "delete me" to confirm, then click Delete. While the instance is deleting, scroll to the right of the screen, and watch the Replication role field. After a few minutes, you should see the read replica's role change from reader to writer.
Go back to your terminal, and let's try inserting additional authors into our table.
INSERT INTO ratings ( author, rating, timerated ) VALUES ( "Another Author", 10, "2018-06-26 10:06:47"), ( "More Authors", 10, "2018-06-23 10:06:27");
The output shows us that we were successfully able to add this new data. Next, run the following command again:
SELECT * FROM ratings;
The table should now include four authors, ratings, and dates/times.
As a final step, let's try taking an instance snapshot. Select the read replica database instance, and click Instance actions and Take snapshot. Name the snapshot "snaptest" and click Take Snapshot.
Select the snapshot by clicking the checkbox on the left, then click Actions and Restore Snapshot. From this page, we could create a new instance from our snapshot, much like we did earlier when we created one from scratch.
Congratulations, you've successfully completed this lab!