Aveek Das
RDS MySQL Environment Created Successfully

How to configure an Amazon RDS environment for MySQL

May 26, 2020 by

In this article, I am going to explain how to configure the RDS MySQL Environment in AWS. RDS is the Relational Database Service Available from the Amazon which enables us to spin up any relational database instance without having to worry about the infrastructure behind it. Users can connect to this instance very easily and start building database applications with ease. This article will focus more on how to configure the RDS MySQL Environment and not much on the MySQL details.

Although there are different tiers available to install the RDS MySQL Environment, for the sake of this tutorial, we will just explore the free tier of the RDS service. You can spin up an instance as required if you need to. Depending upon the processors and time, the service charge for the databases may vary. Using the free-tier, users are eligible to use the RDS service for free up to 750 hours a month and I think that is enough for the service to run without incurring any additional charges.

1. Setting up the RDS environment

Let us begin configuring the RDS MySQL Environment by first signing up for an AWS Account. Once you have successfully created the AWS account, search for RDS in the Find Services bar and hit enter.

RDS MySQL Environment

Figure 1 – Searching for AWS RDS

Open the RDS from the drop-down menu and proceed to create the RDS MySQL Environment. On the next page that appears, click on Create Database. This will open another page where you can define the necessary details required to set up the MySQL Database.

Create Database Button in AWS RDS

Figure 2 – Create Database Button in AWS RDS

2. Creating the MySQL database

Once you click on the Create Database, a new page opens as follows, where you can define the database creation method and other options. Let us go ahead and apply the settings as defined in the figure below. We are going to select Standard Create as the database creation method. This will allow us to configure all the necessary settings on our own. Next, select the Engine Type as MySQL and the latest version. At the time of writing this article, the latest MySQL version in 8.0.16.

Selecting the Database Engine

Figure 3 – Selecting the Database Engine

In the next step, we are going to provide the name and connection details for the MySQL Database that we are going to create. Since we are going to create the database in the free tier, select the Free Tier from the template and proceed. Provide a suitable name for the database instance, for example, I’m going to use the database instance as “mysql-db-test01”. Similarly, provide a suitable master username and password for the same. This is the username and the password, that you will be using later to connect to this MySQL instance later. Also, you must keep these credentials safe so that it can be used later again.

Setting up the Instance Credentials

Figure 4 – Setting up the Instance Credentials

Now, that the instance credentials have been set up, let’s go ahead and set some other properties which are essential to set up the RDS MySQL Environment. Select the Database Instance Size as “db.t2.micro” and Storage Type as General Purpose SSD. By default, the memory size is allocated to 20GB which is fine for the moment.

Specifiying the DB Instance Size

Figure 5 – Specifying the DB Instance Size

In the next step, we should define the Connectivity settings for the RDS Database instance. Select the default VPC connection that is already available within your login. For my use case, I have already created some RDS instances previously, so I’ll be using the same VPC for this instance as well. Additionally, we should also add a Subnet Group within the VPC connection. Since we will be accessing the database instance from outside the AWS Environment, we should enable the Publicly Accessible to Yes. Finally, for VPC Security Group, select “Choose Existing” and proceed forward.

Configuring Connectivity Settings for RDS MySQL Environment

Figure 6 – Configuring Connectivity Settings for AWS RDS

Now that most of the configuration is done, the final step in creating the database is to select the Database Authentication Mode as Password Authentication. Once completed, click on Create Database.

Create Database in AWS RDS

Figure 7 – Create Database in AWS RDS

3. Configuring the RDS MySQL Environment

Once you click on Create Database in the previous step, it might take a while for AWS to create the RDS instance and make it available for use. After a few moments, you will receive a notification that says the database has been created successfully.

RDS MySQL Environment Created Successfully

Figure 8 – RDS Instance for MySQL Created Successfully

As you can see in the figure above, I have already created an RDS instance for SQL Server previously, the newly added MySQL Community instance is also added to the Databases list. Go ahead and click on the DB Identifier for the MySQL Database. A new page will open containing more information about the MySQL database instance. The important thing to note here is the Endpoint which is available. This endpoint information will be used later to connect to the instance using the MySQL Workbench tool.

MySQL Database Instance

Figure 9 – MySQL Database Instance

The next step here is to allow connections from the public network to allow and connect to the instance. In order to enable this, click on the VPC Security Groups and then open the new page.

VPC Security Groups

Figure 10 – VPC Security Groups

In the following page that appears, select the Security Group ID and open it.

Security Group ID

Figure 11 – Security Group ID

In the Security Group page, select the Edit Inbound Rules button. This will allow us to edit the IP addresses that will have access to the MySQL Database instance.

Select Edit Inbound Rules

Figure 12 – Select Edit Inbound Rules

The Edit Inbound Rule page appears. On this page, we will add a custom rule which allows any IP address to connect to the RDS Instance on the port 3306. The port 3306 is the default port on which MySQL is usually configured. If you are using any other port, you should allow traffic to that specific port instead.

Click on the Add Rule button and select the Source as Anywhere. This will allow all traffic from outside the AWS environment to connect to the MySQL instance on the RDS. Click on Save Rules once done.

Allow Inbound Connections

Figure 13 – Allow Inbound Connections

You can see that the new rules have been added to the list and are now effective.

Added Inbound Rules for MySQL

Figure 14 – Added Inbound Rules for MySQL

4. Connecting to the RDS environment using MySQL Workbench

Once we have created the database and all the necessary configurations are done, it is now time that we go ahead and connect to the instance. We will be using MySQL Workbench to connect to the RDS instance. You can also choose any other tool to connect to the instance and it will work the same.

Enter the endpoint that we copied in the previous steps as the hostname and the master username as the username here and click on Test Connection.

Connecting to the RDS Instance

Figure 15 – Connecting to the RDS Instance

You might be prompted to provide the password in the next step.

Password to connect to RDS

Figure 16 – Password to connect to RDS

If the connection is successful, you will receive a notification saying the connection has been successful.

MySQL Connection Successful

Figure 17 – MySQL Connection Successful

You can now go ahead and create your own schemas and tables in the RDS Instance.

Connected to RDS Instance

Figure 18 – Connected to RDS Instance

Conclusion

Amazon RDS is the relational database service offered by AWS using which it is very easy to start any RDBMS without worrying much about the infrastructure. In this article, we have seen how to configure the RDS MySQL Environment in AWS. We have also seen how to create the MySQL instance and how to allow remote connections to it from an external server. Finally, we have connected to the RDS MySQL Environment using the MySQL Workbench in local and executed simple queries.

Aveek Das
AWS RDS, MySQL

About Aveek Das

Aveek is an experienced Data and Analytics Engineer, currently working in Dublin, Ireland. His main areas of technical interest include SQL Server, SSIS/ETL, SSAS, Python, Big Data tools like Apache Spark, Kafka, and cloud technologies such as AWS/Amazon and Azure. He is a prolific author, with over 100 articles published on various technical blogs, including his own blog, and a frequent contributor to different technical forums. In his leisure time, he enjoys amateur photography mostly street imagery and still life. Some glimpses of his work can be found on Instagram. You can also find him on LinkedIn View all posts by Aveek Das

168 Views