In
Amazon Relational Database Service
(Amazon RDS), Multi-AZ deployments can have one or two standby DB instances. A
Multi-AZ DB instance deployment
has a single standby DB instance that provides failover support, but doesn’t serve read traffic. When the deployment has two readable standby DB instances, it’s called a
Multi-AZ DB cluster deployment
. A Multi-AZ DB cluster deployment has standby DB instances that provide failover support and can also serve read traffic.
Multi-AZ DB cluster deployment is best suited for application workloads that need additional read capacity or, for those workloads that are sensitive to transaction latencies and must be highly available. In addition to that, Multi-AZ DB cluster deployment includes new features such as
Amazon RDS Optimized Writes
, which delivers an improvement of up to 2x in write transaction throughput, and
Amazon RDS Optimized Reads
, which provides up to 2x faster complex query processing.
If you are currently in the process of planning a major version upgrade for your existing
Amazon RDS for MySQL
instances running MySQL 5.7.x, you have the opportunity to migrate to an Amazon RDS for MySQL 8.0.x Multi-AZ DB cluster deployment, which can effectively support your RDS for MySQL workloads. With Multi-AZ cluster deployments, Amazon RDS supports database workloads that require more read capacity and consistent transaction latency, while providing high availability and durability. Multi-AZ DB clusters are supported for Amazon RDS for MySQL 8.0.28 and higher, and can be utilized in all Regions where Amazon RDS for MySQL is supported.
In this post, we explore key considerations while upgrading Amazon RDS for MySQL 5.7.x to MySQL 8.0.x. We also discuss how to adopt a Multi-AZ DB cluster deployment for your existing RDS for MySQL workloads, and best practices associated with the migration.
Key new features introduced in Multi-AZ DB clusters
Amazon RDS Multi-AZ DB cluster deployment
is ideal for production database workloads. Multi-AZ DB cluster deployments use semisynchronous replication, which requires acknowledgment from at least one reader DB instance in order for a change to be committed. This means that the primary will not acknowledge a commit to a client until one of the replicas has acknowledged receiving the transaction. In case of an infrastructure failure (for example, instance hardware failure, storage failure, or network disruption), of your writer DB instance in a Multi-AZ DB cluster, Amazon RDS automatically fails over to a reader DB instance in a different Availability Zone. Multi-AZ DB clusters support database workloads that require more read capacity and consistent transaction latency by provisioning one primary and two readable standby DB instances across three Availability Zones. The standby DB instances act as automatic failover targets and serve read traffic.
For read-heavy workloads, Multi-AZ DB cluster deployments also allow
adding read replicas
, to scale beyond the compute or I/O capacity of the cluster. This allows you to direct read traffic to one or more DB instance read replicas, which can be Single-AZ or can also be using Multi-AZ instance deployment. In addition to two readable standbys in the Multi-AZ DB cluster topology, you can create up to 15 read replicas from your Multi-AZ DB cluster.
Multi-AZ DB cluster deployment for Amazon RDS for MySQL supports
Optimized Writes
, where your RDS for MySQL databases write only once when flushing data to durable storage without the need for the doublewrite buffer. This provides up to two times improvement in write transaction throughput for your database. RDS Optimized Writes is available as a default option from
Amazon RDS for MySQL version 8.0.30 and higher
with
specific instance classes
. For more information, refer to
Using RDS Optimized Writes
.
In addition, on a Multi-AZ DB cluster deployment, the
Optimized Reads
feature is turned on by default on
supported instances
. An RDS for MySQL DB instance that uses Optimized Reads can achieve up to two times faster query processing compared to a DB instance that doesn’t use it.
Key considerations while moving to Multi-AZ DB cluster deployment
While planning to use RDS Multi-AZ DB clusters, there are a few things to keep in mind as you prepare the move. The first thing to keep in mind is the engine version. Multi-AZ DB clusters with Amazon RDS for MySQL require
Amazon RDS for MySQL Version 8.0.28 and higher
and
RDS Optimized Writes
requires version 8.0.30 and higher. Therefore, if you are performing a major version upgrade from Amazon RDS for MySQL 5.7.x versions, you can also use this time to upgrade to the latest Amazon RDS for MySQL version 8, which is version 8.0.33 as of this writing. For the latest version, refer to
Supported MySQL minor versions on Amazon RDS
. For reduced downtime and risk during a major version upgrade, you can also use
RDS Blue/Green Deployments
.
Another thing to keep in mind is the supported DB instance class for Multi-AZ DB cluster deployment. Currently, Multi-AZ DB clusters support “d” type instance class for example, db.m5d, db.m6gd, db.r5d, db.r6gd, and db.x2iedn. For the most up-to-date considerations, refer to
Region and version availability
and
Instance class availability
. You can also leverage Reserved DB instances with Multi-AZ DB cluster deployments. For more information, see
Reserved DB instances for a Multi-AZ DB cluster
. In addition, we recommend reviewing the current
limitations
while using Multi-AZ DB cluster deployments to ensure a smooth transition from your current architecture.
MySQL 8.0 introduces several
changes
when compared to MySQL 5.7, which may result in complications during the upgrade process from MySQL 5.7 to MySQL 8.0. When you start an upgrade, Amazon RDS runs prechecks automatically to detect these incompatibilities. If there are incompatibilities, Amazon RDS prevents the upgrade and provides a log for you to learn about them. You can then use the log to prepare your database for the upgrade to MySQL 8.0 by reducing the incompatibilities. Therefore, it is advisable to make
necessary preparations
on your databases, and thoroughly test to ensure a smooth and successful upgrade. You can use a
DB snapshot
to
test the upgrade
by running as many of your quality assurance tests against a
restored instance
that is upgraded. While upgrading, you can choose the
latest MySQL 8.0 minor version
supported on Amazon RDS for MySQL.
Migration options from an existing Single-AZ or Multi-AZ instance to Multi-AZ DB cluster deployment
Now that your DB instance is on a supported Multi-AZ DB cluster version and instance class, let’s review the migration options to move from an existing RDS for MySQL Single-AZ or RDS for MySQL Multi-AZ instance deployment.
Use a read replica with minimal downtime
As one of the most convenient options in terms of effort and minimal downtime, you can create a
Multi-AZ DB cluster as a read replica
from your existing Single-AZ or Multi-AZ DB instance deployment that will act as the replication source. This operation will take care of not only performing an initial load of your data, but also setting up replication for any changes, which means the DB instance can process write transactions during the migration to a Multi-AZ DB cluster.
For this migration method, as a prerequisite, configure gtid-mode , and enforce_gtid_consistency parameters to ON on your source DB instance. After you have upgraded your existing RDS for MySQL instances to version 8.0.28 or higher, you can set these parameters in the corresponding
RDS parameter group
. The apply type for both these parameters is static, so it would require a manual reboot before the parameters go in-sync. You can reboot immediately or wait until your maintenance window. After setting up the parameters, refer to
Creating and promoting the Multi-AZ DB cluster read replica
for complete steps on creating your Multi-AZ DB cluster read replica. Once the Multi-AZ DB replica cluster is in Available status, you can monitor the replication lag using the ReplicaLag CloudWatch metric and connect to it using the
Multi-AZ DB cluster endpoint
to perform any testing as replication from your existing RDS instance is happening in the backend.
When you’re ready, stop any transactions from being written to the source DB instance, and then wait for all updates to be made to the read replica. You can
monitor the replication lag
using the ReplicaLag metric. When the replica lag is a near-zero value, you can promote the read replica to be a standalone Multi-AZ DB cluster. You can create and promote a Multi-AZ DB cluster read replica using the
Amazon Web Services Management Console
,
Amazon Web Services Command Line Interface
(Amazon Web Services CLI), or Amazon RDS API. For more details regarding creating and managing a Multi-AZ DB cluster read replica for the migration, refer to
Migrating to a Multi-AZ DB cluster using a read replica
.
The following diagram illustrates the architecture for this option.
Use an Amazon RDS snapshot
You can also migrate to a Multi-AZ DB cluster option using the Amazon RDS snapshot and restore option. Because you have upgraded your Amazon RDS for MySQL engine version to be using one of the supported engine versions and instances for Multi-AZ DB clusters, you can restore a snapshot of an existing Single-AZ DB instance deployment or Multi-AZ DB instance deployment to a Multi-AZ DB cluster.
You can
create a DB snapshot
or use an existing snapshot and then
restore
from it to form a new RDS instance. Because you’re restoring to an RDS Multi-AZ DB cluster deployment, review the available settings in
Settings for creating Multi-AZ DB clusters
. You can perform this operation through the console or using the Amazon Web Services CLI or Amazon Web Services SDK.
The restore operation can take time depending on the size of the database. As an optional step, if your workload can’t tolerate a longer downtime, you can configure
binary log replication
for new-zero migration downtime. You can
enable binary logs
on the source RDS for MySQL instance and run SHOW MASTER STATUS command to get the binary log position from which you can set up replication.
The following diagram illustrates the architecture for this option.
Use mysqldump and binary log replication
Whether you are migrating from self-managed MySQL or from self-managed MySQL on Amazon EC2 and would like to perform migration to a Multi-AZ DB cluster using MySQL native tools, you can also use popular logical backup tools like
mysqldump
. If you are migrating from an older DB instance class in an existing RDS for MySQL Single-AZ or Multi-AZ instance deployment, you can use this method to take advantage of the Optimized Writes feature in Multi-AZ DB clusters. With this tool, you can export schema definitions and data from your existing MySQL instances and restore the backup to the target Multi-AZ DB cluster. For larger datasets, if you require a near-zero downtime migration, you can use mysqldump to create backup files that include not only the data, but also the binary log position at the time when the consistent backup is taken using the parameter --master-data=2 , as shown in the following example code:
mysqldump --host=<host_name> --user=<source_user> \
--password=<source_user_password> --databases <database_name> \
--single-transaction --compress --order-by-primary \
--master-data=2 -p<local_password> -r backup.sql
Binary log replication would allow for continuous change data capture (CDC) and when the replica lag is near zero, you can perform the cutover. For step-by-step instructions on setting up this replication, refer to
Replicate between your external database and new Amazon RDS DB instance
.
You can also use
mydumper and myloader
for multithreaded migration. They offer advanced features such as dumping and loading data using parallel threads, and creating dump files in a file-per-table fashion.
The following diagram shows the architecture for this option.
Conclusion
When you decide to upgrade your existing RDS for MySQL resources to the latest version on Amazon RDS for MySQL 8.0.x, you can also use the RDS Multi-AZ DB cluster deployment option. This option supports workloads that require more read capacity and consistent transaction latency, while providing high availability and durability. In this post, we discussed the benefits and new enhancements of Multi-AZ DB clusters and possible minimal downtime migration methods.
You can start using the Multi-AZ DB cluster deployment with two readable standbys today by visiting the
Amazon RDS console
and
creating
a new RDS instance or
migrating
your existing RDS instance by upgrading the database engine version to a
supported MySQL version
.
About the Authors
Shagun Arora is a Database Specialist Solutions Architect at Amazon Web Services. She works with customers to design scalable, highly available, and secure solutions in the Amazon Web Services Cloud.
Vijay Karumajji is a Principal Database Solutions Architect with Amazon Web Services. He works with our customers to provide guidance and technical assistance on database projects, helping them improve the value of their solutions when using Amazon Web Services.
