Auto slave promotion without any DBAs intervention using Orchestrator and ProxySQL. If you are looking for a HA solution without going for synchronous replication or Aws RDS then Orchestrator with ProxySQL is a great choice.

sukan August 02, 2019

Subscribe for email updates

In a typical production environment, setting up high availability is one of the most crucial tasks. Having said that most of the setups do not have a HA solution. This is because a traditional MySQL setup doesn’t have an inbuilt failover solution. Luckily we have few options to go for HA.

HA based on synchronous replication

Cluster based solution would always provide HA. MySQL Cluster, Galera Cluster, InnoDB Cluster compromises of 2 or more servers (nodes) which can act as a HA. There are setups where implementing cluster would bring a lot of issues like deadlocks, increased transaction time as data volume goes up. These setup would have higher transactions flowing between the nodes which would cause a negative performance impact .

HA based on asynchronous replication

For a traditional MySQL setups one can use a replication manager / load balancer with failover support and achieve high availability. MHA, orchestrator are some of the replication managers. Orchestrator has lot of support and advanced features comparted to MHA.

In this blog, let us see how we have achieved HA using Orchestrator and ProxySQL

Background of the setup

3 MySQL servers, 1 master and 2 replicas

3 servers running with Orchestrator/raft and ProxySQL

How orchestrator works?

Orchestrator has three different phases namely,

Discovery

Refactoring

Recovery

Discovery phase of orchestrator detects the topology and understands the configuration of backend servers. It reads the replication status, configuration and understands the topology.

Refactoring phase will take care of replication rules, it will understand the binary log ordinates, GTID position and it initiates a recovery process.

Recovery phase of orchestrator will pick the ideal candidate as a master and will apply the promotion rules such as disabling read_only mode and repointing the available replica’s beneath the newly promoted master server.

Broadcasting the topology changes

Orchestrator can successfully detect the master failure and promoted a new master. Now application has to identify the changes and send the traffic to the current master. We have few options to notify the application about the changes.

By using VIP we can detach the machine from the old master and attach to the new master.
By using a Load balancer like ProxySQL.

Author Bio

sukan

Sukan is Database Team Lead at Mafiree with over a decade of experience in database systems, architecture, and performance optimization. He specializes in MySQL, MongoDB, TiDB, and ClickHouse, developing architectural improvements that make data platforms faster, more efficient, and cost-effective. Sukan writes about practical database engineering topics, real-world performance tuning, data replication, and high-scale system design, drawing from extensive hands-on experience solving complex technical challenges.

Name *

Email *

Comment *

Schema changes on large MySQL tables can bring production systems to a halt if not handled correctly. This case study walks through how Mafiree helped a fintech client execute a zero-downtime MySQL schema migration on a 500M+ row production database — covering the real challenges faced, the three-phase tool strategy using gh-ost, pt-online-schema-change, and MySQL 8.0 INSTANT DDL, production configuration settings with performance benchmarks, and best practices for safely evolving your MySQL schema without impacting users

893 views

MySQL Architecture Explained: Performance Tuning & Troubleshooting Guide

MySQL features a unique tiered architecture that separates query processing from data storage through its pluggable storage engine model. This guide explores the core components—from connection handling and the SQL optimizer to the physical storage of data on disk. By understanding how engines like InnoDB provide ACID compliance and row-level locking, you can significantly improve your database's scalability. We also break down the query execution workflow and provide actionable tips for performance tuning, such as optimizing the buffer pool. Whether you're managing a replica set or a standalone instance, mastering MySQL’s internal structure is essential for building high-performance applications.

635 views

The Evolution of MariaDB : Latest Features and What It Offers Compared to MySQL

Discover how MariaDB 11.x is redefining open-source databases with cutting-edge features like system-versioned tables, native AI-ready vector support, UUIDv7 for scalable inserts, and enterprise-grade security—all in the Community Edition, without the paywall.

2413 views

Stop Hackers at the Gate: Restricting Brute-Force Attacks with MySQL’s Connection Control Plugin

“Fortify Your MySQL Security: Slow Down Attackers with Connection Control Plugin” Learn how the MySQL Connection Control Plugin helps defend against brute-force login attempts by introducing intelligent, progressive delays—without locking out legitimate users.

1333 views

8 Major MySQL 8.4 Changes That Every Team Should Review Before Migration

Explore the 8 critical changes in MySQL 8.4 you need to know before migrating your production environment. From authentication updates to InnoDB defaults, this release brings significant operational implications for developers and DBAs.

9247 views