Skip to main content

Creating an Amazon Aurora RDS Database (MySQL Compatible)

Hands-On Lab

 

Photo of Kelby Enevold

Kelby Enevold

AWS Training Architect I

Length

00:45:00

Difficulty

Beginner

In this hands-on lab, we will create an Aurora MySQL database. First, we will verify that the security groups, network ACL (NACL), and route tables are all configured to allow communication between an instance in a public subnet and an RDS database in a private subnet. After verifying the appropriate settings are in place, we will create the database. Once that is done, we will use an EC2 instance as a bastion to allow us to connect to our private database from an external source. We will utilize MySQL Workbench to connect to our private RDS Aurora database through the public EC2 bastion.

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.

Creating an Amazon Aurora RDS Database (MySQL Compatible)

Introduction

In this hands-on lab, we will create an Aurora MySQL database. First, we will verify that the security group, network ACL, and route table are all configured to allow communication between an instance in a public subnet and an RDS database in a private subnet.

After verifying the appropriate settings are in place, we will create the database. Once that is done, we will use an EC2 instance as a bastion to allow us to connect to our private database from an external source. We will utilize MySQL Workbench to connect to our private RDS Aurora database through the public EC2 bastion.

Solution

Log in to the live AWS environment using the credentials provided. Make sure you're in the N. Virginia (us-east-1) region throughout the lab.

Download and install MySQL Workbench on your desktop. This application is necessary to connect to the MySQL database. You can download the file directly from MySQL.

Be sure to select the correct operating system!

Configure the Security Groups, Route Tables, and NACL

Verify Subnet Route Tables and NACL

  1. Navigate to VPC.
  2. Click Subnets in the left-hand menu, and we'll see there are three subnets listed.
  3. With Public Subnet 1 selected, click the Route Table tab lower on the page, and verify it is associated with the route table that contains an internet gateway.
  4. Click the Network ACL tab, and verify it allows all TCP traffic from ports 0 - 65535 and any source.
  5. Un-select Public Subnet 1, and select Private Subnet 2.
  6. Click the Route Table tab, and verify it is associated with the route table that does not contain an internet gateway.
  7. Click the Network ACL tab, and verify it allows all TCP traffic from ports 0 - 65535 and any source.
  8. Un-select Private Subnet 2, and select Private Subnet 3.
  9. Click the Route Table tab, and verify it is associated with the route table that does not contain an internet gateway.
  10. Click the Network ACL tab, and verify it allows all TCP traffic from ports 0 - 65535 and any source.

Configure Security Groups

  1. Click Security Groups in the left-hand menu, and select the one listed.
  2. Click the Inbound Rules tab, and then click Edit rules.
  3. Set the following values:
    • Type: SSH
    • Source: Anywhere
  4. Click Save rules.
  5. Click Create security group.
  6. On the Create security group page, set the following values:
    • Security group name: Database
    • Description: Database security group
    • VPC: Select our listed VPC
  7. Click Create.
  8. Select the Database security group in the table, click the Inbound Rules tab, and click Edit rules.
  9. Click Add Rule, and set the following values:
    • Type: MYSQL/Aurora
    • Source: Custom, and then type sg- and select the default security group
  10. Click Save rules.

Set Up an EC2 Instance for SSH Tunneling

  1. Navigate to the EC2 dashboard, and click Launch Instance.
  2. On the AMI page, select the Amazon Linux AMI.
  3. Leave t2.micro selected, and click Next: Configure Instance Details.
  4. On the Configure Instance Details page:
    • Network: Leave default
    • Subnet: Public Subnet 1
    • Auto-assign Public IP: Enable
  5. Click Next: Add Storage, and then click Next: Add Tags.
  6. On the Add Tags page, add the following tag:
    • Key: Name
    • Value: EC2Bastion
  7. Click Next: Configure Security Group.
  8. Click to Select an existing security group.
  9. Select the default security group (not our Database security group) in the table.
  10. Click Review and Launch, and then Launch.
  11. In the key pair dialog, select Create a new key pair.
  12. Give it a Key pair name of "Bastion".
  13. Click Download Key Pair, and then Launch Instances.
  14. Click View Instances, and give it a few minutes to enter the running state.
  15. Copy its public IP address into a text file, as we'll need it later in the lab.

Create an RDS Aurora Database

Create Database Subnet Group

  1. Navigate to RDS.
  2. Click Subnet groups in the left-hand menu.
  3. Click Create DB Subnet Group, and set the following values:
    • Name: Aurora Subnet Group
    • Description: Aurora Subnet Group
    • VPC: Select our VPC
  4. Click Add all the subnets related to this VPC.
  5. When the list of subnets appear, click to Remove the public subnet.
    • If you can't remember which is which, head back to the VPC dashboard to verify which CIDR block is associated with the public subnet.
  6. Click Create.

Create Database

Note: AWS has recently updated the database creation screen. Please note that the video may show the old database creation process, but this lab guide has updated steps.

  1. Click Databases in the left-hand menu.
  2. Click Create database.
  3. In the Engine options section, set the following values:
    • Engine type: Amazon Aurora
    • Edition: Amazon Aurora with MySQL compatibility
    • Version: Aurora(MySQL)-5.6.10a
    • Database Location: Regional
  4. In the Database features section, select One writer and multiple readers.
  5. In the Templates section, select Dev/Test.
  6. In the Settings section, set the following values:
    • DB cluster identifier: AuroraInstance
    • Master username: Admin
    • Master password: Enter a password you'll be able to remember later
  7. In the DB instance size section, select the following:
    • DB instance class: Burstable classes (includes t classes)
    • From the dropdown, select db.t2.small
  8. In the Availability & durability section, set Multi-AZ deployment to Create an Aurora Replica/Reader node in a different AZ.
  9. In the Connectivity section, ensure the LinuxAcademy VPC is selected.
  10. Expand Additional connectivity configuration.
  11. Select the Aurora Subnet Group we previously created.
  12. Set the Publicly accessible flag to No.
  13. Under VPC security group, select Choose existing, remove the default security group, and select the previously created security group named Database.
  14. In the Additional configuration section, set the following values:
    • In the Database Options section, set DB instance identifier to AuroraInstance
    • In the Database Options section, set Initial database name to Testdb
    • In the Monitoring section, disable enhanced monitoring by un-checking Enable Enhanced monitoring.
    • In the Deletion protection section, disable deletion protection by un-checking Enable Deletion protection.
  15. Click Create database. It will take a few minutes to finish being created.
  16. On the database dashboard, click aurorainstance.
  17. Copy the endpoint name that's associated with the Writer type, and paste it into text file, as we'll need it for the next step. (Note: Make sure you wait until its status is Available before attempting to connect in the next step.)

Verify Connectivity Using MySQL Workbench

  1. Open MySQL Workbench.
  2. Click the plus sign by MySQL Connections.
  3. In the Setup New Connection dialog, set the following values:
    • Connection Name: Aurora
    • Connection Method: Standard TCP/IP over SSH
    • SSH Hostname: Paste in the public IP address of our EC2 instance (which you copied earlier)
    • SSH Username: ec2-user
    • SSH Key File: Select the .pem key file we previously downloaded
    • MySQL Hostname: Paste in the endpoint name you copied in the previous part of the lab
    • Username: Admin
    • Password: Store in Vault..., and enter the password you entered when creating the database
  4. Click Test Connection.
  5. If an error pops up, this is fine — click Ok.
  6. You should eventually see the connection was successful.
  7. Click OK in the Setup New Connection dialog.
  8. Double-click the new Aurora connection option that now exists, which will open the SQL Editor.
  9. Run the query SHOW DATABASES; (by typing it into the editor and clicking the lightning bolt icon), which should return the TestDb schema — the one we identified while creating the Aurora database.

Conclusion

Congratulations on completing this hands-on lab!