MySQL GTID Tags — What you need to know
MySQL Innovate Release 8.3 introduction of GTID (Global Transaction Identifiers) tags in replication processes is a significant enhancement in MySQL management and operations. This new functionality, provides users with the ability to tag groups of transactions for easier identification. Here’s a guide into what GTID tags means, their benefits, and how to utilize them.
Understanding GTID Tags
The GTID tag extends the format of GTIDs in MySQL Replication and Group Replication, allowing for the assignment of a unique name to groups of transactions. This feature enables users to distinguish between different types of transactions, such as differentiating data operations from administrative operations like an ad-hoc data archiving, through the use of tags.
The format for the new GTID is UUID:<TAG>:NUMBER, where <TAG> is an arbitrary string that can be up to 32 characters long. This tagging is enabled by setting the gtid_next system variable to AUTOMATIC:<TAG>, which persists for all transactions originating in the current session unless changed or disconnected.
Setting Up GTID Tags
To configure GTID tags, you need to modify the gtid_next system variable either to AUTOMATIC:<TAG> for session-persistent tags or to <UUID>:<TAG>:NUMBER for assigning a custom UUID and tag to a single transaction. Here are examples of how to apply both settings:
- For session-persistent tags:
SET gtid_next = 'AUTOMATIC:myTag';- For a single transaction with a custom UUID and tag:
SET gtid_next = 'a_uuid_here:customTag:1';After setting up the gtid_next variable as desired, you can proceed with your transactions, and the specified tags will be applied accordingly.
New Privileges and Security
The introduction of GTID tags also brings a new privilege, TRANSACTION_GTID_TAG, necessary for setting the gtid_next to either AUTOMATIC:<TAG> or <UUID>:<TAG>:NUMBER.
mysql> SET gtid_next = 'AUTOMATIC:myTag';
ERROR 6038 (HY000): Access denied; you need the TRANSACTION_GTID_TAG and at least one of the: SYSTEM_VARIABLES_ADMIN, SESSION_VARIABLES_ADMIN or REPLICATION_APPLIER privilege(s) for this operation
mysql>This means that only users or roles with this privilege can assign tags, adding a layer of security and control over transaction tagging.
Use Cases and Benefits
The primary benefit of using GTID tags is the ability to organize and identify transactions more efficiently. For instance, in a replication setup, an administrator can easily filter and manage transactions based on their tags, improving operations like auditing.
Users can even segregate apps, by setting each application to use it’s own tag. Making it easy to identify the source of a particular transaction.
GTID tags also facilitate troubleshooting and analysis by allowing administrators to quickly identify and focus on specific groups of transactions. For applications involving complex transaction flows or multiple databases, GTID tags can simplify monitoring and management tasks.
Example
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 | 1376 | | | 2d85947c-b474-11ee-bee5-6fdc058076cc:1-7 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SET gtid_next = 'AUTOMATIC:bulkInsert';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO tb1 VALUES (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT INTO tb1 VALUES (4),(5),(6);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+---------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+---------------------------------------------------------+
| binlog.000001 | 1980 | | | 2d85947c-b474-11ee-bee5-6fdc058076cc:1-7:bulkinsert:1-2 |
+---------------+----------+--------------+------------------+---------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)After adding the bulkInsert tag and creating the new transaction, Executed_Gtid_Set now reports the set of transaction executed from the newly added tag.
mysql> SET gtid_next = 'AUTOMATIC:bulkDelete';
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM tb1 WHERE ID <= 3;
Query OK, 3 rows affected (0.00 sec)
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+----------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+----------------------------------------------------------------------+
| binlog.000001 | 2282 | | | 2d85947c-b474-11ee-bee5-6fdc058076cc:1-7:bulkdelete:1:bulkinsert:1-2 |
+---------------+----------+--------------+------------------+----------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)You can have multiple tags grouped in the same UUID. In the example above we have the sequence without tag, builkInsert and builDelete sequences.
Conclusion
GTID tags in MySQL enhance replication and transaction management by introducing a flexible, secure way to tag and identify transactions. This functionality streamlines database operations, particularly in complex environments, and offers administrators greater control and insight into their data. As MySQL continues to evolve, features like GTID tags underscore its commitment to providing powerful tools for database management and optimization.