MySQL Replication: Diagnosing Lag, Broken Replicas and GTID Inconsistency

Everything starts here. On MySQL 8.0.22 and later use the current terminology; older releases use SHOW SLAVE STATUS with the same fields under the previous names.

SHOW REPLICA STATUS\G

Seven fields carry the diagnosis:

The two threads fail independently. IO running with SQL stopped means data is arriving but not being applied — look at Last_SQL_Error. SQL running with IO stopped means the replica is applying a backlog it already has and will then go quiet.

Seconds_Behind_Source is widely misread. It is the difference between the timestamp of the event being applied and the replica's clock — which means:

• It reports 0 when the IO thread is disconnected, because there is nothing in the relay log to compare against. A completely stalled replica can show zero lag.
• It is distorted by clock skew between source and replica.
• It measures apply delay only, excluding time the data spent in flight.

Compare GTID sets for a truthful answer:

-- on the source
SELECT @@GLOBAL.gtid_executed;

-- on the replica
SELECT @@GLOBAL.gtid_executed;

-- transactions the source has that this replica does not
SELECT GTID_SUBTRACT('<source_set>', '<replica_set>');

An empty result means the replica is genuinely caught up. A non-empty result names exactly which transactions are missing.

Four causes account for the overwhelming majority of lag, and they are distinguishable.

1. Single-threaded apply. Historically the replica applied serially while the source wrote in parallel. Check and enable parallel apply:

SELECT @@replica_parallel_workers, @@replica_parallel_type,
       @@replica_preserve_commit_order, @@binlog_transaction_dependency_tracking;

STOP REPLICA SQL_THREAD;
SET GLOBAL replica_parallel_workers = 8;
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL replica_preserve_commit_order = ON;
START REPLICA SQL_THREAD;

On the source, binlog_transaction_dependency_tracking = WRITESET substantially improves the parallelism the replica can extract, because it marks which transactions genuinely conflict.

2. One enormous transaction. A single large DELETE or UPDATE applies as one unit and blocks everything behind it:

SELECT * FROM performance_schema.replication_applier_status_by_worker\G
SELECT * FROM sys.session WHERE command = 'Query' AND time > 10;

3. Missing index on the replica. With row-based replication, an UPDATE or DELETE on the replica locates each affected row by key. If the replica lacks an index the source has, it performs a full table scan per row — turning a fast statement into hours of work. Compare schemas before anything else.

4. Disk or fsync contention. Replicas are often provisioned on slower storage than the source on the assumption they are read-only.

A stopped SQL thread names the problem precisely. Read the error before taking any action:

SHOW REPLICA STATUS\G
-- Last_SQL_Errno: 1062
-- Last_SQL_Error: Could not execute Write_rows event on table app.orders;
--   Duplicate entry '48213' for key 'PRIMARY'

Errors 1062 and 1032 both indicate the replica's data no longer matches the source. The data divergence is the fault; the stopped thread is the symptom, and it is doing you a favour by stopping.

Error 1236 means the binary logs needed are gone. Check retention on the source and size it against the longest outage you intend to survive:

SELECT @@binlog_expire_logs_seconds / 86400 AS days_retained;
SHOW BINARY LOGS;

Verify divergence rather than assuming it, using checksums across the whole topology:

pt-table-checksum --replicate=percona.checksums h=source-host
pt-table-sync --print --replicate=percona.checksums h=replica-host

Always run pt-table-sync with --print first and read the statements it proposes before letting it execute anything.

In a GTID topology, every transaction carries a globally unique identifier. A transaction executed directly on a replica receives that replica's UUID, producing a GTID the source has never seen — an errant transaction.

It causes no immediate symptom. It breaks the next failover, when the old source tries to replicate from the promoted replica, finds a GTID it does not recognise, and refuses to start.

-- on the replica: transactions it has that the source does not
SELECT GTID_SUBTRACT(@@GLOBAL.gtid_executed, '<source_gtid_executed>');

A non-empty result is an errant transaction and must be dealt with deliberately. Prevent them in the first place:

SET GLOBAL super_read_only = ON;   -- also blocks users with SUPER
SELECT @@read_only, @@super_read_only;

read_only alone does not stop accounts holding SUPER, which is how most errant transactions are created — by an administrator, not an application.

This is why sql_slave_skip_counter must not be used on GTID topologies. The supported method is to inject an empty transaction with the offending GTID, which marks it applied without executing anything:

STOP REPLICA;
SET GTID_NEXT = 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:1234';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START REPLICA;

Treat this as a conscious decision to discard a transaction. Establish what it contained first — from the source's binary log — and confirm the data can be lost or will be restored another way:

mysqlbinlog --include-gtids='aaaaaaaa-...:1234' \
  --base64-output=DECODE-ROWS -vv binlog.000042

After an unclean shutdown the relay log may be truncated. The error is explicit:

Last_IO_Error: Relay log read failure: Could not parse relay log event entry.

With GTID and auto-positioning, recovery is straightforward because the replica can ask for exactly what it needs:

STOP REPLICA;
RESET REPLICA;                      -- discards relay logs, keeps connection settings
CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION = 1;
START REPLICA;
SHOW REPLICA STATUS\G

Use RESET REPLICA, not RESET REPLICA ALL — the latter also deletes the connection configuration, leaving a replica that cannot find its source.

Enable crash-safe replication so this does not recur. These settings store replication position in InnoDB tables rather than files, keeping position and data consistent through a crash:

relay_log_recovery = ON
relay_log_info_repository = TABLE     -- default in 8.0
master_info_repository = TABLE        -- default in 8.0
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

When divergence is extensive, rebuilding is faster and safer than repairing. The essential requirement is a snapshot that is consistent with a known GTID position.

# clone plugin (MySQL 8.0.17+) — simplest and fastest
SET GLOBAL clone_valid_donor_list = 'source-host:3306';
CLONE INSTANCE FROM 'repl_user'@'source-host':3306
  IDENTIFIED BY '<password>';

# logical dump with GTID position recorded
mysqldump --single-transaction --source-data=2 --set-gtid-purged=ON \
  --all-databases --triggers --routines --events > dump.sql

--single-transaction gives a consistent snapshot without locking InnoDB tables. --set-gtid-purged=ON writes the GTID position into the dump so the restored replica knows precisely where to resume.

mysql < dump.sql
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='source-host', SOURCE_USER='repl_user',
  SOURCE_PASSWORD='<password>', SOURCE_AUTO_POSITION=1;
START REPLICA;

For large datasets, Percona XtraBackup produces a physical copy with far less impact than a logical dump and records the GTID position in xtrabackup_binlog_info.

-- authoritative lag, independent of Seconds_Behind_Source
SELECT CHANNEL_NAME, SERVICE_STATE,
       LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP,
       APPLYING_TRANSACTION_START_APPLY_TIMESTAMP
FROM performance_schema.replication_applier_status_by_coordinator;

-- per-worker progress, to spot one stuck thread
SELECT WORKER_ID, LAST_ERROR_MESSAGE, APPLYING_TRANSACTION
FROM performance_schema.replication_applier_status_by_worker;

-- connection health
SELECT CHANNEL_NAME, SERVICE_STATE, LAST_ERROR_MESSAGE
FROM performance_schema.replication_connection_status;

Alert on three things rather than one: both threads running, GTID gap size against the source, and heartbeat age. A heartbeat table written every few seconds on the source and read on the replica measures true end-to-end staleness and is immune to the quirks of Seconds_Behind_Source.

1. Check both thread states before anything else; they fail independently.
2. Do not trust Seconds_Behind_Source — compare GTID sets with GTID_SUBTRACT.
3. For lag: check parallel workers, one long transaction, and replica-side missing indexes, in that order.
4. Errors 1062 and 1032 mean divergence — verify with pt-table-checksum before repairing.
5. Error 1236 means purged binlogs; review retention.
6. Check for errant GTIDs on every replica, and enforce super_read_only.
7. Never use sql_slave_skip_counter with GTID; inject an empty transaction instead.
8. Rebuild with the clone plugin or a GTID-aware dump rather than repairing extensive divergence.