Documentation Home
MySQL Replication
Related Documentation Download this Excerpt
PDF (US Ltr) - 1.6Mb
PDF (A4) - 1.6Mb
HTML Download (TGZ) - 362.1Kb
HTML Download (Zip) - 368.1Kb


MySQL Replication  /  ...  /  Replication From a Source Without GTIDs to a Replica With GTIDs

2.3.6 Replication From a Source Without GTIDs to a Replica With GTIDs

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 in Section 2.4, “Changing GTID Mode on Online Servers”, use that approach instead. This feature is designed for replication source servers where you cannot enable GTIDs.

You can enable GTID assignment on a replication channel using the ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS option of the CHANGE REPLICATION SOURCE TO statement. LOCAL assigns a GTID including the replica's own UUID (the server_uuid setting). uuid assigns a GTID including the specified UUID, such as the server_uuid setting 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 with ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS on 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 use ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS on any channel.

The replica must have gtid_mode=ON set, and this cannot be changed afterwards. If the replica server is started without GTIDs enabled and with ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS set 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 use ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS, and channels that do not. Channels specific to Group Replication cannot use ASSIGN_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.

Using ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS on 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 with ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS should not be transferred to another server or compared with another server's gtid_executed set. 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.

A replication channel using ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS has 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=1 is set.

  • The SOURCE_LOG_FILE and SOURCE_LOG_POS options of the CHANGE REPLICATION SOURCE TO statement are used to position the replication I/O thread, rather than the MASTER_AUTO_POSITION option.

  • The SET GLOBAL sql_slave_skip_counter statement is used to skip transactions on a replication channel set up with ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS, rather than the CHANGE REPLICATION SOURCE TO statement. For instructions, see Section 2.7.3, “Skipping Transactions”.

  • The UNTIL SQL_BEFORE_GTIDS and UNTIL_SQL_AFTER_GTIDS options of the START REPLICA statement 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 replacement WAIT_FOR_EXECUTED_GTID_SET(), which works across the server, can be used.

The Performance Schema table replication_applier_configuration shows 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. A RESET SLAVE ALL statement resets the ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS setting, but a RESET SLAVE statement does not.