MySQL 8.0 Release Notes
MySQL 8.0 Source Code Documentation
From MySQL 8.0.23, you can set up replication channels to assign a GTID to replicated transactions that do not already have one. This feature enables replication from a source server that does not have GTIDs enabled and does not use GTID-based replication, to a replica that has GTIDs enabled. If it is possible to enable GTIDs on the replication source server, as described inSection 17.1.4, “Changing GTID Mode on Online Servers”, use that approach instead. This feature is designed for replication source servers where you cannot enable GTIDs. Note that as is standard for MySQL replication, this feature does not support replication from MySQL source servers earlier than the previous release series, so MySQL 5.7 is the earliest supported source for a MySQL 8.0 replica.
You can enable GTID assignment on a replication channel using theASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONSoption of theCHANGE REPLICATION SOURCE TOstatement.LOCALassigns a GTID including the replica's own UUID (theserver_uuidsetting).uuidserver_uuidsetting for the replication source server. Using a nonlocal UUID lets you differentiate between transactions that originated on the replica and transactions that originated on the source, and for a multi-source replica, between transactions that originated on different sources. If any of the transactions sent by the source do have a GTID already, that GTID is retained.
Important
A replica set up withASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONSon any channel cannot be promoted to replace the replication source server in the event that a failover is required, and a backup taken from the replica cannot be used to restore the replication source server. The same restriction applies to replacing or restoring other replicas that useASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONSon any channel.
The replica must havegtid_mode=ONset, and this cannot be changed afterwards, unless you remove theASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS=ONsetting. If the replica server is started without GTIDs enabled and withASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONSset for any replication channels, the settings are not changed, but a warning message is written to the error log explaining how to change the situation.
For a multi-source replica, you can have a mix of channels that useASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS, and channels that do not. Channels specific to Group Replication cannot useASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS, but an asynchronous replication channel for another source on a server instance that is a Group Replication group member can do so. For a channel on a Group Replication group member, do not specify the Group Replication group name as the UUID for creating the GTIDs.
UsingASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONSon a replication channel is not the same as introducing GTID-based replication for the channel. The GTID set (gtid_executed) from a replica set up withASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONSshould not be transferred to another server or compared with another server'sgtid_executedset. The GTIDs that are assigned to the anonymous transactions, and the UUID you choose for them, only have significance for that replica's own use. The exception to this is any downstream replicas of the replica where you enabledASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS, and any servers that were created from a backup of that replica.
If you set up any downstream replicas, these servers do not haveASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONSenabled. Only the replica that is receiving transactions directly from the non-GTID source server needs to haveASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONSset on the relevant replication channel. Among that replica and its downstream replicas, you can compare GTID sets, fail over from one replica to another, and use backups to create additional replicas, as you would in any GTID-based replication topology.ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONSis used where transactions are received from a non-GTID server outside this group.
A replication channel usingASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONShas the following behavior differences to GTID-based replication:
GTIDs are assigned to the replicated transactions when they are applied (unless they already had a GTID). A GTID would normally be assigned on the replication source server when the transaction is committed, and sent to the replica along with the transaction. On a multi-threaded replica, this means the order of the GTIDs does not necessarily match the order of the transactions, even if
slave-preserve-commit-order=1is set.The
SOURCE_LOG_FILEandSOURCE_LOG_POSoptions of theCHANGE REPLICATION SOURCE TOstatement are used to position the replication I/O (receiver) thread, rather than theSOURCE_AUTO_POSITIONoption.The
SET GLOBAL sql_replica_skip_counterorSET GLOBAL sql_slave_skip_counterstatement is used to skip transactions on a replication channel set up withASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS, rather than the method of committing empty transactions. For instructions, seeSection 17.1.7.3, “Skipping Transactions”.The
UNTIL SQL_BEFORE_GTIDSandUNTIL_SQL_AFTER_GTIDSoptions of theSTART REPLICAstatement cannot be used for the channel.The function
WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS(), which is deprecated from MySQL 8.0.18, cannot be used with the channel. Its replacementWAIT_FOR_EXECUTED_GTID_SET(), which works across the server, can be used to wait for any downstream replicas of the server that hasASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONSenabled. To wait for the channel withASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONSenabled to catch up with the source, which does not use GTIDs, use theSOURCE_POS_WAIT()function (from MySQL 8.0.26) or theMASTER_POS_WAIT()function.
The Performance Schema tablereplication_applier_configurationshows whether GTIDs are assigned to anonymous transactions on a replication channel, what the UUID is, and whether it is the UUID of the replica server (LOCAL) or a user-specified UUID (MANUAL). The information is also recorded in the applier metadata repository. ARESET REPLICA ALLstatement resets theASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONSsetting, but aRESET REPLICAstatement does not.