Designing Fallback Routing for Async Replication Breaks #

When an asynchronous replication channel that feeds your archiver dies mid-stream, the failure is rarely graceful. The replica I/O thread stops, SHOW REPLICA STATUS starts returning a terminating error, and the archiving pipeline silently stops copying transactions to durable storage. The two signatures that matter are ERROR 1236 (HY000): Could not find first log file name in binary log index file — the source purged a binary log the archiver had not yet captured — and ERROR 3023 (HY000): The GTID set is not a subset of the executed GTID set on a reconnect attempt. Both mean the same thing for point-in-time recovery: every second the pipeline waits on the dead source is a second of committed transactions that may never reach the archive, widening your Recovery Point Objective (RPO) until a failover exposes the hole. This page is the operator procedure for turning that break into a controlled reroute rather than a data-loss incident.

Visual Overview #

Context & Prerequisites #

This page is the detailed topology playbook under the broader Fallback Routing Strategies cluster, and it assumes the control-plane invariant established there: a source is only a valid failover target if the transactions the archive still needs are a subset of what that source can still serve. Expressing that subset requires globally unique transaction identifiers, so a gap-free GTID Tracking & Enforcement foundation (gtid_mode = ON, enforce_gtid_consistency = ON) is a hard prerequisite — file-and-position coordinates cannot survive a source switch. The stream must also be ROW-based so events replay deterministically on the new source; the ROW vs STATEMENT vs MIXED Formats trade-offs explain why STATEMENT and MIXED carry session-context dependencies that can replay differently after a reroute. Beyond that you need MySQL 8.0.23+ on every candidate (so CHANGE REPLICATION SOURCE TO ... SOURCE_AUTO_POSITION = 1 is available), Python 3.10+, mysql-connector-python 8.0+, and scoped credentials per the Security & Access Frameworks guidance — REPLICATION SLAVE, REPLICATION CLIENT, and SELECT, never SUPER.

Step-by-Step Implementation #

Step 1 — Detect and classify the break #

Poll the archiving replica and branch on the exact error before touching topology. Blind retries against a source that has purged your logs only burn RTO. PITR relevance: classification decides whether the missing transactions are still recoverable from a live source or only from a physical backup.

-- MySQL 8.0.22+
SHOW REPLICA STATUS\G
-- Inspect Last_IO_Errno / Last_IO_Error and Last_SQL_Errno / Last_SQL_Error.
-- 1236 -> source purged needed logs; reroute or degrade (do not retry same source).
-- 3023 -> reconnect GTID set is not a subset; the source is behind the archive.

from enum import Enum

class BreakClass(Enum):
    TRANSIENT = "transient"    # network blip; safe to retry same source
    REROUTE = "reroute"        # source cannot serve needed set; try another
    DEGRADE = "degrade"        # nothing live qualifies; fall back to backup

def classify(io_errno: int) -> BreakClass:
    match io_errno:
        case 1236 | 3023:
            return BreakClass.REROUTE
        case 2003 | 2013:       # can't connect / lost connection
            return BreakClass.TRANSIENT
        case _:
            return BreakClass.REROUTE

Step 2 — Compute the needed GTID set #

Subtract what the archive already holds from what the failed source executed. This is the exact set of transactions the archive is still missing, and every downstream decision is measured against it. PITR relevance: this set is your open RPO window expressed as transactions.

-- MySQL 8.0.22+ : run against the failed source if still reachable,
-- otherwise reconstruct executed_set from the last archived manifest.
SELECT GTID_SUBTRACT(@@GLOBAL.gtid_executed, :last_archived_gtid_set) AS needed_set;

Step 3 — Qualify candidate sources #

A candidate can serve the reroute only if the needed set is contained in the transactions it has executed and has not yet purged. Use MySQL’s own set arithmetic — never a string comparison, because GTID sets are ranges, not substrings. PITR relevance: qualifying before switching is what prevents a partial reroute, which is exactly how silent gaps are born.

def source_can_serve(cursor, needed_set: str) -> bool:
    cursor.execute(
        "SELECT GTID_SUBSET("
        "  %s,"
        "  GTID_SUBTRACT(@@GLOBAL.gtid_executed, @@GLOBAL.gtid_purged)"
        ") AS ok",
        (needed_set,),
    )
    row = cursor.fetchone()
    return bool(row and row[0])

Step 4 — Reroute with auto-position, idempotently #

Record a stable break signature before the switch, then repoint the replica at the first qualifying candidate. If the controller crashes after CHANGE REPLICATION SOURCE but before recording completion, the same signature short-circuits the re-run instead of switching twice. PITR relevance: an idempotent handoff guarantees the archive resumes at exactly one contiguous point.

def reroute(cnx, source_host: str, repl_user: str, repl_pass: str) -> None:
    cur = cnx.cursor()
    cur.execute("STOP REPLICA")
    # MySQL 8.0.23+ : auto-position negotiates the resume GTID; no file/pos.
    cur.execute(
        "CHANGE REPLICATION SOURCE TO "
        "SOURCE_HOST=%s, SOURCE_USER=%s, SOURCE_PASSWORD=%s, "
        "SOURCE_AUTO_POSITION=1, SOURCE_SSL=1",
        (source_host, repl_user, repl_pass),
    )
    cur.execute("START REPLICA")
    cnx.commit()

Step 5 — Validate continuity or degrade #

Confirm the retrieved GTID set joins contiguously onto the last archived GTID and that lag settles inside your SLA. If Step 3 found no qualifying source, do not force a switch — degrade to a Base Backup Integration for PITR restore plus binlog replay, which is slower but leaves no gap. PITR relevance: the post-switch gate is the last chance to catch a reroute that opened a hole instead of closing one.

def continuity_ok(cursor, last_archived: str) -> bool:
    # Newly retrieved transactions must contain no gap before last_archived.
    cursor.execute(
        "SELECT GTID_SUBTRACT(%s, @@GLOBAL.gtid_executed) AS missing",
        (last_archived,),
    )
    missing = cursor.fetchone()[0]
    return not missing  # empty string => fully contiguous

Configuration Reference #

Minimal server settings every candidate source and the archiving replica must share for the reroute to be legal. Divergent values here are the most common cause of a handoff that qualifies in theory but stalls in practice.

How long a source stays qualified is governed by its purge horizon, so cross-reference these values with Binlog Retention Boundaries: a candidate whose binlog_expire_logs_seconds is too aggressive will let gtid_purged overtake the needed set before the controller reaches it.

Verification Checklist #

• gtid_mode = ON and enforce_gtid_consistency = ON on the archiving replica and every candidate source.
• The controller intercepts ERROR 1236 and ERROR 3023 and routes them to the reroute path, not a retry loop.
• Candidate qualification uses GTID_SUBSET(needed, GTID_SUBTRACT(gtid_executed, gtid_purged)), not a string match.
• CHANGE REPLICATION SOURCE TO uses SOURCE_AUTO_POSITION = 1 — no SOURCE_LOG_FILE / SOURCE_LOG_POS.
• A break signature is recorded before the switch so a controller restart is a no-op.
• Post-switch continuity gate confirms the retrieved GTID set is contiguous with the last archived GTID.
• A DEGRADE outcome pages an operator and triggers base-backup replay rather than a forced reroute.

Gotchas & Version-Specific Caveats #

• CHANGE MASTER TO still parses but is deprecated. On 8.0.23+ use CHANGE REPLICATION SOURCE TO and SOURCE_AUTO_POSITION; the old MASTER_AUTO_POSITION spelling survives only as an alias and is removed in later 8.4 tooling paths.
• WRITESET dependency tracking changed in 8.4. binlog_transaction_dependency_tracking was deprecated and WRITESET behavior became the effective default; do not set the variable explicitly in 8.4 automation or startup will warn. Parallel-apply throughput after a reroute may differ from an 8.0 baseline, so re-benchmark catch-up time rather than assuming it carries over.
• gtid_purged is a moving target. A candidate that qualifies at Step 3 can purge the needed logs before Step 4 lands if it is rotating aggressively. Keep the qualify-to-switch window tight, and re-check the subset inside the same connection right before issuing CHANGE REPLICATION SOURCE.
• Auto-position needs a compatible gtid_purged on the target replica. If the archiving replica’s own gtid_purged does not line up with the new source’s history, the reroute can itself throw ERROR 1236; reconcile with RESET REPLICA only after confirming no un-archived transactions would be lost.
• A partially-caught-up source is still a DEGRADE, not a partial success. Never follow a candidate that holds some of the needed set — accept only a full subset, or fall back to base-backup replay.

Frequently Asked Questions #

Related #

• Fallback Routing Strategies — the parent control-plane design this playbook implements, including the full routing controller module.
• GTID Tracking & Enforcement — the gap-free identifier foundation every qualify-and-switch decision depends on.
• Base Backup Integration for PITR — the graceful-degradation path when no live source can serve the needed transactions.

Back to Fallback Routing Strategies.