Movatterモバイル変換

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:

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

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_mode andenforce_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. SeeSection 5.1.8.1, “System Variable Privileges”. In previous versions, 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_mode could be set toON orOFF, which corresponded to whether GTIDs were used to identify transactions or not. Whengtid_mode=ON it is not possible to replicate anonymous transactions, and whengtid_mode=OFF only anonymous transactions can be replicated. As of MySQL 5.7.6, thegtid_mode variable has two additional states,OFF_PERMISSIVE andON_PERMISSIVE. Whengtid_mode=OFF_PERMISSIVE thennew transactions are anonymous while permitting replicated transactions to be either GTID or anonymous transactions. Whengtid_mode=ON_PERMISSIVE thennew 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 source withgtid_mode=ON could be replicating to a replica withgtid_mode=ON_PERMISSIVE. The valid values forgtid_mode are as follows and in this order:

It is important to note that the state ofgtid_mode can only be changed by one step at a time based on the above order. For example, ifgtid_mode is currently set toOFF_PERMISSIVE, it is possible to change toOFF orON_PERMISSIVE but 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=ON andgtid_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.

As part of the changes introduced by MySQL 5.7.6, the fields related to GTIDs have been modified so that they 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_SET in thereplication_connection_status Performance Schema table, and the GTID related results ofSHOW SLAVE STATUS, now return the empty string when there are no GTIDs present. Fields that display a single GTID, such asCURRENT_TRANSACTION in the Performance Schemareplication_applier_status_by_worker table, now displayANONYMOUS when GTID transactions are not being used.

Replication from a source usinggtid_mode=ON provides the ability to use auto-positioning, configured using theCHANGE 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, seeSection 16.1.4.2, “Enabling GTID Transactions Online”. The valid combinations ofgtid_mode and auto-positioning on source and replica are shown in the following table, where the source'sgtid_mode is shown on the horizontal and the replica'sgtid_mode is on the vertical:

In the above table, the entries are:

The currently selectedgtid_mode also impacts on thegtid_next variable. The following table shows the behavior of the server for the different values ofgtid_mode andgtid_next.

In the above table, the entries are:

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