A promoted MySQL replica is not a recovered database service. Recovery is complete only when the old writer is fenced, the right successor owns writes, every traffic path agrees, and uncertain client transactions have a reconciliation path.

Situation

RetailCo runs one MySQL order database with 36 replicas. Checkout and order-state mutations use the source. Customer order history, store operations, reporting, backup extraction, and downstream change consumers use different replicas.

Connecting all 36 replicas directly to one source is simple to draw and expensive to operate. The source must maintain every replication connection and send the binary-log stream repeatedly. RetailCo instead uses three direct intermediate replicas. Each intermediate feeds eleven leaves, producing 36 replicas in total:

flowchart TD
  Apps[applications] --> Front[stable proxy front door]
  Front --> Proxy[ProxySQL fleet]
  Proxy --> Source[MySQL source — writes]
  Proxy --> Readers[eligible read pools]
  Source --> IA[intermediate A — preferred candidate]
  Source --> IB[intermediate B — preferred candidate]
  Source --> IC[intermediate C — preferred candidate]
  IA --> LA[eleven leaf replicas]
  IB --> LB[eleven leaf replicas]
  IC --> LC[eleven leaf replicas]
  LA --> Readers
  LB --> Readers
  LC --> Readers

The intermediate layer reduces direct fan-out from the source and creates clear promotion branches. It also creates new failure modes. If Intermediate B fails, eleven replicas lose their source. If Intermediate A is promoted, the other branches must be reattached without losing track of transaction history. Every intermediate must retain binary logging and replica-update logging so it can serve its descendants. MySQL 8.4 documents that replica-update logging is enabled by default when binary logging is enabled, but production should verify the effective settings rather than trust a default: replica options.

RetailCo uses two control-plane components:

  • Orchestrator discovers the topology, analyzes failures, chooses a compatible successor, promotes it, and reparents replicas.
  • ProxySQL pools connections, groups backends by role, observes health and read_only, and routes application traffic.

Neither product turns replication into horizontal write scaling. This topology remains one write domain. The replication article explains the durability boundary; the sharding article explains how multiple MySQL clusters create independent write ownership.

RetailCo is a reference architecture, not a claimed customer deployment. The 36-replica topology and controls below are proposed. This article presents no measured failover time, RPO, RTO, client-error rate, routing-convergence time, or data-loss result.

Treat Orchestrator as an archived upstream plus a pinned downstream build

The original OpenArk repository was archived on February 18, 2025. As of this article’s March 31, 2026 cutoff, there was no newly announced upstream maintainer to treat as a drop-in continuation. Percona published and used its own fork, while explicitly stating that it was not the public maintainer of the upstream project and modified the fork primarily for its operators: Percona Orchestrator fork.

That chronology matters. An architecture review should not treat the archived OpenArk repository as an actively maintained release channel or assume that independently carried patches are interchangeable. Pin the exact repository, commit, build flags, configuration schema, and operational tests. For a critical failover control plane, ownership and patch provenance are architecture inputs.

The Problem

The tempting failure workflow is too short:

source fails
    ↓
Orchestrator promotes a replica
    ↓
ProxySQL routes to it
    ↓
done

At least five independent decisions are hidden inside “done”:

  1. Detection: Is the source dead, or can Orchestrator merely not reach it?
  2. Fencing: Can the old source still accept writes from existing sessions, another proxy, a batch host, or a different network partition?
  3. Durability: Which candidate has received and applied the acknowledged transaction set?
  4. Topology: Which candidate can safely feed the surviving replica branches?
  5. Routing: Have all ProxySQL nodes stopped using the old writer and converged on the new one?

These questions belong to different components:

ResponsibilityOwnerDangerous assumption
Observe MySQL replication topologyOrchestratorOne failed probe proves the source is dead
Choose and promote a successorOrchestratorMost current always means operationally eligible
Stop the old source from writingInfrastructure fenceread_only or proxy removal is a hard fence
Move client trafficProxySQLOne proxy update changes every proxy atomically
Retry and reconcile uncertain commitsApplicationA broken connection means the transaction rolled back
Repair and reseed failed nodesDBA automationRepointing replication repairs divergent data

The worst failure is not a slow promotion. It is two writable histories: Orchestrator promotes Intermediate A while the isolated old source continues serving sessions through a proxy that did not update. Both databases can return successful commits. GTID can identify transactions; it cannot decide which business history should win.

The architecture question is therefore: how does RetailCo make detection, fencing, promotion, routing, and application recovery one observable workflow without pretending they are one atomic operation?

Build a Failover Control Plane

Give every replica an explicit role

Orchestrator’s recovery documentation explains why the most advanced replica is not automatically the right successor. Version, binary-log configuration, replication filters, hardware, and the ability to preserve serving capacity all matter: topology recovery.

RetailCo classifies nodes before failure:

Replica classTopology rolePromotion ruleApplication traffic
Three direct intermediatesFeed leaf branches and receive the source streampreferNone or tightly bounded reads
General serving leavesCustomer and store readsneutral or prefer_notYes
Reporting or ETL leavesLong-running analytical readsmust_notIsolated reporting traffic
Delayed recovery replicaHuman-error recovery windowmust_notNo normal application traffic
Backup sourceSnapshot and backup workmust_notNo application traffic
Filtered replicaSpecial-purpose subsetmust_notOnly its intended workload

The three intermediates should match the source’s MySQL version, character sets, replication filters, durability settings, storage capability, binary-log format, and schema. They need GTID, unique server identifiers, binary logging, and replica-update logging. Candidate registration is operational state, not a one-time installation task. Orchestrator documents prefer, neutral, prefer_not, and must_not promotion rules and notes that dynamic rules expire; configuration automation must refresh and monitor them.

GTID makes transaction identity portable across topology changes. A replicated transaction retains the GTID assigned at origin, and auto-positioning lets a replica request transactions it has not executed: GTID concepts. GTID does not prove zero RPO. The successor still needs the relevant transactions in its executed or recoverable relay-log state.

Semisynchronous replication also needs candidate alignment. An acknowledgment proves that an enabled semisynchronous replica received the transaction into its relay log; it does not prove that an arbitrary promotion candidate applied it. RetailCo should restrict acknowledgment duty to eligible, durable candidates, compare GTID received and executed state, and decide whether promotion waits for the applier or fails closed. Orchestrator exposes controls to fail or delay promotion when the candidate’s SQL thread is not current: recovery configuration.

Keep the control plane highly available but separate from the data plane

Run three Orchestrator nodes across failure domains in Raft mode. The Raft leader is the only node allowed to change topology; a partitioned node without quorum cannot lead. This protects Orchestrator’s decision plane. It does not create quorum for MySQL transactions: Orchestrator Raft.

Run multiple ProxySQL instances behind a stable network endpoint or close to application pools. ProxySQL Cluster synchronizes users, servers, variables, and query rules through peer checksum and version comparison. It does not accept the application’s connection on behalf of another failed ProxySQL node, so RetailCo still needs a load balancer, service discovery, or local proxy deployment.

flowchart TD
  App[application connection pools] --> LB[stable network endpoint]
  LB --> P1[ProxySQL core A]
  LB --> P2[ProxySQL core B]
  LB --> P3[ProxySQL core C]
  P1 --> Writer[writer hostgroup]
  P2 --> Writer
  P3 --> Writer
  P1 --> Read[reader hostgroups]
  P2 --> Read
  P3 --> Read
  O1[Orchestrator node A] --> Leader[Raft leader]
  O2[Orchestrator node B] --> Leader
  O3[Orchestrator node C] --> Leader
  Leader --> Topology[MySQL topology actions]
  Leader --> Fence[infrastructure fencing service]
  Leader --> P1

ProxySQL’s traditional replication monitoring uses read_only to move servers between writer and reader hostgroups and can shun replicas that exceed configured lag: backend monitoring. This is routing observation, not successor election. Orchestrator should remain the authority that decides which server becomes writable.

Treat ProxySQL hostgroups as workload contracts

Do not put 36 replicas into one anonymous reader pool. Separate them by acceptable staleness and workload:

Hostgroup 10  writer — mutations and strict read-after-write
Hostgroup 20  customer reads — low-lag serving replicas
Hostgroup 21  store operations — bounded-staleness replicas
Hostgroup 30  reporting — isolated reporting replicas

The application should use separate read-write and read-only credentials or connection endpoints. The default path goes to the writer. Only explicitly safe read operations use reader pools.

A broad query rule that sends every statement beginning with SELECT to a replica is not a transaction model. SELECT ... FOR UPDATE, session state, stored programs, temporary tables, and a read immediately following a write can all violate the assumption. Prefer routing intent at the connection boundary; use SQL rules only for reviewed, testable exceptions.

ProxySQL configuration also has memory, runtime, and disk layers. A database-row update does not affect traffic until it is loaded to runtime, and a runtime change is not restart-persistent until saved: backend server configuration.

Fence before promoting during an ambiguous failure

Fencing means proving the old source cannot accept new business writes. The mechanism depends on the environment:

  • power off or stop the compute instance through an infrastructure control plane;
  • isolate its database network interface or security policy;
  • revoke or rotate a writer lease checked below the application routing layer;
  • detach exclusive storage where the storage architecture supports it;
  • use a hardware or virtualization fencing controller on-premises.

Changing read_only, removing a server from ProxySQL, or changing DNS is not equivalent. Privileged sessions can bypass read-only controls, existing connections may survive, direct clients may bypass the proxy, and configuration can diverge across proxy nodes.

The archived Orchestrator documentation specifies that a failing PreFailoverProcesses command aborts recovery. RetailCo uses that gate for an idempotent fence command. The hook must return success only after the infrastructure control plane confirms the old source is stopped or isolated. If fencing cannot be proven, automatic promotion stops and the incident moves to a human decision. That intentionally trades availability for protection against two writers.

Coordinate topology recovery and routing without confusing them

Use explicit Orchestrator recovery processes or a separate recovery controller to coordinate ProxySQL. Before promotion, the automation can drain the old source using OFFLINE_SOFT or weight zero, but that remains a traffic action rather than a hard fence. After promotion, it removes the old writer, adds the successor to the writer hostgroup, loads the change to runtime, saves it to disk, and verifies every proxy’s runtime state.

The integration contract must define whether a ProxySQL update failure aborts promotion, blocks application readiness, or pages an operator. ProxySQL Cluster can propagate server-module changes, but propagation is not the same as atomic convergence, and OFFLINE_SOFT permits existing connections to complete. Therefore “Orchestrator recovery succeeded” and “application routing recovered” remain separate service states.

flowchart TD
  Detect[Orchestrator detects actionable source failure] --> Confirm{failure eligible for automatic recovery}
  Confirm -->|no| Page[page operator and preserve topology]
  Confirm -->|yes| Fence[pre-failover hook requests hard fence]
  Fence --> Proven{old source isolated}
  Proven -->|no| Stop[abort promotion and escalate]
  Proven -->|yes| Drain[drain old writer from ProxySQL]
  Drain --> Select[select compatible GTID candidate]
  Select --> Catchup[apply required relay-log transactions]
  Catchup --> Promote[promote one writable successor]
  Promote --> Reparent[repair surviving replica branches]
  Reparent --> Route[update ProxySQL writer hostgroup]
  Route --> Converge[verify every proxy checksum and runtime route]
  Converge --> Resume[applications reconnect, retry, and reconcile]

The application should not resume writes merely because the Orchestrator audit says promotion succeeded. A recovery controller or runbook verifies:

  1. the fence token identifies the failed source and incident;
  2. exactly one MySQL node reports writable and carries the expected source epoch or service identity;
  3. each ProxySQL node’s runtime writer hostgroup contains only the successor;
  4. ProxySQL Cluster checksums have converged;
  5. a canary transaction commits through the same endpoint applications use;
  6. uncertain commits from the failure window enter reconciliation.

Make client recovery explicit

Failover breaks in-flight connections. A client can lose its socket after the server commits but before the response arrives. Retrying an unprotected checkout can create a duplicate order or payment.

Every mutation needs a stable idempotency key and a result lookup. After a connection error:

  1. discard the old pooled connection;
  2. reconnect through the stable ProxySQL endpoint;
  3. query the idempotency record on the authoritative writer;
  4. return the existing result if the transaction committed;
  5. retry only if no result exists and the operation is safe to repeat.

ProxySQL moves future connections and queries. It cannot reconstruct a transaction that was active on a dead connection or determine the business result of an ambiguous commit.

Preserve branch capacity during topology repair

If one intermediate fails, the source may still be healthy while eleven leaves are orphaned. Orchestrator can analyze an intermediate-source failure and relocate descendants using GTID-aware topology recovery. The recovery choice is not free:

flowchart TD
  Failure[intermediate B fails] --> Assess[inspect eleven orphaned leaves]
  Assess --> PromoteLeaf{eligible leaf can feed branch}
  PromoteLeaf -->|yes| NewIntermediate[promote eligible leaf as intermediate]
  PromoteLeaf -->|no| Sibling[attach leaves below surviving intermediate]
  NewIntermediate --> Reattach[reparent remaining branch leaves]
  Sibling --> Capacity[verify sender, network, and storage capacity]
  Reattach --> Capacity
  Capacity --> Restore[rebuild failed intermediate and restore redundancy]

A surviving intermediate that was sized for eleven leaves may not safely feed twenty-two. The recovery plan needs sender capacity, network bandwidth, binary-log retention, replication credentials, TLS, and disk headroom. Filtered or delayed leaves must not silently become normal ancestors.

In Practice

For this 36-replica topology, I would use:

  • three direct, identical, lightly loaded intermediate replicas as preferred candidates;
  • eleven leaves below each intermediate, divided into explicit serving, reporting, delayed, and backup roles;
  • GTID auto-positioning and verified replica-update logging on every possible ancestor;
  • semisynchronous acknowledgment only from eligible candidate class nodes, with operational fallback alerts;
  • three Orchestrator nodes in Raft mode, with automatic recovery enabled only for named production clusters;
  • an aborting pre-failover hook that requires infrastructure fence confirmation;
  • three ProxySQL core nodes plus a stable application-facing endpoint;
  • distinct writer and reader connection identities, not generic SELECT regex routing;
  • post-promotion proof that all runtime hostgroups and cluster checksums agree.

Example configuration fragments should be treated as review inputs, not copy-paste production configuration:

{
  "RaftEnabled": true,
  "RaftBind": "orchestrator-a.internal",
  "RaftDataDir": "/var/lib/orchestrator",
  "RaftNodes": [
    "orchestrator-a.internal",
    "orchestrator-b.internal",
    "orchestrator-c.internal"
  ],
  "RecoverMasterClusterFilters": ["retailco-orders"],
  "RecoverIntermediateMasterClusterFilters": ["retailco-orders"],
  "PreventCrossRegionMasterFailover": true,
  "FailMasterPromotionIfSQLThreadNotUpToDate": true,
  "PreFailoverProcesses": [
    "/opt/retailco/bin/proxysql-drain-writer --host {failedHost}",
    "/opt/retailco/bin/fence-mysql-source --host {failedHost} --incident {failureCluster}"
  ],
  "PostMasterFailoverProcesses": [
    "/opt/retailco/bin/proxysql-promote-writer --successor {successorHost} --cluster {failureCluster}"
  ]
}

There is one important thing this configuration does not contain: any ProxySQL setting. Orchestrator has no built-in ProxySQL integration and no ProxySQL* configuration keys. Every ProxySQL action is an external command invoked through the topology-recovery hooks, so RetailCo owns the script, its idempotency, its exit codes, and its timeout behavior. Treat any design document that configures ProxySQL “inside Orchestrator” as unverified.

That distinction has an operational consequence. Because the ProxySQL update is a hook and not a transaction, its exit status is the only signal Orchestrator has. A proxysql-promote-writer script that exits zero after updating one proxy — but before confirming the other two converged — reports success for an incomplete routing change. The script must poll every proxy’s runtime state and cluster checksum before returning, or the recovery audit will record a convergence that never happened.

Do not place the ProxySQL Admin password in a committed example file or pass it on a hook command line, where it is visible in the process table. Use the secret mechanism supported by the pinned deployment and protect the Admin interface (port 6032) with network controls and TLS.

The ProxySQL writer and reader relationship is explicit:

INSERT INTO mysql_replication_hostgroups (
    writer_hostgroup,
    reader_hostgroup,
    comment
) VALUES (10, 20, 'retailco-orders');

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

The production configuration would add each backend’s role, weight, connection limit, TLS setting, and maximum replication lag. It would also separate reporting from customer-serving readers rather than force both through hostgroup 20.

Historical note — from MHA scripts to topology control

MySQL MHA’s manager project describes itself as tooling for automated source failover and fast source switching: MHA manager. That model remains useful historical context. The architectural change with Orchestrator is continuous discovery and refactoring of the whole topology, including intermediate-source failures and successor compatibility.

This is not a claim that every organization migrated from MHA or that Orchestrator is universally safer. Existing MHA deployments can encode years of environment-specific fencing and service-discovery knowledge. Replacing them requires reproducing those controls, not merely installing a newer topology UI.

Test the timeline as separate control loops

Status: PROPOSED LAB — NO EMPIRICAL RESULTS.

Measure these intervals independently:

flowchart TD
  T0[source stops serving correctly] --> T1[Orchestrator declares actionable failure]
  T1 --> T2[infrastructure fence confirmed]
  T2 --> T3[successor selected and caught up]
  T3 --> T4[successor becomes writable]
  T4 --> T5[replica branches begin repair]
  T4 --> T6[ProxySQL writer route changes]
  T6 --> T7[all proxy configurations converge]
  T7 --> T8[first successful application write]
  T8 --> T9[ambiguous transactions reconciled]

Run at least these failure shapes:

  • planned graceful takeover;
  • hard source process and host loss;
  • network isolation where the old source remains alive;
  • fence-service failure;
  • candidate with received but unapplied transactions;
  • most advanced replica marked must_not;
  • intermediate loss with eleven descendants;
  • one ProxySQL node unavailable during the hook;
  • one ProxySQL satellite refusing to converge;
  • Orchestrator leader loss during discovery and during recovery;
  • old source returning after promotion.

Capture client errors, retries, ambiguous commits, GTID received and executed sets, lost replicas, semisynchronous status, recovery audit records, hook exit states, runtime hostgroups, ProxySQL checksums, and the observed RPO and RTO.

Do not publish one “failover took 12 seconds” number. Detection, fencing, promotion, routing convergence, first successful application write, and full topology repair answer different operational questions.

Where It Breaks

Failure or design errorConsequenceRequired control
Orchestrator loses contact but old source remains writableTwo writers can form after promotionHard fence proof before unplanned promotion
OFFLINE_SOFT treated as fencingExisting or bypass connections can still writeUse it for drain; fence below the proxy
ProxySQL hook fails but Orchestrator succeedsNew source exists while traffic still targets old routeSeparate topology and routing recovery states; alert and reconcile
ProxySQL nodes have different configurationsClients see different writers or reader poolsVerify runtime hostgroups and cluster checksums on every node
Most advanced replica is filtered or undersizedPromotion loses capacity or data scopeExplicit candidate roles and promotion rules
Semisynchronous acknowledgment comes from an ineligible replicaPromoted candidate may lack acknowledged transactionsLimit acknowledgers and inspect received, relay, and executed state
One intermediate failsEleven leaves become orphanedPreplanned branch recovery and spare sender capacity
Query rules send unsafe reads to replicasRead-after-write and locking semantics breakSeparate endpoints or users; narrow allowlisted rules
Old source rejoins automaticallyDivergent history can contaminate topologyKeep fenced; compare GTID sets; rebuild or perform reviewed reconciliation
Orchestrator Raft loses quorumAutomated topology changes stopPreserve MySQL state, restore control-plane quorum, use reviewed manual procedure
Application blindly retriesDuplicate orders or paymentsIdempotency keys and authoritative result lookup
Thirty-six replicas are treated as backupsReplicated deletion reaches every nodeIndependent backups and tested point-in-time recovery

When I would not use this architecture

I would not build a 36-replica tree when the read workload can be reduced with query repair, caching, a search index, a warehouse, or fewer larger replicas. Every replica adds patching, backup, monitoring, credential, schema, and recovery work.

I would not combine ProxySQL and Orchestrator without a team that can own infrastructure fencing and application idempotency. The tools automate topology and routing; they do not remove the need to choose availability versus split-brain risk during an ambiguous partition.

For new deployments that can adopt Group Replication or a managed database, compare their failure contract with this external control plane. For genuine write-capacity limits, do not add more replicas—use the sharding or decomposition options elsewhere in this series.

RetailCo verdict

Good fit:               Large self-managed MySQL replica fleets with one write source
Poor fit:               Small fleets, weak operational ownership, or write-scale problems
Recommended option:     Orchestrator for topology, ProxySQL for traffic, hard external fencing
Main advantage:         Topology-aware recovery and workload-aware routing remain separate
Main limitation:        The combined workflow is not atomic across infrastructure and clients
Operational complexity: High; automation must cover failure, convergence, rebuild, and audit
Migration complexity:   Medium to high; introduce observation, then routing, then recovery

The conclusion is narrower than “ProxySQL plus Orchestrator gives automatic HA.” It gives RetailCo the components for an HA control plane. The production guarantee comes from the contract between them: one eligible successor, one verified fence, one converged writer route, and an application that can resolve uncertain transactions.

What to Do Next

  • Problem: Inventory every path that can write to MySQL, every replica role, and every place where current failover assumes unreachable means dead.
  • Solution: Separate detection, hard fencing, candidate promotion, topology repair, ProxySQL convergence, and application reconciliation into explicit states.
  • Proof: Run planned, hard-failure, network-partition, proxy-divergence, intermediate-loss, Raft-leader-loss, and old-source-return exercises with timestamped evidence.
  • Action: Begin in observation-only mode, register and verify candidate rules, route read-only traffic through ProxySQL, rehearse planned takeover, and enable automatic unplanned recovery only after the fence gate passes repeatedly.

The next article returns to application architecture: decomposing a monolithic database write path without beginning with a disruptive microservices rewrite.

References