To be able to safely configure 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 transactions which are logged. The types of transactions used by replication are as follows:
GTID transactions are identified by a global transaction identifier (GTID) in the form
UUID:NUMBER
. Every GTID transaction in a log is always preceded by aGtid_log_event
. GTID transactions can be addressed using either the GTID or using the file name and position.Anonymous transactions do not have a GTID assigned, and MySQL ensures that every anonymous transaction in a log is preceded by an
Anonymous_gtid_log_event
. In previous versions, anonymous transactions were not preceded by any particular event. Anonymous transactions can only be addressed using file name and position.
When using GTIDs you can take advantage of GTID auto-positioning
and automatic fail-over, as well as use
WAIT_FOR_EXECUTED_GTID_SET()
,
session_track_gtids
, and monitor
replicated transactions using Performance Schema tables.
Transactions in a relay log that was received from a source
running a previous version of MySQL may not be preceded by any
particular event at all, but after being replayed and logged in
the replica's binary log, they are preceded with an
Anonymous_gtid_log_event
.
The ability to configure the replication mode online means that
the gtid_mode
and
enforce_gtid_consistency
variables are now both dynamic and can be set from a top-level
statement by an account that has privileges sufficient to set
global system variables. See
System Variable Privileges. In MySQL 5.6 and
earlier, both of these variables could only be configured using
the appropriate option at server start, meaning that changes to
the replication mode required a server restart. In all versions
gtid_mode
could be set to
ON
or OFF
, which
corresponded to whether GTIDs were used to identify transactions
or not. When gtid_mode=ON
it is
not possible to replicate anonymous transactions, and when
gtid_mode=OFF
only anonymous
transactions can be replicated. When
gtid_mode=OFF_PERMISSIVE
then
new transactions are anonymous while
permitting replicated transactions to be either GTID or anonymous
transactions. When
gtid_mode=ON_PERMISSIVE
then
new transactions use GTIDs while permitting
replicated transactions to be either GTID or anonymous
transactions. This means it is possible to have a replication
topology that has servers using both anonymous and GTID
transactions. For example a source with
gtid_mode=ON
could be replicating
to a replica with
gtid_mode=ON_PERMISSIVE
. The
valid values for gtid_mode
are as
follows and in this order:
OFF
OFF_PERMISSIVE
ON_PERMISSIVE
ON
It is important to note that the state of
gtid_mode
can only be changed by
one step at a time based on the above order. For example, if
gtid_mode
is currently set to
OFF_PERMISSIVE
, it is possible to change to
OFF
or ON_PERMISSIVE
but not
to ON
. This is to ensure that the process of
changing from anonymous transactions to GTID transactions online
is correctly handled by the server. When you switch between
gtid_mode=ON
and
gtid_mode=OFF
, the GTID state (in
other words the value of
gtid_executed
) is persistent.
This ensures that the GTID set that has been applied by the server
is always retained, regardless of changes between types of
gtid_mode
.
The fields related to GTIDs display the correct information
regardless of the currently selected
gtid_mode
. This means that fields
which display GTID sets, such as
gtid_executed
,
gtid_purged
,
RECEIVED_TRANSACTION_SET
in the
replication_connection_status
Performance Schema table, and the GTID related results of
SHOW REPLICA
STATUS
(or before MySQL 8.0.22,
SHOW SLAVE
STATUS
), now return the empty string when there are no
GTIDs present. Fields that display a single GTID, such as
CURRENT_TRANSACTION
in the Performance Schema
replication_applier_status_by_worker
table, now display ANONYMOUS
when GTID
transactions are not being used.
Replication from a source using
gtid_mode=ON
provides the ability
to use GTID auto-positioning, configured using the
SOURCE_AUTO_POSITION
of the
CHANGE REPLICATION SOURCE TO
statement (from MySQL 8.0.23), or the
MASTER_AUTO_POSITION
option of the
CHANGE MASTER TO
statement (before
MySQL 8.0.23). The replication topology being used impacts on
whether it is possible to enable auto-positioning or not, as 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 2.4.2, “Enabling GTID Transactions Online”.
The valid combinations of
gtid_mode
and auto-positioning on
source and replica are shown in the following table, where the
source's gtid_mode
is shown
on the horizontal and the replica's
gtid_mode
is on the vertical. The
meaning of each entry is as follows:
Table 2.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 currently selected gtid_mode
also impacts on the gtid_next
variable. The following table shows the behavior of the server for
the 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 fail to executeSET GTID_NEXT
.UUID:NUMBER
: generate a GTID with the specified UUID:NUMBER.New GTID
: generate a GTID with an automatically generated number.
Table 2.2 Valid Combinations of gtid_mode and gtid_next
binary log on |
binary log off |
|
|
|
---|---|---|---|---|
|
ANONYMOUS |
ANONYMOUS |
ANONYMOUS | Error |
|
ANONYMOUS |
ANONYMOUS |
ANONYMOUS |
UUID:NUMBER |
|
New GTID |
ANONYMOUS |
ANONYMOUS |
UUID:NUMBER |
|
New GTID |
ANONYMOUS |
Error |
UUID:NUMBER |
When the binary log is off and
gtid_next
is set to
AUTOMATIC
, then no GTID is generated. This is
consistent with the behavior of previous versions.