19.1.4.1 Replication Mode Concepts

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:

• Y: The values of gtid_mode on the source and on the replica are compatible.

• N: The values of gtid_mode on the source and on the replica are not compatible.

• *: Auto-positioning can be used with this combination of values.

Table 19.1 Valid Combinations of Source and Replica gtid_mode

gtid_mode	Source OFF	Source OFF_PERMISSIVE	Source ON_PERMISSIVE	Source ON
Replica OFF	Y	Y	N	N
Replica OFF_PERMISSIVE	Y	Y	Y	Y*
Replica ON_PERMISSIVE	Y	Y	Y	Y*
Replica ON	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.