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 masters and replicas are consistent.

You can use GTID-based replication to replicate data with Amazon RDS
MySQL read replicas or with an external MySQL database. For RDS 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.

Parameters for GTID-Based Replication

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.

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 or Aurora MySQL cluster. Both of these values cause your RDS
DB instance or 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 RDS DB instances or 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.

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.

Create one or more read replicas of the DB instance. For more information on
how to do so, see Creating a Read Replica.

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 in the MySQL documentation.

Configuring
GTID-Based Replication for Existing Read Replicas

For an existing RDS 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

If the DB instance or any read replica is using RDS MySQL version 5.7.22 or
lower, upgrade the DB instance or read replica. Upgrade to RDS MySQL version
5.7.23 or a later MySQL 5.7 version.

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.

Reset the GTID parameters for GTID-based replication that allows anonymous
transactions until the read replicas have processed all of them.

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

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.

Wait for all of your anonymous transactions to be replicated. To check
that these are replicated, do the following:

Run the following statement on your primary DB instance.

SHOW MASTER STATUS;

Note the values in the File and Position
columns.

On each read replica, use the file and position information from its
master 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.

Reset the GTID parameters for GTID-based replication only.

Make sure that the parameter group associated with the DB instance and
each read replica has the following parameter settings: