MySQL 9.0 Reference Manual Including MySQL NDB Cluster 9.0
Before setting the replication mode of an online server, it is important to understand some key concepts of replication. This section explains these concepts and is essential reading before attempting to modify the replication mode of an online server.
The modes of replication available in MySQL rely on different techniques for identifying logged transactions. The types of transactions used by replication are listed here:
A GTID transaction is identified by a global transaction
identifier (GTID) which takes one of two forms:
UUID:NUMBER or
UUID:TAG:NUMBER. Every GTID transaction in
the binary log is preceded by a
Gtid_log_event. A GTID transaction can be
addressed either by its GTID, or by the name of the file in
which it is logged and its position within that file.
An anonymous transaction has no GTID; MySQL 9.0
ensures that every anonymous transaction in a log is preceded
by an Anonymous_gtid_log_event. (In old
versions of MySQL, an anonymous transaction was not preceded
by any particular event.) An anonymous transaction can be
addressed by file name and position only.
When using GTIDs you can take advantage of GTID auto-positioning
and automatic failover, and use
WAIT_FOR_EXECUTED_GTID_SET(),
session_track_gtids, and
Performance Schema tables to monitor replicated transactions (see
Section 29.12.11, “Performance Schema Replication Tables”).
A transaction in a relay log from a source running a previous
version of MySQL might not be preceded by any particular event,
but after being replayed and recorded in the replica's binary
log, it is preceded with an
Anonymous_gtid_log_event.
To change the replication mode online, it is necessary to set the
gtid_mode and
enforce_gtid_consistency
variables using an account that has privileges sufficient to set
global system variables; see
Section 7.1.9.1, “System Variable Privileges”. Permitted values for
gtid_mode are listed here, in
order, with their meanings:
OFF: Only anonymous transactions can be
replicated.
OFF_PERMISSIVE: New transactions are
anonymous; replicated transactions may be either GTID or
anonymous.
ON_PERMISSIVE: New transactions use GTIDs;
replicated transactions may be either GTID or anonymous.
ON: All transaction must have GTIDs;
anonymous transactions cannot be replicated.
It is possible to have servers using anonymous and servers using
GTID transactions in the same replication topology. For example, a
source where gtid_mode=ON can
replicate to a replica where
gtid_mode=ON_PERMISSIVE.
Replication from a source using
gtid_mode=ON provides the ability
to use GTID auto-positioning, configured using the
SOURCE_AUTO_POSITION option of
the CHANGE REPLICATION SOURCE TO
statement. The replication topology in use has an impact on
whether it is possible to enable auto-positioning or not, since
this feature relies on GTIDs and is not compatible with anonymous
transactions. It is strongly recommended to ensure there are no
anonymous transactions remaining in the topology before enabling
auto-positioning; see
Section 19.1.4.2, “Enabling GTID Transactions Online”.
Valid combinations of gtid_mode
and auto-positioning on source and replica are shown in the next
table. The meaning of each entry is as follows:
Table 19.1 Valid Combinations of Source and Replica gtid_mode
Source |
Source |
Source |
Source |
|
|---|---|---|---|---|
Replica |
Y |
Y |
N |
N |
Replica |
Y |
Y |
Y |
Y* |
Replica |
Y |
Y |
Y |
Y* |
Replica |
N |
N |
Y |
Y* |
The current value of gtid_mode
also affects gtid_next. The next
table shows the behavior of the server for combinations of
different values of gtid_mode and
gtid_next. The meaning of each
entry is as follows:
ANONYMOUS: Generate an anonymous
transaction.
Error: Generate an error, and do not
execute SET GTID_NEXT.
UUID:NUMBER: Generate a GTID with the
specified UUID:NUMBER.
UUID:TAG:NUMBER: Generate a GTID with the
specified UUID:TAG:NUMBER.
New GTID: Generate a GTID with an
automatically generated number.
Table 19.2 Valid Combinations of gtid_mode and gtid_next
gtid_next = AUTOMATIC (binary log
on) |
gtid_next = AUTOMATIC (binary log
off) |
gtid_next = AUTOMATIC:<TAG> |
gtid_next = ANONYMOUS |
gtid_next =
<UUID>:<NUMBER> |
gtid_next =
<UUID>:<TAG>:<NUMBER> |
|
|---|---|---|---|---|---|---|
gtid_mode = OFF |
ANONYMOUS | ANONYMOUS | Error | ANONYMOUS | Error | Error |
gtid_mode = OFF_PERMISSIVE |
ANONYMOUS | ANONYMOUS | Error | ANONYMOUS | <UUID>:<NUMBER> | <UUID>:<TAG>:<NUMBER> |
gtid_mode = ON_PERMISSIVE |
New GTID | ANONYMOUS | New GTID | ANONYMOUS | <UUID>:<NUMBER> | <UUID>:<TAG>:<NUMBER> |
gtid_mode = ON |
New GTID | ANONYMOUS | New GTID | Error | <UUID>:<NUMBER> | <UUID>:<TAG>:<NUMBER> |
When binary logging is not in use and
gtid_next is
AUTOMATIC, then no GTID is generated.