SQL IO_AFTER_GTIDS

What is IO_AFTER_GTIDS in MySQL replication?

IO_AFTER_GTIDS makes a replica’s IO thread stop after it has fetched all transactions whose GTIDs belong to a supplied GTID set.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL IO_AFTER_GTIDS: MySQL 5.7+, MySQL 8.0+, Percona Server 5.7/8.0. (MariaDB uses different syntax and does not support this keyword.)

SQL IO_AFTER_GTIDS Full Explanation

IO_AFTER_GTIDS is an UNTIL condition that can be added to START REPLICA (or START SLAVE in MySQL 5.7 and earlier). It is used only in environments where GTID-based replication (GTID_MODE = ON) is enabled. When specified, the replica’s IO thread keeps pulling events from the source binary log until it has retrieved every transaction whose GTID appears in the given set. As soon as the last matching transaction has been written to the relay log, the IO thread stops. The SQL thread continues to execute events already stored in the relay log unless it is also stopped explicitly.IO_AFTER_GTIDS is the IO-thread counterpart to SQL_AFTER_GTIDS. Use the IO variant when you care about pulling transactions into the relay log but do not yet need them executed. This is useful for coordinated switchover operations, point-in-time recovery, cloning, and reducing replication lag before a controlled stop.Caveats and behavior:- Works only with GTID-enabled replication.- The supplied GTID set must be a subset of the source’s Executed_Gtid_Set; otherwise START REPLICA fails.- IO_AFTER_GTIDS does not wait for the SQL thread. If you need both threads to stop, combine IO_AFTER_GTIDS with SQL_AFTER_GTIDS or STOP REPLICA SQL_THREAD.- SHOW REPLICA STATUS (or SHOW SLAVE STATUS) reports a non-running IO thread and the Retrieved_Gtid_Set equal to or superset of the provided set when the condition is met.

SQL IO_AFTER_GTIDS Syntax

START REPLICA IO_THREAD
  UNTIL IO_AFTER_GTIDS = 'gtid_set';
-- MySQL 5.7 syntax
START SLAVE IO_THREAD
  UNTIL IO_AFTER_GTIDS = 'gtid_set';

SQL IO_AFTER_GTIDS Parameters

gtid_set (string) - One or more GTID intervals in standard MySQL format, e.g., '41e8b0e4-cd7f-11ed-bd6c-080027e3d8b2|||1-150'.

Example Queries Using SQL IO_AFTER_GTIDS

-- Stop only the IO thread after it has pulled specific transactions
START REPLICA IO_THREAD
  UNTIL IO_AFTER_GTIDS = '41e8b0e4-cd7f-11ed-bd6c-080027e3d8b2:1-300';

-- Verify status
SHOW REPLICA STATUS\G

-- Resume IO thread later
START REPLICA IO_THREAD;

Expected Output Using SQL IO_AFTER_GTIDS

The IO thread continues fetching events until it has written every GTID listed in the set to the relay log, then stops
SHOW REPLICA STATUS shows Replica_IO_Running = No, while Replica_SQL_Running remains Yes (if not stopped separately)

Use Cases with SQL IO_AFTER_GTIDS

Performing a switchover: pre-fetch events so the replica is nearly caught up before promoting it.
Taking a consistent backup of the relay log for PITR without executing the transactions.
Reducing replication lag in stages, allowing the SQL thread to catch up after IO thread stops.
Testing GTID positioning by ensuring relay log contains a known GTID boundary.

Common Mistakes with SQL IO_AFTER_GTIDS

Expecting the SQL thread to stop – IO_AFTER_GTIDS affects only the IO thread.
Supplying a GTID set that the source has not generated yet, causing START REPLICA to fail.
Forgetting to quote the GTID set in SQL statements.
Attempting to use IO_AFTER_GTIDS when GTID_MODE is OFF.

First Introduced In

MySQL 5.7.6

Frequently Asked Questions

What does IO_AFTER_GTIDS control?

IO_AFTER_GTIDS controls the replica IO thread. It stops the thread once all transactions belonging to the specified GTID set have been fetched from the source.

Can I use IO_AFTER_GTIDS without GTIDs enabled?

No. The option is recognized only when GTID_MODE is ON on both the source and the replica.

How do I stop both IO and SQL threads at specific GTIDs?

Combine IO_AFTER_GTIDS with SQL_AFTER_GTIDS in a single START REPLICA statement, or stop the other thread separately after IO_AFTER_GTIDS triggers.

How do I restart replication after it stops at IO_AFTER_GTIDS?

Issue START REPLICA IO_THREAD to restart the IO thread, or START REPLICA to start both threads together.