Movatterモバイル変換

1. A transaction is executed and committed on the replication source server. This client transaction is assigned a GTID composed of the source's UUID and the smallest nonzero transaction sequence number not yet used on this server. The GTID is written to the source's binary log (immediately preceding the transaction itself in the log). If a client transaction is not written to the binary log (for example, because the transaction was filtered out, or the transaction was read-only), it is not assigned a GTID.

2. If a GTID was assigned for the transaction, the GTID is persisted atomically at commit time by writing it to the binary log at the beginning of the transaction (as aGtid_log_event). Whenever the binary log is rotated or the server is shut down, the server writes GTIDs for all transactions that were written into the previous binary log file into themysql.gtid_executed table.

3. If a GTID was assigned for the transaction, the GTID is externalized non-atomically (very shortly after the transaction is committed) by adding it to the set of GTIDs in thegtid_executed system variable (@@GLOBAL.gtid_executed). This GTID set contains a representation of the set of all committed GTID transactions, and it is used in replication as a token that represents the server state. With binary logging enabled (as required for the source), the set of GTIDs in thegtid_executed system variable is a complete record of the transactions applied, but themysql.gtid_executed table is not, because the most recent history is still in the current binary log file.

4. After the binary log data is transmitted to the replica and stored in the replica's relay log (using established mechanisms for this process, seeSection 16.2, “Replication Implementation”, for details), the replica reads the GTID and sets the value of itsgtid_next system variable as this GTID. This tells the replica that the next transaction must be logged using this GTID. It is important to note that the replica setsgtid_next in a session context.

5. The replica verifies that no thread has yet taken ownership of the GTID ingtid_next in order to process the transaction. By reading and checking the replicated transaction's GTID first, before processing the transaction itself, the replica guarantees not only that no previous transaction having this GTID has been applied on the replica, but also that no other session has already read this GTID but has not yet committed the associated transaction. So if multiple clients attempt to apply the same transaction concurrently, the server resolves this by letting only one of them execute. Thegtid_owned system variable (@@GLOBAL.gtid_owned) for the replica shows each GTID that is currently in use and the ID of the thread that owns it. If the GTID has already been used, no error is raised, and the auto-skip function is used to ignore the transaction.

6. If the GTID has not been used, the replica applies the replicated transaction. Becausegtid_next is set to the GTID already assigned by the source, the replica does not attempt to generate a new GTID for this transaction, but instead uses the GTID stored ingtid_next.

7. If binary logging is enabled on the replica, the GTID is persisted atomically at commit time by writing it to the binary log at the beginning of the transaction (as aGtid_log_event). Whenever the binary log is rotated or the server is shut down, the server writes GTIDs for all transactions that were written into the previous binary log file into themysql.gtid_executed table.

8. If binary logging is disabled on the replica, the GTID is persisted atomically by writing it directly into themysql.gtid_executed table. MySQL appends a statement to the transaction to insert the GTID into the table. In this situation, themysql.gtid_executed table is a complete record of the transactions applied on the replica. Note that in MySQL 5.7, the operation to insert the GTID into the table is atomic for DML statements, but not for DDL statements, so if the server exits unexpectedly after a transaction involving DDL statements, the GTID state might become inconsistent. From MySQL 8.0, the operation is atomic for DDL statements as well as for DML statements.

9. Very shortly after the replicated transaction is committed on the replica, the GTID is externalized non-atomically by adding it to the set of GTIDs in thegtid_executed system variable (@@GLOBAL.gtid_executed) for the replica. As for the source, this GTID set contains a representation of the set of all committed GTID transactions. If binary logging is disabled on the replica, themysql.gtid_executed table is also a complete record of the transactions applied on the replica. If binary logging is enabled on the replica, meaning that some GTIDs are only recorded in the binary log, the set of GTIDs in thegtid_executed system variable is the only complete record.

The typical scenario is that the server generates a new GTID for a committed transaction. However, GTIDs can also be assigned to other changes besides transactions, and in some cases a single transaction can be assigned multiple GTIDs.

Every database change (DDL or DML) that is written to the binary log is assigned a GTID. This includes changes that are autocommitted, and changes that are committed usingBEGIN andCOMMIT orSTART TRANSACTION statements. A GTID is also assigned to the creation, alteration, or deletion of a database, and of a non-table database object such as a procedure, function, trigger, event, view, user, role, or grant.

Non-transactional updates as well as transactional updates are assigned GTIDs. In addition, for a non-transactional update, if a disk write failure occurs while attempting to write to the binary log cache and a gap is therefore created in the binary log, the resulting incident log event is assigned a GTID.

When a table is automatically dropped by a generated statement in the binary log, a GTID is assigned to the statement. Temporary tables are dropped automatically when a replica begins to apply events from a source that has just been started, and when statement-based replication is in use (binlog_format=STATEMENT) and a user session that has open temporary tables disconnects. Tables that use theMEMORY storage engine are deleted automatically the first time they are accessed after the server is started, because rows might have been lost during the shutdown.

When a transaction is not written to the binary log on the server of origin, the server does not assign a GTID to it. This includes transactions that are rolled back and transactions that are executed while binary logging is disabled on the server of origin, either globally (with--skip-log-bin specified in the server's configuration) or for the session (SET @@SESSION.sql_log_bin = 0). This also includes no-op transactions when row-based replication is in use (binlog_format=ROW).

XA transactions are assigned separate GTIDs for theXA PREPARE phase of the transaction and theXA COMMIT orXA ROLLBACK phase of the transaction. XA transactions are persistently prepared so that users can commit them or roll them back in the case of a failure (which in a replication topology might include a failover to another server). The two parts of the transaction are therefore replicated separately, so they must have their own GTIDs, even though a non-XA transaction that is rolled back would not have a GTID.

In the following special cases, a single statement can generate multiple transactions, and therefore be assigned multiple GTIDs:

By default, for new transactions committed in user sessions, the server automatically generates and assigns a new GTID. When the transaction is applied on a replica, the GTID from the server of origin is preserved. You can change this behavior by setting the session value of thegtid_next system variable:

Note that after you setgtid_next to a specific GTID, and the transaction has been committed or rolled back, an explicitSET @@SESSION.gtid_next statement must be issued before any other statement. You can use this to set the GTID value back toAUTOMATIC if you do not want to assign any more GTIDs explicitly.

When replication applier threads apply replicated transactions, they use this technique, setting@@SESSION.gtid_next explicitly to the GTID of the replicated transaction as assigned on the server of origin. This means the GTID from the server of origin is retained, rather than a new GTID being generated and assigned by the replica. It also means the GTID is added togtid_executed on the replica even when binary logging or replica update logging is disabled on the replica, or when the transaction is a no-op or is filtered out on the replica.

It is possible for a client to simulate a replicated transaction by setting@@SESSION.gtid_next to a specific GTID before executing the transaction. This technique is used bymysqlbinlog to generate a dump of the binary log that the client can replay to preserve GTIDs. A simulated replicated transaction committed through a client is completely equivalent to a replicated transaction committed through a replication applier thread, and they cannot be distinguished after the fact.

The set of GTIDs in thegtid_purged system variable (@@GLOBAL.gtid_purged) contains the GTIDs of all the transactions that have been committed on the server, but do not exist in any binary log file on the server.gtid_purged is a subset ofgtid_executed. The following categories of GTIDs are ingtid_purged:

You can change the value ofgtid_purged in order to record on the server that the transactions in a certain GTID set have been applied, although they do not exist in any binary log on the server. When you add GTIDs togtid_purged, they are also added togtid_executed. An example use case for this action is when you are restoring a backup of one or more databases on a server, but you do not have the relevant binary logs containing the transactions on the server. In MySQL 5.7, you can only change the value ofgtid_purged whengtid_executed (and thereforegtid_purged) is empty. For details of how to do this, see the description forgtid_purged.

The sets of GTIDs in thegtid_executed andgtid_purged system variables are initialized when the server starts. Every binary log file begins with the eventPrevious_gtids_log_event, which contains the set of GTIDs in all previous binary log files (composed from the GTIDs in the preceding file'sPrevious_gtids_log_event, and the GTIDs of everyGtid_log_event in the preceding file itself). The contents ofPrevious_gtids_log_event in the oldest and most recent binary log files are used to compute thegtid_executed andgtid_purged sets at server startup:

If binary logs from MySQL 5.7.7 or older are involved in these computations, it is possible for incorrect GTID sets to be computed forgtid_executed andgtid_purged, and they remain incorrect even if the server is later restarted. For details, see the description for thebinlog_gtid_simple_recovery system variable, which controls how the binary logs are iterated to compute the GTID sets. If one of the situations described there applies on a server, setbinlog_gtid_simple_recovery=FALSE in the server's configuration file before starting it. That setting makes the server iterate all the binary log files (not just the newest and oldest) to find where GTID events start to appear. This process could take a long time if the server has a large number of binary log files without GTID events.

If you need to reset the GTID execution history on a server, use theRESET MASTER statement. For example, you might need to do this after carrying out test queries to verify a replication setup on new GTID-enabled servers, or when you want to join a new server to a replication group but it contains some unwanted local transactions that are not accepted by Group Replication.

Before issuingRESET MASTER, ensure that you have backups of the server's binary log files and binary log index file, if any, and obtain and save the GTID set held in the global value of thegtid_executed system variable (for example, by issuing aSELECT @@GLOBAL.gtid_executed statement and saving the results). If you are removing unwanted transactions from that GTID set, usemysqlbinlog to examine the contents of the transactions to ensure that they have no value, contain no data that must be saved or replicated, and did not result in data changes on the server.

When you issueRESET MASTER, the following reset operations are carried out:

Note thatRESET MASTER is the method to reset the GTID execution history even if the server is a replica where binary logging is disabled.RESET SLAVE has no effect on the GTID execution history.