Architecture · MySQL and MariaDB
MySQL and MariaDB high availability, designed around how it fails
You're choosing a topology, or the one you have no longer fits. I go through it with your team piece by piece, from replication to the proxies and the second site, and write down what happens when each part fails. If you want, I build it with you.
When this helps
These are the questions this work usually answers. If yours isn't here, it's still worth asking.
- Should we use asynchronous replication, Galera or InnoDB Cluster?
- We're adding a second data centre or region. What changes?
- Reads go to replicas, and some users see stale data. How do we route them?
- Failover still depends on someone deciding at three in the morning. Can that change safely?
- When do we need to think about sharding, and what do we do before then?
What the design covers
Each part is written down with the options I considered and why one was chosen.
Topology choice
Asynchronous or semi-synchronous replication, Galera (including Percona XtraDB Cluster and MariaDB Galera Cluster) or InnoDB Cluster. The choice follows your write pattern and how much data loss you can accept.
Failover and promotion
Something has to notice that the source is gone and promote a replica without ever leaving you with two writers. Orchestrator does that for plain replication, Group Replication does it inside InnoDB Cluster, and a well-tested manual runbook can too. The design says which fits.
Proxies and connection routing
The proxy decides where reads and writes go: ProxySQL, MaxScale, MySQL Router or HAProxy. The design also covers what happens to open connections during a failover, and what the application has to retry.
Multi-site and latency
A second site changes how quorum works. If commits wait on the other site, as they do with Galera or semi-sync, every commit pays the round trip. I look at where the majority lives and at what happens when the link between sites flaps.
Scaling reads and when sharding comes in
Replicas and caching usually come first, along with query changes. If the data or write volume will outgrow one primary, I sketch where the split would fall and what has to be ready before it does.
What you receive
- The topology, with the options considered and the reasons for the choice.
- Failure scenarios and the behaviour you should expect in each.
- How the proxies and the application should handle connections and retries.
- Validation tests to run before you rely on the setup.
- Risks I couldn't close, with what it would take to close them.
- If you want it built: the replication and proxy setup in your environment, with runbooks your team can follow when a node fails.
How it runs
- We agree the question and an effort limit for it.
- Your team shares what exists, from diagrams to configuration files, and I review it.
- I write up the design and go through it with your technical owner.
- If a build is agreed, I set it up with your team, and we run the failover and proxy tests together before handover.
Scope, deliverables, effort limit, fee and schedule are agreed in writing before work begins.
Fixed fee, agreed after a short scope review.
Not included
- Running the system after handover: managed operations, on-call cover or emergency response.
- Guarantees about availability or recovery time.
- Application code changes.
Related reading
- Availability & Recovery Review. Use this instead when the setup exists and the question is whether its failover and restores would work.
- a Galera cluster stretched to a second data centre. An anonymised engagement write-up: why the cluster became unstable once nodes were added in the second data centre.
- replication or Galera Cluster. An article from 2021 on choosing between the two.
- when to use MySQL replication. An article from 2021 on where plain replication fits and where it doesn't.
- MySQL async replication HA, part 1. The first part of a hands-on lab series with ProxySQL and Orchestrator.
- Galera and network latency. An article from 2021 on how latency between nodes shows up in Galera.
- why shard a MySQL database. The first article of a 2022 series on sharding MySQL.
Questions
How is this different from the Availability & Recovery Review?
That review starts from a setup you already run and asks whether its failover and restores would work; your team runs the tests it recommends. This work is about choosing or changing the topology itself, and I can build it with you.
Can you help us choose between Galera and InnoDB Cluster?
Yes. It depends mostly on your write pattern and the distance between sites, and on what your team already knows how to operate. I'll write down the trade-offs for your case rather than a general comparison.
Can you build it for us?
Yes, once the design is agreed. The build gets its own written scope and fee, and I make changes with the access you grant and through your change approval.
Can you design for MariaDB specifically?
Yes. MariaDB has its own replication features and Galera packaging, with MaxScale in front. The design uses them where they fit rather than treating MariaDB as MySQL with a different name.
Choosing or changing a MySQL topology?
Tell me what you run today, what you're planning and what worries you about it. A rough description is enough to start.