MySQL Database Engine

What does it mean to run a DB Instance as a Read Replica?

Read Replicas make it easy to take advantage of MySQL’s built-in replication functionality to elastically scale out beyond the capacity constraints of a single DB Instance for read-heavy database workloads. You can create a Read Replica with a few clicks of the AWS Management Console or using the CreateDBInstanceReadReplica API. Once the Read Replica is created, database updates on the source DB Instance will be replicated using MySQL’s native, asynchronous replication. You can create multiple Read Replicas for a given source DB Instance and distribute your application’s read traffic amongst them. Since Read Replicas use MySQL’s built-in replication, they are subject to its strengths and limitations. In particular, updates are applied to your Read Replica(s) after they occur on the source DB Instance, and replication lag can vary significantly. Read Replicas can be associated with Multi-AZ deployments to gain read scaling benefits in addition to the enhanced database write availability and data durability provided by Multi-AZ deployments.

When would I want to consider using an Amazon RDS Read Replica?

There are a variety of scenarios where deploying one or more Read Replicas for a given source DB Instance may make sense. Common reasons for deploying a Read Replica include:

Which storage engines are supported for use with Read Replicas?

Read Replicas require a transactional storage engine and are only supported for the InnoDB storage engine.

Non-transactional engines such as MyISAM might prevent Read Replicas from working as intended. However, if you still choose to use MyISAM with Read Replicas, we advise you to watch the Amazon CloudWatch “Replica Lag” metric (available via the AWS Management Console or Amazon Cloud Watch APIs) carefully and recreate the Read Replica should it fall behind due to replication errors. The same considerations apply to the use of temporary tables and any other non-transactional engines.

How do I deploy a Read Replica for a given DB Instance?

You can create a Read Replica in minutes using the standard CreateDBInstanceReadReplica API or a few clicks of the Amazon RDS Management Console. When creating a Read Replica, you can identify it as a Read Replica by specifying a SourceDBInstanceIdentifier. The SourceDBInstanceIdentifier is the DB Instance Identifier of the “source” DB Instance from which you wish to replicate. As with a standard DB Instance, you can also specify the Availability Zone, DB Instance class, and preferred maintenance window. The MySQL version (e.g. MySQL 5.1.50) and storage allocation of a Read Replica is inherited from the source DB Instance. When you initiate the creation of a Read Replica, Amazon RDS takes a snapshot of your source DB Instance and begins replication. As a result, you will experience a brief I/O suspension on your source DB Instance as the snapshot occurs. The I/O suspension typically lasts on the order of one minute and can be avoided if the source DB Instance is a Multi-AZ deployment (in the case of Multi-AZ deployments, snapshots are taken from the standby). Amazon RDS is also currently working on an optimization (to be released shortly) such that if you create multiple Read Replicas within a 30 minute window, all of them will use the same source snapshot to minimize I/O impact (“catch-up” replication for each Read Replica will begin after creation).

Amazon RDS Read Replicas are as easy to delete as they are to create; simply use the Amazon RDS Management Console or call the DeleteDBInstance API (specifying the DBInstanceIdentifier for the Read Replica you wish to delete).

When requesting the creation of a Read Replica, here are a couple of additional things to consider:

How do I connect to my Read Replica(s)?

You can connect to a Read Replica just as you would connect to a standard DB Instance, using the DescribeDBInstance API or AWS Management Console to retrieve the endpoint(s) for you Read Replica(s). If you have multiple Read Replicas, it is up to your application to determine how read traffic will be distributed amongst them.

How many Read Replicas can I create for a given source DB Instance?

Amazon RDS allows you to create up to five (5) Read Replicas for a given source DB Instance.

Can I use a Read Replica to enhance database write availability or protect the data on my source DB Instance against failure scenarios?

If you are looking to use replication to increase database write availability and protect recent database updates against various failure conditions, we recommend you run your DB Instance as a Multi-AZ deployment. With Read Replicas and MySQL’s native, asynchronous replication, database writes occur on a Read Replica after they have already occurred on the source DB Instance, and this replication “lag” can vary significantly. In contrast, the replication used by Multi-AZ deployments is synchronous, meaning that all database writes are concurrent on the primary and standby. This protects your latest database updates, since they should be available on the standby in the event a failover is required. In addition, with Multi-AZ deployments replication is fully managed. Amazon RDS automatically monitors for DB Instance failure conditions or Availability Zone failure and initiates automatic failover to the standby if an outage occurs.

Can I create a Read Replica with a Multi-AZ DB Instance deployment as its source?

Since Multi-AZ DB Instances address a different need than Read Replicas, it makes sense to use the two in conjunction for production deployments and to associate a Read Replica with a Multi-AZ DB Instance deployment. The “source” Multi AZ-DB Instance provides you with enhanced write availability and data durability, and the associated Read Replica would improve read traffic scalability.

If my Read Replica(s) use a Multi-AZ DB Instance deployment as a source, what happens if Multi-AZ failover occurs?

In the event of a Multi-AZ failover, any associated and available Read Replicas should automatically resume replication once failover has completed (acquiring updates from the newly promoted primary).

My Read Replica appears “stuck” after a Multi-AZ failover and is unable to obtain or apply updates from the source DB Instance. What do I do?

You may find in some cases that your Read Replica(s) aren’t able to receive or apply updates from their source Multi-AZ DB Instance after a Multi-AZ failover. This is because some MySQL binlog events were not flushed to disk at the time of the failover. After the failover, the Read Replica may ask for binlogs from the source that it doesn’t have. This loss of MySQL binlogs during a crash is described in The Binary Log in the MySQL documentation.

Of particular relevance to this issue is the paragraph near the bottom that describes the MySQL sync-binlog parameter. This parameter controls how MySQL binlogs are flushed to disk, and when using InnoDB, how the binlogs and InnoDB logs may be kept in sync.

To resolve the current issue, you will need to delete the Read Replica and create a new one to replace it. To avoid this issue in the future, setting sync-binlog=1 will greatly reduce the chance that MySQL binlogs needed by the Read Replica will be lost during a crash/failover scenario. As the MySQL documentation explains, even this doesn’t completely resolve the issue. To further reduce the likelihood of hitting this issue, set innodb_support_xa=1. Note that there may be a performance penalty for setting these variables. Both sync_binlog and innodb_support_xa are dynamic variables, so if you find that the performance penalty is too great, you can reset them without taking an outage.

This is ultimately a choice between performance and improving the automatic resynchronization of Read Replicas after a source Multi-AZ failover. One of the advantages of Amazon RDS Read Replicas is that they can be quickly re-instantiated when synchronization issues arise by deleting and re-creating them. As such, you don’t have to take the performance hit from setting sync-binlog and/or innodb_support_xa if manually dropping out of sync Read Replicas and re-creating them meets your needs.

Can I create a Read Replica of another Read Replica?

Creating a Read Replica of another Read Replica is not supported.

Can my Read Replicas only accept database read operations?

Read Replicas are designed to serve read traffic. However, there may be use cases where advanced users wish to complete Data Definition Language (DDL) SQL statements against a Read Replica. Examples might include adding a database index to a Read Replica that is used for business reporting, without adding the same index to the corresponding source DB Instance. If you wish to enable operations other than reads for a given Read Replica, you will need to modify the active DB Parameter Group for the Read Replica, setting the read_only parameter to 0.

Can I convert my Read Replica into a primary DB Instance?

Support for converting a Read Replica into a standard or Multi-AZ DB Instance deployment ("primary") is not available at this time, but we expect to support this option in the future.

Will my Read Replica be kept up-to-date with its source DB Instance?

Updates to a source DB Instance will automatically be replicated to any associated Read Replicas. However, with MySQL’s asynchronous replication technology, a Read Replica can fall behind its source DB Instance for a variety of reasons. Typical reasons include:

Read Replicas are subject to the strengths and weaknesses of MySQL replication. If you are using Read Replicas, you should be aware of the potential for lag between a Read Replica and its source DB Instance, or "inconsistency".

How do I gain visibility into active Read Replica(s)?

You can use the standard DescribeDBInstances API to return a list of all the DB Instances you have deployed (including Read Replicas), or simply click on the "DB Instances" tab of the AWS Management Console.

Amazon RDS allows you to gain visibility into how far a Read Replica has fallen behind its source DB Instance by issuing a standard “Show Slave Status” MySQL command against the Read Replica. The “Seconds_Behind_Master” data returned by “Show Slave Status” is also published as an Amazon CloudWatch metric (“Replica Lag”) available via the AWS Management Console or Amazon Cloud Watch APIs.

My Read Replica has fallen significantly behind its source DB Instance. What should I do?

As discussed in the previous questions, “inconsistency” or lag between a Read Replica and its source DB Instance is common with MySQL asynchronous replication. If an existing Read Replica has fallen too far behind to meet your requirements, you can delete it and create a new one with the same endpoint by using the same DB Instance Identifier and Source DB Instance Identifier as the deleted Read Replica. Keep in mind that the re-creation process will be counter-productive at small lag levels (e.g. under five minutes of lag), and should be used with prudence (i.e. only when the Read Replica is significantly behind its source DB Instance). Also keep in mind that replica lag may naturally grow and shrink over time, depending on your source DB Instance’s steady-state usage pattern.

Scaling the DB Instance class of a Read Replica may reduce replication lag in some cases, particularly if your source DB Instance class is larger than your Read Replica DB Instance class. However, Read Replicas are not guaranteed to work in all cases. There may be scenarios and usage patterns where a Read Replica can never catch up with its source after initial creation, or otherwise remains too far behind its source to meet your use case requirements.

I scaled the compute and/or storage capacity of my source DB Instance, should I scale the resources for associated Read Replica(s) as well?

For replication to work effectively, we recommend that Read Replicas have as much or more compute and storage resources as their respective source DB Instances. Otherwise replication lag is likely to increase or your Read Replica may run out of space to store replicated updates.

Can I configure the replication between my source DB Instance and a Read Replica to use row-based replication?

By default, replication is set to mixed-format (which includes both row-based and statement-based replication), which should meet the requirements of most use cases. However, if you are an advanced user and wish to designate that row-based replication is used, you can do so on a session basis by using a SQL command to set the binlog_format for a DB Instance to “row.” To learn more about the difference between mixed-format replication and row-based replication, go to the MySQL documentation.

Can DB Snapshots or automated backups be taken of Read Replicas?

No. If you are looking to increase database write availability by taking backups from your Read Replica instead of its source DB Instance, you can accomplish the same objective by running your DB Instance as a Multi-AZ deployment. Backups will then be initiated from the Multi-AZ standby to minimize availability impact.

How do I delete a Read Replica? Will it be deleted automatically if its source DB Instance is deleted?

You can easily delete a Read Replica with a few clicks of the AWS Management Console or by passing its DB Instance identifier to the DeleteDBInstance API. A Read Replica will stay active and continue accepting read traffic even after its corresponding source DB Instance has been deleted. If you desire to delete the Read Replica in addition to the source DB Instance, you must explicitly delete the Read Replica using the DeleteDBInstance API or AWS Management Console.

Can I directly access the binary logs for my Database Instance to manage my own replication?

Amazon RDS does not currently provide access to the binary logs for your Database Instance.

How much do Read Replicas cost? When does billing begin and end?

A Read Replica is billed as a standard DB Instance and at the same rates. Just like a standard DB Instance, the rate per “DB Instance hour” for a Read Replica is determined by the DB Instance class of the Read Replica – please see Amazon RDS detail page for up-to-date pricing. You are not charged for the data transfer incurred in replicating data between your source DB Instance and Read Replica. Billing for a Read Replica begins as soon as the Read Replica has been successfully created (i.e. when status is listed as “active”). The Read Replica will continue being billed at standard Amazon RDS DB Instance hour rates until you issue a command to delete it.

Can I control if and when the MySQL version powering Amazon RDS DB Instance is upgraded to new supported versions?

Amazon RDS allows you to control if and when the relational database software powering your MySQL DB Instance is upgraded to new versions supported by Amazon RDS. This provides you with the flexibility to maintain compatibility with specific MySQL versions, test new versions with your application before deploying in production, and perform version upgrades on your own terms and timelines.

Unless you specify otherwise, your DB Instance will automatically be upgraded to new MySQL minor versions as they are supported by Amazon RDS. This patching will occur during your scheduled maintenance window, and will be announced on the Amazon RDS Community Forum in advance. If you wish to turn off automatic version upgrades, you can do so by setting the AutoMinorVersionUpgrade parameter to “false.” Since major version upgrades involve some compatibility risk, they will not occur automatically and must be initiated by you.

This approach to database software patching puts you in the driver’s seat of version upgrades, but still offloads the work of patch application to Amazon RDS. You can learn more about version management by reading the FAQ entires that follow. Alternatively, you can reference our Developer Guide.

While DB Engine version management functionality is intended to give you as much control as possible over how patching occurs, Amazon RDS reserves the right to patch your DB Instance on your behalf in the event of a critical security vulnerability in the database software.

How do I specify which supported MySQL Version I would like my DB Instance to run?

You can specify any currently supported version (minor and/or major) when creating a new DB Instance via the CreateDBInstance API. You simply pass in the desired version to the EngineVersion parameter upon create; if no version is specified, Amazon RDS will default to a supported version, typically the most recent version. If a major version (e.g. MySQL 5.1) is specified but a minor version is not, Amazon RDS will default to a recent release of the major version you have specified. To see a list of supported versions, as well as defaults for newly created DB Instances, simply use the DescribeDBEngineVersions API.

If you have opted out of automatically scheduled upgrades by setting the AutoMinorVersionUpgrade parameter to false but wish to manually initiate an upgrade to a supported minor version or major version release, you can do so using the ModifyDBInstance API. Simply specify the version you wish to upgrade to via the EngineVersion parameter. The upgrade will then be applied on your behalf either immediately (if the ApplyImmediately flag is set to true) or during the next scheduled maintenance window for your DB Instance.

Can I test my DB Instance against a new version before upgrading?

Yes. You can do so by creating a DB Snapshot of your existing DB Instance, restoring from the DB Snapshot to create a new DB Instance, and then initiating a version upgrade for the new DB Instance. You can then experiment safely on the upgraded clone of your DB Instance before deciding whether or not to upgrade your original DB Instance.

How does Amazon RDS distinguish between major and minor version releases?

In the context of MySQL, version numbers are organized as follows:

MySQL version = X.Y.Z

...where X denotes the major version, Y denotes the release level, and Z is the version number within the release series.

For Amazon RDS implementations, a version change would be considered major if either major version or release level is being changed; for example, going from version 5.1.x to 5.5.x. A version change would be considered minor if the version number within the release is being changed - for example, going from version 5.1.45 to version 5.1.49.

Amazon RDS currently supports the MySQL major versions MySQL 5.1 and MySQL 5.5. We plan to support additional major MySQL versions in the future.

Does Amazon RDS provide guidelines for supporting new MySQL version releases and/or deprecating MySQL versions that are currently supported?

Over time, we plan to support additional MySQL versions for Amazon RDS, both minor and major. The number of new version releases supported in a given year will vary based on the frequency and content of the MySQL version releases and the outcome of a thorough vetting of the release by our database engineering team. However, as a general guidance, we aim to support new MySQL versions within 3-5 months of their General Availability release.

In terms of deprecation policy:

How do I create a MySQL 5.5 DB Instance or upgrade my existing MySQL 5.1 DB Instance to MySQL 5.5?

To create a new MySQL 5.5 DB Instance, use the Launch DB Instance Wizard in the AWS Management Console and select a version corresponding to MySQL 5.5, or simply call the CreateDBInstance API with the Engine Version parameter of 5.5.

A direct upgrade from MySQL 5.1 to MySQL 5.5 is not currently supported. However, we intend to provide this functionality in the future. Meanwhile, if you would like to port your existing MySQL 5.1 database to MySQL 5.5, you can use mysqldump to export your database from your existing MySQL 5.1 DB Instance and import it into a new MySQL 5.5 DB Instance.