MySQL Orchestrator: Automated Failover and Topology Management

A silent primary failure at 2 AM is every on-call engineer's nightmare: replication lag spikes, writes stall, and the team scrambles to manually promote a replica before SLA timers expire. MySQL Orchestrator eliminates this scenario by continuously mapping your replication topology, detecting failures within seconds, and executing a safe, automated master promotion — all without human intervention. Originally built by GitHub's infrastructure team and now part of the Percona ecosystem, Orchestrator has become the de-facto standard for MySQL high availability at scale. In this guide you will learn how to install, configure, and operate Orchestrator in production, including Raft-based HA for Orchestrator itself and integration with ProxySQL for seamless traffic rerouting.

What is MySQL Orchestrator?

Orchestrator is an open-source MySQL replication topology manager written in Go. It performs three primary jobs:

• Discovery — periodically connects to every known MySQL instance, reads SHOW SLAVE STATUS and SHOW MASTER STATUS, and builds a live graph of who replicates from whom.
• Visualization — exposes that graph through a web UI and a REST API, giving operators a real-time map of lag, GTID positions, and replica counts.
• Recovery — when a master becomes unreachable, Orchestrator runs an election, promotes the best candidate, re-points all surviving replicas to the new master, and fires hook scripts to update routing layers.

Orchestrator stores its state in a backend MySQL (or SQLite) database and — crucially — supports a three-node Raft cluster so that the tool managing your MySQL HA is itself highly available.

Topology Architecture

A typical production setup looks like this:

Each Orchestrator node independently probes every MySQL instance. The Raft leader is the only node that executes recovery actions, preventing split-brain promotions. The backend state database can itself be a replicated MySQL cluster separate from the application topology being managed.

Installation

Orchestrator ships as a single statically-linked binary. Install it on each of your three Orchestrator nodes:

# Download the latest release (adjust version as needed)
wget https://github.com/openark/orchestrator/releases/download/v3.2.6/orchestrator-3.2.6-linux-amd64.tar.gz
tar -xzf orchestrator-3.2.6-linux-amd64.tar.gz -C /usr/local/

# Create a symlink
ln -s /usr/local/orchestrator/orchestrator /usr/local/bin/orchestrator
ln -s /usr/local/orchestrator/orchestrator-client /usr/local/bin/orchestrator-client

# Create directories
mkdir -p /etc/orchestrator /var/log/orchestrator /var/lib/orchestrator

Next, create the topology user on every MySQL instance Orchestrator will manage. This user must exist on master and all replicas:

-- Run on each MySQL instance
CREATE USER 'orchestrator'@'%' IDENTIFIED BY 'str0ng_orch_pass';
GRANT SUPER, PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'orchestrator'@'%';
GRANT SELECT ON mysql.slave_master_info TO 'orchestrator'@'%';
GRANT SELECT ON performance_schema.replication_group_members TO 'orchestrator'@'%';
FLUSH PRIVILEGES;

Configuration

Orchestrator is configured through a single JSON file. Below is a production-ready /etc/orchestrator/orchestrator.conf.json that covers topology credentials, recovery policies, Raft clustering, and ProxySQL integration hooks.

{
  "Debug": false,
  "EnableSyslog": false,
  "ListenAddress": ":3000",

  "MySQLTopologyUser": "orchestrator",
  "MySQLTopologyPassword": "str0ng_orch_pass",
  "MySQLTopologyCredentialsConfigFile": "",

  "MySQLOrchestratorHost": "orch-backend.internal",
  "MySQLOrchestratorPort": 3306,
  "MySQLOrchestratorDatabase": "orchestrator",
  "MySQLOrchestratorUser": "orch_rw",
  "MySQLOrchestratorPassword": "backend_pass",

  "SlaveLagQuery": "",
  "SlaveStartPostWaitMilliseconds": 1000,
  "DiscoverByShowSlaveHosts": true,

  "RecoverMasterClusterFilters": ["*"],
  "RecoverIntermediateMasterClusterFilters": ["*"],
  "RecoveryPeriodBlockMinutes": 30,
  "RecoveryIgnoreHostnameFilters": [],
  "FailMasterPromotionIfSQLThreadNotUpToDate": true,
  "DelayMasterPromotionIfSQLThreadNotUpToDate": true,

  "PromotionIgnoreHostnameFilters": [],

  "DetachLostReplicasAfterMasterFailover": true,
  "ApplyMySQLPromotionAfterMasterFailover": true,
  "MasterFailoverDetachReplicaMasterHost": true,
  "PostponeReplicaRecoveryOnLagMinutes": 0,

  "OnFailoverDetectionProcesses": [
    "/etc/orchestrator/hooks/on_failover_detection.sh {failureType} {failedHost} {failedPort} {successorHost} {successorPort}"
  ],
  "PostMasterFailoverProcesses": [
    "/etc/orchestrator/hooks/post_master_failover.sh {failureType} {failedHost} {failedPort} {successorHost} {successorPort} {lostReplicas}"
  ],
  "PostFailoverProcesses": [],
  "PreGracefulTakeoverProcesses": [],

  "RaftEnabled": true,
  "RaftDataDir": "/var/lib/orchestrator",
  "RaftBind": "0.0.0.0",
  "DefaultRaftPort": 10008,
  "RaftNodes": [
    "orch-1.internal",
    "orch-2.internal",
    "orch-3.internal"
  ],

  "AuthenticationMethod": "token",
  "HTTPAuthUser": "",
  "HTTPAuthPassword": "",

  "HostnameResolveMethod": "default",
  "MySQLHostnameResolveMethod": "@@hostname",

  "UnseenInstanceForgetHours": 240,
  "DiscoveryMaxConcurrency": 300,
  "InstancePollSeconds": 5
}

The most important settings to understand:

• MySQLTopologyUser — the credential Orchestrator uses to probe every managed MySQL instance. It must exist on all nodes with the grants shown in the installation section.
• RecoverMasterClusterFilters — a list of cluster name patterns for which automated master recovery is enabled. ["*"] enables it for all clusters; use ["prod-cluster", "analytics-cluster"] to be selective.
• FailMasterPromotionIfSQLThreadNotUpToDate — blocks promotion of a replica whose SQL thread has not fully applied its relay log. Combined with DelayMasterPromotionIfSQLThreadNotUpToDate: true, Orchestrator will wait for the SQL thread to catch up rather than promoting a lagging replica.
• RaftEnabled / RaftNodes — activates the three-node consensus mode. The Raft leader is the sole node authorized to issue recovery commands, preventing simultaneous conflicting promotions.

Setting PromotionRule on Replicas

Not all replicas are equal candidates for promotion. Tag each replica with its preferred role using the Orchestrator API or CLI:

# Mark replica-1 as the preferred promotion candidate
orchestrator-client -c set-candidate-promotion-rule \
  -i replica-1.internal:3306 \
  --promotion-rule=prefer

# Mark replica-2 as acceptable but not preferred
orchestrator-client -c set-candidate-promotion-rule \
  -i replica-2.internal:3306 \
  --promotion-rule=neutral

# Mark replica-3 (cross-datacenter) as never eligible for promotion
orchestrator-client -c set-candidate-promotion-rule \
  -i replica-3.internal:3306 \
  --promotion-rule=must_not

Valid PromotionRule values are prefer, neutral, prefer_not, and must_not. Orchestrator scores candidates by combining this rule with GTID execution position, replication lag, and slave count.

Automated Failover Walkthrough

When Orchestrator cannot reach a master after consecutive failed probes (controlled by InstancePollSeconds and internal dead-master thresholds), it initiates the following sequence:

1. Dead master detection — multiple Raft nodes independently confirm the master is unreachable to avoid false positives from network partitions.
2. Candidate election — Orchestrator ranks surviving replicas by PromotionRule, GTID errant transactions, replication lag, and replica count beneath them.
3. Relay log draining — if DelayMasterPromotionIfSQLThreadNotUpToDate is true, Orchestrator waits for the winning candidate's SQL thread to fully apply its relay log.
4. Promotion — STOP SLAVE; RESET SLAVE ALL; on the new master, followed by SET GLOBAL read_only=OFF.
5. Re-pointing replicas — all surviving replicas receive CHANGE MASTER TO MASTER_HOST='new-master', MASTER_AUTO_POSITION=1;.
6. Hook execution — PostMasterFailoverProcesses scripts fire with topology metadata injected as environment variables.

ProxySQL Integration via Hook Scripts

The PostMasterFailover hook is where you update ProxySQL's writer hostgroup to point at the new master. A minimal hook script:

#!/usr/bin/env bash
# /etc/orchestrator/hooks/post_master_failover.sh

FAILURE_TYPE="$1"
FAILED_HOST="$2"
FAILED_PORT="$3"
NEW_MASTER_HOST="$4"
NEW_MASTER_PORT="$5"

PROXYSQL_HOST="proxysql.internal"
PROXYSQL_ADMIN_PORT="6032"
PROXYSQL_USER="radmin"
PROXYSQL_PASS="radmin_pass"

mysql -h"${PROXYSQL_HOST}" -P"${PROXYSQL_ADMIN_PORT}" \
      -u"${PROXYSQL_USER}" -p"${PROXYSQL_PASS}" <

# Seed discovery — tell Orchestrator about a new instance
orchestrator-client -c discover -i master.internal:3306

# Print current topology for a cluster
orchestrator-client -c topology -i master.internal:3306

# Manually initiate a graceful master takeover (planned switchover)
orchestrator-client -c graceful-master-takeover -i master.internal:3306

# Force a specific replica to become master (break-glass)
orchestrator-client -c force-master-failover -i master.internal:3306

# Relocate a replica under a different parent
orchestrator-client -c relocate \
  -i replica-3.internal:3306 \
  --sibling replica-1.internal:3306

# List all clusters known to Orchestrator
orchestrator-client -c clusters

# Show current recovery history
orchestrator-client -c recovery-info

# Acknowledge (suppress) a recovery to stop repeated alerting
orchestrator-client -c ack-recovery -i master.internal:3306 --comment "investigated"