Creating a MySQL Database using Amazon RDS

Photo Credit: ahana.io

What is Amazon RDS?

Amazon RDS (Relational Database Service) is a collection of cloud services provided by AWS which allow the user to simply build and operate databases. There are seven database engines to choose from which include: Amazon Aurora with MySQL compatibility, Amazon Aurora with PostgreSQL compatibility, MySQL, MariaDB, PostgreSQL, Oracle, and SQL server. Today we will be choosing MySQL as the engine for our database

Photo Credit: aws.amazon.com

Getting Started:

A beginner to AWS services or MySQL? No worries! Together, we will walk through the process of setting up an RDS instance with AWS. When we are all finished, all you will need to do is delete the instances and the rest is history.

Prerequisites

For you to proceed through this tutorial, ensure the following are completed:

• AWS free-tier account created
• Administrator IAM user created and logged in
• Have the latest version of MySQL workbench downloaded

Tasks

• Creating a Custom VPC
• Creating an Internet Gateway and Attaching it to our custom VPC
• Creating Public Subnets
• Create a VPC Security Group DB instance
• Create a DB subnet group
• Configure Public Subnet to Automatically assign Public IPv4 Addresses
• Create a Public Route table with an IGW route
• Create a MySQL instance

The Breakdown

• Delete MySQL Database
• Delete the VPC

Architecture

We are using a custom VPC with two public subnets. We use MySQL Workbench to manage our MySQL DB. The image below depicts this.

Creating a Custom VPC

The first step is to create a custom VPC (Virtual Private Cloud). While every AWS account comes with a default VPC, it is general practice to create a new VPC as the default VPC is connected to some AWS services already.

1. First, we need to select the designated region where we will install the instance on the top right of the management console. I will be selecting N. Virginia (us-east-1) but this will differ depending on your location.
2. Navigate to VPC in your management console, on the left sidebar select Your VPCs, then click Create VPC.

Note: Unless stated, everything else will be left as defaulted

3. Add the following configuration:

• Name: myVPC
• CIDR block: 10.0.0.0/16
• IPv6 CIDR block: No IPv6 CIDR block
• Tenancy: Default
• Tags: this should have defaulted with the Key: “Name” and Value: “myVPC”.

4. Click Create VPC. You should now see myVPC on the Your VPCs page.

5. We need to enable DNS hostnames. This is done by selecting myVPC, clicking on the Actions dropdown on the top right, and clicking Edit DNS hostnames.

6. On the Edit DNS hostnames info page, Select Enable under DNS hostnames then click Save Changes.

Creating an Internet Gateway (IGW) and Attaching it to myVPC

The next step is to create an internet gateway and attach it to the VPC we created to enable our Bastion Host to access the Internet.

1. Navigate to the VPC service in the menu, go to the Virtual Private Cloud page, and select Internet Gateways in the left sidebar.
2. Click Create Internet Gateway in the upper right corner and enter the following information:

• Name: myVPCIGW

3. Click Create Internet Gateway. You will now see myVPCIGW displayed on the Internet Gateways page but notice the state is detached. This is because it has not been attached to a VPC yet.

4. To attach the IGW to our VPC, select myVPCIGW then click on the Actions dropdown menu on the top right and select Attach to VPC.

5. Select myVPC then click Attach Internet Gateway.

6. You will see the state has gone from detached to attached.

Creating Public Subnets

An Amazon RDS instance requires either two private subnets or two public subnets. In this tutorial, we will be creating 2 public subnets which are configured within the VPC service.

1. On the VPC service page, click on Subnets in the left sidebar.
2. Click on Create Subnet in the top right corner.
3. Add the following configurations:

• VPC ID: myVPC
• Subnet name: public1Subnet
• Availability Zone: Your Designated Zone [Example: US East(N.Virginia)/us-east-1a]
• IPv4 CIDR block: 10.0.1.0/24
• Tags: leave the default for Key: Name and Value: public1Subnet

DO NOT create your subnet yet. The second public subnet needs to be created as well.

4. Click Add New Subnet and add the following configurations:

• Subnet name: public2Subnet
• Availability Zone: Your Second Designated Zone [Example: US East(N.Virginia)/us-east-1b]
• IPv4 CIDR block: 10.0.2.0/24
• Tags: leave the defaults of Key: Name and Value: public2Subnet

5. Click Create Subnet. You should now see your subnets listed on the Subnets page.

Create a VPC Security Group DB instance

Now it is time to add a security group to our VPC instance. In AWS, a Security Group is a virtual firewall that controls incoming and outgoing traffic with inbound and outbound rules.

1. Go to the VPC service, expand Security and select Security Group
2. In the upper right, click Create security group.
3. On Create security group, fill in the following:

Basic details

• Security group name: my-publicsg
• Description: Used for Creating a Private VPC with MySQL task
• VPC: myVPC

Inbound rules

• Type: SSH
• Source: Anywhere 0.0.0.0/0
• Type: HTTP
• Source: Anywhere 0.0.0.0/0
• Type: MYSQL/AURORA
• Source: Anywhere

Tags

• Key: Name
• Value: my-publicsg

4. Click Create Security Group.

5. You should now see my-publicsg on the Security Groups page.

Create a DB subnet group

A subnet group is as simple as it sounds. It is a group of subnets that are clustered together in an Amazon VPC.

1. Navigate to the RDS service page then click on Subnet Groups in the left sidebar and Create a DB Subnet Group on the top right.
2. On Create DB Subnet Group page, fill in the following:

Subnet group details

• Name: my-db-subnet-group
• Description: task Create MySQL DB
• VPC: myVPC

Add subnets

• Availability Zones: us-east-1a & us-east-1b
• Subnets: Your Designated Subnets (Example: us-east-1a & us-east-1b)

3. Click Create.

4. You should then see my-db-subnet-group on the Subnet Groups page.

Configure Public Subnet to Automatically assign Public IPv4 Addresses

For our instances that are launched into the public subnet, we want them to automatically request public IPv4 addresses.

1. Navigate back to the Subnet page within the VPC service.
2. Select public1Subnet and click on the Actions dropdown menu then select Edit Subnet Settings.
3. Under Auto-assign IP settings, check Enable auto-assign public IPv4 address.
4. Click Save and repeat steps 1 through 4 for public2Subnet.

Create a Public Route table with an IGW route

While you may try and infer that our subnets are public due to their names, public1subnet/public2subnet, they’re not public until they have a route to the internet gateway.

1. Within the VPC service sidebar, select Route Tables.
2. Click Create Route Table and add the following configurations:

• Name: publicRT
• VPC: myVPC

3. Click Create.

4. Select publicRT within the list of route tables, then scroll down the page until you see the Details. Select Subnet Associations.

5. Click on Edit Subnet Associations, select public1Subnet and public2Subnet, then click Save Associations.

6. On the Route Tables page, click on publicRT, then select the Routes tab.

7. Click Edit routes, then click Add route.

8. Add the following configuration:

• Destination: 0.0.0.0/0
• Target: Internet Gateway — myVPCIGW

9. Click Save changes.

10. We now see our route on the Routes page and our subnet is truly public.

Create a MySQL instance

1. Navigate to the RDS service page and click on Databases.
2. In the upper right of the Databases page, click Create database.
3. On Create database page enter the following:

• Choose a database creation method: Standard create
• Engine options: MySQL
• Edition: MySQL Community
• Version: MySQL 8.0.25
• Templates: Free Tier

Settings:

• DB instance identifier: my-mysqldb-instance

Credentials Settings

• Master username: dbadmin
• Master password: <password>
• Confirm password: <password>

DB instance class (accept the default)

• Burstable classes (include t classes)
• db.t2.micro

Storage (accept the defaults)

• Storage type: General Purpose SSD (gp2)
• Allocated storage: 20 GiB
• Storage autoscaling: Enable storage autoscaling
• Maximum storage threshold: 1000 GiB

Connectivity

• Virtual private cloud (VPC): myVPC
• Subnet group: my-db-subnet-group
• Public access: Yes
• VPC Security group: remove the default then select my-publicsg
• Availability Zone: No preference

Expand Additional configuration

• Database port: 3306

Database authentication

• Database authentication options: Password authentication
• Expand Additional configuration

Database options:

• Initial database name: sample
• DB parameter group: default.mysql8.0
• Option group: default: mysql-8–0

Accept the defaults through the rest of the settings then click Create database.

Connect to MySQL using MySQL Workbench

1. We need to get the DB Endpoint in order to connect our Amazon RDS instance to MySQL Workbench. To get the DB Endpoint, on the Databases page, click my-mysqldb-instance.
2. On Connectivity & Security, under Endpoint and Port, copy the Endpoint URL. We will paste this in as the Hostname in the MySQL Workbench.
3. Open MySQL Workbench. On the Welcome to MySQL Workbench page, click on the + to the right of MySQL Connections.
4. On Setup New Connection screen, fill in the following:

• Connection Name: Amazon MySQL DB
• Hostname: <DB Endpoint URL>
• Username: dbadmin
• Password: click Store in Keychain/Vault then enter a password

5. Click Test Connection and the result is a message: Successfully made the
MySQL connection with information related to the connection.

6. On the Welcome to MySQL Workbench page, Amazon MySQL DB is now listed under MySQL Connections.

Here is a video which walks through the process of connecting the DB instance we just created to MySQL Workbench!

The Breakdown

In order to avoid any unwanted changes, we need to clean up the instances we created during this tutorial!

Delete MySQL Database Connection

1. On the MySQL Workbench Welcome Page, click on the wrench to open the Manage Server Connections Page.
2. Select Amazon MySQL DB on the left sidebar, click Delete, then close the page.

Delete Amazon RDS MySQL Database

1. Go to the Databases page and navigate to the database instance we just created.
2. Select my-mysqldb-instance then in the Actions dropdown menu click on Delete.

Delete the VPC

1. Go to VPC service and navigate to your list of VPCs.
2. Select myVPC which we previously created then in the Actions dropdown menu click on Delete VPC.

Congrats! You’ve reached the end of this tutorial!

This is just the beginning! Now it’s time to dive deep into SQL and relational databases to continue your journey.

Was this confusing or did it help? Let me know your thoughts in the comments! Follow me to stay up to date with my latest posts and to continue learning!