engine=innodb, algorithm=copy;
```
In the following sections, set up replication, stop replication, scale reads for your database, optimize binlog replication, and set up enhanced binlog.
**Topics**
+ [
# Setting up binary log replication for Aurora MySQL
](AuroraMySQL.Replication.MySQL.SettingUp.md)
+ [
# Stopping binary log replication for Aurora MySQL
](AuroraMySQL.Replication.MySQL.Stopping.md)
+ [
# Scaling reads for your MySQL database with Amazon Aurora
](AuroraMySQL.Replication.ReadScaling.md)
+ [
# Optimizing binary log replication for Aurora MySQL
](binlog-optimization.md)
+ [
# Setting up enhanced binlog for Aurora MySQL
](AuroraMySQL.Enhanced.binlog.md)
# Setting up binary log replication for Aurora MySQL
Setting up binlog replication
Setting up MySQL replication with Aurora MySQL involves the following steps, which are discussed in detail:
**Contents**
+ [
## 1. Turn on binary logging on the replication source
](#AuroraMySQL.Replication.MySQL.EnableBinlog)
+ [
## 2. Retain binary logs on the replication source until no longer needed
](#AuroraMySQL.Replication.MySQL.RetainBinlogs)
+ [
## 3. Create a copy or dump of your replication source
](#AuroraMySQL.Replication.MySQL.CreateSnapshot)
+ [
## 4. Load the dump into your replica target (if needed)
](#AuroraMySQL.Replication.MySQL.LoadSnapshot)
+ [
## 5. Create a replication user on your replication source
](#AuroraMySQL.Replication.MySQL.CreateReplUser)
+ [
## 6. Turn on replication on your replica target
](#AuroraMySQL.Replication.MySQL.EnableReplication)
+ [
### Setting a location to stop replication to a read replica
](#AuroraMySQL.Replication.StartReplicationUntil)
+ [
## 7. Monitor your replica
](#AuroraMySQL.Replication.MySQL.Monitor)
+ [
## Synchronizing passwords between replication source and target
](#AuroraMySQL.Replication.passwords)
## 1. Turn on binary logging on the replication source
Find instructions on how to turn on binary logging on the replication source for your database engine following.
| Database engine | Instructions |
| --- | --- |
| Aurora MySQL | **To turn on binary logging on an Aurora MySQL DB cluster** Set the `binlog_format` DB cluster parameter to `ROW`, `STATEMENT`, or `MIXED`. `MIXED` is recommended unless you have a need for a specific binlog format. (The default value is `OFF`.) To change the `binlog_format` parameter, create a custom DB cluster parameter group and associate that custom parameter group with your DB cluster. You can't change parameters in the default DB cluster parameter group. If you're changing the `binlog_format` parameter from `OFF` to another value, reboot your Aurora DB cluster for the change to take effect. For more information, see [Amazon Aurora DB cluster and DB instance parameters](USER_WorkingWithDBClusterParamGroups.md#Aurora.Managing.ParameterGroups) and [Parameter groups for Amazon Aurora](USER_WorkingWithParamGroups.md). |
| RDS for MySQL | **To turn on binary logging on an Amazon RDS DB instance** You can't turn on binary logging directly for an Amazon RDS DB instance, but you can turn it on by doing one of the following: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) |
| MySQL (external) | **To set up encrypted replication** To replicate data securely with Aurora MySQL version 2, you can use encrypted replication. If you don't need to use encrypted replication, you can skip these steps. The following are prerequisites for using encrypted replication: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) During encrypted replication, the Aurora MySQL DB cluster acts a client to the MySQL database server. The certificates and keys for the Aurora MySQL client are in files in .pem format. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) **To turn on binary logging on an external MySQL database** [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) |
## 2. Retain binary logs on the replication source until no longer needed
When you use MySQL binary log replication, Amazon RDS doesn't manage the replication process. As a result, you need to ensure that the binlog files on your replication source are retained until after the changes have been applied to the replica. This maintenance helps you to restore your source database in the event of a failure.
Use the following instructions to retain binary logs for your database engine.
| Database engine | Instructions |
| --- | --- |
| Aurora MySQL | **To retain binary logs on an Aurora MySQL DB cluster** You don't have access to the binlog files for an Aurora MySQL DB cluster. As a result, you must choose a time frame to retain the binlog files on your replication source long enough to ensure that the changes have been applied to your replica before the binlog file is deleted by Amazon RDS. You can retain binlog files on an Aurora MySQL DB cluster for up to 90 days. If you're setting up replication with a MySQL database or RDS for MySQL DB instance as the replica, and the database that you are creating a replica for is very large, choose a large time frame to retain binlog files until the initial copy of the database to the replica is complete and the replica lag has reached 0. To set the binary log retention time frame, use the [mysql.rds\$1set\$1configuration](mysql-stored-proc-configuring.md#mysql_rds_set_configuration) procedure and specify a configuration parameter of `'binlog retention hours'` along with the number of hours to retain binlog files on the DB cluster. The maximum value for Aurora MySQL version 2.11.0 and higher and version 3 is 2160 (90 days). The following example sets the retention period for binlog files to 6 days: CALL mysql.rds_set_configuration('binlog retention hours', 144); After replication has been started, you can verify that changes have been applied to your replica by running the `SHOW SLAVE STATUS` (Aurora MySQL version 2) or `SHOW REPLICA STATUS` (Aurora MySQL version 3) command on your replica and checking the `Seconds behind master` field. If the `Seconds behind master` field is 0, then there is no replica lag. When there is no replica lag, reduce the length of time that binlog files are retained by setting the `binlog retention hours` configuration parameter to a smaller time frame. If this setting isn't specified, the default for Aurora MySQL is 24 (1 day). If you specify a value for `'binlog retention hours'` that is higher than the maximum value, then Aurora MySQL uses the maximum. |
| RDS for MySQL | **To retain binary logs on an Amazon RDS DB instance** You can retain binary log files on an Amazon RDS DB instance by setting the binlog retention hours just as with an Aurora MySQL DB cluster, described in the previous row. You can also retain binlog files on an Amazon RDS DB instance by creating a read replica for the DB instance. This read replica is temporary and solely for the purpose of retaining binlog files. After the read replica has been created, call the [mysql.rds\$1stop\$1replication](mysql-stored-proc-replicating.md#mysql_rds_stop_replication) procedure on the read replica. While replication is stopped, Amazon RDS doesn't delete any of the binlog files on the replication source. After you have set up replication with your permanent replica, you can delete the read replica when the replica lag (`Seconds behind master` field) between your replication source and your permanent replica reaches 0. |
| MySQL (external) | **To retain binary logs on an external MySQL database** Because binlog files on an external MySQL database are not managed by Amazon RDS, they are retained until you delete them. After replication has been started, you can verify that changes have been applied to your replica by running the `SHOW SLAVE STATUS` (Aurora MySQL version 2) or `SHOW REPLICA STATUS` (Aurora MySQL version 3) command on your replica and checking the `Seconds behind master` field. If the `Seconds behind master` field is 0, then there is no replica lag. When there is no replica lag, you can delete old binlog files. |
## 3. Create a copy or dump of your replication source
You use a snapshot, clone, or dump of your replication source to load a baseline copy of your data onto your replica. Then you start replicating from that point.
Use the following instructions to create a copy or dump of the replication source for your database engine.
| Database engine | Instructions |
| --- | --- |
| Aurora MySQL | **To create a copy of an Aurora MySQL DB cluster** Use one of the following methods: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) **To determine the binlog file name and position** Use one of the following methods: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) **To create a dump of an Aurora MySQL DB cluster** If your replica target is an external MySQL database or an RDS for MySQL DB instance, then you must create a dump file from your Aurora DB cluster. Be sure to run the `mysqldump` command against the copy of your source DB cluster that you created. This is to avoid locking considerations when taking the dump. If the dump were taken on the source DB cluster directly, it would be necessary to lock the source tables to prevent concurrent writes to them while the dump is in progress. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) |
| RDS for MySQL | **To create a snapshot of an Amazon RDS DB instance** Create a read replica of your Amazon RDS DB instance. For more information, see [Creating a read replica](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ReadRepl.html#USER_ReadRepl.Create) in the *Amazon Relational Database Service User Guide*. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) |
| MySQL (external) | **To create a dump of an external MySQL database** [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) |
## 4. Load the dump into your replica target (if needed)
If you plan to load data from a dump of a MySQL database that is external to Amazon RDS, you might want to create an EC2 instance to copy the dump files to. Then you can load the data into your DB cluster or DB instance from that EC2 instance. Using this approach, you can compress the dump file(s) before copying them to the EC2 instance in order to reduce the network costs associated with copying data to Amazon RDS. You can also encrypt the dump file or files to secure the data as it is being transferred across the network.
**Note**
If you create a new Aurora MySQL DB cluster as your replica target, then you don't need to load a dump file:
You can restore from a DB cluster snapshot to create a new DB cluster. For more information, see [Restoring from a DB cluster snapshot](aurora-restore-snapshot.md).
You can clone your source DB cluster to create a new DB cluster. For more information, see [Cloning a volume for an Amazon Aurora DB cluster](Aurora.Managing.Clone.md).
You can migrate the data from a DB instance snapshot into a new DB cluster. For more information, see [Migrating data to an Amazon Aurora MySQL DB cluster](AuroraMySQL.Migrating.md).
Use the following instructions to load the dump of your replication source into your replica target for your database engine.
| Database engine | Instructions |
| --- | --- |
| Aurora MySQL | **To load a dump into an Aurora MySQL DB cluster** [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) |
| RDS for MySQL | **To load a dump into an Amazon RDS DB instance** [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) |
| MySQL (external) | **To load a dump into an external MySQL database** You can't load a DB snapshot or a DB cluster snapshot into an external MySQL database. Instead, you must use the output from the `mysqldump` command. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) |
## 5. Create a replication user on your replication source
Create a user ID on the source that is used solely for replication. The following example is for RDS for MySQL or external MySQL source databases.
```
mysql> CREATE USER 'repl_user'@'domain_name' IDENTIFIED BY 'password';
```
For Aurora MySQL source databases, the `skip_name_resolve` DB cluster parameter is set to `1` (`ON`) and can't be modified, so you must use an IP address for the host instead of a domain name. For more information, see [skip\$1name\$1resolve](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_skip_name_resolve) in the MySQL documentation.
```
mysql> CREATE USER 'repl_user'@'IP_address' IDENTIFIED BY 'password';
```
The user requires the `REPLICATION CLIENT` and `REPLICATION SLAVE` privileges. Grant these privileges to the user.
If you need to use encrypted replication, require SSL connections for the replication user. For example, you can use one of the following statements to require SSL connections on the user account `repl_user`.
```
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'IP_address';
```
```
GRANT USAGE ON *.* TO 'repl_user'@'IP_address' REQUIRE SSL;
```
**Note**
If `REQUIRE SSL` isn't included, the replication connection might silently fall back to an unencrypted connection.
## 6. Turn on replication on your replica target
Before you turn on replication, we recommend that you take a manual snapshot of the Aurora MySQL DB cluster or RDS for MySQL DB instance replica target. If a problem arises and you need to re-establish replication with the DB cluster or DB instance replica target, you can restore the DB cluster or DB instance from this snapshot instead of having to import the data into your replica target again.
Use the following instructions to turn on replication for your database engine.
| Database engine | Instructions |
| --- | --- |
| Aurora MySQL | **To turn on replication from an Aurora MySQL DB cluster** [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) To use SSL encryption, set the final value to `1` instead of `0`. |
| RDS for MySQL | **To turn on replication from an Amazon RDS DB instance** [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) To use SSL encryption, set the final value to `1` instead of `0`. |
| MySQL (external) | **To turn on replication from an external MySQL database** [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) |
If replication fails, it can result in a large increase in unintentional I/O on the replica, which can degrade performance. If replication fails or is no longer needed, you can run the [mysql.rds\$1reset\$1external\$1master (Aurora MySQL version 2)](mysql-stored-proc-replicating.md#mysql_rds_reset_external_master) or [mysql.rds\$1reset\$1external\$1source (Aurora MySQL version 3)](mysql-stored-proc-replicating.md#mysql_rds_reset_external_source) stored procedure to remove the replication configuration.
### Setting a location to stop replication to a read replica
In Aurora MySQL version 3.04 and higher, you can start replication and then stop it at a specified binary log file location using the [mysql.rds\$1start\$1replication\$1until(Aurora MySQL version 3)](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 replica Aurora MySQL DB cluster as the master user.
1. Run the [mysql.rds\$1start\$1replication\$1until(Aurora MySQL version 3)](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`.
If you use GTID-based replication, use the [mysql.rds\$1start\$1replication\$1until\$1gtid(Aurora MySQL version 3)](mysql-stored-proc-gtid.md#mysql_rds_start_replication_until_gtid) stored procedure instead of the [mysql.rds\$1start\$1replication\$1until(Aurora MySQL version 3)](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).
## 7. Monitor your replica
When you set up MySQL replication with an Aurora MySQL DB cluster, you must monitor failover events for the Aurora MySQL DB cluster when it is the replica target. If a failover occurs, then the DB cluster that is your replica target 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).
You can also monitor how far the replica target is behind the replication source by connecting to the replica target and running the `SHOW SLAVE STATUS` (Aurora MySQL version 2) or `SHOW REPLICA STATUS` (Aurora MySQL version 3) command. In the command output, the `Seconds Behind Master` field tells you how far the replica target is behind the source.
**Important**
If you upgrade your DB cluster and specify a custom parameter group, make sure to manually reboot the cluster after the upgrade finishes. Doing so makes the cluster use your new custom parameter settings, and restarts binlog replication.
## Synchronizing passwords between replication source and target
When you change user accounts and passwords on the replication source using SQL statements, those changes are replicated to the replication target automatically.
If you use the AWS Management Console, the AWS CLI, or the RDS API to change the master password on the replication source, those changes are not automatically replicated to the replication target. If you want to synchronize the master user and master password between the source and target systems, you must make the same change on the replication target yourself.
# Stopping binary log replication for Aurora MySQL
Stopping binlog replication
To stop binary log replication with a MySQL DB instance, external MySQL database, or another Aurora DB cluster, follow these steps, discussed in detail following in this topic.
[1. Stop binary log replication on the replica target](#AuroraMySQL.Replication.MySQL.Stopping.StopReplication)
[2. Turn off binary logging on the replication source](#AuroraMySQL.Replication.MySQL.Stopping.DisableBinaryLogging)
## 1. Stop binary log replication on the replica target
Use the following instructions to stop binary log replication for your database engine.
| Database engine | Instructions |
| --- | --- |
| Aurora MySQL | **To stop binary log replication on an Aurora MySQL DB cluster replica target** Connect to the Aurora DB cluster that is the replica target, and call the [mysql.rds\$1stop\$1replication](mysql-stored-proc-replicating.md#mysql_rds_stop_replication) procedure. |
| RDS for MySQL | **To stop binary log replication on an Amazon RDS DB instance** Connect to the RDS DB instance that is the replica target and call the [mysql.rds\$1stop\$1replication](mysql-stored-proc-replicating.md#mysql_rds_stop_replication) procedure. |
| MySQL (external) | **To stop binary log replication on an external MySQL database** Connect to the MySQL database and run the `STOP SLAVE` (version 5.7) or `STOP REPLICA` (version 8.0) command. |
## 2. Turn off binary logging on the replication source
Use the instructions in the following table to turn off binary logging on the replication source for your database engine.
| Database engine | Instructions |
| --- | --- |
| Aurora MySQL | **To turn off binary logging on an Amazon Aurora DB cluster** [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.Stopping.html) |
| RDS for MySQL | **To turn off binary logging on an Amazon RDS DB instance** You can't turn off binary logging directly for an Amazon RDS DB instance, but you can turn it off by doing the following: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.Stopping.html) |
| MySQL (external) | **To turn off binary logging on an external MySQL database** Connect to the MySQL database and call the `STOP REPLICATION` command. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.Stopping.html) |
# Scaling reads for your MySQL database with Amazon Aurora
Scaling MySQL reads
You can use Amazon Aurora with your MySQL DB instance to take advantage of the read scaling capabilities of Amazon Aurora and expand the read workload for your MySQL DB instance. To use Aurora to scale reads for your MySQL DB instance, create an Amazon Aurora MySQL DB cluster and make it a read replica of your MySQL DB instance. This applies to an RDS for MySQL DB instance, or a MySQL database running external to Amazon RDS.
For information on creating an Amazon Aurora DB cluster, see [Creating an Amazon Aurora DB cluster](Aurora.CreateInstance.md).
When you set up replication between your MySQL DB instance and your Amazon Aurora DB cluster, be sure to follow these guidelines:
+ Use the Amazon Aurora DB cluster endpoint address when you reference your Amazon Aurora MySQL DB cluster. If a failover occurs, then the Aurora Replica that is promoted to the primary instance for the Aurora MySQL DB cluster continues to use the DB cluster endpoint address.
+ Maintain the binlogs on your writer instance until you have verified that they have been applied to the Aurora Replica. This maintenance ensures that you can restore your writer instance in the event of a failure.
**Important**
When using self-managed replication, you're responsible for monitoring and resolving any replication issues that may occur. For more information, see [Diagnosing and resolving lag between read replicas](CHAP_Troubleshooting.md#CHAP_Troubleshooting.MySQL.ReplicaLag).
**Note**
The permissions required to start replication on an Aurora MySQL DB cluster are restricted and not available to your Amazon RDS master user. Therefore, you must use the [mysql.rds\$1set\$1external\$1master (Aurora MySQL version 2)](mysql-stored-proc-replicating.md#mysql_rds_set_external_master) or [mysql.rds\$1set\$1external\$1source (Aurora MySQL version 3)](mysql-stored-proc-replicating.md#mysql_rds_set_external_source) and [mysql.rds\$1start\$1replication](mysql-stored-proc-replicating.md#mysql_rds_start_replication) procedures to set up replication between your Aurora MySQL DB cluster and your MySQL DB instance.
## Start replication between an external source instance and an Aurora MySQL DB cluster
1. Make the source MySQL DB 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 DB 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 MySQL DB instance to the Amazon Aurora MySQL DB cluster 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](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql-importing-data-reduced-downtime.html) in the *Amazon Relational Database Service User Guide*.
For Linux, macOS, or Unix:
```
mysqldump \
--databases \
--single-transaction \
--compress \
--order-by-primary \
-u local_user \
-p local_password | mysql \
--host aurora_cluster_endpoint_address \
--port 3306 \
-u RDS_user_name \
-p RDS_password
```
For Windows:
```
mysqldump ^
--databases ^
--single-transaction ^
--compress ^
--order-by-primary ^
-u local_user ^
-p local_password | mysql ^
--host aurora_cluster_endpoint_address ^
--port 3306 ^
-u RDS_user_name ^
-p RDS_password
```
**Note**
Make sure that there is not a space between the `-p` option and the entered password.
Use the `--host`, `--user (-u)`, `--port` and `-p` options in the `mysql` command to specify the hostname, user name, port, and password to connect to your Aurora DB cluster. The host name is the DNS name from the Amazon Aurora DB cluster endpoint, for example, `mydbcluster.cluster-123456789012.us-east-1.rds.amazonaws.com`. You can find the endpoint value in the cluster details in the Amazon RDS Management Console.
1. Make the source MySQL DB instance writeable again:
```
mysql> SET GLOBAL read_only = OFF;
mysql> UNLOCK TABLES;
```
For more information on making backups for use with replication, see [http://dev.mysql.com/doc/refman/8.0/en/replication-solutions-backups-read-only.html](http://dev.mysql.com/doc/refman/8.0/en/replication-solutions-backups-read-only.html) in the MySQL documentation.
1. In the Amazon RDS Management Console, add the IP address of the server that hosts the source MySQL database to the VPC security group for the Amazon Aurora DB cluster. 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*.
You might also need to configure your local network to permit connections from the IP address of your Amazon Aurora DB cluster, so that it can communicate with your source MySQL instance. To find the IP address of the Amazon Aurora DB cluster, use the `host` command.
```
host aurora_endpoint_address
```
The host name is the DNS name from the Amazon Aurora DB cluster endpoint.
1. Using the client of your choice, connect to the external MySQL instance and create a MySQL user to be used for replication. This account is used solely for replication and must be restricted to your domain to improve security. The following is an example.
```
CREATE USER 'repl_user'@'example.com' IDENTIFIED BY 'password';
```
1. For the external MySQL 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'@'example.com'
IDENTIFIED BY 'password';
```
1. Take a manual snapshot of the Aurora MySQL DB cluster to be the read replica before setting up replication. If you need to reestablish replication with the DB cluster as a read replica, you can restore the Aurora MySQL DB cluster from this snapshot instead of having to import the data from your MySQL DB instance into a new Aurora MySQL DB cluster.
1. Make the Amazon Aurora DB cluster the replica. Connect to the Amazon Aurora DB cluster as the master user and identify the source MySQL database as the replication source by using the [mysql.rds\$1set\$1external\$1master (Aurora MySQL version 2)](mysql-stored-proc-replicating.md#mysql_rds_set_external_master) or [mysql.rds\$1set\$1external\$1source (Aurora MySQL version 3)](mysql-stored-proc-replicating.md#mysql_rds_set_external_source) and [mysql.rds\$1start\$1replication](mysql-stored-proc-replicating.md#mysql_rds_start_replication) procedures.
Use the binlog file name and position that you determined in Step 2. The following is an example.
```
For Aurora MySQL version 2:
CALL mysql.rds_set_external_master ('mymasterserver.example.com', 3306,
'repl_user', 'password', 'mysql-bin-changelog.000031', 107, 0);
For Aurora MySQL version 3:
CALL mysql.rds_set_external_source ('mymasterserver.example.com', 3306,
'repl_user', 'password', 'mysql-bin-changelog.000031', 107, 0);
```
1. On the Amazon Aurora DB cluster, call the [mysql.rds\$1start\$1replication](mysql-stored-proc-replicating.md#mysql_rds_start_replication) procedure to start replication.
```
CALL mysql.rds_start_replication;
```
After you have established replication between your source MySQL DB instance and your Amazon Aurora DB cluster, you can add Aurora Replicas to your Amazon Aurora DB cluster. You can then connect to the Aurora Replicas to read scale your data. For information on creating an Aurora Replica, see [Adding Aurora Replicas to a DB cluster](aurora-replicas-adding.md).
# Optimizing binary log replication for Aurora MySQL
Optimizing binlog replication
Following, you can learn how to optimize binary log replication performance and troubleshoot related issues in Aurora MySQL.
**Tip**
This discussion presumes that you are familiar with the MySQL binary log replication mechanism and how it works. For background information, see [Replication Implementation](https://dev.mysql.com/doc/refman/8.0/en/replication-implementation.html) in the MySQL documentation.
## Multithreaded binary log replication
With multithreaded binary log replication, a SQL thread reads events from the relay log and queues them up for SQL worker threads to apply. The SQL worker threads are managed by the coordinator thread. The binary log events are applied in parallel when possible. The level of parallelism depends on factors including version, parameters, schema design, and workload characteristics.
Multithreaded binary log replication is supported in Aurora MySQL version 3, and in Aurora MySQL version 2.12.1 and higher. For a multithreaded replica to efficiently process binlog events in parallel, you must configure the source for multithreaded binary log replication, and the source must use a version that includes the parallelism information on its binary log files.
When an Aurora MySQL DB instance is configured to use binary log replication, by default the replica instance uses single-threaded replication for Aurora MySQL versions lower than 3.04. To enable multithreaded replication, you update the `replica_parallel_workers` parameter to a value greater than `1` in your custom parameter group.
For Aurora MySQL version 3.04 and higher, replication is multithreaded by default, with `replica_parallel_workers` set to `4`. You can modify this parameter in your custom parameter group.
To increase the resilience of your database against unexpected halts, we recommend that you enable GTID replication on the source and allow GTIDs on the replica. To allow GTID replication, set `gtid_mode` to `ON_PERMISSIVE` on both the source and replica. For more information about GTID-based replication, see [Using GTID-based replication](mysql-replication-gtid.md).
The following configuration options help you to fine-tune multithreaded replication. For usage information, see [Replication and Binary Logging Options and Variables](https://dev.mysql.com/doc/refman/8.0/en/replication-options.html) in the *MySQL Reference Manual*. For more information about multithreaded replication, see the MySQL Blog [https://dev.mysql.com/blog-archive/improving-the-parallel-applier-with-writeset-based-dependency-tracking/](https://dev.mysql.com/blog-archive/improving-the-parallel-applier-with-writeset-based-dependency-tracking/).
Optimal parameter values depend on several factors. For example, performance for binary log replication is influenced by your database workload characteristics and the DB instance class the replica is running on. Thus, we recommend that you thoroughly test all changes to these configuration parameters before applying new parameter settings to a production instance:
+ `binlog_format recommended value` – set to `ROW`
+ `binlog_group_commit_sync_delay`
+ `binlog_group_commit_sync_no_delay_count`
+ `binlog_transaction_dependency_history_size`
+ `binlog_transaction_dependency_tracking` – recommended value is `WRITESET`
+ `replica_preserve_commit_order`
+ `replica_parallel_type` – recommended value is `LOGICAL_CLOCK`
+ `replica_parallel_workers`
+ `replica_pending_jobs_size_max`
+ `transaction_write_set_extraction` – recommended value is `XXHASH64`
Your schema and workload characteristics are factors that affect replication in parallel. The most common factors are the following.
+ Absence of primary keys – RDS can't establish writeset dependency for tables without primary keys. With `ROW` format, a single multi-row statement can be accomplished with a single full table scan on the source, but results in one full table scan per row modified on the replica. The absence of primary keys significantly decreases replication throughput.
+ Presence of foreign keys – If foreign keys are present, Amazon RDS can't use writeset dependency for parallelism of the tables with the FK relationship.
+ Size of transactions – If a single transaction spans dozens or hundreds of megabytes or gigabytes, the coordinator thread and one of the worker threads might spend a long time processing only that transaction. During that time, all other worker threads might remain idle after they conclude processing their previous transactions.
In Aurora MySQL version 3.06 and higher, you can improve performance for binary log replicas when replicating transactions for large tables with more than one secondary index. This feature introduces a thread pool to apply secondary index changes in parallel on a binlog replica. The feature is controlled by the `aurora_binlog_replication_sec_index_parallel_workers` DB cluster parameter, which controls the total number of parallel threads available to apply the secondary index changes. The parameter is set to `0` (disabled) by default. Enabling this feature doesn't require an instance restart. To enable this feature, stop ongoing replication, set the desired number of parallel worker threads, and then start replication again.
## Optimizing binlog replication
In Aurora MySQL 2.10 and higher, Aurora automatically applies an optimization known as the binlog I/O cache to binary log replication. By caching the most recently committed binlog events, this optimization is designed to improve binlog dump thread performance while limiting the impact to foreground transactions on the binlog source instance.
**Note**
This memory used for this feature is independent of the MySQL `binlog_cache` setting.
This feature doesn't apply to Aurora DB instances that use the `db.t2` and `db.t3` instance classes.
You don't need to adjust any configuration parameters to turn on this optimization. In particular, if you had adjusted the configuration parameter `aurora_binlog_replication_max_yield_seconds` to a nonzero value in earlier Aurora MySQL versions, set it back to zero for currently available versions.
The status variables `aurora_binlog_io_cache_reads` and `aurora_binlog_io_cache_read_requests` help you to monitor how often the data is read from the binlog I/O cache.
+ `aurora_binlog_io_cache_read_requests` shows the number of binlog I/O read requests from the cache.
+ `aurora_binlog_io_cache_reads` shows the number of binlog I/O reads that retrieve information from the cache.
The following SQL query computes the percentage of binlog read requests that take advantage of the cached information. In this case, the closer the ratio is to 100, the better it is.
```
mysql> SELECT
(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE VARIABLE_NAME='aurora_binlog_io_cache_reads')
/ (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE VARIABLE_NAME='aurora_binlog_io_cache_read_requests')
* 100
as binlog_io_cache_hit_ratio;
+---------------------------+
| binlog_io_cache_hit_ratio |
+---------------------------+
| 99.99847949080622 |
+---------------------------+
```
The binlog I/O cache feature also includes new metrics related to the binlog dump threads. *Dump threads* are the threads that are created when new binlog replicas are connected to the binlog source instance.
The dump thread metrics are printed to the database log every 60 seconds with the prefix `[Dump thread metrics]`. The metrics include information for each binlog replica such as `Secondary_id`, `Secondary_uuid`, binlog file name, and the position that each replica is reading. The metrics also include `Bytes_behind_primary` representing the distance in bytes between replication source and replica. This metric measures the lag of the replica I/O thread. That figure is different from the lag of the replica SQL applier thread, which is represented by the `seconds_behind_master` metric on the binlog replica. You can determine whether binlog replicas are catching up to the source or falling behind by checking whether the distance decreases or increases.
## In-memory relay log
In Aurora MySQL version 3.10 and higher, Aurora introduces an optimization known as in-memory relay log to improve replication throughput. This optimization enhances relay log I/O performance by caching all intermediate relay log content in memory. As a result, it reduces commit latency by minimizing storage I/O operations since the relay log content remains readily accessible in memory.
By default, the in-memory relay log feature is automatically enabled for Aurora-managed replication scenarios (including blue-green deployments, Aurora-Aurora replication, and cross-region replicas) when the replica meets any of these configurations:
+ Single-threaded replication mode (replica\$1parallel\$1workers = 0)
+ Multi-threaded replication with GTID mode enabled:
+ Auto-position enabled
+ GTID mode set to ON on the replica
+ File-based replication with replica\$1preserve\$1commit\$1order = ON
The in-memory relay log feature is supported on instance classes larger than t3.large, but is not available on Aurora Serverless instances. The relay log circular buffer has a fixed size of 128 MB. To monitor the memory consumption of this feature, you can run the following query:
```
SELECT event_name, current_alloc FROM sys.memory_global_by_current_bytes WHERE event_name = 'memory/sql/relaylog_io_cache';
```
The in-memory relay log feature is controlled by the aurora\$1in\$1memory\$1relaylog parameter, which can be set at either the DB cluster or instance level. You can enable or disable this feature dynamically without restarting your instance:
1. Stop the ongoing replication
1. Set aurora\$1in\$1memory\$1relaylog to ON (to enable) or OFF (to disable) in parameter group
1. Restart replication
Example:
```
CALL mysql.rds_stop_replication;
set aurora_in_memory_relaylog to ON to enable or OFF to disable in cluster parameter group
CALL mysql.rds_start_replication;
```
Even when aurora\$1in\$1memory\$1relaylog is set to ON, the in-memory relay log feature might still be disabled under certain conditions. To verify the feature's current status, you can use the following command:
```
SHOW GLOBAL STATUS LIKE 'Aurora_in_memory_relaylog_status';
```
If the feature is unexpectedly disabled, you can identify the reason by running:
```
SHOW GLOBAL STATUS LIKE 'Aurora_in_memory_relaylog_disabled_reason';
```
This command returns a message explaining why the feature is currently disabled.
# Setting up enhanced binlog for Aurora MySQL
Setting up enhanced binlog
Enhanced binlog reduces the compute performance overhead caused by turning on binlog, which can reach up to 50% in certain cases. With enhanced binlog, this overhead can be reduced to about 13%. To reduce overhead, enhanced binlog writes the binary and transactions logs to storage in parallel, which minimizes the data written at the transaction commit time.
Using enhanced binlog also improves database recovery time after restarts and failovers by up to 99% compared to community MySQL binlog. The enhanced binlog is compatible with existing binlog-based workloads, and you interact with it the same way you interact with the community MySQL binlog.
Enhanced binlog is available on Aurora MySQL version 3.03.1 and higher.
**Topics**
+ [
## Configuring enhanced binlog parameters
](#AuroraMySQL.Enhanced.binlog.enhancedbinlog.parameters)
+ [
## Other related parameters
](#AuroraMySQL.Enhanced.binlog.other.parameters)
+ [
## Differences between enhanced binlog and community MySQL binlog
](#AuroraMySQL.Enhanced.binlog.differences)
+ [
## Amazon CloudWatch metrics for enhanced binlog
](#AuroraMySQL.Enhanced.binlog.cloudwatch.metrics)
+ [
## Enhanced binlog limitations
](#AuroraMySQL.Enhanced.binlog.limitations)
## Configuring enhanced binlog parameters
You can switch between community MySQL binlog and enhanced binlog by turning on/off the enhanced binlog parameters. The existing binlog consumers can continue to read and consume the binlog files without any gaps in the binlog file sequence.
To turn on enhanced binlog, set the following parameters:
| Parameter | Default | Description |
| --- | --- | --- |
| binlog\$1format | – | Set the binlog\$1format parameter to the binary logging format of your choice to turn on enhanced binlog. Make sure the binlog\$1format parameter isn't set to OFF. For more information, see [Configuring Aurora MySQL binary logging](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_LogAccess.MySQL.BinaryFormat.html). |
| aurora\$1enhanced\$1binlog | 0 | Set the value of this parameter to 1 in the DB cluster parameter group associated with the Aurora MySQL cluster. When you change the value of this parameter, you must reboot the writer instance when the DBClusterParameterGroupStatus value is shown as pending-reboot. |
| binlog\$1backup | 1 | Turn off this parameter to turn on enhanced binlog. To do so, set the value of this parameter to 0. |
| binlog\$1replication\$1globaldb | 1 | Turn off this parameter to turn on enhanced binlog. To do so, set the value of this parameter to 0. |
**Important**
You can turn off the `binlog_backup` and `binlog_replication_globaldb` parameters only when you use enhanced binlog.
To turn off enhanced binlog, set the following parameters:
| Parameter | Description |
| --- | --- |
| aurora\$1enhanced\$1binlog | Set the value of this parameter to 0 in the DB cluster parameter group associated with the Aurora MySQL cluster. Whenever you change the value of this parameter, you must reboot the writer instance when the DBClusterParameterGroupStatus value is shown as pending-reboot. |
| binlog\$1backup | Turn on this parameter when you turn off enhanced binlog. To do so, set the value of this parameter to 1. |
| binlog\$1replication\$1globaldb | Turn on this parameter when you turn off enhanced binlog. To do so, set the value of this parameter to 1. |
To check whether enhanced binlog is turned on, use the following command in the MySQL client:
```
mysql>show status like 'aurora_enhanced_binlog';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| aurora_enhanced_binlog | ACTIVE |
+------------------------+--------+
1 row in set (0.00 sec)
```
When enhanced binlog is turned on, the output shows `ACTIVE` for `aurora_enhanced_binlog`.
## Other related parameters
When you turn on the enhanced binlog, the following parameters are affected:
+ The `max_binlog_size` parameter is visible but not modifiable. It's default value `134217728` is automatically adjusted to `268435456` when enhanced binlog is turned on.
+ Unlike in community MySQL binlog, the `binlog_checksum` doesn't act as a dynamic parameter when the enhanced binlog is turned on. For the change to this parameter to take effect, you must manually reboot the DB cluster even when the `ApplyMethod` is `immediate`.
+ The value you set on the `binlog_order_commits` parameter has no effect on the order of the commits when enhanced binlog is turned on. The commits are always ordered without any further performance implications.
## Differences between enhanced binlog and community MySQL binlog
Enhanced binlog interacts differently with clones, backups, and Aurora global database when compared to community MySQL binlog. We recommend that you understand the following differences before using enhanced binlog.
+ Enhanced binlog files from the source DB cluster aren't available on a cloned DB cluster.
+ Enhanced binlog files aren't included in Aurora backups. Therefore, enhanced binlog files from the source DB cluster aren't available after restoring a DB cluster despite any retention period set on it.
+ When used with an Aurora global database, the enhanced binlog files of the primary DB cluster aren't replicated to the DB cluster in the secondary regions.
****Examples****
The following examples illustrate the differences between enhanced binlog and community MySQL binlog.
**On a restored or cloned DB cluster**
When enhanced binlog is turned on, the historical binlog files aren't available in the restored or cloned DB cluster. After a restore or clone operation, if binlog is turned on, the new DB cluster starts writing its own sequence of binlog files, starting from 1 (mysql-bin-changelog.000001).
To turn on enhanced binlog after a restore or clone operation, set the required DB cluster parameters on the restored or cloned DB cluster. For more information, see [Configuring enhanced binlog parameters](#AuroraMySQL.Enhanced.binlog.enhancedbinlog.parameters).
**Example: Clone or restore operation performed when enhanced binlog is turned on**
Source DB Cluster:
```
mysql> show binary logs;
+----------------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------------------+-----------+-----------+
| mysql-bin-changelog.000001 | 156 | No |
| mysql-bin-changelog.000002 | 156 | No |
| mysql-bin-changelog.000003 | 156 | No |
| mysql-bin-changelog.000004 | 156 | No | --> Enhanced Binlog turned on
| mysql-bin-changelog.000005 | 156 | No | --> Enhanced Binlog turned on
| mysql-bin-changelog.000006 | 156 | No | --> Enhanced Binlog turned on
+----------------------------+-----------+-----------+
6 rows in set (0.00 sec)
```
On a restored or cloned DB cluster, binlog files aren't backed up when enhanced binlog is turned on. To avoid discontinuity in the binlog data, the binlog files written before turning on the enhanced binlog are also not available.
```
mysql>show binary logs;
+----------------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------------------+-----------+-----------+
| mysql-bin-changelog.000001 | 156 | No | --> New sequence of Binlog files
+----------------------------+-----------+-----------+
1 row in set (0.00 sec)
```
**Example: Clone or restore operation performed when enhanced binlog is turned off**
Source DB cluster:
```
mysql>show binary logs;
+----------------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------------------+-----------+-----------+
| mysql-bin-changelog.000001 | 156 | No |
| mysql-bin-changelog.000002 | 156 | No | --> Enhanced Binlog enabled
| mysql-bin-changelog.000003 | 156 | No | --> Enhanced Binlog enabled
| mysql-bin-changelog.000004 | 156 | No |
| mysql-bin-changelog.000005 | 156 | No |
| mysql-bin-changelog.000006 | 156 | No |
+----------------------------+-----------+-----------+
6 rows in set (0.00 sec)
```
Enhanced binlog is disabled after `mysql-bin-changelog.000003`. On a restored or cloned DB cluster, binlog files written after turning off the enhanced binlog are available.
```
mysql>show binary logs;
+----------------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------------------+-----------+-----------+
| mysql-bin-changelog.000004 | 156 | No |
| mysql-bin-changelog.000005 | 156 | No |
| mysql-bin-changelog.000006 | 156 | No |
+----------------------------+-----------+-----------+
1 row in set (0.00 sec)
```
**On an Amazon Aurora global database**
On an Amazon Aurora global database, the binlog data of the primary DB cluster isn't replicated to the secondary DB clusters. After a cross-Region failover process, the binlog data isn't available in the newly promoted primary DB cluster. If binlog is turned on, the newly promoted DB cluster starts its own sequence of binlog files, starting from 1 (mysql-bin-changelog.000001).
To turn on enhanced binlog after failover, you must set the required DB cluster parameters on the secondary DB cluster. For more information, see [Configuring enhanced binlog parameters](#AuroraMySQL.Enhanced.binlog.enhancedbinlog.parameters).
**Example: Global database failover operation is performed when enhanced binlog is turned on**
Old primary DB Cluster (before failover):
```
mysql>show binary logs;
+----------------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------------------+-----------+-----------+
| mysql-bin-changelog.000001 | 156 | No |
| mysql-bin-changelog.000002 | 156 | No |
| mysql-bin-changelog.000003 | 156 | No |
| mysql-bin-changelog.000004 | 156 | No | --> Enhanced Binlog enabled
| mysql-bin-changelog.000005 | 156 | No | --> Enhanced Binlog enabled
| mysql-bin-changelog.000006 | 156 | No | --> Enhanced Binlog enabled
+----------------------------+-----------+-----------+
6 rows in set (0.00 sec)
```
New primary DB cluster (after failover):
Binlog files aren't replicated to secondary regions when enhanced binlog is turned on. To avoid discontinuity in the binlog data, the binlog files written before turning on the enhanced binlog aren't available.
```
mysql>show binary logs;
+----------------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------------------+-----------+-----------+
| mysql-bin-changelog.000001 | 156 | No | --> Fresh sequence of Binlog files
+----------------------------+-----------+-----------+
1 row in set (0.00 sec)
```
**Example: Global database failover operation is performed when enhanced binlog is turned off**
Source DB Cluster:
```
mysql>show binary logs;
+----------------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------------------+-----------+-----------+
| mysql-bin-changelog.000001 | 156 | No |
| mysql-bin-changelog.000002 | 156 | No | --> Enhanced Binlog enabled
| mysql-bin-changelog.000003 | 156 | No | --> Enhanced Binlog enabled
| mysql-bin-changelog.000004 | 156 | No |
| mysql-bin-changelog.000005 | 156 | No |
| mysql-bin-changelog.000006 | 156 | No |
+----------------------------+-----------+-----------+
6 rows in set (0.00 sec)
```
**Restored or cloned DB cluster:**
Enhanced binlog is disabled after `mysql-bin-changelog.000003`. Binlog files that are written after turning off the enhanced binlog are replicated and are available in the newly promoted DB cluster.
```
mysql>show binary logs;
+----------------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------------------+-----------+-----------+
| mysql-bin-changelog.000004 | 156 | No |
| mysql-bin-changelog.000005 | 156 | No |
| mysql-bin-changelog.000006 | 156 | No |
+----------------------------+-----------+-----------+
3 rows in set (0.00 sec)
```
## Amazon CloudWatch metrics for enhanced binlog
The following Amazon CloudWatch metrics are published only when enhanced binlog is turned on.
| CloudWatch metric | Description | Units |
| --- | --- | --- |
| ChangeLogBytesUsed | The amount of storage used by the enhanced binlog. | Bytes |
| ChangeLogReadIOPs | The number of read I/O operations performed in the enhanced binlog within a 5-minute interval. | Count per 5 minutes |
| ChangeLogWriteIOPs | The number of write disk I/O operations performed in the enhanced binlog within a 5-minute interval. | Count per 5 minutes |
## Enhanced binlog limitations
The following limitations apply to Amazon Aurora DB clusters when enhanced binlog is turned on.
+ Enhanced binlog is only supported on Aurora MySQL version3.03.1 and higher.
+ The enhanced binlog files written on the primary DB cluster aren't copied to the cloned or restored DB clusters.
+ When used with Amazon Aurora global database, the enhanced binlog files of the primary DB cluster aren't replicated to the secondary DB clusters. Therefore, after the failover process, the historical binlog data isn't available in the new primary DB cluster.
+ The following binlog configuration parameters are ignored:
+ `binlog_group_commit_sync_delay`
+ `binlog_group_commit_sync_no_delay_count`
+ `binlog_max_flush_queue_time`
+ You can't drop or rename a corrupted table in a database. To drop these tables, you can contact Support.
+ The binlog I/O cache is disabled when enhanced binlog is turned on. For more information, see [Optimizing binary log replication for Aurora MySQL](binlog-optimization.md).
**Note**
Enhanced binlog provides similar read performance improvements as binlog I/O cache and better write performance improvements.
+ The backtrack feature is not supported. Enhanced binlog can't be turned on in a DB cluster under the following conditions:
+ DB cluster with the backtrack feature currently enabled.
+ DB cluster where the backtrack feature was previously enabled, but is now disabled.
+ DB cluster restored from a source DB cluster or a snapshot with the backtrack feature enabled.
# Using GTID-based replication
GTID-based replication
The following content explains how to use global transaction identifiers (GTIDs) with binary log (binlog) replication between an Aurora MySQL cluster and an external source.
**Note**
For Aurora, you can use this feature only with Aurora MySQL clusters that use binlog replication to or from an external MySQL database. The other database might be an Amazon RDS MySQL instance, an on-premises MySQL database, or an Aurora DB cluster in a different AWS Region. To learn how to configure that kind of replication, see [Replication between Aurora and MySQL or between Aurora and another Aurora DB cluster (binary log replication)](AuroraMySQL.Replication.MySQL.md).
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 Aurora MySQL version 2 and 3.
**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 an Aurora MySQL cluster
](mysql-replication-gtid.configuring-aurora.md)
+ [
# Disabling GTID-based replication for an Aurora MySQL DB cluster
](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.
**Note**
When Aurora synchronizes data among the DB instances in a cluster, that replication mechanism doesn't involve the binary log (binlog). For Aurora MySQL, GTID-based replication only applies when you also use binlog replication to replicate into or out of an Aurora MySQL DB cluster from an external MySQL-compatible database.
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 typically use GTID-based replication with Aurora when replicating from an external MySQL-compatible database into an Aurora cluster. You can set up this replication configuration as part of a migration from an on-premises or Amazon RDS database into Aurora MySQL. If the external database already uses GTIDs, enabling GTID-based replication for the Aurora cluster simplifies the replication process.
You configure GTID-based replication for an Aurora MySQL cluster by first setting the relevant configuration parameters in a DB cluster parameter group. You then associate that parameter group with the cluster.
## 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 DB cluster parameter group for your Aurora MySQL DB cluster:
+ `ON` and `ON_PERMISSIVE` apply only to outgoing replication from an Aurora MySQL cluster. Both of these values cause your Aurora DB cluster to use GTIDs for transactions that are replicated to an external database. `ON` requires that the external database also use GTID-based replication. `ON_PERMISSIVE` makes GTID-based replication optional on the external database.
+ `OFF_PERMISSIVE`, if set, means that your Aurora DB cluster can accept incoming replication from an external database. It can do this whether the external database uses GTID-based replication or not.
+ `OFF`, if set, means that your Aurora DB cluster only accepts incoming replication from external databases that don't use GTID-based replication.
**Tip**
Incoming replication is the most common binlog replication scenario for Aurora MySQL clusters. For incoming replication, we recommend that you set the GTID mode to `OFF_PERMISSIVE`. That setting allows incoming replication from external databases regardless of the GTID settings at the replication source.
For more information about parameter groups, see [Parameter groups for Amazon Aurora](USER_WorkingWithParamGroups.md).
# Enabling GTID-based replication for an Aurora MySQL cluster
Enabling GTID-based replication
When GTID-based replication is enabled for an Aurora MySQL DB cluster, the GTID settings apply to both inbound and outbound binlog replication.
**To enable GTID-based replication for an Aurora MySQL cluster**
1. Create or edit a DB cluster parameter group using the following parameter settings:
+ `gtid_mode` – `ON` or `ON_PERMISSIVE`
+ `enforce_gtid_consistency` – `ON`
1. Associate the DB cluster parameter group with the Aurora MySQL cluster. To do so, follow the procedures in [Parameter groups for Amazon Aurora](USER_WorkingWithParamGroups.md).
1. (Optional) Specify how to assign GTIDs to transactions that don't include them. To do so, call the stored procedure in [mysql.rds\$1assign\$1gtids\$1to\$1anonymous\$1transactions (Aurora MySQL version 3)](mysql-stored-proc-gtid.md#mysql_assign_gtids_to_anonymous_transactions).
# Disabling GTID-based replication for an Aurora MySQL DB cluster
Disabling GTID-based replication
You can disable GTID-based replication for an Aurora MySQL DB cluster. Doing so means that the Aurora cluster can't perform inbound or outbound binlog replication with external databases that use GTID-based replication.
**Note**
In the following procedure, *read replica* means the replication target in an Aurora configuration with binlog replication to or from an external database. It doesn't mean the read-only Aurora Replica DB instances. For example, when an Aurora cluster accepts incoming replication from an external source, the Aurora primary instance acts as the read replica for binlog replication.
For more details about the stored procedures mentioned in this section, see [Aurora MySQL stored procedure reference](AuroraMySQL.Reference.StoredProcs.md).
**To disable GTID-based replication for an Aurora MySQL DB cluster**
1. On the Aurora replicas, run the following procedure:
For version 3
```
CALL mysql.rds_set_source_auto_position(0);
```
For version 2
```
CALL mysql.rds_set_master_auto_position(0);
```
1. Reset the `gtid_mode` to `ON_PERMISSIVE`.
1. Make sure that the DB cluster parameter group associated with the Aurora MySQL cluster has `gtid_mode` set to `ON_PERMISSIVE`.
For more information about setting configuration parameters using parameter groups, see [Parameter groups for Amazon Aurora](USER_WorkingWithParamGroups.md).
1. Restart the Aurora MySQL DB cluster.
1. Reset the `gtid_mode` to `OFF_PERMISSIVE`.
1. Make sure that the DB cluster parameter group associated with the Aurora MySQL cluster has `gtid_mode` set to `OFF_PERMISSIVE`.
1. Restart the Aurora MySQL DB cluster.
1. Wait for all of the GTID transactions to be applied on the Aurora primary instance. To check that these are applied, do the following steps:
1. On the Aurora primary instance, run the `SHOW MASTER STATUS` command.
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:
For version 3
```
SELECT SOURCE_POS_WAIT('file', position);
```
For version 2
```
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:
For version 3
```
SELECT SOURCE_POS_WAIT('mysql-bin-changelog.000031', 107);
```
For version 2
```
SELECT MASTER_POS_WAIT('mysql-bin-changelog.000031', 107);
```
1. Reset the GTID parameters to disable GTID-based replication.
1. Make sure that the DB cluster parameter group associated with the Aurora MySQL cluster has the following parameter settings:
+ `gtid_mode` – `OFF`
+ `enforce_gtid_consistency` – `OFF`
1. Restart the Aurora MySQL DB cluster.