Configure MySQL GTID Consistency

Enforcing Global Transaction Identifier (GTID) consistency on a MySQL source server ensures that every transaction is assigned a unique identifier, simplifying replication management and preventing data drift. This article provides a straightforward, step-by-step guide to configuring your MySQL source server to enforce GTID consistency, covering the required configuration file updates and the runtime commands needed for a seamless transition.

Step 1: Update the MySQL Configuration File

To make the GTID configuration permanent, you must edit the MySQL configuration file (my.cnf on Linux or my.ini on Windows). Open the file in a text editor and locate the [mysqld] section.

Add or modify the following configuration directives:

[mysqld]
# Enable binary logging (required for GTID)
log_bin = mysql-bin

# Enable GTID mode
gtid_mode = ON

# Enforce GTID consistency
enforce_gtid_consistency = ON

The enforce_gtid_consistency variable ensures that only statements that can be safely logged using GTID are allowed. This prevents operations that are incompatible with GTIDs, such as creating temporary tables inside transactions.

Step 2: Restart the MySQL Service

For the configuration file changes to take effect, restart the MySQL service. Use the appropriate command for your operating system:

On Linux (systemd):

sudo systemctl restart mysql

On Windows:

net stop mysql
net start mysql

Step 3: Online Configuration (Without Downtime)

If you are running MySQL 5.7.6 or later, you can transition to GTID mode online without restarting the server. To safely enforce GTID consistency on a live server, execute the following SQL commands:

Set the consistency enforcement to warning mode to check for incompatible statements in your application logs:
```
SET GLOBAL enforce_gtid_consistency = WARN;
```
Once you verify there are no compatibility warnings, enforce GTID consistency:
```
SET GLOBAL enforce_gtid_consistency = ON;
```

Enable GTID mode in stages:

SET GLOBAL gtid_mode = OFF_PERMISSIVE;
SET GLOBAL gtid_mode = ON_PERMISSIVE;
SET GLOBAL gtid_mode = ON;

Note: You must still update the my.cnf or my.ini file as shown in Step 1 to ensure these settings persist after a server restart.

Step 4: Verify GTID Status

To confirm that GTID consistency is successfully enforced on your source server, log into the MySQL command-line interface and run the following query:

SHOW VARIABLES LIKE '%gtid%';

Verify that the output displays the following values:

gtid_mode is ON
enforce_gtid_consistency is ON

With these settings active, your MySQL source server is now safely enforcing GTID consistency and is ready for secure, transaction-based replication.