SoFunction
Updated on 2025-03-03

Specific use of MySQL gtid

1. What is GTID

Starting from MySQL 5.6.5, a GTID-based replication method has been added.GTID (Global Transaction ID) is a global transaction ID, a unique identifier that is bound to the transaction generated on the main library. This identifier is not only unique on the main library, but also unique in the MySQL replication environment.

2. GTID format and meaning

GTID = source_uuid:transaction_id

source_uuid is the server_uuid that initiates the transaction server, which is the unique identifier of a MySQL instance, and can be used throughshow variables like 'server_uuid'Check. transaction_id represents the number of transactions that have been submitted on this instance, and is a monotonically increasing sequence number, starting from 1, 1-2 represents the second transaction; 1-n represents n transactions.

In the GTID replication topology, the transaction's GTID remains unchanged no matter how many times the transaction is copied. Once a transaction is submitted on the server, all subsequent transactions with the same GTID will be ignored. This mechanism can effectively avoid duplicate replication and keep the data consistent.

Example 1：7a7f2A47-71CA-11E1-9E33-00163e429562:1Meaning: 7a7f2A47-71CA-11E1-9E33-00163e429562 is the server_uuid of this node. 1 is the transaction number of the first transaction submitted on this node. If 10 transactions are submitted, the GTID will be: 7a7f2A47-71CA-11E1-9E33-00163e429562:1-10

【If there are multiple ranges to represent, the range of transaction number can be usedcolonSeparation】
Example 2：7a7f2A47-71CA-11E1-9E33-00163e429562:1-3:11:47-49Meaning: The 1-3 transaction, 11th transaction, 47-49th transaction from the 7a7f2A47-71CA-11E1-9E33-00163e429562 server was executed

[GTID sets can also represent transaction sets from different data sources]
Example 3：7a7f2A47-71CA-11E1-9E33-00163e429562:1-77439,82b6d927-81cc-11ee-9edf-00163e202091:1-98191Meaning: The 1-77439th transaction from the 7a7f2A47-71CA-11E1-9E33-00163e429562 server and the 1-98191st transaction from the 82b6d927-81cc-11ee-9edf-00163e202091 server were executed

MySQL 8.3 Innovation was released on January 16, 2024, which extends MySQL
The global transaction identifier (GTID) format is used in replication and group replication, and supports tagging of GTIDs to support identifying transaction groups. This enhancement can be GTID for a specific transaction group
Assign a unique identifier. For example: transactions containing data operations can be easily distinguished from transactions generated by management operations, only by comparing their GTIDs.
Format with labelsThe extended GTID format is source_id:<tag>:transaction_id, where tag is any string with a maximum length of 8 characters. Enable by setting the value of the system variable gtid_next to automatic: or setting gtid_next to uuid::transaction_id to set the uuid of a single transaction to any value and assign it a custom tag.

3. GTID-related parameters

Check out the system parameters about gtid:

mysql> show variables like '%gtid%';
+-----------------------------------+-----------------------------------------------+
| Variable_name                     | Value                                         |
+-----------------------------------+-----------------------------------------------+
| binlog_gtid_simple_recovery       | ON                                            |
| enforce_gtid_consistency          | ON                                            |
| gtid_executed                     | be423c64-90c8-11ed-bf93-00163e0fcdea:1-1169392|
| gtid_executed_compression_period  | 1000                                          |
| gtid_mode                         | ON                                            |
| gtid_next                         | AUTOMATIC                                     |
| gtid_owned                        |                                               |
| gtid_purged                       | be423c64-90c8-11ed-bf93-00163e0fcdea:1-1137671|
| session_track_gtids               | OFF                                           |
+-----------------------------------+-----------------------------------------------+
9 row in set (0.00 sec)

3.1 gtid_mode

【Function】Control whether to enable GTID mode logging and transaction types that can be included in the log
【Optional Value】

  • OFF: Disable GTID master-slave replication, and new and replicated transactions must be anonymous transactions.
  • ON: Enable GTID master-slave replication, but the GTID set is not automatically created. Both newly added and copied transactions must be GTID transactions.
  • ON_PERMISSIVE: Enable GTID master-slave replication and allow automatic creation of GTID sets of GTIDs in the slave library. The newly added transaction is a GTID transaction, and the copied transaction can be anonymous transaction or a GTID transaction. (This option is not directly supported in the standard MySQL version)
  • ON_ENFORCING: Enable GTID master-slave replication and require the GTID set to be created manually by the slave library.

3.2 enforce_gtid_consistency

【Function】Specify the consistency requirements when executing transactions from the library, and control whether SQL statements only allow violations of GTID consistency
【Optional Value】

  • OFF: No consistency requirements when executing transactions from the library.
  • ON: Consistency requirements when executing transactions from the library. (usually set to ON to ensure consistency)
  • WARN: Allows violation of GTID consistency, but generates alarm messages.

Turn on this parameter server to allow only GTID-safe statements to execute, preventing some unsafe statements from causing GTID copy to fail. It must be set to true before enabling GTID mode replication. When set to true, the following operations will no longer be available:

  • create table … select statement (after MySQL8.0.21, if the engine supports atomic DDL, you can use this statement).
  • Create temporary table and drop temporary table within a transaction.
  • Update transactions or statements that are both transaction tables and non-transaction tables.

The GTID compatibility of the enforce_gtid_consistency verification statement is inWrite binary logsWhen the binary log is disabled on the server or the filter deletes the statement without writing the binary log, it will not be checked.

3.3 gtid_executed

Global variables (@@global.gtid_executed), contains the GTID set of all transactions executed by the server and the variables that gtid_purged are set.
@@global.gtid_executedandshow master statusorshow slave statusStatement outputexecuted_gtid_setThe fields have the same values.
When the server starts, it willbinlog_gtid_simple_reocoveryParameter selection different methods to initialize their values.

3.4 gtid_next

gtid_nextis a session-level variable that represents the GTID that the next transaction will be allocated. When a transaction is committed in the main library, the system will automatically assign a new GTID. When the transaction is copied to the slave library, the GTID assigned by the master will be retained instead of the new GTID assigned by the slave library. GTID can be set to the following values:

  • automatic: The system automatically generates GTID for transactions

  • anonymous: Transactions do not have GTID (anonymous transaction), and can only be located by filename and offset

  • UUID:NUMBER: Clearly set a valid GTID, and the next transaction will assign this GTID.

The function of this parameter is alsogtid_modeThe influence of parameters, ifgtid_modeSet asOFF(Only anonymous transactions), then this parameter is invalid.

【Note】IfSERVER_UUID:TRANSACTION_NUMBERThe transaction has been assigned manually, regardless of transaction commit or rollback, it must be manually set again before starting the next transaction.gtid_nextor set toautomatic。

3.5 gtid_purged

gtid_purgedis a global variable representing the GTID set of all transactions committed on the server but not in the binary log.gtid_purgedyesgtid_exeuctedsubset of .

GTID in gtid_purged contains the following categories:

  • Disable binarylog from the library, the committed transaction GTID will also be added to gtid_purged.
  • Enable binarylog from the library, and the transaction is also written to the binary log, but the log file is purged (deleted).
  • passset @@global.gtid_purgedStatement displays added to the collection.

gtid_purgedandgtid_executedLikewise, the server will start or restart according tobinlog_gtid_simple_reocoveryParameter selection different methods to initialize their values.

If you require filtering certain transactions, you can manually set the gtid_purged values, which will be added to gtid_executed even if the transactions are never executed.
There are two ways to set gtid_purged:

  • SET @@global.gtid_purged = 'gtid_set'– Replacement

  • SET @@global.gtid_purged = '+gtid_set'– Additional

binlog_gtid_simple_reocoveryControls how to retrieve binary logs for calculations when MySQL starts or restartsgtid_executedandgtid_purgedThe initial value of the variable. whenbinlog_gtid_simple_recovery=trueWhen only the oldest and latest binlog files are read, otherwise iterate through all binlog files.

3.6 gtid_owned

The gtid representing the transaction currently being executed. Global or session-level read-only variables, usually only used internally (its output may not be very intuitive for the average user), helps MySQL manage and track transactions currently being processed.

  • Global level: Contains the list of all GTIDs that the server is using and the corresponding Thread ID.
  • Session Level: Contains a single GTID that the session is currently using.

Since it is global and session-level, what it displays may vary depending on the current session and global state.

3.7 gtid_executed_compression_period

Control compressionmysql.gtid_executedThe number of transaction thresholds for the table.

mysql> show variables like 'gtid_executed_compression_period';
+-----------------------------------+---------+
| Variable_name                     | Value   |
+-----------------------------------+---------+
| gtid_executed_compression_period  | 1000    |
+-----------------------------------+---------+
1 row in set (0.00 sec)

The default value of this parameter is 1000, which means that the compression of the gtid_executed table is executed after every 1000 transactions is executed. The method is to merge transactions like source_id (only effective when the binary log is not enabled). When the binary log is turned on, this parameter is invalid and compressed only when the binary log is rotated.

3.8 binlog_gtid_simple_recovery

binlog_gtid_simple_recoveryIt is an important system variable in MySQL. It is used to control how to traverse binary log files (binlogs) to find global transaction identifiers (GTIDs) when MySQL is started or restarted. This variable is particularly important for environments that use GTID for replication, as it affects how MySQL initializes and validates GTID collections.

【Release introduction】In MySQL version 5.7.5, this variable issimplified_binlog_gtid_recoveryThe form of the 'MySQL version 5.7.6' was renamed tobinlog_gtid_simple_recovery。

  • whenbinlog_gtid_simple_recovery=FALSEhour:
  • gtid_executedInitialization: MySQL willup to dateThe binlog file starts,Reverse traversalto the first one includingPrevious_gtids_log_eventorGtid_log_eventfile. Read all GTIDs from this file and store them in internal variables and then calculategtid_executedvalue.
  • gtid_purgedInitialization: MySQL willEarliestThe binlog file starts,Forward traversalto the first one containing non-emptyPrevious_gtids_log_eventOr at least oneGtid_log_eventfile. Calculate based on these eventsgtid_purgedvalue.
  • [Performance Impact]: If the number of binlog files is large and there are no GTID events, this process can be very time-consuming.
  • whenbinlog_gtid_simple_recovery=TRUEWhen (the default value for MySQL 5.7.7 and later versions):
    gtid_executedandgtid_purgedInitialization: MySQLIterate through only the latest and earliest binlog filesand according to thePrevious_gtids_log_eventorGtid_log_eventTo calculategtid_executedandgtid_purgedvalue.
    【Performance Advantages】: This method significantly reduces the number of traversing binlog files, thereby improving performance during MySQL startup and restart.

3.9 session_track_gtids

session_track_gtidsis a system variable in MySQL that controls whether the status of the global transaction identifier (GTID) is tracked in a MySQL session. This variable is especially important for a MySQL environment that uses GTID for replication and read-write separation, as it can help ensure data consistency and accuracy.

【Optional Value】:

  • OFF: Default value, indicating that GTID is not tracked. In this mode, the MySQL session does not record status information related to GTID.
  • OWN_GTID: Indicates tracking all new GTIDs submitted by the current session after the last feedback. This option is mainly used to track GTIDs generated within the session so that when needed, it can accurately identify which GTIDs are generated by the current session.
  • ALL_GTIDS: means to return directlygtid_executedThe value of the system variable, that is, read all executed GTIDs after the current session transaction has completed the commit. This option provides a more comprehensive GTID tracking capability, but may introduceAdditional performance overhead(There may be additional CPU and memory overhead due to the need to read and record all executed GTIDs).

[Configuration Recommendations] In most cases, if complex read-write separation or data consistency verification is not required, it can be maintained.OFFTo reduce performance overhead. If you need these functions, you can choose according to the actual situation.OWN_GTIDorALL_GTIDS。

3.10 Other related parameters

Although it is not directly related to the configuration of GTID, the following parameters are also very important when configuring GTID master-slave replication:

  • server-id: Specify the unique ID number of the MySQL server. Each MySQL server (including the master and slave) must have a different ID number.
  • log-bin: Enable binary logging function, which is the basis for MySQL master-slave replication.
  • binlog_format: Specifies the format of the binary log. For GTID master-slave copying, it is usually set to ROW to record row-level changes.

4. GTID life cycle

4.1 Generation and allocation of GTIDs

  • Transaction commit and GTID allocation: When a transaction is executed and committed on the main library, MySQL will automatically assign a GTID to the transaction.
  • Write to binary log: After the GTID is allocated, the GTID will be written to the binary log file of the main library as a Gtid_log_event event (the GTID and the transaction itself) when the transaction is committed.
    This is an atomic operation to ensure that the GTID and transaction content are written to the log at the same time.

4.2 2. Persistence and Recording of GTID

In a short time after the transaction is committed, write the GTID to the @@global.gtid_executed state variable. This variable contains all executed transactions and represents the current state of the main library.

  • Writemysql.gtid_executedsurface: When the binary log switches or the MySQL server is shut down, all GTIDs written to the binary log will be written tomysql.gtid_executedin the table to achieve persistence of GTID.
  • renewGLOBAL.gtid_executedSystem variables: MySQL will also add this GTID to the transaction when committing@@GLOBAL.gtid_executedin system variables. This variable is a GTID collection that represents all transactions that have been executed so far.

4.3 GTID transmission and application

  • Transfer to slave library: During the master-slave replication process, the binary log containing the GTID will be transferred to the slave library and stored in the relay log of the slave library (relay log)middle.
  • Apply GTID transactions from the library: Read the GTID in the relay log from the library and transfer itgtid_nextThe variable is set to this GTID, indicating the next upcoming transaction from the library. Then, the transaction corresponding to the GTID is executed from the library, and a check will be performed before execution to ensure that this GTID is not executed and that no other sessions currently read this GTID.
    If multiple sessions read this GTID, only 1 can be executed, and the others will be blocked. From the librarygtid_ownedSystem variables@@global.gtid_ownedShows the currently used GTID and the thread ID that owns it. If the GTID has been used, the transaction is ignored by the automatic skip function and no error is thrown.
    If this GITD has not been executed, the slave library applies this transaction and uses the GTID generated by the master library, and will not be reassigned.
  • Record GTID from library: If binary logging is enabled from the library, it will also write the GTID to its own binary log file through the gtid_log_event atomic event. If not enabled, when the logs are rotated or the server is shut down, the GTID will be logged inmysql.gtid_executedIn the table (when the binary log is turned onmysql.gtid_executedDoes not represent the latest status of the server).
    Write GTID to the slave library within a short time after committing from the library@@global.gtid_executedStatus variables (recommended to always query@@global.gtid_executedCheck the server's status)

4.4 Cleaning and resetting of GTID

  • Clean up GTID_PURGED: If the binary log is deleted, the GTID information of the deleted part will be updated to@@GLOBAL.GTID_PURGEDmiddle. This variable is@@GLOBAL.gtid_executedsubset of , representing the collection of GTIDs that have been cleaned.
  • Reset GTID information:useRESET MASTERThe command will be cleared@@global.gtid_purged、@@global.gtid_executed、mysql.gtid_executedTable and clean binlog.
    andRESET SLAVEandRESET SLAVE ALLThe command will be cleaned@@global.gtid_executedand@@global.gtid_purged。

4.5 Parallel copying of GTID

Multithreaded replication: If multithreaded replication is enabled (slave_parallel_workers > 0), MySQL will execute GTID transactions in parallel. at this time,gtid_executedThe values ​​of variables may have GAP, and the system will automatically update these values ​​to reflect the state of parallel execution.

5. Automatic GTID positioning

GTID automatic positioning is an important feature in MySQL replication. It allows replicas (‌slave) to automatically locate to the correct location of the master library for data synchronization without manually specifying log files and locations, greatly simplifying the management and switching process of master-slave replication.
When replicating using GTID, the ‌copy is automatically connected to the main library by enabling the SOURCE_AUTO_POSITION or MASTER_AUTO_POSITION options. ‌This option allows the replica to send a GTID collection containing transactions that have been received, committed, or both during the initial handshake, so that the replica can automatically locate to the correct location for data synchronization. ‌

CHANGE MASTER TO 
MASTER_HOST='master_host_name', 
MASTER_USER='replication_user_name', 
MASTER_PASSWORD='replication_password', 
MASTER_AUTO_POSITION = 1;

MASTER_AUTO_POSITION = 1 means that the slave should automatically locate the master's GTID.
If you already have a configured slave and want to enable or change the automatic GTID positioning, you can use the following command:

STOP SLAVE;
CHANGE MASTER TO 
MASTER_AUTO_POSITION = 1;
START SLAVE;

6. GTID replication monitoring and management

6.1 mysql.gtid_executed table

mysql.gtid_executedThe table records the GTID that is the transaction that has been executed on the server. The structure of the table is as follows:

+------------------+-------------+------+-------+------------+-----------+
| Field            | Type        | Null | Key   | Default    | Extra     |
+------------------+-------------+------+-------+------------+-----------+
| source_uuid      | char(36)    | NO   | PRI   | NULL       |           |
+------------------+-------------+------+-------+------------+-----------+
| interval_start   | bigint      | NO   | PRI   | NULL       |           |
+------------------+-------------+------+-------+------------+-----------+
| interval_end     | bigint      | NO   |       | NULL       |           |
+------------------+-------------+------+-------+------------+-----------+
3 rows in set (0.00 sec)

So when binary logging is enabled,mysql.gtid_executedThe table does not save the latest executed transaction GTID, the latest status can begtid_executedGlobal system variables (@@global.gtid_executed) provides, the variable is updated after each transaction is committed. To save space, MySQL servers are compressed regularlymysql.gtid_executedTable, by representing multiple rows as a single row GTID set.

6.2 Monitoring and replicating heartbeat

After the copy is established, the master library will send a heartbeat signal to the slave library regularly (even if no transaction is sent). The heartbeat can be changed master to the change master when establishing the master-slave copy.master_heartbeat_periodclause specified. If not specified, the default is half the connection timeout from the library:

mysql> show variables like 'slave_net_timeout';
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| slave_net_timeout | 10      |
+-------------------+---------+
1 row in set (0.00 sec)

The last heartbeat time can be passedperformance_schema.replication_connection_statusThe tablelast_heartbeat_timestampCheck

This is the end of this article about the specific use of MySQL gtid. For more related content of MySQL gtid, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!