Replicating transactions using GTIDs

The following stored procedures control how transactions are replicated using global transaction identifiers (GTIDs) with Aurora MySQL. To learn how to use replication based on GTIDs with Aurora MySQL, see Using GTID-based replication.

mysql.rds_assign_gtids_to_anonymous_transactions (Aurora MySQL version 3)

Configures the ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS option of the CHANGE REPLICATION SOURCE TO statement. It makes the replication channel assign a GTID to replicated transactions that don't have one. That way, you can perform binary log replication from a source that doesn't use GTID-based replication to a replica that does. For more information, see CHANGE REPLICATION SOURCE TO Statement and Replication From a Source Without GTIDs to a Replica With GTIDs in the MySQL Reference Manual.

Syntax

CALL mysql.rds_assign_gtids_to_anonymous_transactions(gtid_option);

Parameters

Usage notes

This procedure has the same effect as issuing the statement CHANGE REPLICATION SOURCE TO ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = gtid_option in community MySQL.

GTID must be turned to ON for gtid_option to be set to LOCAL or a specific UUID.

The default is OFF, meaning that the feature isn't used.

LOCAL assigns a GTID including the replica's own UUID (the server_uuid setting).

Passing a parameter that is a UUID assigns a GTID that includes the specified UUID, such as the server_uuid setting for the replication source server.

Examples

To turn off this feature:

mysql> call mysql.rds_assign_gtids_to_anonymous_transactions('OFF');
+-------------------------------------------------------------+
| Message  |
+-------------------------------------------------------------+
| ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS has been set to: OFF |
+-------------------------------------------------------------+
1 row in set (0.07 sec)

To use the replica's own UUID:

mysql> call mysql.rds_assign_gtids_to_anonymous_transactions('LOCAL');
+---------------------------------------------------------------+
| Message  |
+---------------------------------------------------------------+
| ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS has been set to: LOCAL |
+---------------------------------------------------------------+
1 row in set (0.07 sec)

To use a specified UUID:

mysql> call mysql.rds_assign_gtids_to_anonymous_transactions('317a4760-f3dd-3b74-8e45-0615ed29de0e');
+----------------------------------------------------------------------------------------------+
| Message |
+----------------------------------------------------------------------------------------------+
| ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS has been set to: 317a4760-f3dd-3b74-8e45-0615ed29de0e |
+----------------------------------------------------------------------------------------------+
1 row in set (0.07 sec)

mysql.rds_gtid_purged (Aurora MySQL version 3)

Sets the global value of the system variable gtid_purged to a given global transaction identifier (GTID) set. The gtid_purged system variable is a GTID set that consists of the GTIDs of all transactions that have been committed on the server, but don't exist in any binary log file on the server.

To allow compatibility with MySQL 8.0, there are two ways to set the value of gtid_purged:

Syntax

To replace the value of gtid_purged with your specified GTID set:

CALL mysql.rds_gtid_purged (gtid_set);

To append the value of gtid_purged to your specified GTID set:

CALL mysql.rds_gtid_purged (+gtid_set);

Parameters

Usage notes

The master user must run the mysql.rds_gtid_purged procedure.

This procedure is supported for Aurora MySQL version 3.04 and higher.

Examples

The following example assigns the GTID 3E11FA47-71CA-11E1-9E33-C80AA9429562:23 to the gtid_purged global variable.

CALL mysql.rds_gtid_purged('3E11FA47-71CA-11E1-9E33-C80AA9429562:23');

mysql.rds_skip_transaction_with_gtid (Aurora MySQL version 2 and 3)

Skips replication of a transaction with the specified global transaction identifier (GTID) on an Aurora primary instance.

You can use this procedure for disaster recovery when a specific GTID transaction is known to cause a problem. Use this stored procedure to skip the problematic transaction. Examples of problematic transactions include transactions that disable replication, delete important data, or cause the DB instance to become unavailable.

Syntax

CALL mysql.rds_skip_transaction_with_gtid (
gtid_to_skip
);

Parameters

Usage notes

The master user must run the mysql.rds_skip_transaction_with_gtid procedure.

This procedure is supported for Aurora MySQL version 2 and 3.

Examples

The following example skips replication of the transaction with the GTID 3E11FA47-71CA-11E1-9E33-C80AA9429562:23.

CALL mysql.rds_skip_transaction_with_gtid('3E11FA47-71CA-11E1-9E33-C80AA9429562:23');

mysql.rds_start_replication_until_gtid (Aurora MySQL version 3)

Initiates replication from an Aurora MySQL DB cluster and stops replication immediately after the specified global transaction identifier (GTID).

Syntax

CALL mysql.rds_start_replication_until_gtid(gtid);

Parameters

Usage notes

The master user must run the mysql.rds_start_replication_until_gtid procedure.

This procedure is supported for Aurora MySQL version 3.04 and higher.

The mysql.rds_start_replication_until_gtid stored procedure isn't supported for managed replication, which includes the following:

When the gtid parameter specifies a transaction that has already been run by the replica, replication is stopped immediately.

Examples

The following example initiates replication and replicates changes until it reaches GTID 3E11FA47-71CA-11E1-9E33-C80AA9429562:23.

call mysql.rds_start_replication_until_gtid('3E11FA47-71CA-11E1-9E33-C80AA9429562:23');