Online Conversion from Binlog-Based Replication to GTID-Based Replication in MySQL 8.x

Traditionally, MySQL replication relies on binary log file names and positions to track replication progress. While this method is reliable, it becomes operationally challenging during failovers, topology changes, replica rebuilds, and disaster recovery activities.

Global Transaction Identifiers (GTIDs) solve these challenges by assigning a unique identifier to every committed transaction. Instead of tracking replication using binlog coordinates, replicas automatically determine which transactions they have already executed and request only the missing ones.

This blog explains how to perform an online migration from File/Position-Based Replication to GTID-Based Replication in MySQL 8.x without rebuilding the replica and with only a brief interruption to replication.

Binlog-Based Replication (File/Position Replication)

In traditional MySQL replication, the replica tracks its position using:

• Binary Log File Name (mysql-bin.000001)
• Binary Log Position (12345678)

Example:

SHOW REPLICA STATUS\G

Output:

Master_Log_File: mysql-bin.000125
Read_Master_Log_Pos: 456789123
Relay_Master_Log_File: mysql-bin.000125
Exec_Master_Log_Pos: 456789123

The replica continuously reads changes from the specified binary log file and position.

Challenges with File/Position Replication

• Manual tracking of binlog file and position.
• Difficult failover management.
• Reconfiguration required after source switch.
• Error-prone during replica rebuilds.
• Complicated multi-source and high-availability environments.

What is GTID-Based Replication?

GTID (Global Transaction Identifier) assigns a unique identifier to every committed transaction.

A GTID consists of:

Server_UUID:Transaction_ID

Example:

3E11FA47-71CA-11E1-9E33-C80AA9429562:10567

Instead of tracking:

mysql-bin.000125:456789123

the replica tracks:

Executed GTIDs and automatically requests only the transactions it has not yet processed.

Replication Architecture Comparison

Compare how replication state is tracked between traditional offset matching and GTID sets.

Show Both File/Pos Only GTID Only

File/Position Replication

Source

Replica

mysql-bin.000125 : 456789123

Coordinate Matching: Manual Offset

Failover Switch: High Overhead

Auto-Discovery: Disabled

GTID-Based Replication

Source

Replica

3E11FA47-71CA-11E1-9E33-C80AA9429562 : 1-10567

Coordinate Matching: Automatic Set

Failover Switch: Instantaneous

Auto-Discovery: Enabled

Advantages of GTID Replication

1. Simplified Failover

GTID replication eliminates the need to manually identify binary log file names and positions during failover. Since each transaction is assigned a unique Global Transaction Identifier (GTID), replicas can automatically determine which transactions have already been executed and continue replication from the correct point.

2. Easier Replica Recovery

When a replica is rebuilt or rejoined to the replication topology, it does not require manual configuration of binary log file names and positions. Using GTIDs, the replica automatically identifies the transactions it has already processed and requests only the missing transactions from the source server. This simplifies replica provisioning and recovery, reduces administrative overhead, and helps ensure data consistency while minimizing the chances of replication configuration errors.

3. Faster Source Switchover

GTID replication enables replicas to seamlessly switch from one source server to another without manually determining the correct binary log coordinates. Since GTIDs uniquely identify every transaction, replicas can automatically resume replication from the appropriate transaction on the new source.

Objective

Convert an existing file/position-based replication topology to GTID-based replication without rebuilding replicas and with only a brief replication interruption during the final switchover.

Architecture

Architecture Conversion Flow

Transitioning from traditional binlog file/position coordinates to GTID auto-positioning.

MySQL Source

Binlog-based
replication mysql-bin.000001 : 12345

MySQL Replica

Online Conversion

MySQL Source

GTID-based
replication UUID : Transaction Set

MySQL Replica

Prerequisites

• Source and replica servers should be running MySQL 5.7.6 or later.
• Replication must be healthy and fully synchronized before starting the GTID migration process.

Version Requirement

Both the Source and Replica servers must be running MySQL 5.7.6 or later, as GTID replication is fully supported and stable starting with this version. It is also recommended that all servers in the replication topology run the same MySQL major version (for example, MySQL 8.0.x on both source and replicas) to avoid compatibility issues and ensure seamless replication and failover operations.

Verify Replication Health

Before enabling or migrating to GTID replication, ensure that replication is healthy and fully synchronized. Ensure replication lag is minimal or zero (Seconds_Behind_Source = 0)

On Replica:

SHOW REPLICA STATUS\G

Expected:

Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Seconds_Behind_Source: 0
Last_IO_Error:
Last_SQL_Error:

Do not proceed if replication is broken.

Check Current GTID Status

Before starting the migration, verify the current GTID configuration on both the source and replica servers. Check the values of gtid_mode, enforce_gtid_consistency, gtid_executed, and gtid_purged to understand the existing GTID state and determine whether GTID is already enabled or if the environment is still using anonymous transactions. 

Source and Replica:

mysql> SHOW GLOBAL VARIABLES LIKE 'enforce_gtid_consistency';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON    |
+--------------------------+-------+
1 row in set (0.01 sec)

mysql>SHOW GLOBAL VARIABLES LIKE 'gtid_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

Downtime Considerations

One of the most common questions during GTID migration is whether the procedure requires downtime.

Application Downtime

No application downtime is required.

The source (primary) database remains online throughout the migration process and continues to serve both read and write traffic. Applications can continue operating normally while GTID mode is being enabled.

Step 1 – Enable GTID Consistency

What is enforce_gtid_consistency?

GTID replication requires all transactions to be safely represented using GTIDs.

This variable prevents execution of statements that are incompatible with GTID tracking.

Examples:

• CREATE TABLE ... SELECT
• Non-transactional updates mixed with transactional updates

Check Current Value

Source: Validate the current status of ‘enforce_gtid_consistency’

SHOW GLOBAL VARIABLES LIKE 'enforce_gtid_consistency';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| enforce_gtid_consistency | OFF   |
+--------------------------+-------+
1 row in set (0.01 sec)

Replica: Validate the current status of ‘enforce_gtid_consistency’

SHOW GLOBAL VARIABLES LIKE 'enforce_gtid_consistency';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| enforce_gtid_consistency | OFF   |
+--------------------------+-------+
1 row in set (0.01 sec)

Enable on Source First

Enable enforce_gtid_consistency, which ensures that only transactions compatible with GTID replication are allowed to execute. It prevents the use of certain statements that can cause inconsistencies in GTID environments and prepares the server for transitioning to GTID_MODE=ON.

SET GLOBAL enforce_gtid_consistency=ON;

Verify:

SHOW GLOBAL VARIABLES LIKE 'enforce_gtid_consistency';
+--------------------------+------+
| Variable_name            | Value|
+--------------------------+------+
| enforce_gtid_consistency | ON   |
+--------------------------+------+
1 row in set (0.01 sec)

Enable on Replica

SET GLOBAL enforce_gtid_consistency=ON;

Verify:

SHOW GLOBAL VARIABLES LIKE 'enforce_gtid_consistency';
+--------------------------+------+
| Variable_name            | Value|
+--------------------------+------+
| enforce_gtid_consistency | ON   |
+--------------------------+------+
1 row in set (0.01 sec)

Step 2 – Move GTID Mode to OFF_PERMISSIVE

After enabling enforce_gtid_consistency, the next step in the GTID migration process is to transition the server from GTID_MODE=OFF to GTID_MODE=OFF_PERMISSIVE.

MySQL does not allow a direct transition from:

OFF → ON

Instead, GTID mode must be enabled through a series of intermediate states:

OFF -  OFF_PERMISSIVE - ON_PERMISSIVE - ON

This staged approach ensures that the server can safely transition from traditional anonymous transactions to fully GTID-enabled transactions without disrupting ongoing workloads or replication.

Source & Replica:

SET GLOBAL gtid_mode=OFF_PERMISSIVE;
Query OK, 0 rows affected (0.01 sec)

Verify:

SHOW GLOBAL VARIABLES LIKE 'gtid_mode';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| gtid_mode     | OFF_PERMISSIVE |
+---------------+----------------+
1 row in set (0.01 sec)

Step 3 – Move GTID Mode to ON_PERMISSIVE

Once we have converted the gtid_mode from  OFF_PERMISSIVE to ON_PERMISSIVE, MySQL begins generating GTIDs while still accepting anonymous transactions.

This allows smooth online migration.

Source & Replica:

SET GLOBAL gtid_mode=ON_PERMISSIVE;
Query OK, 0 rows affected (0.01 sec)

Verify:

SHOW GLOBAL VARIABLES LIKE 'gtid_mode';
+---------------+---------------+
| Variable_name | Value         |
+---------------+---------------+
| gtid_mode     | ON_PERMISSIVE |
+---------------+---------------+
1 row in set (0.01 sec)

Before enabling full GTID mode (gtid_mode=ON), ensure that all existing anonymous (non-GTID) transactions have been completed.

This prevents replication inconsistencies during the final GTID transition.

• In ON_PERMISSIVE mode, both anonymous and GTID transactions can coexist.
• Switching to GTID_MODE=ON is only allowed when there are no remaining anonymous transactions.
• This ensures all future transactions are tracked exclusively using GTIDs.

On Replica:

Verify:

SHOW STATUS LIKE 'ongoing_anonymous_transaction_count';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| Ongoing_anonymous_transaction_count  | 5     |
+--------------------------------------+-------+

The value must be 0 before proceeding to the next step.

If the value is greater than 0, wait and continue monitoring until it drops to 0.

It is recommended to verify the value remains 0 across multiple checks to ensure no anonymous transactions are still in progress.

After some time:

SHOW STATUS LIKE 'ongoing_anonymous_transaction_count';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| Ongoing_anonymous_transaction_count  | 0     |
+--------------------------------------+-------+

Keep checking for several minutes.

Step 5 – Enable Full GTID Mode

Once anonymous transactions disappear completely, ensure that every new transaction is assigned a unique Global Transaction Identifier (GTID). From this point onward, replication uses GTIDs instead of binary log file names and positions, enabling simplified failover, easier replica recovery, and seamless source switchovers.

Ensure the following conditions are met:

• enforce_gtid_consistency = ON
• gtid_mode = ON_PERMISSIVE
• Ongoing_anonymous_transaction_count = 0 on both Source and Replica

Once enabled, all new transactions are assigned GTIDs and replication becomes fully GTID-aware.

After this point, anonymous (non-GTID) transactions are no longer permitted.

Source & Replica:

SET GLOBAL gtid_mode=ON;
Query OK, 0 rows affected (0.01 sec)

Verify:

SHOW GLOBAL VARIABLES LIKE 'gtid_mode';
+---------------+------+
| Variable_name | Value|
+---------------+------+
| gtid_mode     | ON   |
+---------------+------+
Query OK, 0 rows affected (0.01 sec)

Step 6 – Configure GTID Auto Positioning

Now that GTID mode has been successfully enabled on both the Source and Replica servers, all new transactions are being assigned GTIDs and tracked using GTID sets. However, the replication channel is still configured using the traditional binary log file and position coordinates.

The next phase is to convert the existing replication configuration from file/position-based replication to GTID auto-positioning.

Now, we can stop the replication and reset to reconfigure GTID based replication

Configure replica to use GTIDs.

CHANGE REPLICATION SOURCE TO
SOURCE_HOST='10.0.1.10',
SOURCE_USER='replication_user',
SOURCE_PASSWORD='password',
SOURCE_PORT=3306,
SOURCE_AUTO_POSITION=1;
Query OK, 0 rows affected (0.02 sec)

This involves reconfiguring the replica to use SOURCE_AUTO_POSITION=1, allowing it to automatically identify and retrieve only the transactions it has not yet executed based on GTID information, eliminating the dependency on binary log filenames and positions

Post-Migration Validation

The replication configuration has now been successfully converted from binlog file/position-based replication to GTID-based replication with auto-positioning enabled.

After the migration, monitor the replication status closely to ensure that replication is functioning normally and that there are no conflicts, lag, or replication errors. Validate that both the I/O and SQL threads are running, GTID auto-positioning is enabled (Auto_Position: 1), and the replica remains in sync with the source.

SHOW REPLICA STATUS\G

Verify :

Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Auto_Position: 1
Seconds_Behind_Source: 0
Last_IO_Error:
Last_SQL_Error:

GTID Auto-Positioning Protocol

How SOURCE_AUTO_POSITION=1 automatically synchronizes missing transaction sets.

Source Database GTID Executed: 1-120

⇄

Replica Database GTID Executed: 1-100

1

Replica sends executed set (1-100) during channel connect.

2

Source compares sets and calculates delta: missing transactions (101-120).

3

Source streams only missing GTID transactions automatically without offset entries.

Auto_Position: 1 Replica_IO_Running: Yes Seconds_Behind_Source: 0

Successful validation confirms that the replica is now operating using GTID-based replication, and the migration has been completed without replication breakage or data inconsistency. Continuous monitoring is recommended for a period after the change to ensure stable replication and normal application behavior.

To explore further GTID management capabilities in MySQL, check out our guide on Tagged GTIDs in MySQL 8.4 and our strategy overview for MySQL Asynchronous Replication Failover.

Need help upgrading or converting your MySQL replication topology? Mydbops offers end-to-end MySQL consulting, replication setup, high-availability architecture, and 24/7 managed database services to keep your infrastructure running smoothly without downtime.