Amazon Relational Database Service
User Guide (API Version 2014-09-01)
« PreviousNext »
View the PDF for this guide.Go to the AWS Discussion Forum for this product.Go to the Kindle Store to download this guide in Kindle format.Did this page help you?  Yes | No |  Tell us about it...

Working with Read Replicas

Amazon RDS uses MySQL’s built-in replication functionality to create a special type of DB instance called a read replica from a source DB instance. Updates made to the source DB instance are copied to the read replica. You can reduce the load on your source DB instance by routing read queries from your applications to the read replica. Read replicas allow you to elastically scale out beyond the capacity constraints of a single DB instance for read-heavy database workloads.

Note

The information in this topic applies to creating Amazon RDS read replicas, either in the same region as the source Amazon RDS DB instance, or in a separate AWS region. The topic does not apply to setting up replication with an instance of MySQL that is running either in an Amazon EC2 instance or on-premises. Amazon RDS currently supports those replication configurations only for the time needed to import or export large databases to or from DB instances of MySQL 5.6. For more information, see Importing and Exporting Data From a MySQL DB Instance.

When you create a read replica, you specify an existing DB instance as the source. Amazon RDS takes a snapshot of the source instance and creates a read-only instance from the snapshot. Amazon RDS then uses the asynchronous replication that is native to MySQL to update the read replica whenever there is a change to the source DB instance. The read replica operates as a DB instance that allows only read-only connections; applications can connect to a read replica the same way they would any DB instance. Amazon RDS replicates all databases in the source DB instance. For more information about read replicas, see Read Replicas.

Before a DB instance can serve as a replication source, you must enable automatic backups on the source DB instance by setting the backup retention period to a value other than 0. This requirement also applies to a read replica that is the source DB instance for another read replica. Automatic backups are supported only for read replicas running MySQL 5.6, not 5.1 or 5.5.

You can create up to 5 read replicas from one DB instance. In order for replication to operate effectively, each read replica should have as much compute and storage resources as the source DB instance. If you scale the source DB instance, you should also scale the read replicas.

If a read replica is running MySQL 5.6, you can specify it as the source DB instance for another read replica. For example, you can create ReadReplica1 from MyDBInstance, and then create ReadReplica2 from ReadReplica1. Updates made to MyDBInstance are replicated to ReadReplica1 and then replicated from ReadReplica1 to ReadReplica2. You cannot have more than three instances involved in a replication chain. For example, you can create ReadReplica1 from MySourceDBInstance, and then create ReadReplica2 from ReadReplica1, but you cannot create a ReadReplica3 from ReadReplica2. To enable automatic backups on an Amazon RDS MySQL version 5.6 read replica, first create the read replica, then modify the read replica to enable automatic backups.

By default, a read replica is created with the same storage type as the source DB instance. However, you can create a read replica that has a different storage type from the source DB instance based on the options listed in the following table.

Source DB instance storage typeSource DB instance storage allocationRead Replica storage type options
PIOPS100 GB - 3 TBPIOPS | GP2 | Standard
GP2100 GB - 3 TBPIOPS | GP2 | Standard
GP2Less than 100 GBGP2 | Standard
Standard100 GB - 3 TBPIOPS | GP2 | Standard
StandardLess than 100 GBGP2 | Standard

Amazon RDS does not support circular replication. You cannot configure a DB instance to serve as a replication source to an existing DB instance; you can only create a new read replica from an existing DB instance. For example, if MyDBInstance replicates to ReadReplica1, you cannot configure ReadReplica1 to replicate back to MyDBInstance. From ReadReplica1, you can only create a new read replica, such as ReadReplica2.

Read replicas are designed to support read queries, but there may be a need for occasional updates, such as adding an index to speed the specific types of queries accessing the replica. You can enable updates by setting the read_only parameter to 0 in the DB parameter group for the read replica.

You can run multiple concurrent read replica create or delete actions that reference the same source DB instance, as long as you stay within the limit of 5 read replicas for the source instance.

You can create a read replica from either Single-AZ or Multi-AZ DB instance deployments. You use a Multi-AZ deployment to improve the durability and availability of a critical system, but you cannot use the Multi-AZ secondary to serve read-only queries. You must create read replicas from a high-traffic, Multi-AZ DB instance to offload read queries from the source DB instance. If the source instance of a Multi-AZ deployment fails over to the secondary, any associated read replicas will be switched to use the secondary as their replication source. It is possible that the read replicas cannot be switched to the secondary if some MySQL binlog events are not flushed during the failure. In this case, you must manually delete and recreate the read replicas. You can reduce the chance of this happening in MySQL 5.1 or 5.5 by setting the sync_binlog=1 and innodb_support_xa=1 dynamic variables. These settings may reduce performance, so test their impact before implementing the changes to a production environment. These problems are less likely to occur if you are using MySQL 5.6. For instances running MySQL 5.6, the parameters are set by default to sync_binlog=1 and innodb_support_xa=1.

When you create a read replica, Amazon RDS sets up a secure communications channel using public key encryption between the source DB instance and the read replica. RDS establishes any AWS security configurations, such as adding security group entries, needed to enable the secure channel.

You usually configure replication between Amazon RDS DB instances, but you can configure replication to import databases from instances of MySQL running outside of Amazon RDS, or to export databases to such instances. For more information, see Importing Data From a MySQL Instance Running External to Amazon RDS and Using Replication to Export Amazon RDS MySQL 5.6 Data.

You can stop and restart the replication process on an Amazon RDS DB instance by calling the system stored procedures mysql.rds_stop_replication and mysql.rds_start_replication. You can do this when replicating between two Amazon RDS instances for long running operations such as creating large indexes. You also need to stop and start replication when importing or exporting databases. For more information, see Importing Data From a MySQL Instance Running External to Amazon RDS and Using Replication to Export Amazon RDS MySQL 5.6 Data.

You must explicitly delete read replicas, using the same mechanisms for deleting a DB instance. If you delete the source DB instance without deleting the replicas, each replica is promoted to a standalone, Single-AZ DB instance.

Replicating Across Regions

Replicating from one region to another includes benefits such as improving your disaster recovery capabilities, scaling read operations into a region closer to end users, or making it easier to migrate from a data center in one region to a data center in another region. Creating a read replica in a different region than the source instance is very similar to creating a replica in the same region. You run the create read replica command in the region where you want the read replica, and specify the Amazon Resource Name (ARN) of the source DB instance.

Cross Region Replication Considerations

All of the considerations for performing replication within a region apply to cross region replication. The following extra considerations apply when replicating between regions:

  • You can only replicate between regions when using Amazon RDS DB instances of MySQL 5.6.

  • You can only cross one regional boundary in a given replication chain. You can create a cross-region Amazon RDS read replica from:

    • A source Amazon RDS DB instance that is not a read replica of another Amazon RDS DB instance.

    • An Amazon RDS DB instance that is a read replica of an on-premises or Amazon EC2 instance of MySQL that is not in Amazon RDS.

  • You cannot set up a replication channel into or out of the AWS GovCloud (US) Region.

  • You should expect to see some higher level of lag time for any read replica that is in a different region than the source instance, due to the longer network channels between regional data centers.

  • Within a region, all cross-region replicas created from the same source DB instance must either be in the same Amazon VPC or be outside of a VPC. For those read replicas, any of the create read replica commands that specify the --db-subnet-group-name parameter must specify a DB subnet group from the same VPC.

  • You can create a cross-region read replica in an VPC from a source DB instance that is not in an VPC. You can also create a cross-region read replica that is not in an VPC from a source DB instance that is in a VPC.

Cross Region Replication Costs

The data transferred for cross region replication incurs Amazon RDS data transfer charges. These cross region replication actions generate charges for the data transferred out of the source region:

  • When you create the read replica, Amazon RDS takes a snapshot of the source instance and transfers the snapshot to the read replica region.

  • For each data modification made in the source databases, Amazon RDS transfers data from the source region to the read replica region.

For more information about Amazon RDS data transfer pricing, go to Amazon Relational Database Service Pricing.

You can reduce your data transfer costs by reducing the number of cross region read replicas you create. For example, if you have a source DB instance in one region and want to have three read replicas in another region, only create one of the read replicas from the source DB instance, and then create the other two replicas from the first read replica instead of the source. For example, if you have source-instance-1 in one region, you can:

  • Create read-replica-1 in the new region, specifying source-instance-1 as the source.

  • Create read-replica-2 from read-replica-1.

  • Create read-replica-3 from read-replica-1.

In this example, you will only be charged for the data transferred from source-instance-1 to read-replica-1. You will not be charged for the data transferred from read-replica-1 to the other two replicas because they are all in the same region. If you created all three replicas directly from source-instance-1, you would be charged for the data transfers to all three replicas.

Examples

Example Create Cross Region Read Replica Outside of any VPC

This is an example of creating a read replica in us-west-2 from a source DB instance in us-east-1. The read replica is created outside of a VPC:

PROMPT> rds-create-db-instance-read-replica SimCoProd01Replica01 --region us-west-2 --source-db-instance-identifier arn:aws:rds:us-east-1:123456789012:db:SimcoProd01
        

Example Create Cross Region Read Replica in a VPC

This is an example of creating a read replica in us-west-2 from a source DB instance in us-east-1. The read replica is created in the VPC associated with the specified DB subnet group:

PROMPT> rds-create-db-instance-read-replica SimCoProd01Replica01 --region us-west-2 --db-subnet-group-name my-us-west-2-subnet --source-db-instance-identifier arn:aws:rds:us-east-1:123456789012:db:SimcoProd01
        

Cross Region Replication Process

Amazon RDS uses the following process to create a cross region read replica. Depending on the regions involved and the amount of data in the databases, this process could take hours to complete. You can use this information to determine how far the process has proceeded when you create a cross region read replica:

  1. Amazon RDS begins configuring the source DB instance as a replication source and sets the status to modifying.

  2. Amazon RDS begins setting up the specified read replica in the destination region and sets the status to creating.

  3. Amazon RDS creates an automated DB snapshot of the source DB instance in the source region. The format of the DB snapshot name is rds:<InstanceID>-<timestamp>, where <InstanceID> is the identifier of the source instance, and <timestamp> is the date and time the copy started. For example, rds:mysourceinstance-2013-11-14-09-24 was created from the instance mysourceinstance at 2013-11-14-09-24. During this phase, the source DB instance status remains modifying, the read replica status remains creating, and the DB snapshot status is creating. The progress column of the DB snapshot page in the console reports how far the DB snapshot creation has progressed. When the DB snapshot is complete, the status of both the DB snapshot and source DB instance are set to available.

  4. Amazon RDS begins a cross region snapshot copy for the initial data transfer. The snapshot copy is listed as an automated snapshot in the destination region with a status of creating. It has the same name as the source DB snapshot. The progress column of the DB snapshot display indicates how far the copy has progressed. When the copy is complete, the status of the DB snapshot copy is set to available.

  5. Amazon RDS then uses the copied DB snapshot for the initial data load on the read replica. During this phase, the read replica will be in the list of DB instances in the destination, with a status of creating. When the load is complete, the read replica status is set to available, and the DB snapshot copy is deleted.

  6. When the read replica reaches the available status, Amazon RDS starts by replicating the changes made to the source instance since the start of the create read replica operation. During this phase, the replication lag time for the read replica will be greater than 0. You can monitor this in Amazon CloudWatch by viewing the Amazon RDS ReplicaLag metric. The ReplicaLag metric reports the value of the Seconds_Behind_Master field of the MySQL SHOW SLAVE STATUS command. For more information, see SHOW SLAVE STATUS. When the ReplicaLag metric reaches 0, the replica has caught up to the source DB instance. If the ReplicaLag metric returns -1, then replication is currently not active. ReplicaLag = -1 is equivalent to Seconds_Behind_Master = NULL. Common causes for ReplicaLag returning -1 are the following:

    • A network outage.

    • Writing to tables with indexes on a read replica. If the read_only parameter is not set to 0 on the read replica, it can break replication.

    • Using a non-transactional storage engine such as MyISAM. Replication is only supported for the InnoDB storage engine.

Creating a Read Replica

You can create a read replica from an existing MySQL DB instance using the AWS Management Console, CLI, or API. You create a read replica by specifying the SourceDBInstanceIdentifier, which is the DB instance identifier of the source DB instance from which you wish to replicate.

When you initiate the creation of a read replica, Amazon RDS takes a DB 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 DB snapshot occurs. The I/O suspension typically lasts about one minute and can be avoided if the source DB instance is a Multi-AZ deployment (in the case of Multi-AZ deployments, DB snapshots are taken from the standby). An active, long-running transaction can slow the process of creating the read replica, so wait for long-running transactions to complete before creating a read replica. If you create multiple read replicas in parallel from the same source DB instance, Amazon RDS takes only one snapshot at the start of the first create action.

When creating a read replica, there are a few things to consider. First, you must enable automatic backups on the source DB instance by setting the backup retention period to a value other than 0. This requirement also applies to a read replica that is the source DB instance for another read replica. Automatic backups are supported only for read replicas running MySQL 5.6, not 5.1 or 5.5. To enable automatic backups on an Amazon RDS MySQL version 5.6 read replica, first create the read replica, then modify the read replica to enable automatic backups.

Second, if you are using a non-transactional engine such as MyISAM, you will need to perform the following steps to successfully set up your read replica. These steps are required to ensure that the read replica has a consistent copy of your data. Note that these steps are not required if all of your tables use a transactional engine such as InnoDB.

  1. Stop all DML and DDL operations on non-transactional tables in the source DB instance and wait for them to complete. SELECT statements can continue running.

  2. Flush and lock the tables in the source DB instance.

  3. Create the read replica using one of the methods in the following sections.

  4. Check the progress of the read replica creation using, for example, the DescribeDBInstances API operation. Once the read replica is available, unlock the tables of the source DB instance and resume normal database operations.

AWS Management Console

To create a read replica from a source MySQL DB instance

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. In the navigation pane, click DB Instances.

  3. In the My DB Instances pane, right click the MySQL DB instance that you want to use as the source for a read replica and select Create Read Replica.

  4. In the DB Instance Identifier text box, type a name for the read replica. Adjust other settings as needed.

  5. In the Destination Region box, specify the region for the read replica if it is different than the region of the source DB instance.

  6. In the Destination DB Subnet Group box, specify a DB subnet group associated with a VPC if you want the read replica to be created in that VPC. Leave the box empty if you want the read replica to be created outside of any VPC. The VPC and DB subnet group must exist in the destination region. Within a given region, all read replicas created from the same source DB instance must be either:

    • All created in the same VPC.

    • All created outside of any VPC.

  7. Click Yes, Create Read Replica.

CLI

To create a read replica from a source MySQL DB instance

API

To create a read replica from a source MySQL DB instance

Promoting a Read Replica to be a DB Instance

You can promote a MySQL read replica into a standalone, Single-AZ DB instance. There are several reasons you might want to convert a read replica into a Single-AZ DB instance. It is important to remember that when promoting a read replica, the instance will be rebooted before it becomes available.

Some DDL operations, such as creating indexes or other maintenance tasks, can be a lengthy process, and some operations can affect performance of a DB instance. You can perform these operations on a read replica, instead. When the DDL operations are complete and the read replica is updated by the source DB instance, you can promote the read replica. You can also promote a read replica as part of a disaster recovery plan.

The new DB instance that is created when you promote a read replica retains the backup retention period, backup window period, and parameter group of the former read replica source. The promotion process can take several minutes or longer to complete, depending on the size of the read replica. Once you promote the read replica into a Single-AZ DB instance, it is just like any other Single-AZ DB instance. For example, you can convert the new DB instance into a Multi-AZ DB instance, and you can create read replicas from it. You can also take DB snapshots and perform point-in-time restore operations. Because the promoted DB instance is no longer a read replica, you cannot use it as a replication target. If a source DB instance has several read replicas, promoting one of the read replicas to a DB instance has no effect on the other replicas.

If you promote a read replica that is in turn replicating to other read replicas, those replications stay active. Consider an example where MyDBInstance1 replicates to MyDBInstance2, and MyDBInstance2 replicates to MyDBInstance3. If you promote MyDBInstance2, there will no longer be any replication from MyDBInstance1 to MyDBInstance2, but MyDBInstance2 will still replicate to MyDBInstance3.

We recommend that you enable automated backups on your read replica before promoting the read replica. This ensures that no backup is taken during the promotion process. Once the instance is promoted to a primary instance, backups are taken based on your backup settings.

The following steps show the general process for promoting a read replica to a Single-AZ DB instance.

  1. Stop any transactions from being written to the read replica source DB instance, and then wait for all updates to be made to the read replica. Database updates occur on the read replica after they have occurred on the source DB instance, and this replication "lag" can vary significantly. Use the Replica Lag metric to determine when all updates have been made to the read replica.

  2. To be able to make changes to the read replica, you must the set the read_only parameter to 0 in the DB parameter group for the read replica.

  3. Perform all needed DDL operations, such as creating indexes, on the read replica. Actions taken on the read replica do not affect the performance of the source DB instance.

  4. Promote the read replica by using the Promote Read Replica option on the Amazon RDS console, the CLI command rds-promote-read-replica, or the PromoteReadReplica API operation.

    Note

    The promotion process takes a few minutes to complete. When you promote a read replica, replication is stopped and the read replica is rebooted. When the reboot is complete, the read replica is available as a Single-AZ DB instance.

AWS Management Console

To promote a read replica to a DB instance

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. In the Amazon RDS Console, click Read Replicas.

  3. In the Read Replicas pane, select the check box beside the read replica that you want to promote.

  4. Click Promote Read Replica.

  5. In the Promote Read Replica dialog box, enter the backup retention period and the backup window for the new promoted DB instance.

  6. When the settings are as you want them, click Continue.

  7. On the acknowledgment page, click Yes, Promote.

CLI

To promote a read replica to a DB instance

API

To promote a read replica to a DB instance

Monitoring Read Replication

You can monitor the status of a read replica in several ways. The Amazon RDS console shows the status of a read replica; you can also see the status of a read replica using the CLI command rds-describe-db-instances or the API action DescribeDBInstances.

read replica status

The status of a read replica can be one of the following:

  • ReplicatingThe read replica is replicating successfully.

  • ErrorAn error has occurred with the replication. Check the Replication Error field in the Amazon RDS console or the event log to determine the exact error. For more information about troubleshooting a replication error, see Troubleshooting a Read Replica Problem.

  • StoppedReplication has stopped because of a customer initiated request.

  • TerminatedThe read replica has lagged the source DB instance for more than 30 days due to replication errors and is terminated. The read replica is still accessible for read operations but cannot synchronize with the source instance.

If replication errors occur in a read replica for 30 days, replication is terminated to prevent increased storage requirements and long failover times. Broken replication can effect storage because the binlogs can grow in size and number due to the high volume of errors messages being written to the binlog. Broken replication can also effect failure recovery due to the time Amazon RDS requires to maintain and process the large number of binlogs during recovery.

You can monitor how far a replica is lagging the source DB instance by viewing the Seconds_Behind_Master data returned by the MySQL Show Slave Status command, or the CloudWatch Replica Lag statistic. If a replica lags too far behind for your environment, consider deleting and recreating the read replica. Also consider increasing the scale of the read replica to speed replication.

Troubleshooting a Read Replica Problem

MySQL's read replication technology is asynchronous. Because it is asynchronous, occasional BinLogDiskUsage increases on the source DB instance and ReplicaLag on the read replica are to be expected. For example, a high volume of writes to the source DB instance can occur in parallel, while writes to the read replica are serialized using a single I/O thread, can lead to a lag between the source instance and read replica. For more information about read replicas in the MySQL documentation, see Replication Implementation Details.

There are several things you can do to reduce the lag between updates to a source DB instance and the subsequent updates to the read replica, such as:

  • Sizing a read replica to have a storage size and DB instance class comparable to the source DB instance.

  • Ensuring that parameter settings in the DB parameter groups used by the source DB instance and the read replica are compatible. For more information and an example, see the discussion of the max_allowed_packet parameter later in this section.

Amazon RDS monitors the replication status of your read replicas and updates the Replication State field of the read replica instance to Error if replication stops for any reason, such as DML queries being run on your read replica that conflict with the updates made on the source DB instance. You can review the details of the associated error thrown by the MySQL engine by viewing the Replication Error field. Events that indicate the status of the read replica are also generated, including RDS-EVENT-0045, RDS-EVENT-0046, and RDS-EVENT-0047. For more information about events and subscribing to events, see Using Amazon RDS Event Notification. If the MySQL error message is returned, review the error number in the MySQL error message documentation.

One common issue that can cause replication errors is when the value for the max_allowed_packet parameter for a read replica is less than the max_allowed_packet parameter for the source DB instance. The max_allowed_packet parameter is a custom parameter that you can set in a DB parameter group that is used to specify the maximum size of DML that can be executed on the database. If the max_allowed_packet parameter value in the DB parameter group associated with a source DB instance is smaller than the max_allowed_packet parameter value in the DB parameter group associated with the source's read replica, the replication process can throw an error (Packet bigger than 'max_allowed_packet' bytes) and stop replication. You can fix the error by having the source and read replica use DB parameter groups with the same max_allowed_packet parameter values.

Other common situations that can cause replication errors include:

  • Writing to tables on a read replica. If you are creating indexes on a read replica, you need to have the read_only parameter set to 0 to create the indexes. If you are writing to tables on the read replica, it may break replication.

  • Using a non-transactional storage engine such as MyISAM. Read replicas require a transactional storage engine. Replication is only supported for the InnoDB storage engine.

  • Using unsafe non-deterministic queries such as SYSDATE(). For more information, see Determination of Safe and Unsafe Statements in Binary Logging.

If you decide that you can safely skip an error, you can follow the steps described in the section Skipping the Current Replication Error. Otherwise, you can delete the read replica and create a instance using the same DB instance identifier so that the endpoint remains the same as that of your old read replica. If a replication error is fixed, the Replication State changes to replicating.