# Working with MySQL replication in Amazon RDS
You usually use read replicas to configure replication between Amazon RDS DB instances. For general information about read replicas, see [Working with DB instance read replicas](USER_ReadRepl.md). For specific information about working with read replicas on Amazon RDS for MySQL, see [Working with MySQL read replicas](USER_MySQL.Replication.ReadReplicas.md).
You can use global transaction identifiers (GTIDs) for replication with RDS for MySQL. For more information, see [Using GTID-based replication](mysql-replication-gtid.md).
You can also set up replication between an RDS for MySQL DB instance and a MariaDB or MySQL instance that is external to Amazon RDS. For information about configuring replication with an external source, see [Configuring binary log file position replication with an external source instance](MySQL.Procedural.Importing.External.Repl.md).
For any of these replication options, you can use either row-based replication, statement-based, or mixed replication. Row-based replication only replicates the changed rows that result from a SQL statement. Statement-based replication replicates the entire SQL statement. Mixed replication uses statement-based replication when possible, but switches to row-based replication when SQL statements that are unsafe for statement-based replication are run. In most cases, mixed replication is recommended. The binary log format of the DB instance determines whether replication is row-based, statement-based, or mixed. For information about setting the binary log format, see [Configuring RDS for MySQL binary logging for Single-AZ databases](USER_LogAccess.MySQL.BinaryFormat.md).
**Note**
You can configure replication to import databases from a MariaDB or MySQL instance that is external to Amazon RDS, or to export databases to such instances. For more information, see [Importing data to an Amazon RDS for MySQL database with reduced downtime](mysql-importing-data-reduced-downtime.md) and [Exporting data from a MySQL DB instance by using replication](MySQL.Procedural.Exporting.NonRDSRepl.md).
After you restore your DB instance from a snapshot or perform a point-in-time recovery, you can view the last recovered binlog position from the source database in the RDS console. Under **Logs & events**, enter **binlog**. The binlog position appears under **System notes**.
**Topics**
+ [Working with MySQL read replicas](USER_MySQL.Replication.ReadReplicas.md)
+ [Using GTID-based replication](mysql-replication-gtid.md)
+ [Configuring binary log file position replication with an external source instance](MySQL.Procedural.Importing.External.Repl.md)
+ [Configuring multi-source-replication for Amazon RDS for MySQL](mysql-multi-source-replication.md)
# Working with MySQL read replicas
Following, you can find specific information about working with read replicas on RDS for MySQL. For general information about read replicas and instructions for using them, see [Working with DB instance read replicas](USER_ReadRepl.md).
For more information about MySQL read replicas, see the following topics.
+ [Configuring replication filters with MySQL](USER_MySQL.Replication.ReadReplicas.ReplicationFilters.md)
+ [Configuring delayed replication with MySQL](USER_MySQL.Replication.ReadReplicas.DelayReplication.md)
+ [Updating read replicas with MySQL](USER_MySQL.Replication.ReadReplicas.Updates.md)
+ [Working with Multi-AZ read replica deployments with MySQL](USER_MySQL.Replication.ReadReplicas.MultiAZ.md)
+ [Using cascading read replicas with RDS for MySQL](USER_MySQL.Replication.ReadReplicas.Cascading.md)
+ [Monitoring replication lag for MySQL read replicas](USER_MySQL.Replication.ReadReplicas.Monitor.md)
+ [Starting and stopping replication with MySQL read replicas](USER_MySQL.Replication.ReadReplicas.StartStop.md)
+ [Troubleshooting a MySQL read replica problem](USER_ReadRepl.Troubleshooting.md)
## Configuring read replicas with MySQL
Before a MySQL DB instance can serve as a replication source, make sure to enable automatic backups on the source DB instance. To do this, set 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 for read replicas running any version of MySQL. You can configure replication based on binary log coordinates for a MySQL DB instance.
You can configure replication using global transaction identifiers (GTIDS) on the following versions:
+ RDS for MySQL version 5.7.44 and higher 5.7 versions
+ RDS for MySQL version 8.0.28 and higher 8.0 versions
+ RDS for MySQL version 8.4.3 and higher 8.4 versions
For more information, see [Using GTID-based replication](mysql-replication-gtid.md).
You can create up to 15 read replicas from one DB instance within the same Region. For replication to operate effectively, each read replica should have the same amount of compute and storage resources as the source DB instance. If you scale the source DB instance, also scale the read replicas.
RDS for MySQL supports cascading read replicas. To learn how to configure cascading read replicas, see [Using cascading read replicas with RDS for MySQL](USER_MySQL.Replication.ReadReplicas.Cascading.md).
You can run multiple read replica create and delete actions at the same time that reference the same source DB instance. When you perform these actions, stay within the limit of 15 read replicas for each source instance.
A read replica of a MySQL DB instance can't use a lower DB engine version than its source DB instance.
### Preparing MySQL DB instances that use MyISAM
If your MySQL DB instance uses a nontransactional engine such as MyISAM, you need to perform the following steps to successfully set up your read replica. These steps are required to make sure that the read replica has a consistent copy of your data. These steps are not required if all of your tables use a transactional engine such as InnoDB.
1. Stop all data manipulation language (DML) and data definition language (DDL) operations on non-transactional tables in the source DB instance and wait for them to complete. SELECT statements can continue running.
1. Flush and lock the tables in the source DB instance.
1. Create the read replica using one of the methods in the following sections.
1. 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.
# Configuring replication filters with MySQL
You can use replication filters to specify which databases and tables are replicated with a read replica. Replication filters can include databases and tables in replication or exclude them from replication.
The following are some use cases for replication filters:
+ To reduce the size of a read replica. With replication filtering, you can exclude the databases and tables that aren't needed on the read replica.
+ To exclude databases and tables from read replicas for security reasons.
+ To replicate different databases and tables for specific use cases at different read replicas. For example, you might use specific read replicas for analytics or sharding.
+ For a DB instance that has read replicas in different AWS Regions, to replicate different databases or tables in different AWS Regions.
**Note**
You can also use replication filters to specify which databases and tables are replicated with a primary MySQL DB instance that is configured as a replica in an inbound replication topology. For more information about this configuration, see [Configuring binary log file position replication with an external source instance](MySQL.Procedural.Importing.External.Repl.md).
**Topics**
+ [Setting replication filtering parameters for RDS for MySQL](#USER_MySQL.Replication.ReadReplicas.ReplicationFilters.Configuring)
+ [Replication filtering limitations for RDS for MySQL](#USER_MySQL.Replication.ReadReplicas.ReplicationFilters.Limitations)
+ [Replication filtering examples for RDS for MySQL](#USER_MySQL.Replication.ReadReplicas.ReplicationFilters.Examples)
+ [Viewing the replication filters for a read replica](#USER_MySQL.Replication.ReadReplicas.ReplicationFilters.Viewing)
## Setting replication filtering parameters for RDS for MySQL
To configure replication filters, set the following replication filtering parameters on the read replica:
+ `replicate-do-db` – Replicate changes to the specified databases. When you set this parameter for a read replica, only the databases specified in the parameter are replicated.
+ `replicate-ignore-db` – Don't replicate changes to the specified databases. When the `replicate-do-db` parameter is set for a read replica, this parameter isn't evaluated.
+ `replicate-do-table` – Replicate changes to the specified tables. When you set this parameter for a read replica, only the tables specified in the parameter are replicated. Also, when the `replicate-do-db` or `replicate-ignore-db` parameter is set, make sure to include the database that includes the specified tables in replication with the read replica.
+ `replicate-ignore-table` – Don't replicate changes to the specified tables. When the `replicate-do-table` parameter is set for a read replica, this parameter isn't evaluated.
+ `replicate-wild-do-table` – Replicate tables based on the specified database and table name patterns. The `%` and `_` wildcard characters are supported. When the `replicate-do-db` or `replicate-ignore-db` parameter is set, make sure to include the database that includes the specified tables in replication with the read replica.
+ `replicate-wild-ignore-table` – Don't replicate tables based on the specified database and table name patterns. The `%` and `_` wildcard characters are supported. When the `replicate-do-table` or `replicate-wild-do-table` parameter is set for a read replica, this parameter isn't evaluated.
The parameters are evaluated in the order that they are listed. For more information about how these parameters work, see the MySQL documentation:
+ For general information, see [ Replica Server Options and Variables](https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html).
+ For information about how database replication filtering parameters are evaluated, see [ Evaluation of Database-Level Replication and Binary Logging Options](https://dev.mysql.com/doc/refman/8.0/en/replication-rules-db-options.html).
+ For information about how table replication filtering parameters are evaluated, see [ Evaluation of Table-Level Replication Options](https://dev.mysql.com/doc/refman/8.0/en/replication-rules-table-options.html).
By default, each of these parameters has an empty value. On each read replica, you can use these parameters to set, change, and delete replication filters. When you set one of these parameters, separate each filter from others with a comma.
You can use the `%` and `_` wildcard characters in the `replicate-wild-do-table` and `replicate-wild-ignore-table` parameters. The `%` wildcard matches any number of characters, and the `_` wildcard matches only one character.
The binary logging format of the source DB instance is important for replication because it determines the record of data changes. The setting of the `binlog_format` parameter determines whether the replication is row-based or statement-based. For more information, see [Configuring RDS for MySQL binary logging for Single-AZ databases](USER_LogAccess.MySQL.BinaryFormat.md).
**Note**
All data definition language (DDL) statements are replicated as statements, regardless of the `binlog_format` setting on the source DB instance.
## Replication filtering limitations for RDS for MySQL
The following limitations apply to replication filtering for RDS for MySQL:
+ Each replication filtering parameter has a 2,000-character limit.
+ Commas aren't supported in replication filters for parameter values. In a list of parameters, commas can only be used as value separators. For example, `ParameterValue='`a,b`'` isn’t supported, but `ParameterValue='a,b'` is.
+ The MySQL `--binlog-do-db` and `--binlog-ignore-db` options for binary log filtering aren't supported.
+ Replication filtering doesn't support XA transactions.
For more information, see [ Restrictions on XA Transactions](https://dev.mysql.com/doc/refman/8.0/en/xa-restrictions.html) in the MySQL documentation.
## Replication filtering examples for RDS for MySQL
To configure replication filtering for a read replica, modify the replication filtering parameters in the parameter group associated with the read replica.
**Note**
You can't modify a default parameter group. If the read replica is using a default parameter group, create a new parameter group and associate it with the read replica. For more information on DB parameter groups, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md).
You can set parameters in a parameter group using the AWS Management Console, AWS CLI, or RDS API. For information about setting parameters, see [Modifying parameters in a DB parameter group in Amazon RDS](USER_WorkingWithParamGroups.Modifying.md). When you set parameters in a parameter group, all of the DB instances associated with the parameter group use the parameter settings. If you set the replication filtering parameters in a parameter group, make sure that the parameter group is associated only with read replicas. Leave the replication filtering parameters empty for source DB instances.
The following examples set the parameters using the AWS CLI. These examples set `ApplyMethod` to `immediate` so that the parameter changes occur immediately after the CLI command completes. If you want a pending change to be applied after the read replica is rebooted, set `ApplyMethod` to `pending-reboot`.
The following examples set replication filters:
+ [Including databases in replication](#rep-filter-in-dbs-mysql)
+ [Including tables in replication](#rep-filter-in-tables-mysql)
+ [Including tables in replication with wildcard characters](#rep-filter-in-tables-wildcards-mysql)
+ [Excluding databases from replication](#rep-filter-ex-dbs-mysql)
+ [Excluding tables from replication](#rep-filter-ex-tables-mysql)
+ [Excluding tables from replication using wildcard characters](#rep-filter-ex-tables-wildcards-mysql)
**Example Including databases in replication**
The following example includes the `mydb1` and `mydb2` databases in replication.
For Linux, macOS, or Unix:
```
aws rds modify-db-parameter-group \
--db-parameter-group-name myparametergroup \
--parameters "ParameterName=replicate-do-db,ParameterValue='mydb1,mydb2',ApplyMethod=immediate"
```
For Windows:
```
aws rds modify-db-parameter-group ^
--db-parameter-group-name myparametergroup ^
--parameters "ParameterName=replicate-do-db,ParameterValue='mydb1,mydb2',ApplyMethod=immediate"
```
**Example Including tables in replication**
The following example includes the `table1` and `table2` tables in database `mydb1` in replication.
For Linux, macOS, or Unix:
```
aws rds modify-db-parameter-group \
--db-parameter-group-name myparametergroup \
--parameters "ParameterName=replicate-do-table,ParameterValue='mydb1.table1,mydb1.table2',ApplyMethod=immediate"
```
For Windows:
```
aws rds modify-db-parameter-group ^
--db-parameter-group-name myparametergroup ^
--parameters "ParameterName=replicate-do-table,ParameterValue='mydb1.table1,mydb1.table2',ApplyMethod=immediate"
```
**Example Including tables in replication using wildcard characters**
The following example includes tables with names that begin with `order` and `return` in database `mydb` in replication.
For Linux, macOS, or Unix:
```
aws rds modify-db-parameter-group \
--db-parameter-group-name myparametergroup \
--parameters "ParameterName=replicate-wild-do-table,ParameterValue='mydb.order%,mydb.return%',ApplyMethod=immediate"
```
For Windows:
```
aws rds modify-db-parameter-group ^
--db-parameter-group-name myparametergroup ^
--parameters "ParameterName=replicate-wild-do-table,ParameterValue='mydb.order%,mydb.return%',ApplyMethod=immediate"
```
**Example Excluding databases from replication**
The following example excludes the `mydb5` and `mydb6` databases from replication.
For Linux, macOS, or Unix:
```
aws rds modify-db-parameter-group \
--db-parameter-group-name myparametergroup \
--parameters "ParameterName=replicate-ignore-db,ParameterValue='mydb5,mydb6',ApplyMethod=immediate"
```
For Windows:
```
aws rds modify-db-parameter-group ^
--db-parameter-group-name myparametergroup ^
--parameters "ParameterName=replicate-ignore-db,ParameterValue='mydb5,mydb6',ApplyMethod=immediate"
```
**Example Excluding tables from replication**
The following example excludes tables `table1` in database `mydb5` and `table2` in database `mydb6` from replication.
For Linux, macOS, or Unix:
```
aws rds modify-db-parameter-group \
--db-parameter-group-name myparametergroup \
--parameters "ParameterName=replicate-ignore-table,ParameterValue='mydb5.table1,mydb6.table2',ApplyMethod=immediate"
```
For Windows:
```
aws rds modify-db-parameter-group ^
--db-parameter-group-name myparametergroup ^
--parameters "ParameterName=replicate-ignore-table,ParameterValue='mydb5.table1,mydb6.table2',ApplyMethod=immediate"
```
**Example Excluding tables from replication using wildcard characters**
The following example excludes tables with names that begin with `order` and `return` in database `mydb7` from replication.
For Linux, macOS, or Unix:
```
aws rds modify-db-parameter-group \
--db-parameter-group-name myparametergroup \
--parameters "ParameterName=replicate-wild-ignore-table,ParameterValue='mydb7.order%,mydb7.return%',ApplyMethod=immediate"
```
For Windows:
```
aws rds modify-db-parameter-group ^
--db-parameter-group-name myparametergroup ^
--parameters "ParameterName=replicate-wild-ignore-table,ParameterValue='mydb7.order%,mydb7.return%',ApplyMethod=immediate"
```
## Viewing the replication filters for a read replica
You can view the replication filters for a read replica in the following ways:
+ Check the settings of the replication filtering parameters in the parameter group associated with the read replica.
For instructions, see [Viewing parameter values for a DB parameter group in Amazon RDS](USER_WorkingWithParamGroups.Viewing.md).
+ In a MySQL client, connect to the read replica and run the `SHOW REPLICA STATUS` statement.
In the output, the following fields show the replication filters for the read replica:
+ `Replicate_Do_DB`
+ `Replicate_Ignore_DB`
+ `Replicate_Do_Table`
+ `Replicate_Ignore_Table`
+ `Replicate_Wild_Do_Table`
+ `Replicate_Wild_Ignore_Table`
For more information about these fields, see [Checking Replication Status](https://dev.mysql.com/doc/refman/8.0/en/replication-administration-status.html) in the MySQL documentation.
# Configuring delayed replication with MySQL
You can use delayed replication as a strategy for disaster recovery. With delayed replication, you specify the minimum amount of time, in seconds, to delay replication from the source to the read replica. In the event of a disaster, such as a table deleted unintentionally, you complete the following steps to recover from the disaster quickly:
+ Stop replication to the read replica before the change that caused the disaster is sent to it.
Use the [mysql.rds\$1stop\$1replication](mysql-stored-proc-replicating.md#mysql_rds_stop_replication) stored procedure to stop replication.
+ Start replication and specify that replication stops automatically at a log file location.
You specify a location just before the disaster using the [mysql.rds\$1start\$1replication\$1until](mysql-stored-proc-replicating.md#mysql_rds_start_replication_until) stored procedure.
+ Promote the read replica to be the new source DB instance by using the instructions in [Promoting a read replica to be a standalone DB instance](USER_ReadRepl.Promote.md).
**Note**
On RDS for MySQL 8.4, delayed replication is supported for MySQL 8.4.3 and higher. On RDS for MySQL 8.0, delayed replication is supported for MySQL 8.0.28 and higher. On RDS for MySQL 5.7, delayed replication is supported for MySQL 5.7.44 and higher.
Use stored procedures to configure delayed replication. You can't configure delayed replication with the AWS Management Console, the AWS CLI, or the Amazon RDS API.
You can use replication based on global transaction identifiers (GTIDs) in a delayed replication configuration for the following versions:
RDS for MySQL version 5.7.44 and higher 5.7 versions
RDS for MySQL version 8.0.28 and higher 8.0 versions
RDS for MySQL version 8.4.3 and higher 8.4 versions
If you use GTID-based replication, use the [mysql.rds\$1start\$1replication\$1until\$1gtid](mysql-stored-proc-gtid.md#mysql_rds_start_replication_until_gtid) stored procedure instead of the [mysql.rds\$1start\$1replication\$1until](mysql-stored-proc-replicating.md#mysql_rds_start_replication_until) stored procedure. For more information about GTID-based replication, see [Using GTID-based replication](mysql-replication-gtid.md).
**Topics**
+ [Configuring delayed replication during read replica creation](#USER_MySQL.Replication.ReadReplicas.DelayReplication.ReplicaCreation)
+ [Modifying delayed replication for an existing read replica](#USER_MySQL.Replication.ReadReplicas.DelayReplication.ExistingReplica)
+ [Setting a location to stop replication to a read replica](#USER_MySQL.Replication.ReadReplicas.DelayReplication.StartUntil)
+ [Promoting a read replica](#USER_MySQL.Replication.ReadReplicas.DelayReplication.Promote)
## Configuring delayed replication during read replica creation
To configure delayed replication for any future read replica created from a DB instance, run the [mysql.rds\$1set\$1configuration](mysql-stored-proc-configuring.md#mysql_rds_set_configuration) stored procedure with the `target delay` parameter.
**To configure delayed replication during read replica creation**
1. Using a MySQL client, connect to the MySQL DB instance to be the source for read replicas as the master user.
1. Run the [mysql.rds\$1set\$1configuration](mysql-stored-proc-configuring.md#mysql_rds_set_configuration) stored procedure with the `target delay` parameter.
For example, run the following stored procedure to specify that replication is delayed by at least one hour (3,600 seconds) for any read replica created from the current DB instance.
```
call mysql.rds_set_configuration('target delay', 3600);
```
**Note**
After running this stored procedure, any read replica you create using the AWS CLI or Amazon RDS API is configured with replication delayed by the specified number of seconds.
## Modifying delayed replication for an existing read replica
To modify delayed replication for an existing read replica, run the [mysql.rds\$1set\$1source\$1delay](mysql-stored-proc-replicating.md#mysql_rds_set_source_delay) stored procedure.
**To modify delayed replication for an existing read replica**
1. Using a MySQL client, connect to the read replica as the master user.
1. Use the [mysql.rds\$1stop\$1replication](mysql-stored-proc-replicating.md#mysql_rds_stop_replication) stored procedure to stop replication.
1. Run the [mysql.rds\$1set\$1source\$1delay](mysql-stored-proc-replicating.md#mysql_rds_set_source_delay) stored procedure.
For example, run the following stored procedure to specify that replication to the read replica is delayed by at least one hour (3600 seconds).
```
call mysql.rds_set_source_delay(3600);
```
1. Use the [mysql.rds\$1start\$1replication](mysql-stored-proc-replicating.md#mysql_rds_start_replication) stored procedure to start replication.
## Setting a location to stop replication to a read replica
After stopping replication to the read replica, you can start replication and then stop it at a specified binary log file location using the [mysql.rds\$1start\$1replication\$1until](mysql-stored-proc-replicating.md#mysql_rds_start_replication_until) stored procedure.
**To start replication to a read replica and stop replication at a specific location**
1. Using a MySQL client, connect to the source MySQL DB instance as the master user.
1. Run the [mysql.rds\$1start\$1replication\$1until](mysql-stored-proc-replicating.md#mysql_rds_start_replication_until) stored procedure.
The following example initiates replication and replicates changes until it reaches location `120` in the `mysql-bin-changelog.000777` binary log file. In a disaster recovery scenario, assume that location `120` is just before the disaster.
```
call mysql.rds_start_replication_until(
'mysql-bin-changelog.000777',
120);
```
Replication stops automatically when the stop point is reached. The following RDS event is generated: `Replication has been stopped since the replica reached the stop point specified by the rds_start_replication_until stored procedure`.
## Promoting a read replica
After replication is stopped, in a disaster recovery scenario, you can promote a read replica to be the new source DB instance. For information about promoting a read replica, see [Promoting a read replica to be a standalone DB instance](USER_ReadRepl.Promote.md).
# Updating read replicas with MySQL
Read replicas are designed to support read queries, but you might need occasional updates. For example, you might need to add an index to optimize the specific types of queries accessing the replica.
Although you can enable updates by setting the `read_only` parameter to `0` in the DB parameter group for the read replica, we recommend that you don't do so because it can cause problems if the read replica becomes incompatible with the source DB instance. For maintenance operations, we recommend that you use blue/green deployments. For more information, see [Using Blue/Green Deployments for database updates](blue-green-deployments.md).
If you disable read-only on a read replica, change the value of the `read_only` parameter back to `1` as soon as possible.
# Working with Multi-AZ read replica deployments with MySQL
You can create a read replica from either single-AZ or Multi-AZ DB instance deployments. You use Multi-AZ deployments to improve the durability and availability of critical data, but you can't use the Multi-AZ secondary to serve read-only queries. Instead, you can create read replicas from high-traffic Multi-AZ DB instances to offload read-only queries. If the source instance of a Multi-AZ deployment fails over to the secondary, any associated read replicas automatically switch to use the secondary (now primary) as their replication source. For more information, see [Configuring and managing a Multi-AZ deployment for Amazon RDS](Concepts.MultiAZ.md).
You can create a read replica as a Multi-AZ DB instance. Amazon RDS creates a standby of your replica in another Availability Zone for failover support for the replica. Creating your read replica as a Multi-AZ DB instance is independent of whether the source database is a Multi-AZ DB instance.
# Using cascading read replicas with RDS for MySQL
RDS for MySQL supports cascading read replicas. With *cascading read replicas*, you can scale reads without adding overhead to your source RDS for MySQL DB instance.
With cascading read replicas, your RDS for MySQL DB instance sends data to the first read replica in the chain. That read replica then sends data to the second replica in the chain, and so on. The end result is that all read replicas in the chain have the changes from the RDS for MySQL DB instance, but without the overhead solely on the source DB instance.
You can create a series of up to three read replicas in a chain from a source RDS for MySQL DB instance. For example, suppose that you have an RDS for MySQL DB instance, `mysql-main`. You can do the following:
+ Starting with `mysql-main`, create the first read replica in the chain, `read-replica-1`.
+ Next, from `read-replica-1`, create the next read replica in the chain, `read-replica-2`.
+ Finally, from `read-replica-2`, create the third read replica in the chain, `read-replica-3`.
You can't create another read replica beyond this third cascading read replica in the series for `mysql-main`. A complete series of instances from an RDS for MySQL source DB instance through to the end of a series of cascading read replicas can consist of at most four DB instances.
For cascading read replicas to work, each source RDS for MySQL DB instance must have automated backups turned on. To turn on automatic backups on a read replica, first create the read replica, and then modify the read replica to turn on automatic backups. For more information, see [Creating a read replica](USER_ReadRepl.Create.md).
As with any read replica, you can promote a read replica that's part of a cascade. Promoting a read replica from within a chain of read replicas removes that replica from the chain. For example, suppose that you want to move some of the workload from your `mysql-main` DB instance to a new instance for use by the accounting department only. Assuming the chain of three read replicas from the example, you decide to promote `read-replica-2`. The chain is affected as follows:
+ Promoting `read-replica-2` removes it from the replication chain.
+ It is now a full read/write DB instance.
+ It continues replicating to `read-replica-3`, just as it was doing before promotion.
+ Your `mysql-main` continues replicating to `read-replica-1`.
For more information about promoting read replicas, see [Promoting a read replica to be a standalone DB instance](USER_ReadRepl.Promote.md).
# Monitoring replication lag for MySQL read replicas
For MySQL read replicas, you can monitor replication lag in Amazon CloudWatch by viewing the Amazon RDS `ReplicaLag` metric. The `ReplicaLag` metric reports the value of the `Seconds_Behind_Master` field of the `SHOW REPLICA STATUS` command.
Common causes for replication lag for MySQL are the following:
+ A network outage.
+ Writing to tables that have different indexes on a read replica. If the `read_only` parameter is set to `0` on the read replica, replication can break if the read replica becomes incompatible with the source DB instance. After you've performed maintenance tasks on the read replica, we recommend that you set the `read_only` parameter back to `1`.
+ Using a nontransactional storage engine such as MyISAM. Replication is only supported for the InnoDB storage engine on MySQL.
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`.
# Starting and stopping replication with MySQL read replicas
You can stop and restart the replication process on an Amazon RDS DB instance by calling the system stored procedures [mysql.rds\$1stop\$1replication](mysql-stored-proc-replicating.md#mysql_rds_stop_replication) and [mysql.rds\$1start\$1replication](mysql-stored-proc-replicating.md#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 to an Amazon RDS for MySQL database with reduced downtime](mysql-importing-data-reduced-downtime.md) and [Exporting data from a MySQL DB instance by using replication](MySQL.Procedural.Exporting.NonRDSRepl.md).
If replication is stopped for more than 30 consecutive days, either manually or due to a replication error, Amazon RDS terminates replication between the source DB instance and all read replicas. It does so to prevent increased storage requirements on the source DB instance and long failover times. The read replica DB instance is still available. However, replication can't be resumed because the binary logs required by the read replica are deleted from the source DB instance after replication is terminated. You can create a new read replica for the source DB instance to reestablish replication.
# Troubleshooting a MySQL read replica problem
For MySQL DB instances, in some cases read replicas present replication errors or data inconsistencies (or both) between the read replica and its source DB instance. This problem occurs when some binary log (binlog) events or InnoDB redo logs aren't flushed during a failure of the read replica or the source DB instance. In these cases, manually delete and recreate the read replicas. You can reduce the chance of this happening by setting the following parameter values: `sync_binlog=1` and `innodb_flush_log_at_trx_commit=1`. These settings might reduce performance, so test their impact before implementing the changes in a production environment.
**Warning**
In the parameter group associated with the source DB instance, we recommend keeping these parameter values: `sync_binlog=1` and `innodb_flush_log_at_trx_commit=1`. These parameters are dynamic. If you don't want to use these settings, we recommend temporarily setting those values before executing any operation on the source DB instance that might cause it to restart. These operations include, but are not limited to, rebooting, rebooting with failover, upgrading the database version, and changing the DB instance class or its storage. The same recommendation applies to creating new read replicas for the source DB instance.
Failure to follow this guidance increases the risk of read replicas presenting replication errors or data inconsistencies (or both) between the read replica and its source DB instance.
The replication technologies for MySQL are asynchronous. Because they are 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 write operations to the source DB instance can occur in parallel. In contrast, write operations to the read replica are serialized using a single I/O thread, which can lead to a lag between the source instance and read replica. For more information about read-only replicas in the MySQL documentation, see [Replication implementation details](https://dev.mysql.com/doc/refman/8.0/en/replication-implementation-details.html).
You can do several things to reduce the lag between updates to a source DB instance and the subsequent updates to the read replica, such as the following:
+ 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. An example might be if DML queries run on your read replica 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](USER_Events.Messages.md#RDS-EVENT-0045), [RDS-EVENT-0046](USER_Events.Messages.md#RDS-EVENT-0046), and [RDS-EVENT-0047](USER_Events.Messages.md#RDS-EVENT-0047). For more information about events and subscribing to events, see [Working with Amazon RDS event notification](USER_Events.md). If a MySQL error message is returned, review the error number in the [MySQL error message documentation](https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html).
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. You use `max_allowed_packet` to specify the maximum size of DML code that can be run on the database. In some cases, the `max_allowed_packet` value in the DB parameter group associated with a read replica is smaller than the `max_allowed_packet` value in the DB parameter group associated with the source DB instance. In these cases, the replication process can throw the error `Packet bigger than 'max_allowed_packet' bytes` and stop replication. To fix the error, have the source DB instance and read replica use DB parameter groups with the same `max_allowed_packet` parameter values.
Other common situations that can cause replication errors include the following:
+ Writing to tables on a read replica. In some cases, you might create indexes on a read replica that are different from the indexes on the source DB instance. If you do, set the `read_only` parameter to `0` to create the indexes. If you write to tables on the read replica, it might break replication if the read replica becomes incompatible with the source DB instance. After you perform maintenance tasks on the read replica, we recommend that you set the `read_only` parameter back to `1`.
+ 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 on MySQL.
+ Using unsafe nondeterministic queries such as `SYSDATE()`. For more information, see [ Determination of safe and unsafe statements in binary logging](https://dev.mysql.com/doc/refman/8.0/en/replication-rbr-safe-unsafe.html).
If you decide that you can safely skip an error, you can follow the steps described in the section [Skipping the current replication error for RDS for MySQL](Appendix.MySQL.CommonDBATasks.SkipError.md). Otherwise, you can first delete the read replica. Then you create an 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*.
# Using GTID-based replication
The following content explains how to use global transaction identifiers (GTIDs) with binary log (binlog) replication among Amazon RDS for MySQL DB instances.
If you use binlog replication and aren't familiar with GTID-based replication with MySQL, see [Replication with global transaction identifiers](https://dev.mysql.com/doc/refman/5.7/en/replication-gtids.html) in the MySQL documentation.
GTID-based replication is supported for the following versions:
+ All RDS for MySQL 8.4 versions
+ All RDS for MySQL 8.0 versions
+ All RDS for MySQL 5.7 versions
All MySQL DB instances in a replication configuration must meet this version requirement.
**Topics**
+ [Overview of global transaction identifiers (GTIDs)](#mysql-replication-gtid.overview)
+ [Parameters for GTID-based replication](#mysql-replication-gtid.parameters)
+ [Enabling GTID-based replication for new read replicas for RDS for MySQL](mysql-replication-gtid.configuring-new-read-replicas.md)
+ [Enabling GTID-based replication for existing read replicas for RDS for MySQL](mysql-replication-gtid.configuring-existing-read-replicas.md)
+ [Disabling GTID-based replication for a MySQL DB instance with read replicas](mysql-replication-gtid.disabling.md)
## Overview of global transaction identifiers (GTIDs)
*Global transaction identifiers (GTIDs)* are unique identifiers generated for committed MySQL transactions. You can use GTIDs to make binlog replication simpler and easier to troubleshoot.
MySQL uses two different types of transactions for binlog replication:
+ *GTID transactions* – Transactions that are identified by a GTID.
+ *Anonymous transactions* – Transactions that don't have a GTID assigned.
In a replication configuration, GTIDs are unique across all DB instances. GTIDs simplify replication configuration because when you use them, you don't have to refer to log file positions. GTIDs also make it easier to track replicated transactions and determine whether the source instance and replicas are consistent.
You can use GTID-based replication to replicate data with RDS for MySQL read replicas. You can configure GTID-based replication when you are creating new read replicas, or you can convert existing read replicas to use GTID-based replication.
You can also use GTID-based replication in a delayed replication configuration with RDS for MySQL. For more information, see [Configuring delayed replication with MySQL](USER_MySQL.Replication.ReadReplicas.DelayReplication.md).
## Parameters for GTID-based replication
Use the following parameters to configure GTID-based replication.
| Parameter | Valid values | Description |
| --- | --- | --- |
| `gtid_mode` | `OFF`, `OFF_PERMISSIVE`, `ON_PERMISSIVE`, `ON` | `OFF` specifies that new transactions are anonymous transactions (that is, don't have GTIDs), and a transaction must be anonymous to be replicated. `OFF_PERMISSIVE` specifies that new transactions are anonymous transactions, but all transactions can be replicated. `ON_PERMISSIVE` specifies that new transactions are GTID transactions, but all transactions can be replicated. `ON` specifies that new transactions are GTID transactions, and a transaction must be a GTID transaction to be replicated. |
| `enforce_gtid_consistency` | `OFF`, `ON`, `WARN` | `OFF` allows transactions to violate GTID consistency. `ON` prevents transactions from violating GTID consistency. `WARN` allows transactions to violate GTID consistency but generates a warning when a violation occurs. |
**Note**
In the AWS Management Console, the `gtid_mode` parameter appears as `gtid-mode`.
For GTID-based replication, use these settings for the parameter group for your DB instance or read replica:
+ `ON` and `ON_PERMISSIVE` apply only to outgoing replication from an RDS DB instance. Both of these values cause your RDS DB instance to use GTIDs for transactions that are replicated. `ON` requires that the target database also use GTID-based replication. `ON_PERMISSIVE` makes GTID-based replication optional on the target database.
+ `OFF_PERMISSIVE`, if set, means that your RDS DB instances can accept incoming replication from a source database. They can do this regardless of whether the source database uses GTID-based replication.
+ `OFF`, if set, means that your RDS DB instance only accepts incoming replication from source databases that don't use GTID-based replication.
For more information about parameter groups, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md).
# Enabling GTID-based replication for new read replicas for RDS for MySQL
When GTID-based replication is enabled for an RDS for MySQL DB instance, GTID-based replication is configured automatically for read replicas of the DB instance.
**To enable GTID-based replication for new read replicas**
1. Make sure that the parameter group associated with the DB instance has the following parameter settings:
+ `gtid_mode` – `ON` or `ON_PERMISSIVE`
+ `enforce_gtid_consistency` – `ON`
For more information about setting configuration parameters using parameter groups, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md).
1. If you changed the parameter group of the DB instance, reboot the DB instance. For more information on how to do so, see [Rebooting a DB instance](USER_RebootInstance.md).
1. Create one or more read replicas of the DB instance. For more information on how to do so, see [Creating a read replica](USER_ReadRepl.Create.md).
Amazon RDS attempts to establish GTID-based replication between the MySQL DB instance and the read replicas using the `MASTER_AUTO_POSITION`. If the attempt fails, Amazon RDS uses log file positions for replication with the read replicas. For more information about the `MASTER_AUTO_POSITION`, see [ GTID auto-positioning](https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-auto-positioning.html) in the MySQL documentation.
# Enabling GTID-based replication for existing read replicas for RDS for MySQL
For an existing MySQL DB instance with read replicas that doesn't use GTID-based replication, you can configure GTID-based replication between the DB instance and the read replicas.
**To enable GTID-based replication for existing read replicas**
1. If the DB instance or any read replica is using an 8.0 version of RDS for MySQL version lower than 8.0.26, upgrade the DB instance or read replica to 8.0.26 or a higher MySQL 8.0 version. All RDS for MySQL 8.4 versions and 5.7 versions support GTID-based replication.
For more information, see [Upgrades of the RDS for MySQL DB engine](USER_UpgradeDBInstance.MySQL.md).
1. (Optional) Reset the GTID parameters and test the behavior of the DB instance and read replicas:
1. Make sure that the parameter group associated with the DB instance and each read replica has the `enforce_gtid_consistency` parameter set to `WARN`.
For more information about setting configuration parameters using parameter groups, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md).
1. If you changed the parameter group of the DB instance, reboot the DB instance. If you changed the parameter group for a read replica, reboot the read replica.
For more information, see [Rebooting a DB instance](USER_RebootInstance.md).
1. Run your DB instance and read replicas with your normal workload and monitor the log files.
If you see warnings about GTID-incompatible transactions, adjust your application so that it only uses GTID-compatible features. Make sure that the DB instance is not generating any warnings about GTID-incompatible transactions before proceeding to the next step.
1. Reset the GTID parameters for GTID-based replication that allows anonymous transactions until the read replicas have processed all of them.
1. Make sure that the parameter group associated with the DB instance and each read replica has the following parameter settings:
+ `gtid_mode` – `ON_PERMISSIVE`
+ `enforce_gtid_consistency` – `ON`
1. If you changed the parameter group of the DB instance, reboot the DB instance. If you changed the parameter group for a read replica, reboot the read replica.
1. Wait for all of your anonymous transactions to be replicated. To check that these are replicated, do the following:
1. Run the following statement on your source DB instance.
**MySQL 8.4**
```
SHOW BINARY LOG STATUS;
```
**MySQL 5.7 and 8.0**
```
SHOW MASTER STATUS;
```
Note the values in the `File` and `Position` columns.
1. On each read replica, use the file and position information from its source instance in the previous step to run the following query.
```
SELECT MASTER_POS_WAIT('file', position);
```
For example, if the file name is `mysql-bin-changelog.000031` and the position is `107`, run the following statement.
```
SELECT MASTER_POS_WAIT('mysql-bin-changelog.000031', 107);
```
If the read replica is past the specified position, the query returns immediately. Otherwise, the function waits. Proceed to the next step when the query returns for all read replicas.
1. Reset the GTID parameters for GTID-based replication only.
1. Make sure that the parameter group associated with the DB instance and each read replica has the following parameter settings:
+ `gtid_mode` – `ON`
+ `enforce_gtid_consistency` – `ON`
1. Reboot the DB instance and each read replica.
1. On each read replica, run the following procedure.
**MySQL 8.4 and higher major versions**
```
CALL mysql.rds_set_source_auto_position(1);
```
**MySQL 8.0 and lower major versions**
```
CALL mysql.rds_set_master_auto_position(1);
```
# Disabling GTID-based replication for a MySQL DB instance with read replicas
You can disable GTID-based replication for a MySQL DB instance with read replicas.
**To disable GTID-based replication for a MySQL DB instance with read replicas**
1. On each read replica, run the following procedure:
**MySQL 8.4 and higher major versions**
```
CALL mysql.rds_set_source_auto_position(0);
```
**MySQL 8.0 and lower major versions**
```
CALL mysql.rds_set_master_auto_position(0);
```
1. Reset the `gtid_mode` to `ON_PERMISSIVE`.
1. Make sure that the parameter group associated with the MySQL DB instance and each read replica has `gtid_mode` set to `ON_PERMISSIVE`.
For more information about setting configuration parameters using parameter groups, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md).
1. Reboot the MySQL DB instance and each read replica. For more information about rebooting, see [Rebooting a DB instance](USER_RebootInstance.md).
1. Reset the `gtid_mode` to `OFF_PERMISSIVE`.
1. Make sure that the parameter group associated with the MySQL DB instance and each read replica has `gtid_mode` set to `OFF_PERMISSIVE`.
1. Reboot the MySQL DB instance and each read replica.
1. Wait for all of the GTID transactions to be applied on all of the read replicas. To check that these are applied, do the following steps:
1. On the MySQL DB instance, run the following command:
**MySQL 8.4**
```
SHOW BINARY LOG STATUS
```
**MySQL 5.7 and 8.0**
```
SHOW MASTER STATUS
```
Your output should be similar to the following output.
```
File Position
------------------------------------
mysql-bin-changelog.000031 107
------------------------------------
```
Note the file and position in your output.
1. On each read replica, use the file and position information from its source instance in the previous step to run the following query:
**MySQL 8.4 and MySQL 8.0.26 and higher MySQL 8.0 versions**
```
SELECT SOURCE_POS_WAIT('file', position);
```
**MySQL 5.7**
```
SELECT MASTER_POS_WAIT('file', position);
```
For example, if the file name is `mysql-bin-changelog.000031` and the position is `107`, run the following statement:
**MySQL 8.4 and MySQL 8.0.26 and higher MySQL 8.0 versions**
```
SELECT SOURCE_POS_WAIT('mysql-bin-changelog.000031', 107);
```
**MySQL 5.7**
```
SELECT MASTER_POS_WAIT('mysql-bin-changelog.000031', 107);
```
1. Reset the GTID parameters to disable GTID-based replication.
1. Make sure that the parameter group associated with the MySQL DB instance and each read replica has the following parameter settings:
+ `gtid_mode` – `OFF`
+ `enforce_gtid_consistency` – `OFF`
1. Reboot the MySQL DB instance and each read replica.
# Configuring binary log file position replication with an external source instance
You can set up replication between an RDS for MySQL or MariaDB DB instance and a MySQL or MariaDB instance that is external to Amazon RDS using binary log file replication.
**Topics**
+ [Before you begin](#MySQL.Procedural.Importing.External.Repl.BeforeYouBegin)
+ [Configuring binary log file position replication with an external source instance](#MySQL.Procedural.Importing.External.Repl.Procedure)
## Before you begin
You can configure replication using the binary log file position of replicated transactions.
The permissions required to start replication on an Amazon RDS DB instance are restricted and not available to your Amazon RDS master user. Because of this, make sure that you use the Amazon RDS [mysql.rds\$1set\$1external\$1master (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower)](mysql-stored-proc-replicating.md#mysql_rds_set_external_master) or [mysql.rds\$1set\$1external\$1source (RDS for MySQL major versions 8.4 and higher)](mysql-stored-proc-replicating.md#mysql_rds_set_external_source), and [mysql.rds\$1start\$1replication](mysql-stored-proc-replicating.md#mysql_rds_start_replication) commands to set up replication between your live database and your Amazon RDS database.
To set the binary logging format for a MySQL or MariaDB database, update the `binlog_format` parameter. If your DB instance uses the default DB instance parameter group, create a new DB parameter group to modify the `binlog_format` parameter. In MariaDB and MySQL 8.0 and lower versions, `binlog_format` defaults to `MIXED`. However, you can also set `binlog_format` to `ROW` or `STATEMENT` if you need a specific binary log (binlog) format. Reboot your DB instance for the change to take effect. In MySQL 8.4 and higher versions, `binlog_format` defaults to `ROW`.
For information about setting the `binlog_format` parameter, see [Configuring RDS for MySQL binary logging for Single-AZ databases](USER_LogAccess.MySQL.BinaryFormat.md). For information about the implications of different MySQL replication types, see [Advantages and disadvantages of statement-based and row-based replication](https://dev.mysql.com/doc/refman/8.0/en/replication-sbr-rbr.html) in the MySQL documentation.
## Configuring binary log file position replication with an external source instance
Follow these guidelines when you set up an external source instance and a replica on Amazon RDS:
+ Monitor failover events for the Amazon RDS DB instance that is your replica. If a failover occurs, then the DB instance that is your replica might be recreated on a new host with a different network address. For information on how to monitor failover events, see [Working with Amazon RDS event notification](USER_Events.md).
+ Maintain the binlogs on your source instance until you have verified that they have been applied to the replica. This maintenance makes sure that you can restore your source instance in the event of a failure.
+ Turn on automated backups on your Amazon RDS DB instance. Turning on automated backups makes sure that you can restore your replica to a particular point in time if you need to re-synchronize your source instance and replica. For information on backups and point-in-time restore, see [Backing up, restoring, and exporting data](CHAP_CommonTasks.BackupRestore.md).
**To configure binary log file replication with an external source instance**
1. Make the source MySQL or MariaDB instance read-only.
```
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;
```
1. Run the `SHOW MASTER STATUS` command on the source MySQL or MariaDB instance to determine the binlog location.
You receive output similar to the following example.
```
File Position
------------------------------------
mysql-bin-changelog.000031 107
------------------------------------
```
1. Copy the database from the external instance to the Amazon RDS DB instance using `mysqldump`. For very large databases, you might want to use the procedure in [Importing data to an Amazon RDS for MySQL database with reduced downtime](mysql-importing-data-reduced-downtime.md).
For Linux, macOS, or Unix:
```
mysqldump --databases database_name \
--single-transaction \
--compress \
--order-by-primary \
-u local_user \
-plocal_password | mysql \
--host=hostname \
--port=3306 \
-u RDS_user_name \
-pRDS_password
```
For Windows:
```
mysqldump --databases database_name ^
--single-transaction ^
--compress ^
--order-by-primary ^
-u local_user ^
-plocal_password | mysql ^
--host=hostname ^
--port=3306 ^
-u RDS_user_name ^
-pRDS_password
```
**Note**
Make sure that there isn't a space between the `-p` option and the entered password.
To specify the host name, user name, port, and password to connect to your Amazon RDS DB instance, use the `--host`, `--user (-u)`, `--port` and `-p` options in the `mysql` command. The host name is the Domain Name Service (DNS) name from the Amazon RDS DB instance endpoint, for example `myinstance.123456789012.us-east-1.rds.amazonaws.com`. You can find the endpoint value in the instance details in the AWS Management Console.
1. Make the source MySQL or MariaDB instance writeable again.
```
mysql> SET GLOBAL read_only = OFF;
mysql> UNLOCK TABLES;
```
For more information on making backups for use with replication, see [the MySQL documentation](https://dev.mysql.com/doc/refman/8.0/en/replication-solutions-backups-read-only.html).
1. In the AWS Management Console, add the IP address of the server that hosts the external database to the virtual private cloud (VPC) security group for the Amazon RDS DB instance. For more information on modifying a VPC security group, see [Security groups for your VPC](https://docs.aws.amazon.com/vpc/latest/userguide/VPC_SecurityGroups.html) in the *Amazon Virtual Private Cloud User Guide*.
The IP address can change when the following conditions are met:
+ You are using a public IP address for communication between the external source instance and the DB instance.
+ The external source instance was stopped and restarted.
If these conditions are met, verify the IP address before adding it.
You might also need to configure your local network to permit connections from the IP address of your Amazon RDS DB instance. You do this so that your local network can communicate with your external MySQL or MariaDB instance. To find the IP address of the Amazon RDS DB instance, use the `host` command.
```
host db_instance_endpoint
```
The host name is the DNS name from the Amazon RDS DB instance endpoint.
1. Using the client of your choice, connect to the external instance and create a user to use for replication. Use this account solely for replication and restrict it to your domain to improve security. The following is an example.
```
CREATE USER 'repl_user'@'mydomain.com' IDENTIFIED BY 'password';
```
**Note**
Specify a password other than the prompt shown here as a security best practice.
1. For the external instance, grant `REPLICATION CLIENT` and `REPLICATION SLAVE` privileges to your replication user. For example, to grant the `REPLICATION CLIENT` and `REPLICATION SLAVE` privileges on all databases for the '`repl_user`' user for your domain, issue the following command.
```
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'mydomain.com';
```
1. Make the Amazon RDS DB instance the replica. To do so, first connect to the Amazon RDS DB instance as the master user. Then identify the external MySQL or MariaDB database as the source instance by using the [mysql.rds\$1set\$1external\$1source (RDS for MySQL major versions 8.4 and higher)](mysql-stored-proc-replicating.md#mysql_rds_set_external_source) or [mysql.rds\$1set\$1external\$1master (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower)](mysql-stored-proc-replicating.md#mysql_rds_set_external_master) command. Use the master log file name and master log position that you determined in step 2. The following commands are examples.
**MySQL 8.4**
```
CALL mysql.rds_set_external_source ('mysourceserver.mydomain.com', 3306, 'repl_user', 'password', 'mysql-bin-changelog.000031', 107, 1);
```
**MariaDB and MySQL 8.0 and 5.7**
```
CALL mysql.rds_set_external_master ('mymasterserver.mydomain.com', 3306, 'repl_user', 'password', 'mysql-bin-changelog.000031', 107, 1);
```
**Note**
On RDS for MySQL, you can choose to use delayed replication by running the [mysql.rds\$1set\$1external\$1source\$1with\$1delay (RDS for MySQL major versions 8.4 and higher)](mysql-stored-proc-replicating.md#mysql_rds_set_external_source_with_delay) or [mysql.rds\$1set\$1external\$1master\$1with\$1delay (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower)](mysql-stored-proc-replicating.md#mysql_rds_set_external_master_with_delay) stored procedure instead. On RDS for MySQL, one reason to use delayed replication is to turn on disaster recovery with the [mysql.rds\$1start\$1replication\$1until](mysql-stored-proc-replicating.md#mysql_rds_start_replication_until) stored procedure. Currently, RDS for MariaDB supports delayed replication but doesn't support the `mysql.rds_start_replication_until` procedure.
1. On the Amazon RDS DB instance, issue the [mysql.rds\$1start\$1replication](mysql-stored-proc-replicating.md#mysql_rds_start_replication) command to start replication.
```
CALL mysql.rds_start_replication;
```
# Configuring multi-source-replication for Amazon RDS for MySQL
With multi-source replication, you can set up an Amazon RDS for MySQL DB instance as a replica that receives binary log events from more than one RDS for MySQL source DB instance. Multi-source replication is supported for RDS for MySQL DB instances running the following engine versions:
+ All MySQL 8.4 versions
+ 8.0.35 and higher minor versions
+ 5.7.44 and higher minor versions
For information about MySQL multi-source replication, see [MySQL Multi-Source Replication](https://dev.mysql.com/doc/refman/8.0/en/replication-multi-source.html) in the MySQL documentation. The MySQL documentation contains detailed information about this feature, while this topic describes how to configure and manage the multi-source replication channels on your RDS for MySQL DB instances.
## Use cases for multi-source replication
The following cases are good candidates for using multi-source replication on RDS for MySQL:
+ Applications that need to merge or combine multiple shards on separate DB instances into a single shard.
+ Applications that need to generate reports from data consolidated from multiple sources.
+ Requirements to create consolidated long-term backups of data that's distributed among multiple RDS for MySQL DB instances.
## Prerequisites for multi-source replication
Before you configure multi-source replication, complete the following prerequisites.
+ Make sure that each source RDS for MySQL DB instance has automatic backups enabled. Enabling automatic backups enables binary logging. To learn how to enable automatic backups, see [Enabling automated backups](USER_WorkingWithAutomatedBackups.Enabling.md).
+ To avoid replication errors, we recommended that you block write operations to the source DB instances. You can do so by setting the `read-only` parameter to `ON` in a custom parameter group attached to the RDS for MySQL source DB instance. You can use the AWS Management Console or the AWS CLI to create a new custom parameter group or to modify an existing one. For more information, see [Creating a DB parameter group in Amazon RDS](USER_WorkingWithParamGroups.Creating.md) and [Modifying parameters in a DB parameter group in Amazon RDS](USER_WorkingWithParamGroups.Modifying.md).
+ For each source DB instance, add the IP address of the instance to the Amazon virtual private cloud (VPC) security group for the multi-source DB instance. To identify the IP address of a source DB instance, you can run the command `dig RDS Endpoint`. Run the command from an Amazon EC2 instance in the same VPC as the destination multi-source DB instance.
+ For each source DB instance, use a client to connect to the DB instance and create a database user with the required privileges for replication, as in the following example.
```
CREATE USER 'repl_user' IDENTIFIED BY 'password';
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user';
```
**Note**
Starting with MySQL 8.4, the `REPLICATION SLAVE` privilege has been deprecated and replaced with `REPLICATION REPLICA`. For MySQL 8.4 and later versions, use the following syntax instead:
```
CREATE USER 'repl_user' IDENTIFIED BY 'password';
GRANT REPLICATION CLIENT, REPLICATION REPLICA ON *.* TO 'repl_user';
```
## Configuring multi-source replication channels on RDS for MySQL DB instances
Configuring multi-source replication channels is similar to configuring single source replication. For multi-source replication, you first turn on binary logging on the source instance. Then, you import data from the sources to the multi-source replica. Then, you start replication from each source by using the binary log coordinates or by using GTID auto-positioning.
To configure an RDS for MySQL DB instance as a multi-source replica of two or more RDS for MySQL DB instances, perform the following steps.
**Topics**
+ [Step 1: Import data from the source DB instances to the multi-source replica](#mysql-multi-source-replication-import)
+ [Step 2: Start replication from the source DB instances to the multi-source replica](#mysql-multi-source-replication-setting-up-start-replication-other)
### Step 1: Import data from the source DB instances to the multi-source replica
Perform the following steps on each source DB instance.
Before you import the data from a source to the multi-source replica, determine the current binary log file and position by running the `SHOW MASTER STATUS` command. Take note of these details for use in the next step. In this example output, the file is `mysql-bin-changelog.000031` and the position is `107`.
**Note**
Starting with MySQL 8.4, the `SHOW MASTER STATUS` command has been deprecated and replaced with `SHOW BINARY LOG STATUS`. For MySQL 8.4 and later versions, use `SHOW BINARY LOG STATUS` instead.
```
File Position
-----------------------------------
mysql-bin-changelog.000031 107
-----------------------------------
```
Now copy the database from the source DB instance to the multi-source replica by using `mysqldump`, as in the following example.
```
mysqldump --databases database_name \
--single-transaction \
--compress \
--order-by-primary \
-u RDS_user_name \
-p RDS_password \
--host=RDS Endpoint | mysql \
--host=RDS Endpoint \
--port=3306 \
-u RDS_user_name \
-p RDS_password
```
After copying the database, you can set the read-only parameter to `OFF` on the source DB instance.
### Step 2: Start replication from the source DB instances to the multi-source replica
For each source DB instance, use the administrative user credentials to connect to the instance, and run the following two stored procedures. These stored procedures configure replication on a channel and start replication. This example uses the binlog file name and position from the example output in the previous step.
```
CALL mysql.rds_set_external_source_for_channel('mysourcehost.example.com', 3306, 'repl_user', 'password', 'mysql-bin-changelog.000031', 107, 1, 'channel_1');
CALL mysql.rds_start_replication_for_channel('channel_1');
```
For more information about using these stored procedures and others to set up and manage your replication channels, see [Managing multi-source replication](mysql-stored-proc-multi-source-replication.md).
## Using filters with multi-source replication
You can use replication filters to specify which databases and tables are replicated with in multi-source replica. Replication filters can include databases and tables in replication or exclude them from replication. For more information on replication filters, see [Configuring replication filters with MySQL](USER_MySQL.Replication.ReadReplicas.ReplicationFilters.md).
With multi-source replication, you can configure replication filters globally or at the channel level. Channel-level filtering is available only with supported DB instances running version 8.0 or version 8.4. The following examples show how to configure filters globally or at the channel level.
Note the following requirements and behavior with filtering in multi-source replication:
+ Back quotes (``) around the channel names are required.
+ If you change replication filters in the parameter group, the multi-source replica's `sql_thread` for all channels with updates are restarted to apply the changes dynamically. If an update involves a global filter, then all replication channels in the running state are restarted.
+ All global filters are applied before any channel-specific filters.
+ If a filter is applied globally and at the channel level, then only the channel-level filter is applied. For example, if the filters are `replicate_ignore_db="db1,`channel_22`:db2"`, then `replicate_ignore_db` set to `db1` is applied to all channels except for `channel_22`, and only `channel_22` ignores changes from `db2`.
Example 1: Setting a global filter
In the following example, the `temp_data` database is excluded from replication in every channel.
For Linux, macOS, or Unix:
```
aws rds modify-db-parameter-group \
--db-parameter-group-name myparametergroup \
--parameters "ParameterName=replicate-ignore-db,ParameterValue='temp_data',ApplyMethod=immediate"
```
Example 2: Setting a channel-level filter
In the following example, changes from the `sample22` database are only included in channel `channel_22`. Similarly, changes from the `sample99` database are only included in channel `channel_99`.
For Linux, macOS, or Unix:
```
aws rds modify-db-parameter-group \
--db-parameter-group-name myparametergroup \
--parameters "ParameterName=replicate-do-db,ParameterValue='\`channel_22\`:sample22,\`channel_99\`:sample99',ApplyMethod=immediate"
```
## Monitoring multi-source replication channels
You can monitor individual channels in a multi-source replica by using the following methods:
+ To monitor the status of all channels or a specific channel, connect to the multi-source replica and run the `SHOW REPLICA STATUS` or `SHOW REPLICA STATUS FOR CHANNEL 'channel_name'` command. For more information, see [Checking Replication Status](https://dev.mysql.com/doc/refman/8.0/en/replication-administration-status.html) in the MySQL documentation.
+ To receive notification when a replication channel is started, stopped, or removed, use RDS event notification. For more information, see [Working with Amazon RDS event notification](USER_Events.md).
+ To monitor the lag for a specific channel, check the `ReplicationChannelLag` metric for it. Data points for this metric have a period of 60 seconds (1 minute) are available for 15 days. To locate the replication channel lag for a channel, use the instance identifier and the replication channel name. To receive notification when this lag exceeds a particular threshold, you can set up a CloudWatch alarm. For more information, see [Monitoring Amazon RDS metrics with Amazon CloudWatch](monitoring-cloudwatch.md).
## Considerations and best practices for multi-source replication
Before you use multi-source replication on RDS for MySQL, review the following considerations and best practices:
+ Make sure that a DB instance configured as a multi-source replica has sufficient resources such as throughput, memory, CPU, and IOPS to handle the workload from multiple source instances.
+ Regularly monitor resource utilization on your multi-source replica and adjust the storage or instance configuration to handle the workload without straining resources.
+ You can configure multi-threaded replication on a multi-source replica by setting the system variable `replica_parallel_workers` to a value greater than `0`. In this case, the number of threads allocated to each channel is the value of this variable, plus one coordinator thread to manage the applier threads.
+ Configure replication filters appropriately to avoid conflicts. To replicate an entire database to another database on a replica, you can use the `--replicate-rewrite-db` option. For example, you can replicate all tables in database A to database B on a replica instance. This approach can be helpful when all source instances are using the same schema naming convention. For information about the `--replicate-rewrite-db` option, see [Replica Server Options and Variables](https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html) in the MySQL documentation.
+ To avoid replication errors, avoid writing to the replica. We recommended that you enable the `read_only` parameter on multi-source replicas to block write operations. Doing so helps to eliminate replication issues caused by conflicting write operations.
+ To increase the performance of read operations such as sorts and high-load joins that are executed on the multi-source replica, consider using RDS Optimized Reads. This feature can help with queries that depend on large temporary tables or sort files. For more information, see [Improving query performance for RDS for MySQL with Amazon RDS Optimized Reads](rds-optimized-reads.md).
+ To minimize replication lag and improve the performance of a multi-source replica, consider enabling optimized writes. For more information, see [Improving write performance with RDS Optimized Writes for MySQL](rds-optimized-writes.md).
+ Perform management operations (such as changing configuration) on one channel at a time, and avoid performing changes to multiple channels from multiple connections. These practices can lead to conflicts in replication operations. For example, simultaneously executing `rds_skip_repl_error_for_channel` and `rds_start_replication_for_channel` procedures from multiple connections can cause skipping of events on a different channel than intended.
+ You can enable backups on a multi-source replication instance and export data from that instance to an Amazon S3 bucket to store it for long-term purposes. However, it's important to also configure backups with appropriate retention on the individual source instances. For information about exporting snapshot data to Amazon S3, see [Exporting DB snapshot data to Amazon S3 for Amazon RDS](USER_ExportSnapshot.md).
+ To distribute the read workload on a multi-source replica, you can create read replicas from a multi-source replica. You can locate these read replicas in different AWS Regions based on your application's requirements. For more information about read replicas, see [Working with MySQL read replicas](USER_MySQL.Replication.ReadReplicas.md).
## Limitations for multi-source replication on RDS for MySQL
The following limitations apply to multi-source replication on RDS for MySQL:
+ Currently, RDS for MySQL supports configuring a maximum of 15 channels for a multi-source replica.
+ A read replica instance can't be configured as a multi-source replica.
+ To configure multi-source replication on RDS for MySQL running engine version 5.7, Performance Schema must be enabled on the replica instance. Enabling Performance Schema is optional on RDS for MySQL running engine version 8.0 or 8.4.
+ For RDS for MySQL running engine version 5.7, replication filters apply to all replication channels. For RDS for MySQL running engine version 8.0 or 8.4, you can configure filters that apply to all replication channels or to individual channels.
+ Restoring an RDS snapshot or performing a Point-in-time-Restore (PITR) doesn't restore multi-source replica channel configurations.
+ When you create a read replica of a multi-source replica, it only replicates data from the multi-source instance. It doesn't restore any channel configuration.
+ MySQL doesn't support setting up a different number of parallel workers for each channel. Every channel gets the same number of parallel workers based on the `replica_parallel_workers` value.
The following additional limitations apply if your multi-source replication target is a Multi-AZ DB cluster:
+ A channel must be configured for a source RDS for MySQL instance before any writes to that instance occur.
+ Each source RDS for MySQL instance must have GTID-based replication enabled.
+ A failover event on the DB cluster removes the multi-source replication configuration. Restoring that configuration requires repeating the configuration steps.