17.1.3.3 GTID Auto-Positioning

GTIDs replace the file-offset pairs previously required to determine points for starting, stopping, or resuming the flow of data between master and slave. When GTIDs are in use, all the information that the slave needs for synchronizing with the master is obtained directly from the replication data stream.

To start a slave using GTID-based replication, you do not include MASTER_LOG_FILE or MASTER_LOG_POS options in the CHANGE MASTER TO statement used to direct the slave to replicate from a given master. These options specify the name of the log file and the starting position within the file, but with GTIDs the slave does not need this nonlocal data. Instead, you need to enable the MASTER_AUTO_POSITION option. For full instructions to configure and start masters and slaves using GTID-based replication, see Section 17.1.3.4, “Setting Up Replication Using GTIDs”.

The MASTER_AUTO_POSITION option is disabled by default. If multi-source replication is enabled on the slave, you need to set the option for each applicable replication channel. Disabling the MASTER_AUTO_POSITION option again makes the slave revert to file-based replication, in which case you must also specify one or both of the MASTER_LOG_FILE or MASTER_LOG_POS options.

When a replication slave has GTIDs enabled (GTID_MODE=ON, ON_PERMISSIVE, or OFF_PERMISSIVE ) and the MASTER_AUTO_POSITION option enabled, auto-positioning is activated for connection to the master. The master must have GTID_MODE=ON set in order for the connection to succeed. In the initial handshake, the slave sends a GTID set containing the transactions that it has already received, committed, or both. This GTID set is equal to the union of the set of GTIDs in the gtid_executed system variable (@@GLOBAL.gtid_executed), and the set of GTIDs recorded in the Performance Schema replication_connection_status table as received transactions (the result of the statement SELECT RECEIVED_TRANSACTION_SET FROM PERFORMANCE_SCHEMA.replication_connection_status).

The master responds by sending all transactions recorded in its binary log whose GTID is not included in the GTID set sent by the slave. This exchange ensures that the master only sends the transactions with a GTID that the slave has not already received or committed. If the slave receives transactions from more than one master, as in the case of a diamond topology, the auto-skip function ensures that the transactions are not applied twice.

If any of the transactions that should be sent by the master have been purged from the master's binary log, or added to the set of GTIDs in the gtid_purged system variable by another method, the master sends the error ER_MASTER_HAS_PURGED_REQUIRED_GTIDS to the slave, and replication does not start. The GTIDs of the missing purged transactions are identified and listed in the master's error log in the warning message ER_FOUND_MISSING_GTIDS. The slave cannot recover automatically from this error because parts of the transaction history that are needed to catch up with the master have been purged. Attempting to reconnect without the MASTER_AUTO_POSITION option enabled only results in the loss of the purged transactions on the slave. The correct approach to recover from this situation is for the slave to replicate the missing transactions listed in the ER_FOUND_MISSING_GTIDS message from another source, or for the slave to be replaced by a new slave created from a more recent backup. Consider revising the binary log expiration period (binlog_expire_logs_seconds) on the master to ensure that the situation does not occur again.

If during the exchange of transactions it is found that the slave has received or committed transactions with the master's UUID in the GTID, but the master itself does not have a record of them, the master sends the error ER_SLAVE_HAS_MORE_GTIDS_THAN_MASTER to the slave and replication does not start. This situation can occur if a master that does not have sync_binlog=1 set experiences a power failure or operating system crash, and loses committed transactions that have not yet been synchronized to the binary log file, but have been received by the slave. The master and slave can diverge if any clients commit transactions on the master after it is restarted, which can lead to the situation where the master and slave are using the same GTID for different transactions. The correct approach to recover from this situation is to check manually whether the master and slave have diverged. If the same GTID is now in use for different transactions, you either need to perform manual conflict resolution for individual transactions as required, or remove either the master or the slave from the replication topology. If the issue is only missing transactions on the master, you can make the master into a slave instead, allow it to catch up with the other servers in the replication topology, and then make it a master again if needed.