SQL IO_BEFORE_GTIDS

What is IO_BEFORE_GTIDS in MySQL replication?

IO_BEFORE_GTIDS is a replication UNTIL option that stops a replica’s I/O thread when it has fetched all binary log events whose GTIDs precede a specified 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_BEFORE_GTIDS: Supported: MySQL 5.7.6+, MySQL 8.x. Not supported: MariaDB, PostgreSQL, SQL Server, Oracle, SQLite.

SQL IO_BEFORE_GTIDS Full Explanation

IO_BEFORE_GTIDS is used with START REPLICA (or legacy START SLAVE) and STOP REPLICA to control how far the replica I/O thread should read from the source’s binary log. When IO_BEFORE_GTIDS is supplied, the I/O thread continues requesting events until it encounters an event whose GTID is equal to or exceeds the first GTID in the supplied set. It then stops, leaving the SQL thread free to apply everything already queued. This mechanism lets administrators bring a replica to an exact point in the transaction history without executing the GTIDs listed in the set. It is frequently paired with SQL_BEFORE_GTIDS or SQL_AFTER_GTIDS to achieve precise point-in-time cloning, delayed replication, or controlled failover. Important caveats: - The replica must be configured for GTID-based replication (GTID_MODE = ON on both source and replica). - IO_BEFORE_GTIDS only affects the I/O thread. The SQL thread may still apply events already downloaded unless separately controlled. - If the specified GTID set is not present on the source, the I/O thread waits indefinitely unless a timeout is configured.

SQL IO_BEFORE_GTIDS Syntax

START REPLICA IO_THREAD UNTIL IO_BEFORE_GTIDS = 'uuid:1-145,uuid2:30';
-- or
STOP REPLICA IO_THREAD UNTIL IO_BEFORE_GTIDS = 'uuid:1-145';

SQL IO_BEFORE_GTIDS Parameters

IO_BEFORE_GTIDS (string) - One or more GTID ranges in the canonical GTID set format 'uuid:low-high' separated by commas.

Example Queries Using SQL IO_BEFORE_GTIDS

-- Catch up to but not including GTID 3E11FA47-71CA-11E1-9E33-C80AA9429562:250
START REPLICA IO_THREAD UNTIL IO_BEFORE_GTIDS = '3E11FA47-71CA-11E1-9E33-C80AA9429562:250';

-- Later, resume normal I/O thread operation
START REPLICA IO_THREAD;

-- Stop downloading events once GTID set reached during cloning
STOP REPLICA IO_THREAD UNTIL IO_BEFORE_GTIDS = 'a1b2c3d4-00aa-11ed-bf1e-080027b2ae7d:1-500';

Expected Output Using SQL IO_BEFORE_GTIDS

The replica I/O thread remains active until the first event whose GTID matches or exceeds the given set arrives, then transitions to the Stopped state
The SQL thread continues applying any queued events
No events with GTIDs in or after the set are downloaded

Use Cases with SQL IO_BEFORE_GTIDS

Point-in-time recovery without applying unwanted transactions
Cloning a replica up to a safe GTID boundary before promotion
Delayed or staged replication for disaster recovery testing
Creating a backup snapshot consistent up to, but not including, a critical schema change

Common Mistakes with SQL IO_BEFORE_GTIDS

Forgetting to enable GTID_MODE, leading to an error that the option is unsupported
Expecting the SQL thread to stop as well; IO_BEFORE_GTIDS controls only the I/O thread
Supplying an empty or malformed GTID set, which causes a syntax error
Using IO_BEFORE_GTIDS on servers older than MySQL 5.7.6

First Introduced In

MySQL 5.7.6

Frequently Asked Questions

What is the difference between IO_BEFORE_GTIDS and SQL_BEFORE_GTIDS?

IO_BEFORE_GTIDS targets the I/O thread so only the download phase stops, while SQL_BEFORE_GTIDS targets the SQL thread to pause event execution.

Can I use IO_BEFORE_GTIDS without GTID replication?

No. The clause is only valid when GTID_MODE is ON. Without GTIDs the server raises a syntax error.

How do I resume replication after IO_BEFORE_GTIDS stops the I/O thread?

Simply issue `START REPLICA IO_THREAD;` (or `START SLAVE IO_THREAD;` on older versions) to restart downloading events.

What happens if the GTID set supplied to IO_BEFORE_GTIDS never appears on the source?

The I/O thread waits indefinitely, so administrators often set `SOURCE_CONNECT_RETRY` or manually monitor and intervene if necessary.