MySQL database on AWS
Contents
Overview
This is a how-to that goes through the process of setting up a MySQL database through Amazon Web Services (AWS).
Materials/Prerequisites
An existing AWS account
Process
Establishing your RDS instance
Log in to your AWS account and then navigate to Services > RDS
Next, create a database under Resources > DB Instances > Create database
There is a plethora of settings involved in configuring a database- when in doubt, leave it alone.
First, go to the bottom of the page and check the Only enable options eligible for RDS Free Usage Tier- this will ensure that you don't select a database which will consume hundreds of dollars over the course of the semester.
For Engine options, select MySQL and click Next.
In Instance specifications, leave all options alone. Ensure DB instance class is db.t2.micro.
Under settings, specify an instance identifier, master username, and master password. Please write down your username and password. The instance identified will be used only in AWS, and is separate from the name of your database.
Select Next.
After setting up the basic database info, you want to ensure the database is publicly accessible, which means that you can access it from anywhere with the correct endpoint, username, and password. Under Network & Security, ensure Public accessibility is Yes
Under Database options, specify a Database name. This will be used to connect to your database and potentially when issuing queries. Leave all other settings as their default.
Click Create Database.
Select the instance from the RDS dashboard and the page should look like this:
You will find the endpoint that AWS has created for you under Endpoint & port > Endpoint. Make a note of this endpoint, we will reference it later. Make sure that it is listed as publicly accessible (located under Security > Public accesibility, and your database is set up and ready to go.
Connecting MySQL Workbench to your RDS instance
Feel free to use another MySQL connection tool if you have one you're comfortable with, but we will be using MySQL workbench.
Install MySQL Workbench, which you can find here. It is a great tool that allows you to directly interact with your database via SQL query or GUI.
Once it is installed, the welcome page will prompt you to create a new connection. Do so, and then enter your database information.
In the Hostname line, copy and paste your database endpoint from your RDS instance. The port should be the same as listed on AWS (it should be the default port, 3306). Input the database name, username, and password. All of the other inputs for creating a database connection should be left blank or on default. Use the Test Connection button to ensure your connection settings are valid before clicking OK.
Once you create a connection, you will be able to start editing your database. Make sure to double click on your database name listed under "Schema" in the bottom left corner of the workbench home, and then you can begin executing queries against your database or managing it via the built in tools in MySQL workbench.
Troubleshooting
If at any point you are unable to connect to your database, you probably need to re-enable external access to it. Navigate to your DB instance on AWS, click on the security group, and then at the bottom of the screen under inbound add a TCP rule for all ports with the source 0.0.0.0/0.
Authors
Stephen Richardson, Garrett Passamonti Fall 2018
Ethan Shry Fall 2018
