MySQL database on AWS

From ESE205 Wiki
Jump to navigation Jump to search

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

Select 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.

Set Up Database Info

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

Publicly Accessible

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.

Database Name

Click Create Database.

Select the instance from the RDS dashboard and the page should look like this:

DB Instance

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.

DB Info

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.

Setting up a connection on MySQL Workbench

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

Easy Stir Fry Project

Link to Project Wiki

Weekly Log