MySQL database on AWS
Revision as of 01:12, 14 December 2018 by Stephen.c.richardson (talk | contribs)
Overview
This is a how-to that goes through the process of setting up a MySQL database through Amazon Web Services (AWS).
Materials/Prerequisites=
There are no materials besides a computer, and the only prerequisite is having an AWS account, although creating one is very simple.
Process
- First, create an AWS account, and then use the services dropdown and select "RDS" under databases
- Next, create a database.
- Next, select the recommended case.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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:
- 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.
- 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.
- 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.
- 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 you create the table, you'll be able to add columns for the table to organize the data you'll be storing.
- 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.
- 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.
- 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 and Garrett Passamonti
