17.1.5.1 Replication Mode Concepts

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 a
Gtid_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.

Transactions in a relay log that was received from a master
running a previous version of MySQL may not be preceded by any
particular event at all, but after being replayed and logged in
the slave'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 the
SYSTEM_VARIABLES_ADMIN or
SUPER privilege. In MySQL 5.6 and
below, 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 master with
gtid_mode=ON could be replicating
to a slave 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 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 replication_applier_status_by_worker
Performance Schema table, now display
ANONYMOUS when GTID transactions are not being
used.

Replication from a master using
gtid_mode=ON provides the ability
to use auto-positioning, configured using the CHANGE
MASTER TO MASTER_AUTO_POSITION = 1; statement. 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. An error is
generated if auto-positioning is enabled and an anonymous
transaction is encountered. It is strongly recommended to ensure
there are no anonymous transactions remaining in the topology
before enabling auto-positioning, see
Section 17.1.5.2, “Enabling GTID Transactions Online”.

The valid combinations of
gtid_mode and auto-positioning on
master and slave are shown in the following table, where the
master's gtid_mode is shown
on the horizontal and the slave's
gtid_mode is on the vertical. The
meaning of each entry is as follows:

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: