Five Layers of Database High Availability: From WAL to Orchestrator Auto-Failover

The article opens by establishing why database high availability is critical: databases are stateful services at the end of the application chain, and their failure causes irreversible damage — lost writes, wrong reads, split-brain. It cites three major incidents: GitHub's 2018 primary-primary split-brain that took 24 hours to recover, an AWS RDS/Aurora AZ outage where cross-AZ failover wasn't enabled, and Alibaba Cloud Hong Kong Zone C's 15+ hour outage caused by proxy services co-located in the same AZ.

Five-Layer HA Model

High availability is not a single technology but a stack of guarantees:

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

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

Layer ③ GTID : After failover, how does replica know which transactions it's missing? Keywords: Global transaction ID, auto-position.

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

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

Single-Node Durability: WAL, Redo/Undo/Binlog, 2PC

The foundation is single-node persistence: a MySQL instance must never lose a transaction it acknowledged as committed, and must never leave uncommitted transactions behind. This relies on three logs plus two-phase commit.

Three Logs and Their Roles

redo log (InnoDB engine, Physical — page offset changes): Crash recovery — redo committed transactions (roll forward). 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.

Key distinction: redo is the engine's internal ledger (fixed-size, circular); binlog is the external change stream (append-only, archivable). Replication and PITR depend on binlog; crash recovery depends on redo.

Write-Ahead Logging (WAL)

InnoDB follows WAL: when modifying data, it doesn't immediately flush data pages (random I/O, slow). Instead, it sequentially writes "what changed" to redo log (sequential I/O, fast); data pages can be flushed later. Benefit: commit only waits for redo to hit disk, not data pages — major speedup. Even if the machine crashes before data pages flush, restart can redo them from the log.

Two-Phase Commit (2PC) Between 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 forever lacks that transaction). MySQL uses internal 2PC to guarantee both logs either both take effect or neither:

redo prepare

binlog write (this is the commit point)

redo commit

Binlog durability is the ultimate arbiter of whether a transaction counts as committed.

Crash Recovery: Deciding Fate of PREPARE Transactions

On restart, InnoDB scans redo for transactions in PREPARE state (have prepare but no commit). For each, it checks whether the transaction's binlog was fully flushed to disk. If yes → commit; if no → rollback. This ensures the primary's recovery result matches what replicas see via binlog.

"Dual-1" Parameters: The Baseline for No Data Loss

innodb_flush_log_at_trx_commit = 1

: Flush redo to disk on every commit. Values 0/2: batch flush, lose last ~1 second on crash. sync_binlog = 1: Flush binlog to disk on every commit. Values 0/N: batch flush, lose last several transactions on crash.

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

Master-Slave Replication: Extending Durability Across Nodes

Even a perfectly durable single node is useless if the machine dies. Data must be replicated in real time to a replica.

Replication Essence

Replication is simply shipping the primary's binlog to the replica and replaying it.

Three Threads

Async Replication: Default, Fastest, May Lose Data

Default mode: primary commits and immediately returns to client without waiting for replica. Pros: fast, primary not blocked by replica. Cons: on primary crash, transactions acknowledged but not yet shipped to replica are lost — amount depends on replication lag at that moment.

Semi-Sync Replication: At Least One Replica Acknowledges

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

AFTER_SYNC (5.7+ default, recommended) : After primary binlog flushed to disk, before engine commit. Consistency: Strong — acknowledged transactions are externally visible. 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 doesn't — "phantom" data.

Key parameters: rpl_semi_sync_master_wait_for_slave_count (how many replicas must ack, typically 1); rpl_semi_sync_master_timeout (timeout in ms). Critical caveat: on timeout, semi-sync degrades to async! This is the "nominally strong, silently falls back" pitfall. Production core systems set timeout to 86400000 (24 hours) to effectively disable degradation, combined with three-data-center deployment to achieve RPO=0.

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

GTID: Making Replication and Failover "Identifiable and Non-Duplicating"

Pain Points of Traditional Binlog Position

Pre-GTID, replicas tracked progress via primary-local file name + 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. During failover, DBAs had to manually map "last transaction on replica" to "position on new primary" — error-prone, causing data loss or duplicate execution.

GTID: Global Identity Card for Every Transaction

GTID = 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 — wherever it's replicated, its ID stays the same.

Replicas maintain a gtid_executed set, compactly represented as intervals: gtid_executed = 3E11FA47-...:1-100:102-105 (missing 101).

Auto-Position: Automatic Alignment After Failover

With GTID enabled, replicas connect using MASTER_AUTO_POSITION=1. They report their GTID set; the new primary automatically computes and sends the missing transactions (set difference). This is the foundation that makes automated repointing (Orchestrator) safe.

Idempotency: No Duplicate Execution

Before applying a transaction, the replica checks if its GTID is already in gtid_executed. If present, it skips — guaranteeing a transaction never replays twice (e.g., after topology changes push the same transaction twice).

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

Failover Consistency: No Data Loss, No Split-Brain

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

Three-Step Failover

Fencing: Preventing the Old Primary from "Resurrecting"

Most dangerous scenario: old primary isn't truly dead (just network partition / pseudo-hang). After failover, it recovers and continues accepting writes — now both old and new primaries write, causing split-brain with divergent data that's extremely hard to repair.

Fencing = ensuring the old primary can never write again. Techniques: set read_only on old primary, kill its connections, deregister its metadata.

Planned (PRS) vs Emergency (ERS) Reparenting

PRS (Planned Reparent) : Trigger: Admin-initiated. Old Primary: Alive, can cooperate. RTO: Near-zero (catch up then switch). Priority: Orderly, zero-loss.

ERS (Emergency Reparent) : Trigger: Primary already down. Old Primary: May be unreachable. RTO: Minimize (pick least-lagged replica). Priority: Speed, prevent split-brain first.

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

Orchestrator: Leader Election and Probe-Agent

Previous chapters defined "how to fail over safely." But at 3 AM when primary crashes, relying on humans to "detect → judge → elect → execute" is too slow and error-prone.

Orchestrator (orc) is the brain that automates this entire flow; probe-agent is its deployed "eyes and hands" on every DB host.

Topology Discovery: Mapping the Replication Graph

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

Failure Detection: The Refutation Method (Orc's Best Lesson)

Biggest pitfall: orc can't connect to primary → declares primary dead → false failover. Network blips or orc-to-primary link failures cause misjudgment; false failover is extremely costly (unnecessary failover or even split-brain).

Orc's solution: refutation method (holistic / cross-check) . To judge if primary is truly dead, don't just check if orc can connect; check "can the primary's replicas still connect to it?"

DeadMaster : Primary unreachable, and replicas also unreachable → 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 connect, but replicas still can → No (suspected network issue)

UnreachableMasterWithLaggingReplicas : Can't connect + replica lag increasing → Observe

LockedSemiSyncMaster : Semi-sync stuck (waiting for replica ack, writes blocked) → Yes (special handling)

MasterNotWriteAble : Primary alive but not writable (e.g., accidental read_only) → Case by case

Candidate Selection: Who Becomes New Primary

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

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

Promotion rule: admin-assigned priority — must/prefer/neutral/prefer_not/must_not. E.g., cross-dc read-only replica marked must_not never gets promoted.

Candidate marking: entries in candidate_database_instance table.

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

Failover Execution: Promote + Repoint + Callbacks

Probe-Agent (orc-agent): Filling "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 failing to connect can't distinguish "DB truly dead" from "network broken" or "DB too busy to respond." Probe-agent runs locally on the DB host and sees internals: heartbeat.go: Periodic heartbeat ( hostname -I for local IP + timestamp) to orc. Host-side active proof "I'm alive", distinguishes "DB dead" from "network down". load_checker.go: Checks local load (connections, threads) via NewConfigurableLoadChecker. External view can't see real-time host load. big_transaction.go: Uses primary-replica GTID diff via GtidComparisonChecker to detect long-running transactions; records primary/replica GTID snapshots at failure time. Must know before failover: large transactions make switch slow and risky. disk_check.go: Disk probing ( disk failure is JD's #1 host failure ). Writes to local disk. probe_agent.go: Orchestrator: write detection ( enableWriteDetect), consecutive failure counting ( consecutiveFailures). Local "can I really write?" is far more reliable than external ping.

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

Division of labor:

Layered High Availability

We push HA to the extreme at each layer. Production uses semi-sync with no degradation to guarantee RPO=0; future three-data-center construction 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 "brief unavailability windows," if applications treat every write as "must synchronously succeed or error to user," a database blip cascades into application blip.

Truly resilient systems = application and database doing HA together — during those tens of seconds of DB unavailability, can the application gracefully degrade instead of avalanching?

Common application-side fallback patterns:

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

Eventual consistency : Don't require "write completes → globally visible immediately"; allow convergence over time. Applicable: Counters, status sync, cross-system data distribution.

Read degradation : When primary unavailable, read requests go to replica/cache, returning slightly stale but usable data. Applicable: Read-heavy, write-light, display scenarios tolerating brief staleness.

Write degradation / fallback storage : When DB write fails, persist locally / cache / fallback table; replay after recovery. Applicable: Critical writes that can't be lost but can tolerate delay.

Fast fail + circuit breaker : DB probe detects unavailability → immediate fail + circuit break, preventing connection pool exhaustion from slow queries. Applicable: Basic self-protection for all scenarios.

Database side pushes HA layer by layer to minimize the "unavailability window"; application side embraces "database may be briefly unavailable at any time" and prepares degradation plans to survive that window. Both systems meeting halfway — that's what it truly means to treat database HA as a "top data priority."