MySQL GTID Management

Database Technologies
1

MySQL GTID Management

GTID (Global Transaction ID) is a feature introduced in MySQL 5.6.5, which ensures that each transaction committed on the primary database has a unique ID in the cluster. This feature reinforces standby consistency, fault recovery, and fault tolerance of the database. In this article, we will delve into the GTID management process, its limitations, and solutions.

Understanding GTID

GTID is a global transaction ID that is generated when a transaction is committed on the primary database or application. This ID can be used to locate and track every transaction, making it a significant feature for DBAs. With GTID, you can easily build replication without manual intervention to find the offset value. In the event of a fault, you can employ the MASTER_AUTO_POSITION option to recover the database.

From an architectural design perspective, GTID is a good way to practice distributed ID management. There are two basic requirements for distributed ID:

  1. Globally Unique: The ID should be unique across the entire system, making it easy to identify in a distributed environment.
  2. Trends in Increments: The ID should follow a trend, making it easier to extract information when necessary.

The industry has adopted the Twitter-based ID generation algorithm, known as Snowflake, to generate GTIDs. This approach provides a elegant distributed design.

Enabling GTID

To enable GTID, you need to configure a few parameters in the my.cnf file:

  1. log-bin = mysql-bin
  2. binlog_format = row
  3. log_slave_updates = 1
  4. gtid_mode = ON
  5. enforce_gtid_consistency = ON

The log_slave_updates option is not mandatory in MySQL 5.7, as the introduction of the gtid_executed table in the MySQL database provides a solution.

GTID Limitations and Solutions

While GTID is well-developed in MySQL 5.7, there are some limitations to its use. Two notable limitations are:

  1. Create Table Statements: The CREATE TABLE ... SELECT statement is not supported with GTID, as it will be split into two parts: CREATE TABLE and INSERT. To overcome this limitation, you can use the CREATE TABLE LIKE statement, followed by an INSERT statement.
  2. Temporary Tables: When using GTID, you cannot create temporary tables with autocommit = 1, as this will prevent the GTID information from being synchronized to the slave. However, you can create temporary tables with autocommit = 0, and then drop them when you are finished.

Viewing GTID from Different Perspectives

To understand GTID, it is essential to view it from different perspectives:

  1. Variable View: GTID variables include executed_gtid_set, gtid_purged, gtid_next, and retrieved_gtid_set.
  2. Table and File View: The gtid_executed table in the MySQL database stores the GTID information, which is written to the binlog file. The gtid_executed_compression_period parameter controls the number of transactions per execution, and the gtid_executed_compression_period thread manages the compression of the gtid_executed table.
  3. Operational View: The operational view of GTID includes the gtid_next variable, which is used to specify the next GTID, and the MASTER_AUTO_POSITION option, which is used to recover the database in the event of a fault.

In conclusion, GTID is a powerful feature in MySQL that ensures the consistency and fault tolerance of the database. While there are some limitations to its use, there are also solutions available to overcome these limitations. By understanding GTID from different perspectives, you can effectively manage and maintain your MySQL database.