Difference between revisions of "MySQL database on AWS"

From ESE205 Wiki
Jump to navigation Jump to search
 
(11 intermediate revisions by 2 users not shown)
Line 3: Line 3:
 
This is a how-to that goes through the process of setting up a MySQL database through Amazon Web Services (AWS).
 
This is a how-to that goes through the process of setting up a MySQL database through Amazon Web Services (AWS).
  
−
==Materials/Prerequisites===
+
==Materials/Prerequisites==
−
There are no materials besides a computer, and the only prerequisite is having an AWS account, although creating one is very simple.
+
An existing AWS account
  
 
==Process==
 
==Process==
  
−
* First, create an AWS account, and then use the services dropdown and select "RDS" under databases
+
===Establishing your RDS instance===
 +
 
 +
Log in to your AWS account and then navigate to '''Services > RDS'''
  
 
[[File:MySQL1.png|Center|300px|Select RDS]]
 
[[File:MySQL1.png|Center|300px|Select RDS]]
  
−
* Next, create a database.
+
Next, create a database under '''Resources > DB Instances > Create database'''
  
−
[[File:MySQL2.png|Center|300px|Create a Database]]
+
There is a plethora of settings involved in configuring a database- when in doubt, leave it alone.
  
−
* Next, select the recommended case.
+
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.
 +
<!-- JDF: Do we need a picture here with a circle on the box to click? -->
  
−
[[File:MySQL3.png|Center|300px|Select Case]]
+
For '''Engine options''', select '''MySQL''' and click '''Next'''.
  
−
* Next, you have to name your database. After selecting a case, you will be brought to a page where you establish setting for your database instance.  
+
In '''Instance specifications, leave all options alone. Ensure '''DB instance class''' is '''db.t2.micro'''.
  
−
* For "DB Instance Identifier", select a name that is related to your project but make sure it isn't too long or complicated. For the master username, the general standard for databases is to use "root" as the username and it is easier and recommended to use that.  
+
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.
−
 
 
−
* The password should not be easy to guess, and it is important to have a good password because if someone gains access to your database they can potentially use way more space than is provided for free and result in charges to your AWS account.  
 
  
 
[[File:MySQL4.png|Center|300px|Set Up Database Info]]
 
[[File:MySQL4.png|Center|300px|Set Up Database Info]]
  
−
* After setting up the basic database info, you have to input more advanced settings. You want to make the database publicly accessible, which means that you can access it from anywhere with the correct endpoint, username, and password.  
+
Select '''Next'''.
  
−
* Re-enter your database name, and leave the cluster-identifier blank, as AWS will provide a cluster-identifier. The cluster identifier is the endpoint for your database, which is similar to an IP address, only it is used for databases. Leave all other advanced settings as their defaults.
+
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'''
  
 
[[File:MySQL5.png|Center|300px|Publicly Accessible]]
 
[[File:MySQL5.png|Center|300px|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.
  
 
[[File:MySQL6.png|Center|300px|Database Name]]
 
[[File:MySQL6.png|Center|300px|Database Name]]
  
−
* You are now ready to create your instance. After doing so, select the instance from the RDS dashboard and the page should look like this:  
+
Click '''Create Database'''.
 +
 
 +
Select the instance from the RDS dashboard and the page should look like this:  
  
 
[[File:MySQL7.png|Center|300px|DB Instance]]
 
[[File:MySQL7.png|Center|300px|DB Instance]]
  
−
* Scroll through this page, and you will find the endpoint that AWS has created for you. Make sure that it is listed as publicly accessible, and your database is set up and ready to go.  
+
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.  
  
 
[[File:MySQL8.png|Center|300px|DB Info]]
 
[[File:MySQL8.png|Center|300px|DB Info]]
  
−
* Next, install MySQL Workbench. It is a great tool that allows you to create tables and other schema within your database without having to actually code.
+
===Connecting MySQL Workbench to your RDS instance===
−
 
 
−
* 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 AWS instance, make sure the port is the same as listed on AWS (it should be the default port, 3306). Make sure to correctly input the database name, username, and password. All of the other inputs for creating a database connection should be left blank or on default.  
+
Feel free to use another MySQL connection tool if you have one you're comfortable with, but we will be using MySQL workbench.
  
−
[[File:MySQL9.png|Center|300px|Setting up a connection on MySQL Workbench]]
+
Install MySQL Workbench, which you can find [https://dev.mysql.com/downloads/workbench/ here]. It is a great tool that allows you to directly interact with your database via SQL query or GUI.
  
−
* 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 create a table by clicking the table with a plus symbol on it in the top right corner.  
+
Once it is installed, the welcome page will prompt you to create a new connection. Do so, and then enter your database information.  
  
−
[[File:MySQL11.png|Center|300px|Create a Table]]
+
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 the table, you'll be able to add columns for the table to organize the data you'll be storing.  
+
[[File:MySQL9.png|Center|300px|Setting up a connection on MySQL Workbench]]
  
−
* You'll probably want to make the first column an id number that corresponds to the row of the database, and to do this make the id column auto-incrementing and the primary key. This will also automatically change it to non-null. Doing this makes it easier to keep track of your rows.  
+
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.
  
−
* For the other columns, you can change the data type to a string (listed in MySQL workbench as var char), an int, or many others. There are a lot of other tools within MySQL workbench, and using them is not challenging. For a basic database, it is easy to use and visualize, as well as being versatile enough to be used for complex databases.
+
== Troubleshooting ==
  
−
[[File:MySQL13.png|Center|300px|Editing your table]]
+
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.
  
−
* With your AWS database instance and MySQL workbench, using your database for whatever you are designing should run smoothly and be easy to edit. For information on MySQL syntax and coding, w3schools is an excellent resource.
+
==Authors==
 +
Stephen Richardson, Garrett Passamonti Fall 2018
  
−
==Authors==
+
Ethan Shry Fall 2018
−
Stephen Richardson and Garrett Passamonti
 
  
 
==Easy Stir Fry Project==
 
==Easy Stir Fry Project==
 
[[BD_App|Link to Project Wiki]]
 
[[BD_App|Link to Project Wiki]]
 +
 
[[BD_App_Weekly_Log|Weekly Log]]
 
[[BD_App_Weekly_Log|Weekly Log]]
  

Latest revision as of 23:20, 4 February 2019

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

Retrieved from "http://classes.engineering.wustl.edu/ese205/core/index.php?title=MySQL_database_on_AWS&oldid=16459"