Database High Availability: 5-Layer Architecture from WAL to Automated Failover

Introduction: Why Database High Availability Matters

Databases are stateful services at the end of the application call chain; their failure causes irreversible data loss unlike cache or gateway failures. Real-world incidents illustrate the stakes: GitHub's 2018 primary split-brain took 24 hours to recover; an AWS RDS/Aurora AZ outage left customers waiting for manual failover; Alibaba Cloud Hong Kong AZ C outage (Dec 2022, >15h) disabled cross-AZ switching because the proxy service resided in the same AZ.

Panorama: Five Layers of HA

High availability is a stack of guarantees; each layer answers a specific question:

Layer ① Single-node : After power loss, are committed transactions durable? Keywords: WAL, 2PC, crash recovery

Layer ② Replication : How does data reach the replica? How much can be lost? Keywords: Async / semi-sync, RPO

Layer ③ GTID : After failover, how does the replica know which transactions it misses? Keywords: Global transaction ID, auto-position

Layer ④ Failover : How to promote a replica safely without data loss or split-brain? Keywords: Catch-up, fencing, PRS/ERS

Layer ⑤ Automation : Who detects, decides, and executes failover? Keywords: Leader election, probe-agent

Chapter 1: Single-Node Consistency — Making a Transaction Crash-Safe

The foundation is single-node durability : a restarted MySQL must never lose a transaction acknowledged as committed, nor retain uncommitted ones. This is achieved via three logs + two-phase commit.

1.1 Three Logs and Their Roles

Key distinction: redo is the engine's internal ledger; binlog is the external change stream . Redo is fixed-size and circular; binlog is append-only and archivable. Replication and PITR rely on binlog; crash recovery relies on redo.

redo log (InnoDB engine, Physical page offset changes): Crash roll-forward of committed transactions → durability. Writer: InnoDB.

undo log (InnoDB engine, Logical reverse operations): Rollback uncommitted transactions; support MVCC snapshot reads. Writer: InnoDB.

binlog (MySQL Server, Logical row/statement changes): Replication + Point-in-Time Recovery (PITR) . Writer: Server.

1.2 WAL: Write-Ahead Logging

InnoDB follows WAL (Write-Ahead Logging) : instead of flushing data pages immediately (random I/O), it sequentially writes the changes to redo log first (fast sequential I/O), deferring data-page flush. Benefits: commit only waits for redo fsync, greatly improving throughput; even if the machine crashes before data pages flush, restart can roll forward using redo.

1.3 Two-Phase Commit: Coordinating Redo and Binlog

Redo (engine) and binlog (server) are independent logs. If only one persists before a crash, primary and replica diverge (e.g., redo committed but binlog missing → replica never sees the transaction). MySQL uses an internal two-phase commit to ensure both logs either take effect or not. The critical order: redo-prepare → binlog-write → redo-commit. Binlog fsync success is the core criterion for the commit point.

1.4 Crash Recovery: Deciding the Fate of Prepared Transactions

On restart, InnoDB scans redo for transactions in PREPARE state (prepare present, commit absent). It checks whether the corresponding binlog was fully flushed: if yes, commit; if no, roll back. Conclusion: binlog durability is the final arbiter of whether a transaction counts as committed , ensuring primary recovery matches what replicas see via binlog.

1.5 The "Dual-1" Parameters

"Dual-1" means setting both parameters to 1 for maximum safety: innodb_flush_log_at_trx_commit=1: Flush redo to disk on every commit. ≠1 (0/2): batch flush, lose last ~1 second on crash. sync_binlog=1: Flush binlog to disk on every commit. ≠1 (0/N): batch flush, lose recent transactions on crash.

"Dual-1" is the prerequisite for single-node durability . Production core databases must use dual-1; cost is two fsyncs per commit.

Summary: Single-node layer uses WAL for speed, 2PC for redo/binlog consistency, crash recovery to adjudicate half-committed transactions, and dual-1 to guarantee durability. This ensures "one machine doesn't lose data" — but what if the whole machine dies? That's where replication comes in.

Chapter 2: Primary-Replica Replication — Moving Data from Primary to Replica

Even a reliable single node can fail. Data must be replicated in real time to a replica . Replication essence: ship the primary's binlog to the replica and replay it .

2.1 Replication Pipeline: Three Threads

2.2 Asynchronous Replication: Default, Fastest, May Lose Data

Default mode: primary returns to client immediately after commit, without waiting for replica . Pros: fast, primary not blocked by replica. Cons: transactions acknowledged but not yet shipped are lost on primary crash — loss volume equals replication lag at crash moment.

2.3 Semi-Synchronous Replication: At Least One Replica Acknowledges

To shrink the data-loss window, semi-sync makes the primary wait for at least N replicas to acknowledge receipt of binlog before returning to client. Two wait points ( rpl_semi_sync_master_wait_point):

AFTER_SYNC (5.7+ default, recommended): After primary binlog fsync, before engine commit , wait for replica ack. Consistency: Strong (visible only after ack). Risk: Fewer phantom reads.

AFTER_COMMIT : After engine commit , then wait for replica ack. Consistency: Weak (primary visible but replica may not have it). Risk: Primary can read, replica lacks — "phantom" data.

Key parameters: rpl_semi_sync_master_wait_for_slave_count (how many replicas to wait for, typically 1); rpl_semi_sync_master_timeout (timeout in ms). Critical caveat: on timeout, semi-sync silently falls back to async! This is the "nominally strong, actually may degrade" pitfall. Production sets timeout to 86400000 ms (effectively no fallback) combined with three-data-center deployment to achieve RPO=0.

Summary: Replication extends "single-node no-loss" to "another node survives". Async is fast but loses more; semi-sync trades one network round-trip for zero data loss. But when the primary actually dies, how does the replica know which transactions it missed and where to resume? That's where GTID comes in.

Chapter 3: GTID Internals — Making Replication and Failover Identifiable and Idempotent

3.1 Pain Points of Traditional Binlog Position

Pre-GTID, replicas tracked progress via binlog filename + position (e.g., mysql-bin.000123:4567). Problems: positions are primary-local; after failover the same transaction has a completely different position on the new primary. DBAs had to manually map "last transaction on replica → position on new primary" — error-prone, causing data loss or duplicate execution.

3.2 GTID: A Global ID Card for Every Transaction

GTID (Global Transaction ID) = server_uuid:transaction_id

, e.g., 3E11FA47-...:23. server_uuid identifies the originating instance; transaction_id is a monotonically increasing sequence on that instance. A transaction's GTID is globally unique and immutable — it never changes regardless of which machine replicates it. Each replica maintains a GTID set (compact interval notation) of executed transactions, e.g.:

gtid_executed = 3E11FA47-...:1-100:102-105
         (missing 101)

3.3 Auto-Position: Automatic Alignment After Failover

With GTID enabled, replicas connect using MASTER_AUTO_POSITION=1: Core value: after failover, replicas simply report their GTID set; the new primary automatically sends the missing transactions (set difference) . This is the foundation for safe automated repointing (Orchestrator).

3.4 Idempotency: No Duplicate Execution

Before applying a transaction, the replica checks if its GTID exists in gtid_executed — if present, skip . This guarantees a transaction never replays twice (e.g., if topology changes cause duplicate push).

Summary: GTID upgrades "replication progress" from "primary-local position" to "global transaction ID set", enabling automatic alignment and idempotency after failover. With GTID, we can finally safely discuss "how to promote a replica when the primary dies".

Chapter 4: Failover Consistency — No Data Loss, No Split-Brain

When the primary fails (or planned maintenance), a replica must be promoted and other replicas repointed. Two fears: data loss and dual-primary (split-brain) .

4.1 Three-Step Failover

4.3 Fencing: Preventing the Old Primary from "Resurrecting"

Most dangerous scenario: old primary isn't truly dead (network partition / false death), comes back accepting writes while new primary also writes → split-brain, divergent data, extremely hard to repair. Fencing = ensure old primary can never write again . Techniques: on old primary side, SET read_only, kill connections, deregister metadata.

4.4 Planned (PRS) vs Emergency (ERS) Reparent

Vitess/Orchestrator defines two reparent types:

PRS (Planned Reparent) : Trigger: Planned (maintenance, upgrade). Old Primary: Alive, cooperates with demotion. RTO: Near zero (catch-up then switch). Key Goal: Orderly, zero-loss.

ERS (Emergency Reparent) : Trigger: Primary already down. Old Primary: Possibly unreachable. RTO: Minimize (pick least-lag replica). Key Goal: Fast, prevent split-brain first.

Summary: Failover consistency = fencing (no dual-primary). GTID simplifies repointing; fencing stops old primary writes. But — who decides "primary is truly dead"? Who picks which replica? Who executes the three steps? Manual 3 AM intervention is too slow and error-prone. That's what Orchestrator solves.

Chapter 5: Orchestrator Leader Election and Probe-Agent Roles

Previous chapters defined "how to failover safely". But at 3 AM, relying on humans to "detect → judge → elect → execute" is too slow and risky. Orchestrator (orc) is the brain that automates this loop ; probe-agent is its eyes and hands deployed on every DB host .

5.1 Topology Discovery: Mapping the Replication Graph

Orc requires no manual topology registration. Given any instance, it actively crawls SHOW SLAVE HOSTS / SHOW SLAVE STATUS to discover the full replication graph (who is primary, who hangs under whom) and continuously refreshes it — the basis for all subsequent decisions.

5.2 Failure Detection Core: Proof by Contradiction (Orc's Best Lesson)

Biggest pitfall: orc cannot reach primary → declares it dead → false failover. Network blips or orc-to-primary link failures cause misjudgment; false failover cost is huge (unnecessary switch, potential split-brain). Orc's solution: proof by contradiction (holistic cross-check) : to judge if primary is truly dead, don't just check orc's own connectivity; check whether the primary's replicas can still reach it .

DeadMaster

: Primary unreachable, and replicas also unreachable → Trigger failover: Yes (truly dead). DeadMasterAndSomeReplicas: Primary dead, some replicas also lost → Yes. DeadMasterAndReplicas: Primary dead, all replicas lost → Yes. DeadMasterWithoutReplicas: Primary dead, no replicas → Depends on config. UnreachableMaster: Orc can't reach, but replicas still can → No (suspected network issue). UnreachableMasterWithLaggingReplicas: Unreachable + replica lag increasing → Observe. LockedSemiSyncMaster: Semi-sync stalled (waiting for replica ack, writes blocked) → Yes (special handling). MasterNotWriteAble: Primary alive but not writable (e.g., accidental read_only) → Case by case.

5.3 Candidate Selection: Choosing the New Primary

After deciding to failover, orc picks from surviving replicas based on:

Most up-to-date data : prefer replica with most complete GTID set / furthest replication position (least data loss).

Promotion rule : admin-assigned priority — must / prefer / neutral / prefer_not / must_not. E.g., tag cross-DC read-only replica as must_not so it's never elected.

Candidate marking : preferred candidates registered in candidate_database_instance.

Same DC / cell, version compatibility : prefer same region (lower latency); new primary version must not be lower than its replicas (otherwise replication breaks).

5.4 Failover Execution: Promote + Repoint + Callbacks

5.5 Probe-Agent: Providing "Internal View + Execution Hands"

Orc is an external observer with two inherent blind spots; probe-agent fills them:

Blind spot 1: External probes can't see host-internal truth. Orc can't distinguish "DB truly down" from "network broken" or "DB too busy". Probe-agent runs locally on the DB host, exposing internal state: heartbeat.go: Periodic heartbeat ( hostname -I for local IP + timestamp) to orc. Why orc can't: Host-side "I'm alive" proof; distinguishes DB death from network partition. load_checker.go: Checks local load (connections, threads) via NewConfigurableLoadChecker. Why orc can't: External view cannot see real-time host load. big_transaction.go: Uses primary-replica GTID diff via GtidComparisonChecker to detect large transactions; records primary/replica GTID snapshots at failure moment. Why orc can't: Must know before failover: large transactions prolong switch and increase risk. disk_check.go: Disk health checks ( disk failure is JD's most common host failure ). Why orc can't: Writes to local disk for verification. probe_agent.go: Aggregator: write detection ( enableWriteDetect), consecutive failure counter ( consecutiveFailures). Why orc can't: Local "can it really write?" is far more reliable than external ping.

Blind spot 2: Orc decides but needs "hands" on the host to execute/fallback.

Division of labor summary:

Conclusion: Layered High Availability

We push each layer to its limit to maximize HA. Production uses non-fallback semi-sync for RPO=0; future three-DC deployment will provide data-center-level disaster recovery. But is that the end?

Database HA Isn't Just the Database's Job — Applications Must Participate

Since databases always have a "brief unwriteable window", if applications treat every write as "must synchronously succeed or error to user", a DB blip cascades into application outage. Truly resilient systems co-design HA across application and database — during those tens of seconds of DB unavailability, can the application gracefully degrade instead of collapsing?

Common application-side fallback patterns:

Async + MQ Buffer : Write requests enter message queue first; app returns "accepted" quickly; background consumers persist slowly; DB blip causes queue backlog not data loss. Applicable: Writes tolerating "persist later" (post-order processing, logs, notifications).

Eventual Consistency : Don't require "immediate global visibility"; allow convergence over time. Applicable: Counters, status sync, cross-system data distribution.

Read Degradation : When primary unavailable, serve reads from replica/cache with slightly stale data. Applicable: Read-heavy, display-oriented scenarios tolerating brief staleness.

Write Degradation / Fallback Storage : On DB write failure, persist locally / in cache / fallback table; replay after recovery. Applicable: Critical writes that must not be lost but can tolerate delay.

Fast Fail + Circuit Breaker : Detect DB unavailability → immediate failure + circuit break to avoid connection pool exhaustion by slow queries. Applicable: Basic self-protection for all scenarios.

Final Word

On the database side, we push layered HA to the extreme to minimize the "unavailable window"; on the application side, we embrace "database may be briefly unavailable at any time" and prepare degradation plans to survive that window. Both systems meeting halfway is what makes a system truly highly available — the complete meaning of treating database HA as a "top data priority".