10bet网址
MySQL Replication
Related Documentation Download this Excerpt
PDF (US Ltr)- 1.7Mb
PDF (A4)- 1.7Mb


2.4.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 formUUID: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 anAnonymous_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 useWAIT_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 anAnonymous_gtid_log_event.

The ability to configure the replication mode online means that thegtid_modeandenforce_gtid_consistencyvariables 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. SeeSystem 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 versionsgtid_modecould be set toONorOFF, which corresponded to whether GTIDs were used to identify transactions or not. Whengtid_mode=ONit is not possible to replicate anonymous transactions, and whengtid_mode=OFFonly anonymous transactions can be replicated. Whengtid_mode=OFF_PERMISSIVEthennewtransactions are anonymous while permitting replicated transactions to be either GTID or anonymous transactions. Whengtid_mode=ON_PERMISSIVEthennewtransactions 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 withgtid_mode=ONcould be replicating to a replica withgtid_mode=ON_PERMISSIVE. The valid values forgtid_modeare as follows and in this order:

  • OFF

  • OFF_PERMISSIVE

  • ON_PERMISSIVE

  • ON

It is important to note that the state ofgtid_modecan only be changed by one step at a time based on the above order. For example, ifgtid_modeis currently set toOFF_PERMISSIVE, it is possible to change toOFForON_PERMISSIVEbut not toON. 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 betweengtid_mode=ONandgtid_mode=OFF, the GTID state (in other words the value ofgtid_executed) is persistent. This ensures that the GTID set that has been applied by the server is always retained, regardless of changes between types ofgtid_mode.

The fields related to GTIDs display the correct information regardless of the currently selectedgtid_mode. This means that fields which display GTID sets, such asgtid_executed,gtid_purged,RECEIVED_TRANSACTION_SETin thereplication_connection_statusPerformance Schema table, and the GTID related results ofSHOW 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 asCURRENT_TRANSACTIONin the Performance Schemareplication_applier_status_by_workertable, now displayANONYMOUSwhen GTID transactions are not being used.

Replication from a source usinggtid_mode=ONprovides the ability to use GTID auto-positioning, configured using theSOURCE_AUTO_POSITIONof theCHANGE REPLICATION SOURCE TOstatement (from MySQL 8.0.23), or theMASTER_AUTO_POSITIONoption of theCHANGE MASTER TOstatement (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, see2.4.2节,“使交易GTID在线”.

The valid combinations ofgtid_modeand auto-positioning on source and replica are shown in the following table, where the source'sgtid_modeis shown on the horizontal and the replica'sgtid_modeis on the vertical. The meaning of each entry is as follows:

  • Y: thegtid_modeof source and replica is compatible

  • N: thegtid_modeof source and replica is not compatible

  • *: auto-positioning can be used with this combination

Table 2.1 Valid Combinations of Source and Replica gtid_mode

gtid_mode

SourceOFF

SourceOFF_PERMISSIVE

SourceON_PERMISSIVE

SourceON

ReplicaOFF

Y

Y

N

N

ReplicaOFF_PERMISSIVE

Y

Y

Y

Y*

ReplicaON_PERMISSIVE

Y

Y

Y

Y*

ReplicaON

N

N

Y

Y*


The currently selectedgtid_modealso impacts on thegtid_nextvariable. The following table shows the behavior of the server for the different values ofgtid_modeandgtid_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

gtid_nextAUTOMATIC

binary log on

gtid_nextAUTOMATIC

binary log off

gtid_nextANONYMOUS

gtid_nextUUID:NUMBER

gtid_modeOFF

ANONYMOUS

ANONYMOUS

ANONYMOUS

Error

gtid_modeOFF_PERMISSIVE

ANONYMOUS

ANONYMOUS

ANONYMOUS

UUID:NUMBER

gtid_modeON_PERMISSIVE

New GTID

ANONYMOUS

ANONYMOUS

UUID:NUMBER

gtid_modeON

New GTID

ANONYMOUS

Error

UUID:NUMBER


When the binary log is off andgtid_nextis set toAUTOMATIC, then no GTID is generated. This is consistent with the behavior of previous versions.