17.1.5.1 Replication Mode Concepts

Contents

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.

When using GTIDs you can take advantage of 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. With GTIDs enabled you cannot use sql_slave_skip_counter, instead use empty transactions.

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 privileges sufficient to set global system variables. See Section 5.1.9.1, “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 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 Performance Schema replication_applier_status_by_worker 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:

Y: the gtid_mode of master and slave is compatible
N: the gtid_mode of master and slave is not compatible
*: auto-positioning can be used with this combination

Table 17.1 Valid Combinations of Master and Slave gtid_mode

`gtid_mode`	Master `OFF`	Master `OFF_PERMISSIVE`	Master `ON_PERMISSIVE`	Master `ON`
Slave `OFF`	Y	Y	N	N
Slave `OFF_PERMISSIVE`	Y	Y	Y	Y*
Slave `ON_PERMISSIVE`	Y	Y	Y	Y*
Slave `ON`	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 execute SET GTID_NEXT.
UUID:NUMBER: generate a GTID with the specified UUID:NUMBER.
New GTID: generate a GTID with an automatically generated number.

Table 17.2 Valid Combinations of gtid_mode and gtid_next

	`gtid_next` AUTOMATIC binary log on	`gtid_next` AUTOMATIC binary log off	`gtid_next` ANONYMOUS	`gtid_next` UUID:NUMBER
`gtid_mode` `OFF`	ANONYMOUS	ANONYMOUS	ANONYMOUS	Error
`gtid_mode` `OFF_PERMISSIVE`	ANONYMOUS	ANONYMOUS	ANONYMOUS	UUID:NUMBER
`gtid_mode` `ON_PERMISSIVE`	New GTID	ANONYMOUS	ANONYMOUS	UUID:NUMBER
`gtid_mode` `ON`	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.

Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-replication-mode-change-online-concepts.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut