Build an AWS MySQL RDS Database and connect to it from your network
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:
Login to your AWS console and search for RDS in the Find Services field — click through to the Create Database section and select your database type under Engine options (This example is using MySQL). After selecting your engine options you will need to select your use case and click next:
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.
Name your database and supply it with a username and password you can remember.
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:
Note: VPC’s and Subnets are one of the most important and complicated parts of managing and maintaining AWS infrastructure. I would recommend reading through the AWS Documentation regarding VPC’s and Subnets. However, you should be able to complete this tutorial without an intimate understanding of these components.
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)
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.
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.
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.
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.
Route Table Overview:
The Routes tab should show two destinations and two targets:
The Subnet Associations tab should show the two subnets we created:
The subnets we created should also reveal the Internet Gateway we associated with them via the Route Table.
Back to the database:
Return to the database creation tab we left open before creating the VPC and click on the refresh icon under the VPC subheading. Click on the drop down after the refresh and the VPC we created in the previous steps should be available (if it is not, double check that the VPC and/or database instance was created in the same region). The subnet group should automatically populate with the ID field that reads something like default-vpc-<id_number>. If this isn’t the case, then we have not successfully tied the two subnet groups to the VPC in the previous steps and will need to trouble shoot the issue. We will need to select Public Availability -> Yes if we want to access this database from outside the VPC it was created in. (We will also create a new security group for the database and apply rules that only allow specific IP addresses to connect.) But first, we need to get the database created. There are quite a few options in the Advanced Settings portion of the database creation, most of which are outside the scope of this tutorial, however, I would recommend you read over all of them. To finish this up, we simply need to name the database, Enable deletion protection and click Create database.
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:
Click into DB Instances and then click on the database that was created:
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:
If you left Create new VPC security group selected (as is shown above), and RDS security group was created in the VPC Dashboard -> Security Groups menus option. Open up the Security Groups option and select the newly created SG so we can apply a new rule:
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.
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
Where -h is the endpoint (host) provided, -P is the port provided, -u is the username we supplied and -p tells mysql to prompt for a password after the command is issued. Alternatively you can use something like Sequel Pro or MySqlWorkbench and create a connection the database. An example of a database connection using Sequel Pro can be found below.
That should do it.
If everything went off without a hitch and I didn’t forget some crucial step in my write up, your RDS DB should now be accessible from your network. If you discovered an error or inaccuracy in the instructions, or simply need some clarification on the process, please comment below and I will do my best to correct the error or help you out.
Don’t forget to check out my other AWS articles linked below:
