MySQL in Amazon (RDS)

March 16, 2021
Tags: , , ,

Up until now we have seen MDS (MySQL Database Service) and MySQL in Azure.
As the Cloud technology keeps moving fast, I thought it would be a good idea to see how to set up a MySQL in Amazon , as a service, that is RDS.

Let’s get started then!

What we need is an Amazon subscription that is really easy to get one and there is also a Free Tier. The AWS Free Tier is available to you for 12 months starting with the date on which you create your AWS account. When your free usage expires or if your application use exceeds the free usage tiers, you simply pay standard, pay-as-you-go service rates.

For the specific session, I had root access. There is also the IAM user. AWS Identity and Access Management (IAM) is an AWS service that helps an administrator securely control access to AWS resources. IAM administrators control who can be authenticated (signed in) and authorized (have permissions) to use Amazon RDS resources. 

Once inside the Amazon Subscription, we are seeing the Management Console:

From the Management Console, we are able to identify the RDS service and choose it:

Once inside the RDS service, we click on the Create Database:

From the Databases provided, we are choosing MySQL and Standard Create, as we would like to set up our configuration.
With Easy Create enabled, you specify only the DB engine type, DB instance size, and DB instance identifier. 
Easy Create uses the default setting for other configuration options.

With Easy Create not enabled, you specify more configuration options when you create a database, including ones for availability, security, backups, and maintenance.

Now, on MySQL versions we have chosen the latest provided until now for Amazon, that is 8.0.21 :

If you noticed, there is also the choice to choose Production , Dev or Free Tier template.
For Production Template, there are default options for High Availability and consistent performance. We have chosen the Free Tier, as this is for educational purposes.

We have also given a Database Identifier, database-1. Each DB instance has a DB instance identifier.
This customer-supplied name uniquely identifies the DB instance when interacting with the Amazon RDS API and AWS CLI commands. The DB instance identifier must be unique for that customer in an AWS Region.

Moving on next are the admin’s credentials, the super user of the database:

The DB instance class determines the computation and memory capacity of an Amazon RDS DB instance. The DB instance class you need depends on your processing power and memory requirements. Apparently, since we are only testing this we have a basic tier:

db.t2 – Instance classes that provide a baseline performance level, with the ability to burst to full CPU usage.

Moving along with the rest of our options:

Amazon RDS provides three storage types: General Purpose SSD (also known as gp2), Provisioned IOPS SSD (also known as io1), and magnetic (also known as standard).

They differ in performance characteristics and price, which means that you can tailor your storage performance and cost to the needs of your database workload. 

You can create MySQL, MariaDB, Oracle, and PostgreSQL RDS DB instances with up to 64 tebibytes (TiB) of storage. You can create SQL Server RDS DB instances with up to 16 TiB of storage.
For this amount of storage, use the Provisioned IOPS SSD and General Purpose SSD storage types.

The following list briefly describes the three storage types:

  • General Purpose SSD – General Purpose SSD volumes offer cost-effective storage that is ideal for a broad range of workloads. These volumes deliver single-digit millisecond latencies and the ability to burst to 3,000 IOPS for extended periods of time. Baseline performance for these volumes is determined by the volume’s size.
  • Provisioned IOPS – Provisioned IOPS storage is designed to meet the needs of I/O-intensive workloads, particularly database workloads, that require low I/O latency and consistent I/O throughput.
  • Magnetic – Amazon RDS also supports magnetic storage for backward compatibility. We recommend that you use General Purpose SSD or Provisioned IOPS for any new storage needs. The maximum amount of storage allowed for DB instances on magnetic storage is less than that of the other storage types.

One the Availability & Durability section, you are seeing the option Multi-AZ deployment. Amazon RDS Multi-AZ deployments provide enhanced availability and durability for database instances, making them a natural fit for production database workloads. When you provision a Multi-AZ database instance, Amazon RDS synchronously replicates your data to a standby instance in a different Availability Zone (AZ).

The next section to check is that on of the Connectivity:

Be certain to choose the correct Virtual private cloud, as it cannot be changed after the database is created and set the proper access (IP-ranges) for your subnets.

Security groups control the access that traffic has in and out of a DB instance. Each VPC security group rule enables a specific source to access a DB instance in a VPC that is associated with that VPC security group.
The source can be a range of addresses, or another VPC security group. By specifying a VPC security group as the source, you allow incoming traffic from all instances (typically application servers) that use the source VPC security group.

Moving on next, we have the Database options, we choose the DB name and we have the default parameter group & the option group. We will talk about these in more detail in a few minutes.

The backups sections is pretty straightforward, we may choose automatic ones and there is a retention period from 7 to 35 days.

We can set a specific window for the backups and we may enable the logs that we wish:

Here, we have chosen the error logs and the slow query log.

And now we are ready to create our database:

Please note that once the MySQL RDS is ready, it is the only time we are able to see the connection details and specifically the password, so we need to take a note of this.

Once our database is ready for use, we will see a notification at the top of the page:

Now, remember the default-group that we saw earlier while setting our MySQL RDS?
Basically, a DB parameter group acts as a container for engine configuration values that are applied to one or more DB instances.

If you create a DB instance without specifying a DB parameter group, the DB instance uses a default DB parameter group. Each default DB parameter group contains database engine defaults and Amazon RDS system defaults based on the engine, compute class, and allocated storage of the instance. You can’t modify the parameter settings of a default parameter group.

So, if we try to edit the default parameter group, we will receive the following error:

Therefore, if we wish to edit and configure the parameter values, we will need to create a new parameter group and assign it to our database.

Pretty straightforward, we name our parameter group and add a description if we wish:

And we are able to edit it and save the changes:

To apply it on our MySQL instance, we need to modify and set the corresponding parameter group:

We may also modify our backup options and set a window that we didn’t do while creating the MySQL:

On the Events tab, we may see all the actions that have been performed on our instance:

We are also able to check the Recommendations regarding the performance and our queries if we have the enhanced monitoring is enabled:

And last but not least, on the Actions we may create a replica :

To sum up, this is how to set up a MySQL RDS and the options that we have.

Conclusion

We have seen MySQL PaaS on the top Cloud Providers so far and there is definitely much more to see on that department, so stay tuned!

I don’t need a hard disk in my computer if I can get to the server faster… carrying around these non-connected computers is byzantine by comparison.

Steve Jobs

References

Leave a Reply