Replication and GTIDs

In asynchronous replication the replica's I/O thread copies the source's binary log events into its relay log, and applier threads replay them. A GTID (server_uuid:number) names every transaction, so with SOURCE_AUTO_POSITION = 1 the replica tells the source which GTIDs it has and receives the rest. On the source: CREATE USER 'repl'@'%' IDENTIFIED BY 'Repl-2026-pass' and GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'. The spare server was emptied with RESET BINARY LOGS AND GTIDS and seeded with util load-dump /backup/seed --updateGtidSet=replace from a fresh util dump-instance of the source (the loader needs local_infile = ON).

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'source', SOURCE_PORT = 3306,
  SOURCE_USER = 'repl', SOURCE_PASSWORD = 'Repl-2026-pass',
  SOURCE_AUTO_POSITION = 1, SOURCE_SSL = 1;
START REPLICA;
SHOW REPLICA STATUS\G

*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
...
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
...
        Seconds_Behind_Source: 0
...
            Executed_Gtid_Set: f8b4dc41-b72a-11f1-9c16-a282e0d51493:1-36,
f8f7601e-b72a-11f1-baf7-3a8d15598219:1-15

SOURCE_SSL = 1 matters: caching_sha2_password refuses an unencrypted first login. Both threads must say Yes; otherwise Last_IO_Error or Last_SQL_Error says why. The second UUID is the replica's own: the load wrote 15 transactions to its local binary log. Failover tools flag such errant transactions, so run replicas with super_read_only = ON, after which even root's DELETE fails with ERROR 1290.

Lag is the delay from commit on the source to apply on the replica. While the source inserted 200,000 rows (Partitioning), Seconds_Behind_Source read 4, 5 and 6 a second apart. The Performance Schema times each transaction:

SELECT worker_id, last_applied_transaction,
  TIMESTAMPDIFF(MICROSECOND, last_applied_transaction_original_commit_timestamp,
                last_applied_transaction_end_apply_timestamp) / 1000000 AS lag_seconds
FROM performance_schema.replication_applier_status_by_worker
ORDER BY last_applied_transaction_end_apply_timestamp DESC LIMIT 1;

+-----------+-----------------------------------------+-------------+
| worker_id | last_applied_transaction                | lag_seconds |
+-----------+-----------------------------------------+-------------+
|         1 | f8b4dc41-b72a-11f1-9c16-a282e0d51493:39 |      3.0240 |
+-----------+-----------------------------------------+-------------+

Send a replica only reads that may be seconds stale, and take backups there to spare the source.