Database HA Deep Dive: Five Layers from WAL to Automated Failover
This article systematically explains database high availability through five layers: single-node crash recovery via WAL and two-phase commit, replication modes (async vs semi-sync) and RPO trade-offs, GTID-based global transaction identification for safe failover, fencing and PRS/ERS switching protocols to prevent split-brain, and Orchestrator's cross-check failure detection with probe-agent for automated failover.
Introduction: Why Database High Availability Matters
Databases are stateful services at the end of the application chain. While cache failures can fall back to origin and gateway failures can reroute traffic, database failures cause irreversible damage: lost writes, wrong reads, and replication chaos. Real-world incidents illustrate the stakes:
GitHub primary split-brain : Network partition made both primary and replica writable; auto-failover script failed; manual recovery took 24 hours; global pushes, PRs, and CI halted. Postmortem admitted "our HA mechanism didn't work."
AWS RDS/Aurora AZ failure : Single-AZ RDS instances became unavailable; replica in another AZ lacked auto-failover; businesses waited for AWS fix; e-commerce lost orders and faced refund floods.
Alibaba Cloud Hong Kong AZ C outage (2022-12-18, >15h) : Cross-AZ RDS instances failed because proxy service was deployed in the same AZ C; proxy death blocked access and prevented failover. Single-AZ HA instances froze. Some customers suffered >24h downtime, including Macau Monetary Authority, Galaxy, and Aomi. Alibaba called it "the longest large-scale failure in over a decade of operations."
Panorama: Five Layers Stacked for Complete HA
High availability is not a single technology but layered guarantees. Each layer achieves a degree of HA; stacking all five yields the most complete state:
Layer 1: Single-node — Question: Machine loses power — are committed transactions still there? Keywords: WAL, 2PC, crash recovery.
Layer 2: Replication — Question: How does primary data reach replica? How much can be lost? Keywords: Async / semi-sync, RPO.
Layer 3: GTID — Question: After failover, how does replica know which transactions it's missing? Keywords: Global transaction ID, auto-position.
Layer 4: Failover — Question: How to promote a replica without data loss or split-brain? Keywords: Catch-up, fencing, PRS/ERS.
Layer 5: Automation — Question: Who detects, decides, and executes? Keywords: Leader election, probe-agent.
Chapter 1: Single-Node Consistency — How a Transaction Survives Crash
The foundation of HA is single-node durability : after a MySQL restart, transactions acknowledged as committed must never be lost; uncommitted ones must never remain. This is achieved via three logs + two-phase commit.
1.1 Three Logs and Their Roles
Many confuse redo, undo, and binlog. Their division of labor is clear:
redo log (InnoDB engine layer, physical log): Crash roll-forward for committed transactions → durability. Written by InnoDB.
undo log (InnoDB engine layer, logical log): Rollback uncommitted transactions; support MVCC snapshot reads. Written by InnoDB.
binlog (MySQL Server layer, logical log): Replication + Point-in-Time Recovery (PITR) . Written by Server.
Key distinction: redo is the engine's internal ledger; binlog is the external broadcast change stream . Redo is fixed-size, circular; binlog is append-only, archivable. Replication and PITR rely on binlog; crash recovery relies on redo.
1.2 WAL: Write Log First, Flush Data Pages Later
InnoDB follows WAL (Write-Ahead Logging) : when modifying data, don't immediately flush data pages to disk (random I/O is slow). Instead, sequentially write "what changed" into redo log (sequential I/O is fast); data pages can be flushed later. Benefit: Commit only waits for redo sequential fsync, not data page random fsync , greatly speeding up commits. Even if data pages aren't flushed and the machine crashes, restart can use redo to "roll forward" them.
1.3 2PC: Two-Phase Commit Between Redo and Binlog
Problem: redo (engine layer) and binlog (Server layer) are two independent logs . If only one is written before crash, primary and replica become inconsistent (e.g., redo committed but binlog missing → replica permanently lacks that transaction). MySQL uses internal two-phase commit to guarantee both logs "either both take effect or neither does": Sequence is critical: redo-prepare → binlog-write → redo-commit . Binlog successfully written is the core basis for the "commit point."
1.4 Crash Recovery: Deciding Fate of Prepared Transactions
On restart, InnoDB scans redo. For transactions in PREPARE state (have prepare but no commit), it decides:
If binlog contains the transaction's XID → commit (roll forward)
If binlog lacks the XID → rollback (roll back)
Conclusion: Whether binlog is fully persisted is the final verdict on whether a transaction counts as committed . This ensures primary's recovery result always matches what replicas see via binlog.
1.5 Key Parameters: What Is "Dual 1"
"Dual 1" refers to two parameters set to 1: innodb_flush_log_at_trx_commit=1: Flush redo to disk on every commit. Values 0/2: batch flush; crash loses last ~1 second. sync_binlog=1: Flush binlog to disk on every commit. Values 0/N: batch flush; crash loses recent N transactions.
"Dual 1" (both = 1) = prerequisite for single-node not losing committed transactions . 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-done transactions, and dual-1 to guarantee durability. This layer ensures "one machine doesn't lose data itself" — but what if the whole machine dies? That's where replication comes in.
Chapter 2: Primary-Replica Replication — How Data Flows from Primary to Replica
No matter how reliable a single node is, machine crashes or disk failures lose data. So data must be replicated to replicas in real time . Replication's essence: ship primary's binlog to replica and replay it .
2.1 Replication Pipeline: Three Threads
Primary binlog dump thread → replica I/O thread receives → replica SQL thread applies.
2.2 Async 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: At the moment primary crashes, transactions already acknowledged but not yet shipped to replica are all lost — loss amount depends on replication lag at that moment.
2.3 Semi-Sync Replication: At Least One Replica Acknowledges
To shrink the data-loss window, semi-sync was introduced: primary waits for at least N replicas to confirm 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. Strong consistency: visible only after ack. Fewer phantom reads.
AFTER_COMMIT : After engine commit , then wait for replica ack. Weak consistency: primary visible but replica may not have it. Primary can read, replica lacks — "phantom" data.
Key parameters: rpl_semi_sync_master_wait_for_slave_count: how many replicas must ack; production sets to 1. rpl_semi_sync_master_timeout: wait timeout (ms). On timeout, falls back to async! — this is the semi-sync pitfall: "nominally strong, actually may silently degrade." Network blip → reverts to async.
Production core systems set timeout to 86400000 (24h), effectively disabling fallback; combined with three-data-center deployment, this achieves RPO=0 .
Summary: Replication extends "single-node doesn't lose" to "one machine down, another still has data." Async is fast but loses more; semi-sync trades one network round-trip for no data loss. But — when primary truly dies, how does replica know which transactions it's missing and where to resume? That's where GTID comes in.
Chapter 3: GTID Mode Principles — Making Replication and Failover "Identifiable, No Duplicates"
3.1 Pain Points of Traditional Binlog Positioning
Pre-GTID, replicas tracked progress via binlog filename + position (e.g., mysql-bin.000123:4567). Problems:
Position is primary-local ; after failover, same transaction has completely different position in new primary's binlog.
During failover, DBA must manually find "replica's last transaction → corresponding position in new primary's binlog" — error-prone; mistakes cause data loss or duplicate execution.
3.2 GTID: Global ID Card for Every Transaction
<strong>GTID (Global Transaction ID) = server_uuid:transaction_id</strong>, e.g., 3E11FA47-...:23. server_uuid: unique ID of the instance where transaction originally executed. transaction_id: monotonically increasing sequence number on that instance.
A transaction's GTID is globally unique and immutable for life — wherever replicated, its ID stays the same.
Replica maintains a GTID set , compactly representing executed transactions via intervals:
gtid_executed = 3E11FA47-...:1-100:102-105
(missing 101)3.3 Auto-Position: Automatic Alignment After Failover
With GTID enabled, replica connects to primary using MASTER_AUTO_POSITION=1:
Core value: After failover, replicas don't need manual position hunting; they report their GTID set, new primary automatically sends the difference set. This is the foundation for safe automated repointing (Orchestrator).
3.4 Idempotency: No Duplicate Execution
Before applying a transaction, replica checks if its GTID is already in gtid_executed — if present, skip . Guarantees same transaction never replays twice (e.g., topology changes after failover pushing 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."
Chapter 4: Failover Consistency — No Data Loss, No Split-Brain
When primary dies (or planned maintenance requires switchover), a replica must be promoted to new primary and other replicas repointed. Two fears:
Data loss
Dual-primary (split-brain)
4.1 Three-Step Failover
1. Select candidate → 2. Fence old primary → 3. Promote new primary & repoint replicas.
4.2 Catch-Up: Replica Must Be Up-to-Date Before Promotion
Before promoting, ensure candidate has received and applied all possible transactions from old primary (especially critical in semi-sync where primary may have unacked transactions).
4.3 Fencing: Prevent Old Primary "Resurrection"
Most dangerous scenario: old primary isn't truly dead (network partition / false death), comes back accepting writes after failover — now both old and new primary write → split-brain , data diverges, extremely hard to repair. Fencing = guarantee old primary can never write again . Methods:
On old primary side: set read_only / kill connections / deregister metadata.
4.4 Planned (PRS) vs Emergency (ERS) Reparenting
Vitess/orchestrator defines two reparent types:
PRS (Planned Reparent) : Trigger: Planned (maintenance, upgrade). Old Primary: Alive, can cooperate with demotion. RTO: Near-zero (catch-up then switch). Key: Orderly, zero-loss.
ERS (Emergency Reparent) : Trigger: Primary already dead. Old Primary: May be unreachable. RTO: Minimize (pick least-lag replica). Key: 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 these three steps? Manual 3 AM wake-ups are too slow and error-prone. That's what Orchestrator solves.
Chapter 5: How Orchestrator Elects Leaders and probe-agent's Role
Previous chapters explained "how to failover 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 .
5.1 Topology Discovery: Map the Replication Graph
Orc doesn't need manual registration. Given any instance, it actively crawls via SHOW SLAVE HOSTS / SHOW SLAVE STATUS to discover the entire replication topology (who is primary, who hangs under whom) and continuously refreshes it. This is the basis for all subsequent judgments.
5.2 Failure Detection Essence: Proof by Contradiction (Orc's Most Valuable Lesson)
Biggest pitfall: orc can't connect to master → judges master dead → false failover. Network blip or orc-to-master single-path failure causes misjudgment; false failover cost is extreme (unnecessary failover or even split-brain). Orc's solution: Proof by contradiction (holistic / cross-check) : to judge if master is truly dead, don't just check if orc can connect; check "can master's replicas still connect to it?" .
Analysis codes and meanings: DeadMaster: Master unreachable, and replicas also unreachable → Trigger failover (truly dead). DeadMasterAndSomeReplicas: Master dead, some replicas also lost → Trigger failover. DeadMasterAndReplicas: Master dead, all replicas lost → Trigger failover. DeadMasterWithoutReplicas: Master dead, no replicas → Per config. UnreachableMaster: Orc can't connect, but replicas still can → No failover (suspected network issue). UnreachableMasterWithLaggingReplicas: Can't connect + replica lag increasing → Observe. LockedSemiSyncMaster: Semi-sync stuck (waiting for replica ack, writes blocked) → Trigger failover (special handling). MasterNotWriteAble: Master alive but not writable (e.g., accidental read_only) → Case by case.
5.3 Candidate Selection: Who Becomes New Primary
After deciding to failover, orc picks from surviving replicas, considering:
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., tag cross-DC read-only replica as must_not so it's never elected.
Candidate marker : preferred candidates registered in candidate_database_instance.
Same DC/cell, version compatible : prefer same region (reduce cross-DC latency); new primary version must not be lower than its replicas (else replication incompatible).
5.4 Failover Execution: Promote + Repoint + Callbacks
Orchestration flow with hooks (pre/post failover callbacks).
5.5 probe-agent (orc-agent): Filling "Internal View + Execution Hands"
Orc is an "external observer"; remote probing has two inherent gaps that probe-agent fills:
Gap 1: External probes can't see host-internal truth. Orc can't connect from outside — can't distinguish "DB truly dead" vs "network broken" vs "DB too busy to respond." Probe-agent runs locally on DB host, sees internals: heartbeat.go: Periodically report heartbeat ( hostname -I for local IP + timestamp) to orc. Host-side active proof "I'm alive"; distinguishes "DB dead" from "network broken". load_checker.go: Check local load (connections, threads) via NewConfigurableLoadChecker. External view can't see real-time host load. big_transaction.go: Use primary-replica GTID diff via GtidComparisonChecker to detect large running transactions; record 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: Integration: write detection ( enableWriteDetect), consecutive failure counter ( consecutiveFailures). Local "can it really write" is far more reliable than external ping.
Gap 2: Orc decides, but needs "hands" on host to execute / fall back.
Division of labor summary : orc = brain (decide), probe-agent = eyes/hands (detect locally, execute locally).
Conclusion: Layered High Availability
Through layered design, each layer pushed to its extreme, HA capability maximized. Production uses semi-sync without fallback to guarantee RPO=0 ; future three-data-center construction will provide data-center-level disaster recovery.
Database HA Isn't Just Database's Job — Application Must Participate
Since databases always have "brief unwriteable windows," if applications design every write as "must sync-write to DB successfully, else error to user," a DB blip cascades to application blip. Truly resilient systems are 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 strategies:
Async + MQ Peak Shaving : Write requests enter message queue first; app returns "accepted" quickly; background consumers persist slowly; DB blip → message 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 after a window. Applicable: Counters, status sync, cross-system data distribution.
Read Degradation : When primary unavailable, read requests route to replica/cache, return slightly stale but usable data. Applicable: Read-heavy, write-light; display scenarios tolerating brief staleness.
Write Degradation / Fallback Storage : When DB write fails, store locally/cache/fallback table first; replay compensation 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; prevent connection pool exhaustion by slow queries dragging down entire app. Applicable: Basic self-protection for all scenarios.
Final Word
On the database side, pushing HA layer by layer to the extreme minimizes the "unavailable window"; on the application side, embracing "database may be briefly unavailable at any time" and preparing degradation plans ensures survival within that window. Both systems running toward each other — that's what makes a system truly highly available, and the complete meaning of treating database HA as a "top data priority."
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
