MySQL 8.3 Release Notes
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:NUMBERorUUID:TAG:NUMBER. Every GTID transaction in the binary log is preceded by aGtid_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 8.3 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 executeSET 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.