How to Deploy a Managed MySQL Instance in an Amazon RDS Database.

Introduction

Amazon Relational Database Service (RDS) is a web-service that makes it easier to set-up, operate, and scale-up relational databases in the cloud and manages common administration tasks. Amazon RDS currently supports the MariaDB, MySQL, Oracle, PostgreSQL, and Microsoft SQL engines.

AWS currently offers more than 15 purpose-built database options to support various database models. These models include: relational, key-value, in-memory, graph, time-series, wide-column and ledger databases.
To choose what model to use, you should consider; The business objective, migration strategy from on-premise database to the cloud, purpose-built optimized for what you need, data considerations, security considerations and much more. To fully understand how to choose the best database service for your business, you can visit AWS Decision Guide

In this tutorial, we will be discussing how to deploy a managed MySQL instance using an Amazon RDS database. A managed service is preferred to setting it up manually on an EC2 instance because it fully provides continuous monitoring, self-healing storage, and auto-scaling to help you focus on the application development and your users.

Amazon RDS for MySQL

Prerequisites

To follow this tutorial, you will need an AWS account. If you do not have one yet, you can follow this guide on how to set-up your environment.
The steps described here are under the Free Tier, so feel free to play around.

Create a MySQL DB Instance

• To get started, open the AWS Management Console, then select Services on the left of the top pane. This would display a list of services offered by AWS from Databases, to Containers and much more. Since we are interested in the Database service, we click on it, then choose RDS to open the Amazon RDS console

• In the Amazon RDS Console, the first thing to do is choose the region in which we want to create the database. Note: Some regions are inaccessible depending on your account type.

Regions are isolated form other regions as a fault tolerance and stability mechanism and also, data is not automatically transferred from one region to another. So, if you switch regions, you won't find the same data in the new region.

• To create the database, scroll to the Create database section and choose Create database

• If you would like to set your own configuration options including those for availability and security upon creation, the Standard Create option should be used, otherwise, you can go with Easy Create which uses the recommended best practices to configure your database some of which can be changed after creation.

• Select the engine you would want to run from the list of Engine options supported by RDS. For the purpose of this tutorial, the MySQL engine would be used and the default version and edition should be maintained. Select the Free Tier template

A Region has multiple isolated locations known as Availability Zones. Enabling Multi-AZ deployments would automatically provision and maintain a synchronous standby in other Availability Zones to ensure fault tolerance in case of failure of one zone. However, this is a paid feature, so it'll not be discussed in this tutorial.

• The next thing to do is define some key settings for the DB instance. These include;

  DB Instance Identifier : unique name across all DB instances owned by your AWS account in the current region.

  Master Username: the username that will be used to login to the mysql instance.

  Master password: A password that contains at least 8 ASCII characters (excluding ', ", @, and /)

  Confirm master password: Retype the password you had above

• For the instance specification, select the db.t2.micro — 1vCPU, 1 GiB RAM for the Instance Type, General Purpose (SSD) for Storage Type and 20 GB for Allocated Storage. This specifications ensure we stay in the Free Tier range. For more about RDS pricing, see Amazon RDS pricing

Autoscaling should be enabled for unpredictable workloads but that is beyond the free tier usage capacity.

• Under Connectivity, one key thing to take note of is the VPC. Amazon virtual private cloud (VPC) makes it possible to launch AWS resources into a virtual private cloud such that you can create your own subnets, access control lists, choose your own IP address and configure routing. There is no additional cost in running the DB instance in a VPC and all new DB instances are created in the default VPC except explicitly changed. For this tutorial, we'll use the default VPC.

  Under VPC security groups, choose Create new. This creates an inbound rule that allows connection from the IP address of the device you are currently using to the database created.

  Also, make sure your DB instance is Publicly Accessible by setting this field to 'Yes' to make it possible to directly connect to the database from your device.

  The RDS Proxy allows your applications to pool and share database connections, hence improving their ability to scale. We will leave this option unchecked. To see more on the pricing for RDS proxy, visit Amazon RDS Proxy pricing

  The other settings are left as default for this configuration.

• Database Authentication defines how you would want users to be authenticated before they can view the database. The options are:

  Password Authentication; uses the database password only.

  Password and IAM database authentication; uses the database password and the user's credentials through IAM users and roles. This option is available only on MySQL and PostgreSQL engines.

  Password and Kerberos authentication; uses the database password and kerberos authentication through AWS Managed Microsoft AD created with AWS Directory Service.

  For simplicity, choose the Password Authentication method from the list of options.

To stay within the free tier, leave the Enable Enhanced Monitoring unchecked. Monitoring gives you metrics in real time for the OS that the DB instance is running on.

In the Additional Configuration section, under Database options, the Initial Database Name is the name of your database on you DB instance. If a name is not provided, RDS does not create a database while creating (except for Oracle or PostgreSQL). We would use the default Option Group and DB parameter group. Sometimes, you might have the ability to create your own DB parameter group or Option Group.

RDS creates a storage volume snapshot of the entire database instance, backing up the entire database instance and not just particular databases.
Under Backup, check the option to Enable automated backups.

The Backup retention period determines the number of days for which the automatic backups are kept, for this tutorial, set it to 1 day.

If you would like to set a daily time range during which backups should occur, select Choose a window option and set the time for your preferred window. If not, you can just select No preference which is what we will do here.

Turn on Enable auto minor version update under Maintenance. This will allow us to receive automatic updates when they become available. Just like in Backup, we can set the set a particular maintenance window or select no preference.

The last thing we'll need to do is turn off Enable deletion protection for this tutorial. Enabling this option will prevent you from accidentally deleting your database.

Voila! Select Create Database to create the DB instance.

This might take a while depending on the instance class and storage allocated for the database. When the status changes to Available, then your instance has been completed.

While this is creating, you can move on to the next step of setting up your MySQL environment on your local computer.

Connect to the MySQL Database

In this step, we will connect to the MySQL database that was created using the MySQL CLI. Before moving on, make sure you have installed the MySQL CLI. The connection string is:

mysql -h  **_endpoint_**  -P  **_port_**  -u  **_user_**  -p

The endpoint and port for the database can be found under the Configuration and Security tab. The user is the Master Username that was set when creating the database which can be found under the Configuration tab.
When you click enter, you'll be prompted for a password, this would also be the Master password which was also set upon creation of the DB instance

And Voila, you're connected to your database. If we display the list of databases, we would see some default databases that come with MySQL and also the database we created when creating the instance.

And that's how we connect to the Amazon RDS MySQL Database instance.
Congratulations. Now you can insert data and run queries. You could also create new databases.

Delete the DB Instance

In order not to waste resources and incur cost over time, it is best we delete the DB instance if you are not using it.

To do so, return to the RDS console. Choose Databases, then select the instance you want to delete and under Actions dropdown, select Delete.

You would be asked if you want to create a final snapshot of your database. Select this option if you would like to restore the current state of your database with all its data in future. For this tutorial, we would leave that option unchecked.

Confirm that you want to delete the instance and then click Delete

Performing these actions would permanently delete the database instance.

Conclusion

In this tutorial, we have seen what an Amazon RDS database is and why it is preferred to manually setting up your own database. We also looked at the various engines that are supported by RDS and focused on using the MySQL engine to create a MySQL database instance on the RDS service. Then, we were able to connect this remote instance to our local computer and run basic queries. Finally, we saw how to permanently delete the database instance.

Troubleshooting

If you restart your network, the VPC security group that created the inbound function permitting you to connect to the instance from your computer might not work anymore because your computer's IP address might have changed.

To fix this, select your database and under Connectivity and Security select your VPC security group under VPC security groups.

This would take you to the Security Groups page where you can see your different security groups, the outbound and inbound rules and configure them. Inbound rules define what resources are permitted to connect to the instance and Outbound rules define what resources the instance is permitted to make a connection with.
From here, we can go ahead and edit our inbound rules in order to permit our device to connect with it.

The only thing you will need to edit here is, under Sources of the rule, select My IP to change the IP address the resource is allowed to connect to, to your device's IP address. Click on save rules, and now, you will be able to connect like before.