# 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 Amazon Web Services 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 Amazon Web Services 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.