Shulou(Shulou.com)06/01 Report--

Summary: when using GTIDs, each transaction can be identified and tracked because it is committed on the original server and is applied by any slave; to put it simply, all transactions committed by master are applied once in slaves, and their data can be consistent. In addition, transactions of the same GTID can only be committed once on the same server. Format: GTID is represented as a pair of coordinates, separated by a colon (:) GTID = source_id:transaction_idsource_id to identify the original server. Typically, the server's server_uuid is used for this purpose. Transaction_id is a sequence number determined by the order in which transactions are committed on the server; for example, the transaction_id of the first transaction to commit is 1, while the transaction_id of the tenth transaction committed on the same source server is 10. It is not possible for a transaction to use 0 as the sequence number in GTID. For example, the 23rd transaction committed on the server that originally used UUID 3E11FA47-71CA-11E1-9E33-C80AA9429562 has the following GTID:

3E11FA47-71CA-11E1-9E33-C80AA9429562:23 as written in the output of statements such as SHOW MASTER STATUS or SHOW SLAVE STATUS, a series of GTID from the same server can be collapsed into a single expression, as shown here

3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5

The example just shown represents the first to fifth transactions originating from the MySQL server, whose server_uuid is 3E11FA47-71CA-11E1-9E33-C80AA9429562. Life cycle: transactions are executed and committed on the master. This client transaction is assigned a GTID, which consists of the UUID of the master and the smallest non-zero transaction sequence number that has not been used on this server. GTID is written to the binary log of master (immediately before the transaction itself in the log). If a client transaction is not written to the binary log (for example, because the transaction has been filtered out, or the transaction is read-only), it will not be assigned GTID. If GTID is assigned to the transaction, the GTID is persisted atomically at commit time by writing it to the binary log at the beginning of the transaction (as Gtid_log_event). Whenever the binary log is rotated or the server is shut down, the server writes the GTID of all transactions written to the previous binary log file to the mysql.gtid_ executed table. If GTID is assigned to the transaction, gtid_executed is externalized non-atomically (shortly after the transaction is committed) by adding GTID to the GTIDs set in the GTID system variable (@ @ GLOBAL.gtid_executed). This GTID set contains a representation of all committed GTID transaction sets and is used as a token to indicate the status of the server in replication. Enable binary logging (as required by master), the GTID set in the gtid_executed system variable is the full record of the application transaction, but the mysql.gtid_executed table is not, because the latest history record still transfers the binary log data to slave in the current binary log file and stores it in slave's relay log, and then slave reads GTID and sets the value of its gtid_next system variable to this GTID. This tells slave that the next transaction must be recorded using this GTID. It is worth noting that slave sets gtid_next in the context of the session. Slave verifies that no thread takes ownership of the GTID in the gtid_next to process the transaction. By first reading and checking the GTID of the replication transaction, before processing the transaction itself, the slave ensures that not only the previous transaction with this GTID is not applied on the slave, but that no other session has read the GTID but has not committed the related transaction. Therefore, if multiple clients try to apply the same transaction at the same time, the server allows only one of them to execute, thus resolving this problem. The gtid_owned system variable of slave (@ @ GLOBAL.gtid_owned) shows each GTID currently in use and the ID of the thread that owns it. If GTID is already used, no error is raised, and the transaction is ignored using the automatic skip feature. If GTID is not already used, slave applies the replicated transaction. Because gtid_next is set to the GTID assigned by master, slave does not attempt to generate a new GTID for this transaction, but uses the GTID stored in gtid_next. If binary logging is enabled on slave, GTID persists atomically on commit by writing it to the binary log (as Gtid_log_event) at the beginning of the transaction. Whenever the binary log is rotated or the server is shut down, the server writes the GTID of all transactions written to the previous binary log file to the mysql.gtid_ executed table. If binary logging is disabled on slave, GTID is atomically preserved by writing GTID directly to the mysql.gtid_ executed table. MySQL adds a statement to the transaction to insert GTID into the table. In this case, the mysql.gtid_ executed table is a complete record of the transactions applied on the slave. Note that in MySQL 5.7, inserting GTID into a 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 may become inconsistent. Starting with MySQL 8.0, the operations of both DDL and DML statements are atomic. Shortly after the replication transaction is committed on slave, GTID is externalized in a non-atomic manner by adding GTID to the GTIDs collection in the gtid_executed system variable (@ @ global.gtid_execute) of slave. For master, this GTID collection contains a representation of the set of all committed GTID transactions. If binary logging is disabled on slave, the mysql.gtid_ executed table is also a complete record of transactions applied on slave. If binary logging is enabled on slave, it means that some GTIDs is recorded only in binary logs, and the GTIDs set in the gtid_executed system variable is the only complete record.

Reference: https://dev.mysql.com/doc/refman/5.7/en/replication-gtids.html

PREV: 3: add a slave to the existing replication environment (based on binary log file location) https://blog.51cto.com/itzhoujun/2351567

NEXT:5: replicating https://blog.51cto.com/itzhoujun/2352693 with GTID

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.