Simplify MySQL Replication: Why GTID Beats Position‑Based Sync and How to Monitor Lag

Background

In a test environment a MySQL dual‑master (master‑master) cluster was deployed to guarantee high availability; when one master failed traffic could be switched to the other, and the two masters replicated each other’s data.

Frequent data‑conflict issues led to a switch to a master‑slave read/write‑separation mode, where the primary handles writes and reads, and a replica handles I/O‑intensive tasks such as large‑scale statistics. The replica used position‑based replication, specifying a binlog file and offset.

Pain Points of Position‑Based Replication

Initial Setup

Both primary and replica must be synchronized before starting.

Locate the primary’s binlog position.

Configure the replica with that position.

Start the replica’s I/O thread.

Recovery

Identify the binlog position where the replica stopped.

Resolve the failing transaction or manually skip errors (e.g., slave_skip_errors=1032,1062).

These manual steps are error‑prone; MySQL 5.6 introduced GTID to eliminate them.

GTID Solution

What Is GTID?

GTID (Global Transaction Identifier) is a unique identifier generated for each committed transaction, formatted as server_uuid:gno, where server_uuid is a globally unique identifier created on first start‑up and gno is an incrementing integer.

Advantages

Simpler failover—no need to locate log_file and log_pos.

Easier master‑slave setup.

Higher safety compared with traditional binlog replication.

Continuous, gap‑free transaction history ensures data consistency and zero loss.

Enabling GTID

# GTID configuration
gtid_mode=ON
enforce_gtid_consistency=ON

On the replica, configure the connection:

CHANGE MASTER TO
  MASTER_HOST=$host_name
  MASTER_PORT=$port
  MASTER_USER=$user_name
  MASTER_PASSWORD=$password
  master_auto_position=1

With master_auto_position=1 the GTID protocol is used, eliminating the need for MASTER_LOG_FILE and MASTER_LOG_POS.

GTID Synchronization Process

The primary maintains a GTID set of all transactions it has executed.

The replica sends its GTID set to the primary.

The primary computes the difference (transactions present in the primary but missing on the replica).

The primary streams the missing binlog events to the replica.

The replica writes them to its relay log, the SQL thread replays them, and the replica catches up.

Unlike position‑based replication, GTID automatically determines the correct starting point, reducing manual effort.

Detecting Replication Lag

Three methods are commonly used:

1. seconds_behind_master

Run SHOW SLAVE STATUS\G on the replica and check the Seconds_Behind_Master value. It reports lag in seconds but may be imprecise.

2. Compare Binlog Positions

Inspect Master_Log_File and Read_Master_Log_Pos (the latest position read from the primary) against Relay_Master_Log_File and Exec_Master_Log_Pos (the position the replica has executed). Equality of both pairs indicates no lag.

3. Compare GTID Sets

From SHOW SLAVE STATUS\G read Retrieved_Gtid_Set (GTIDs received) and Executed_Gtid_Set (GTIDs applied). If the executed set contains the retrieved set, the replica is fully synchronized.

GTID‑based comparison is more accurate than seconds_behind_master, though sub‑second precision still requires semi‑synchronous replication.

Conclusion

Switching to GTID removes the manual steps and error‑prone position handling of traditional replication, simplifies failover, and provides reliable mechanisms for monitoring replication lag.