Build an AWS MySQL RDS Database and connect to it from your network

Image for post
Image for post

I was working on a small greenfield project with a friend of mine and things were moving fast. We each had our own development environments set up on our local workstations, and can easily work on different features and branches in parallel, thanks to git, however keeping our development databases synced up was a different story. We found ourselves copying and pasting SQL dumps into Slack to keep each other up to date. Dev teams, there is a better way, and for the low price of ~$14 a month (also free-tier eligible), you to can deploy an RDS instance to AWS, and have your team use it as its source of development truth! Here’s how…

This tutorial will switch between standing up the database and the network/VPC configuration throughout. I would recommend quickly reading through the process first, then taking it step by step.

The Database:

Image for post
Image for post

On the Specify DB Details page the options we are going to concern ourselves with include what is shown below. To come in around the $14 a month mark, we are going to use the smallest instance type available with 20 GiB of storage. If these settings do not fulfill your needs, you can make adjustments to the Instance Class and the Allocated Storage fields and AWS will estimate the monthly cost for the parameters you specify.

Image for post
Image for post

Name your database and supply it with a username and password you can remember.

Image for post
Image for post

AWS recommends using AWS IAM accounts to control access to their databases. Setting up those IAM users is outside of the scope of this tutorial. For more information, please see the AWS RDS Security Best Practices in the AWS Documentation.

The Network:

The next step in the process is setting up the Networking and Security. To do this, we are going to make a quick pivot and walk through the steps of setting up a new Virtual Private Cloud, defining subnets, creating an internet gateway and wiring it all together. This is required if we want to make the database publicly accessible to our dev team.

First, open a new tab and visit the VPC Dashboard then create a new VPC and name it something useful. We will supply the IPv4 address with a /22 CIDR block only because we are also going to need to create two unique subnets in two different availability zones if we wish to attach them to our RDS instance. (We could provide a smaller range of IPs using a different CIDR, but we probably won’t end up with too many machines in this VPC so configuring a flat network isn’t going to result in a bunch of broadcast traffic traversing the IP space)

Image for post
Image for post

Create two subnets (an example of creating one is below) and supply them with their own names — attach the VPC we just created to them, and select an availability zone for each. Note: Each subnet will need to be in a different AZ. The image below shows no preference, but if we select the drop down box we will see a list of the availability zones accessible within the region. For this IPv4 CIDR block I have chosen to go with 10.0.3.0/24. The next subnet we create will need a different IPv4 CIDR range (10.0.2.0/24) and AZ.

Image for post
Image for post

Select Internet Gateway from the VPC Dashboard and click Create internet gateway. The window that appears will have a single name field to edit. Name the IGW something useful, and then attach it to the VPC we created in the previous steps.

Image for post
Image for post

Create a Route Table and associate the Internet Gateway with the table. Click on Route Tables from the VPC Dashboard and Create route table — the route table creation table will have a name field and a VPC selection dropdown. Name it something useful and select the VPC we created. Select the newly created route table from the list and click on the Routes tab. Click the Edit routes button and add a route. There should be a default route in the table that shares the IPv4 CIDR block from the VPC with the target local, what we need to add is a route with the destination 0.0.0.0/0 with a Target pointing to the IGW we created in the previous steps. Click Save routes.

Image for post
Image for post

Next we need to associate the subnets we created with the routing table. Click the Subnet Association tab and Edit subnet associations — the two subnets we created in the previous steps should be available to select. Add them both and click Save.

Image for post
Image for post

Route Table Overview:

The Routes tab should show two destinations and two targets:

Image for post
Image for post

The Subnet Associations tab should show the two subnets we created:

Image for post
Image for post

The subnets we created should also reveal the Internet Gateway we associated with them via the Route Table.

Image for post
Image for post

Back to the database:

Image for post
Image for post

The next screen we will see should contain a green box letting us know the database is being created. Click back to the Amazon RDS dashboard:

Image for post
Image for post

Click into DB Instances and then click on the database that was created:

Image for post
Image for post

Under Info we see Creating. This can take some time, so we should modify our Security Group while we wait for the database to stand up.

Back to the network:

Image for post
Image for post

With the Inbound Rules tab selected, click on Edit rules and in the Type column, select MYSQL/Aurora (if that was the database engine selected in the first steps). You will notice that it defaults to the same port that the database setup defaulted too, this is the MySQL standard port. If you made a port change in the setup process you are also going to have to write a custom rule for this security group. In the source field, use the drop down to change the field from Custom to My IP, the empty field should populate with your IP address (If it does not auto populate, or the IP doesn’t look right, you can visit https://ifconfig.co/ to discover your public IP). Add a description if you’d like, something like office, or home, to identify the source of that address, and click Save rules. You will need add a rule to the SG for each person and/or location you would like access from. (Do not use a generic rule or 0.0.0.0/0 as the IP, this will open up the database to the world)

For more on Controlling Access with Security Groups, check the AWS Documentation.

Our database should be running at this point, so click back to the Amazon RDS dashboard and check if it is ready. Your Connectivity and security section should not look like mine. If you read the right hand column, I failed to enable Public Accessibility. A properly configured database should show Yes in this field. If yours does not, click on the Modify button at the top right of the screen and turn it on.

Image for post
Image for post

Testing the connection:

Under the Endpoint and port header in the image above, you will find the Endpoint sub-heading for your database. You have a couple of options to test this connection. Using the terminal, you can connect using mysql if you have it installed on your:

mysql -h <your_endpoint_here> -P 3306 -u <username> -p

Image for post
Image for post

That should do it.

Don’t forget to check out my other AWS articles linked below:

Spinning up an EC2 instance.

Medium, Lambda, and Me (or how I export Medium stories to my website)

Setting up your Identity and Access Management for AWS

Connection an AWS VPC to your VPN — From the Cloud to the Colo.

Written by

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store