A Guide to Setting Up and Connecting to MySQL Database on Amazon RDS

6 min readJan 10, 2024

Introduction

Amazon Relational Database Service (Amazon RDS) is a managed service that provides an easy way to set up, run and scale databases in the cloud.

Amazon RDS supports a variety of database engines for data organization and storage. These engines include Amazon Aurora, PostgreSQL, MySQL, MariaDB, Microsoft SQL Server and Oracle.

Relational database management tasks such as data migration, backup, recovery and patching are made easier with the help of Amazon RDS.

This article provides a comprehensive guide for setting up an Amazon RDS instance on AWS and connecting to it using MySQL Workbench. This guide will introduce you to the basics of provisioning and managing a relational database in the cloud.

Prerequisites

To get the most out of this guide, you should have a basic familiarity of the AWS services as well as the AWS Management Console. Having an active AWS account is highly recommended in order to get the most out of this guide.

You should have MySQL workbench installed on your machine. An installation guide can be found here. Familiarity with basic SQL is also recommended.

The services used in this guide will be within the AWS Free Tier.

1. Creating an RDS Instance

The first step will be to create an RDS instance.

In the RDS Console, click on Create Database.

Select Standard Create.

Select the MySQL engine

There are pre-configured templates to choose from: Production, Dev/Test and Free tier. Select the Production template. As you proceed, you will configure this template within the Free Tier limits.

Under the Availability and durability section, click on Single DB instance.

Leave the DB Instance Identifier and Master username as database-1 and admin respectively.

Provide and confirm a master password.

NOTE: You will later use this password as you connect to the database on MySQL workbench, so keep it handy.

Under Instance configuration, toggle the Include previous generation classes switch, select Burstable classes (Includes t classes) and select db.t2.micro which is Free Tier eligible.

In the Storage section, choose the General Purpose SSD (gp2) storage type and allocate 20GiB storage.

Scroll to the Connectivity section and select Don’t connect to an EC2 compute resource.

Leave the default options for the VPC and subnet group.

Enable Public access by clicking on Yes.

You will create a new VPC security group. Select Create new, provide a VPC security group name and select No preference for Availability Zone.

Scroll to the Database authentication section and select Password authentication.

Open the Additional configuration section, and provide an Initial database name.

Finally, AWS will provide you with an Estimated Monthly. However, if you selected for a db.t2.micro, you are within the Free Tier limits.

Click on Create Database.

Your RDS instance will be created after a couple of minutes. Wait for the status to read Available.

Once your database is created, click on the DB identifier. Information about the database such as CPU, Class, Status and Region & AZ is provided.

Your next task will be to modify the Security Groups and provide necessary permissions that allow you to connect to the database.

Scroll down to Connectivity & Security and click on security group under VPC security groups.

Select the security group you created earlier.

Scroll down, select the Inbound rules tab and click on Edit inbound rules.

Change the Source to Anywhere-IPv4.

Click on Save rules.

You have now created an RDS instance with the necessary permissions to establish a connection.

2. Connecting to the Database

Open MySQL workbench on your machine and click on the + sign to establish a connection.

Configure the following settings:

Connection Name: Provide a name for the connection.
Connection Method: Standard (TCP/IP)
Hostname: Copy the RDS instance’s endpoint (The instance’s endpoint can be found under Connectivity & security)
Port: 3306
Username: admin
Password: Click on Store in Keychain and enter the same password as you used when creating the RDS instance.

Click on Test Connection.

A success message should pop out indicating that the connection has been successfully made.

Click on OK.

The new connection will appear under MySQL Connections.

You have now successfully connected to your Amazon RDS instance.

3. Perform Basic Database Operations

Once connected, you can create tables, insert data and run SQL queries to interact with the database.

Click on the connection you established. An SQL Editor should open up.

Run and execute the following SQL query:

CREATE TABLE mytable (
  first_name VARCHAR(20),
  last_name VARCHAR(20)
);

Once executed, you can view your tables under Schemas on the navigation bar at the left side of the window.

You’ve now performed an SQL operation on your database.

4. Resource Cleanup

To avoid incurring ongoing charges, terminate the RDS instance after successfully completing this walk-through.

Conclusion

Well done for learning how to set up an Amazon RDS instance and use MySQL Workbench to connect to it. You can explore how to perform complex SQL queries against your AWS-hosted database.

I hope you found this guide helpful as I continue to provide you with tips and insights in the world of AWS Cloud. Feel free to connect with me via LinkedIn.