Online Enabling and Disabling of GTID Replication in MySQL

MySQL supports two replication positioning methods: binlog file/position and GTID (global transaction ID). GTID offers advantages for master‑slave failover and troubleshooting, and can be toggled online in MySQL 5.7.6+ using the GTID_MODE and ENFORCE_GTID_CONSISTENCY variables.

Online Enabling of GTID

1. Set ENFORCE_GTID_CONSISTENCY to WARN

This allows GTID‑inconsistent statements on the primary while only emitting warnings in the error log, enabling observation before full activation.

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;

2. Set ENFORCE_GTID_CONSISTENCY to ON

After confirming no warnings, enforce GTID consistency; statements like CREATE TABLE AS SELECT will now error if unsupported.

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;

3. Set GTID_MODE to OFF_PERMISSIVE

SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;

4. Set GTID_MODE to ON_PERMISSIVE

SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;

5. Ensure all anonymous transactions have been replayed

Check that ONGOING_ANONYMOUS_TRANSACTION_COUNT is 0 or that performance_schema.replication_applier_status_by_worker shows only GTID transactions.

SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';
SELECT * FROM performance_schema.replication_applier_status_by_worker;

6. Flush logs to start a new binlog containing only GTID transactions

FLUSH LOGS;

7. Set GTID_MODE to ON

SET @@GLOBAL.GTID_MODE = ON;
SELECT @@GTID_MODE, @@ENFORCE_GTID_CONSISTENCY;

8. Persist GTID settings in my.cnf

gtid-mode = ON
enforce-gtid-consistency = 1

9. Switch replication to GTID mode

STOP SLAVE;
CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
START SLAVE;
SHOW SLAVE STATUS\G

Online Disabling of GTID

The reverse process mirrors the enable steps:

Convert replication back to position‑based mode using CHANGE MASTER TO MASTER_AUTO_POSITION = 0, MASTER_LOG_FILE='...', MASTER_LOG_POS=... .

Set GTID_MODE to ON_PERMISSIVE.

Set GTID_MODE to OFF_PERMISSIVE.

Verify GTID_OWNED is empty and all applier workers show anonymous transactions.

Flush logs.

Set GTID_MODE to OFF.

Set ENFORCE_GTID_CONSISTENCY to OFF.

Update my.cnf to turn GTID parameters off.

SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
SELECT @@GLOBAL.GTID_OWNED;
SELECT * FROM performance_schema.replication_applier_status_by_worker;
FLUSH LOGS;
SET @@GLOBAL.GTID_MODE = OFF;
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = OFF;

gtid-mode = OFF
enforce-gtid-consistency = 0

Command Cheat Sheet

Enable GTID:

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';
SELECT * FROM performance_schema.replication_applier_status_by_worker;
FLUSH LOGS;
SET @@GLOBAL.GTID_MODE = ON;
# my.cnf changes
# Switch replication
STOP SLAVE;
CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
START SLAVE;
SHOW SLAVE STATUS\G

Disable GTID:

STOP SLAVE;
CHANGE MASTER TO MASTER_AUTO_POSITION = 0, MASTER_LOG_FILE='...', MASTER_LOG_POS=...;
START SLAVE;
SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
SELECT @@GLOBAL.GTID_OWNED;
SELECT * FROM performance_schema.replication_applier_status_by_worker;
FLUSH LOGS;
SET @@GLOBAL.GTID_MODE = OFF;
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = OFF;
# my.cnf changes

Technical Summary

Although the online GTID enable/disable procedures involve many commands, they can be executed quickly with negligible impact on business; the most critical part is the pre‑activation validation to ensure no anonymous transactions remain.

References: MySQL Documentation – Enable GTIDs Online , MySQL Documentation – Disable GTIDs Online .