Mastering Amazon Relational Database Service for MySQL: Building and configuring MySQL instances (English Edition)

C

HAPTER

1

Amazon Relational Database Service

Introduction

An AWS Relational Database Service (RDS) MySQL instance is a powerful database solution within the Amazon Web Services ecosystem. It leverages the robust and popular MySQL database engine while providing the convenience of AWS’s cloud infrastructure. This service offers a seamless way to set up, manage, and scale MySQL databases, making it an ideal choice for businesses and developers who require a reliable and fully managed relational database.

With AWS RDS MySQL, you benefit from automated backups, security features, and high availability options, all handled by AWS, allowing you to focus your efforts on developing applications rather than database maintenance. Whether you are launching a new project or migrating an existing MySQL database to the cloud, AWS RDS MySQL instances offer the scalability and reliability needed to support your data-driven applications while simplifying the management of your database infrastructure.

Structure

In this chapter, we will cover the following topics:

Definition of Amazon RDS

Advantages of using Amazon RDS for MySQL

Essential components of Amazon RDS

Understanding supported Amazon RDS MySQL features

Choosing the right storage engine for Amazon RDS for MySQL

MySQL on Amazon RDS versions

Amazon RDS MySQL major version lifecycle and support dates

Objectives

This chapter provides an overview of Amazon RDS for MySQL, including its definition, advantages, and supported features by region and engine. Additionally, it covers topics such as DB instances, DB instance classes, and DB instance storage.

Definition of Amazon RDS

Amazon RDS is a managed database service offered by Amazon Web Services (AWS) that simplifies the process of setting up, operating, and scaling a relational database in the cloud. With Amazon RDS, you can easily create, operate, and scale a highly available and secure MySQL database in the cloud.

Advantages of using Amazon RDS for MySQL

Following are the advantages of using Amazon RDS for MYSQL:

Easy setup and management: Amazon RDS automates many of the time-consuming database administration tasks, such as backups, software patching, and monitoring.

Scalability: Amazon RDS allows you to scale your database resources up or down with a few clicks or API calls, without any downtime.

High availability: Amazon RDS provides multi-AZ deployment options that automatically replicate your database across multiple Availability Zones for high availability and data durability.

Security: Amazon RDS provides a range of security features, such as encryption at rest and in transit, network isolation, and IAM integration.

Cost-effective: With Amazon RDS, you only pay for the resources you use, and you can choose from a range of pricing options based on your needs.

Performance: Amazon RDS supports a range of instance types optimized for different workloads, and you can also use features like read replicas and automated backups to improve performance.

Compatibility: Amazon RDS supports popular MySQL features like InnoDB, replication, and Amazon Aurora MySQL-compatible features like Aurora read replicas.

Integration: Amazon RDS integrates with other AWS services like Amazon EC2, Amazon VPC, and AWS Identity and Access Management (IAM) for seamless management and security.

Monitoring and metrics: Amazon RDS provides comprehensive monitoring and metrics through Amazon CloudWatch, allowing you to monitor database performance Insights and set alarms for critical events.

Flexibility: Amazon RDS allows you to easily migrate your existing MySQL databases to the cloud, and you can also use it to build new applications with ease.

Essential components of Amazon RDS

We will understand DB instances, instance classes, storage, and Amazon VPC in the following:

DB instances are the building blocks of Amazon RDS, and they represent a running database environment.

DB instance classes define the CPU, memory, and network capacity of a DB instance, and they determine the price of the instance.

DB instance storage refers to the amount of storage allocated to a DB instance. It can be scaled up or down as needed, and it is charged separately from the instance itself.

Amazon Virtual Private Cloud (Amazon VPC) empowers you to run your DB instance within a customized virtual networking environment, offering control over IP address range, subnets, routing, and access control lists.

Understanding supported Amazon RDS MySQL Features: Variations by region and engine

Amazon RDS supports a range of MySQL features, including InnoDB, MyISAM, replication, and Amazon Aurora MySQL-compatible features. The specific features supported may vary by region and engine.

For example, the following are some of the supported features for Amazon RDS for MySQL in the US East (N. Virginia) region:

InnoDB storage engine

Read replicas

Multi-AZ deployments

Automated backups

Point-in-time recovery

Encryption at rest

Enhanced monitoring

Amazon RDS Performance Insights

Slow query log access

Cross-region replication

Amazon RDS blue/green deployments

It is important to check the AWS documentation for the specific region and engine you are using to ensure that you can take advantage of the desired features.

Choosing the right storage engine for Amazon RDS for MySQL

When selecting a storage engine for Amazon RDS for MySQL, it is important to understand that while MySQL supports multiple storage engines, not all are optimized for recovery and data durability.

InnoDB: Amazon RDS provides fully support for the InnoDB storage engine in MySQL DB instances. This engine is strongly recommended due to its ability to facilitate features like Point-In-Time restore and snapshot restore, essential for ensuring recoverable storage.

FEDERATED storage engine: It is important to note that Amazon RDS for MySQL does not currently offer support for the FEDERATED storage engine.

MyISAM: This storage engine lacks reliable recovery support, which may lead to data loss or corruption when MySQL is restarted after a recovery process. Additionally, it hinders the proper functioning of features like point-in-time restore and snapshot restore. Nevertheless, MyISAM is employed in system tables within the MySQL schema. If you opt to continue using MyISAM with Amazon RDS, snapshots can prove beneficial under specific circumstances.

To convert existing MyISAM tables to InnoDB tables, you can use the ALTER TABLE command, like so:

Keep in mind that MyISAM and InnoDB possess distinct advantages and disadvantages. Therefore, it is important to assess how this transition may affect your applications before proceeding.

AWS Regions

AWS Regions represent distinct geographical zones. Within these AWS Regions, you will find multiple Availability Zones, each physically separated and self-contained. These Availability Zones are interconnected by a network with minimal latency, high throughput, and robust redundancy. Let us learn about them briefly in the following:

Isolation and fault tolerance: Each AWS Region is an independent geographic area with its own infrastructure. AWS designs Regions to be isolated from each other, ensuring fault tolerance and stability. A failure in one Region does not affect other Regions.

Resource replication across AWS Regions: Resources, including AWS RDS MySQL DB instances, are not automatically replicated across AWS Regions. To achieve cross-Region redundancy, you must set up replication mechanisms like Amazon RDS Read Replicas.

Availability Zones

Availability Zones comprise one or more individual data centers, each equipped with backup power, networking, and connectivity. These data centers are situated in distinct facilities for added resilience.

Choosing an availability zone for DB Instances: When creating an AWS RDS MySQL DB instance, you have the option to select a specific Availability Zone or let Amazon RDS choose one randomly. Availability Zones are distinct locations within an AWS Region, identified by a unique code.

Multi-AZ DB Deployment: In a Multi-AZ deployment, Amazon RDS automatically provisions and maintains a standby replica in a different Availability Zone for enhanced availability and automatic failover.

Local zones

AWS Local Zones represent a specific form of AWS infrastructure deployment. They strategically position computing, storage, databases, and specific services in proximity to major population centers, industrial hubs, and IT focal points. This proximity facilitates the delivery of applications that demand ultra-low latency, measured in single-digit milliseconds, to end-users.

Extending VPC to Local Zones: Local Zones are geographically close extensions of AWS Regions. You can extend your Virtual Private Cloud (VPC) from the parent AWS Region to a Local Zone by creating a new subnet and associating it with the Local Zone.

Creating DB Instances in Local Zones: When creating an AWS RDS MySQL DB instance, you can choose a subnet in a Local Zone. Local Zones have their own internet connections, enabling low-latency communications to serve local users effectively.

Best practices

In today’s cloud-driven landscape, achieving high availability, minimizing latency, and ensuring robust disaster recovery are paramount. This guide outlines essential best practices to help you effectively distribute resources, address latency considerations, and fortify your disaster recovery strategies for a resilient and responsive AWS environment.

Distributing resources for high availability: Distributing your AWS RDS MySQL DB instances across multiple Availability Zones within a Region or across different Regions enhances availability and resiliency.

Latency considerations: Placing resources in Local Zones closer to your users reduces latency and improves the user experience.

Disaster recovery strategies: Implement disaster recovery strategies by utilizing Multi-AZ deployments and cross-Region replication to safeguard against data center failures and natural disasters.

Enhancing performance with memcached option in RDS for MySQL DB instances

RDS for MySQL DB instances support the memcached option, a simple, key-based cache. This is enabled through option groups that allow you to select additional features for your DB instance.

Optimizing performance with InnoDB cache warming in MySQL DB Instances

Enabling InnoDB cache warming can boost the performance of your MySQL DB instance. This feature operates by storing the existing state of the buffer pool during DB instance shutdown and then restoring the buffer pool from the saved data when the DB instance is restarted.

To enable InnoDB cache warming, set the following parameters to 1 in the parameter group for your DB instance:

Keep in mind that modifying these parameters will impact all MySQL DB instances utilizing the same parameter group. If you wish to enable InnoDB cache warming for particular MySQL DB instances, you may have to create a new parameter group dedicated to those instances.

Note: If your MySQL database instance fails to shut down normally, such as during a failover, the buffer pool state will not be saved to disk. When you restart the DB instance, MySQL will load whichever buffer pool file is available at that time. Although no harm is done, the restored buffer pool may not reflect the most recent state prior to the restart. To ensure that you have an up-to-date buffer pool state available to warm the InnoDB cache on startup, we highly recommend periodically dumping the buffer pool on demand.

You have the option to create an event that will automatically and regularly dump the buffer pool. For instance, the following statement creates an event called periodic_buffer_pool_dump that dumps the buffer pool every hour.

Managing InnoDB Cache On-Demand in MySQL DB Instances

You also have the option to save and load the InnoDB cache as needed.

To save the current buffer pool state to disk, utilize the mysql.rds_innodb_buffer_pool_dump_now stored procedure.

CALL mysql.rds_innodb_buffer_pool_dump_now();

To restore the previously saved buffer pool state from disk, invoke the mysql.rds_innodb_buffer_pool_load_now stored procedure.

CALL mysql.rds_innodb_buffer_pool_load_now();

To abort a loading operation currently in progress, make use of the mysql.rds_innodb_buffer_pool_load_abort stored procedure.

CALL mysql.rds_innodb_buffer_pool_load_abort();

Limitations and restrictions of Amazon RDS for MySQL Databases

Amazon RDS does not currently support the following MySQL features:

Authentication plugin

Error logging to the system log

Group Replication plugin

InnoDB Tablespace Encryption

Password Strength Plugin

Persisted system variables

Rewriter Query Rewrite Plugin

Semisynchronous replication

Transportable tablespace

X Plugin

Finally, to deliver a managed service experience, Amazon RDS does not provide shell access to DB instances, nor does it allow direct host access to a DB instance by using Telnet, Secure Shell (SSH), or Windows Remote Desktop Connection.

MySQL on Amazon RDS versions

This section provides detailed information and best practices for managing MySQL versions on Amazon RDS. It covers versioning conventions, supported minor versions, default versions, and upgrading procedures. By following these best practices, you can effectively utilize MySQL on Amazon RDS and ensure compatibility and stability in your database environment.

Understanding MySQL versions on Amazon RDS

Understanding MySQL’s versioning system is crucial for effectively managing your database deployments on Amazon RDS. This will Clarify MySQL version numbers, major and minor version changes, and provides insights into Amazon RDS’s support for various MySQL minor versions, complete with release dates and support timelines.

MySQL versioning scheme

MySQL version numbers follow the format X.Y.Z, where X.Y represents the major version and Z represents the minor version.

In Amazon RDS, a major version change occurs when the major version number (X) changes, while a minor version change occurs when only the minor version number (Z) changes.

Configuring MySQL versions on Amazon RDS

When setting up a new Amazon RDS DB instance, you can choose a specific MySQL version, including major and minor versions, or let it default to the latest supported version. Use the describe-db-engine-versions AWS CLI command to check available options and defaults.

Specifying MySQL versions

When creating a new DB instance on Amazon RDS, you can specify any currently supported MySQL version.

You can specify the major version (for example, MySQL 8.0) and any supported minor version for that major version.

If no version is specified, Amazon RDS defaults to the most recent supported version.

To view the list of supported versions and defaults for newly created DB instances, you can use the describe-db-engine-versions AWS CLI command.

Example command to list supported engine versions

Shell script to discover the supported engine versions of an RDS MySQL instance:

Figure 1.1: describe_rds_mysql

Save the above script in a file, for example, describe_rds_mysql.sh. Make the script executable by running the command:

To use the script, run the following command on your EC2 instance:

Figure 1.2: (Insert caption here)

Determining Default MySQL Minor Version

The default MySQL version may vary by AWS Region.

To determine the default minor version for an AWS Region, use the following AWS CLI command:

Replace major-engine-version with the major engine version (for example., 8.0) and region with the AWS Region.

Shell script to determine the default MySQL minor version of an RDS MySQL instance:

Figure 1.3: Describe MySQL minor version

To use this script, you can pass the –engine-version parameter with the desired version of the MySQL engine. Optionally, you can also provide the –region parameter to specify the AWS region (defaults to us-west-2 if not provided). Here is an example of how to use the script:

Figure 1.4: Using script

When executing this script, you must provide the -engine and -region parameters with their respective values.$

<

Figure 1.5: Using script

Controlling major version upgrades

Amazon RDS gives you the flexibility to decide when to upgrade your MySQL instance to a new major version supported by the service.

Upgrading to major versions necessitates a modification request for the DB instance and should undergo comprehensive testing before applying the upgrade to production instances.

Automatic minor version upgrades

When automatic minor version upgrades are enabled, Amazon RDS automatically updates your DB instance to the latest supported MySQL minor versions as they become available.

These upgrades take place within the designated maintenance window for your DB instance.

You have the option to adjust a DB instance’s settings to either enable or disable automatic minor version upgrades.

Supported major version upgrades

Amazon RDS facilitates major version upgrades for MySQL, allowing transitions from version 5.6 to 5.7 and from version 5.7 to 8.0.

Major version upgrades come with compatibility risks, and they do not happen automatically; instead, they require a modification request for the DB instance.

Testing a new version

Before upgrading your primary DB instance, you can perform a version test by taking a DB snapshot of your current instance, using it to create a new DB instance, and subsequently initiating a version upgrade for the new instance.

This provides you with a secure opportunity to test the upgraded clone, enabling you to make an informed decision about upgrading your production instance.

Amazon RDS MySQL major version lifecycle and support dates

RDS for MySQL major versions will continue to be accessible until the corresponding community version reaches its end of life within the community. To help in your planning, the table below presents essential dates for each supported major version of MySQL on Amazon RDS.

You can utilize the following dates to strategize your testing and upgrade schedules:

Table 1.1: RDS MySQL major version lifecycle

Please be aware that dates containing only a month and year are estimated and will be refined with specific dates once available. It is vital to take these dates into account when planning your MySQL deployment on Amazon RDS.

Best practices

Stay up-to-date with the latest MySQL versions: Keep track of the latest MySQL releases and plan to upgrade your RDS instances to supported versions before the end of standard support date. This ensures that you can leverage the latest features, performance improvements, and security fixes provided by MySQL.

Example configuration code: