MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
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 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.
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).
assigns a GTID
including the specified UUID, such as the
uuid
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.
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 17.1.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.