MySQL InnoDB Cluster: where automatic failover breaks
MySQL InnoDB Cluster keeps writes on a single primary node, while the rest receive changes via the replication stream.
As long as all nodes are in service, the scheme works unnoticed: the application does not notice a replica failure at all, and a primary failure must be resolved by failover in seconds. The gap opens where failover does not start — and writes stop entirely.
The decision to change the primary is made by a majority of nodes, not by a separate control process. If a node stops responding due to network, a hung disk, or a crashed service, the remaining two retain a majority and elect a new primary — the application continues to write.
But once quorum is lost, for example when two out of three nodes fail or communication between sites is severed, the group switches all nodes to read-only mode. Automatic failover does not trigger because it is broken, but because there is no one to confirm it.
Then a manual procedure kicks in: the on-call engineer must notice that the primary is silent, check the group status, raise the priority of the required node, and restore writes.
While this is happening, the application gets write errors, the connection pool holds dead connections, and some operations never reach the database.
The procedure is written for a calm single-failure scenario and does not cover the case when there is nothing with which to make a decision.
We analyze how this is addressed: which checks catch quorum loss before the on-call engineer does, how failover is configured without manual intervention, and what the application needs to survive a primary change without downtime.
What automatic failover delivers: measurable effect
Automatic failover is implemented not for the sake of "the database will come up by itself," but for predictable downtime and a clear list of incidents that the on-call engineer will not even see. Below are four effects that are visible in monitoring within a week of running an InnoDB cluster.
Recovery time after failure: minutes of manual work versus seconds
Nodes notice the primary's unavailability in a matter of seconds and hand over the role to a candidate by majority consensus. At that moment, the application reconnects through Router, which maintains an up-to-date cluster map — writes resume without human involvement.
The manual scenario with diagnostics, choosing a candidate node, and editing the connection string takes a quarter of an hour or more.
Share of automatic failovers
Around 90-95% of single-node and primary failures are resolved automatically: the cluster remains in operating mode, and the team learns about the event from a notification.
A narrow group of cases remains — quorum loss and network partitions, where automation must stop writes — and there the decision is made by an engineer, not a timer.
Failure predictability and node quorum
Database fault tolerance is arithmetic: three nodes survive one failure, five survive two, and this is known in advance during design.
Quorum guarantees that two isolated halves will not start writing simultaneously, so data divergence after a network failure does not require manual reconciliation.
Planned maintenance window
Upgrades and maintenance are performed node by node: while one is taken out, the others maintain quorum and serve the load. Planned maintenance stops meaning a write outage — the application continues to operate normally.
MySQL fault-tolerance designs: single site, two data centers, cloud
A MySQL fault-tolerance design is determined not only by the replication mode but also by how nodes are distributed across sites. A three-node cluster in a single rack calmly survives a server failure and goes down completely if the rack is lost.
The reason is quorum: group replication confirms a transaction on a majority of nodes, and if that majority resides in a single point of failure, the design protects only against local failures.
Therefore we compare placement options by three questions: how many simultaneous failures the design survives, how long automatic failover takes, and how much synchronous mode increases write overhead. Below are real alternatives that are chosen between when designing MySQL InnoDB Cluster.
| Design | When it fits | Limitations |
|---|---|---|
| Three nodes on one site | Node failure, planned maintenance, test benches and internal systems | Loss of a rack or data center takes down the entire cluster; a standby node here is not protection against a site failure |
| Two sites with nodes in both and an arbiter in a third location | Need to survive the loss of one data center without manual intervention | Transaction confirmation latency increases by the channel time between sites; without an arbiter, a communication break causes both segments to lose quorum |
| Stretched cluster across three cloud zones | No own data center, fast node replacement matters | Dependence on the inter-zone channel and provider limits, synchronous write overhead increases |
| Asynchronous replication to a standby node in another data center | Loss of the last seconds of data is acceptable, simple insurance is needed | Manual failover, some confirmed transactions may not arrive |
On a single site, the reasonable limit is node failure or maintenance. Two sites cover a data center outage but require a low-latency channel and a third location for quorum.
A cloud layout across zones removes the issue of owning hardware, but adds dependence on inter-zone traffic.
Implementation order: from topology audit to handover to operations
The work order is structured so that no step is performed "on live" without a rollback path.
We start with an audit and end with handover to operations with training for the on-call shift — between these points, each stage is confirmed by a test result, not by a completion report.
- Topology audit. We capture a server map, versions, replication status, volumes, and load profile. We record current points of failure and determine which nodes are even capable of taking the primary role without data loss.
- Cluster design. We determine the number of nodes and quorum, placement across availability zones, the write agreement mode, and behavior when communication between sites is lost. Here we also decide where to place Router and how to distribute read load.
- Data preparation and migration. We convert data into a consistent format, restore it on the new nodes, and verify checksums per table. Discrepancies are eliminated before the cluster is enabled, not after.
- Failover configuration. We set failure thresholds, node priorities, timeouts, and the policy for returning the former primary node. We separately describe what happens to the application at the moment of leader change.
- Failure scenario testing. We take down the primary node, a replica, Router, and an entire zone one by one — measuring write recovery time and data completeness after returning to service. The scenario is repeated until the result becomes predictable.
- Handover to operations. We provide diagrams, policies, backup and recovery procedures, and the on-call checklist. We train the shift: how to read cluster status, what to do on quorum loss, and when to escalate.
Configurations, diagrams, and procedures: handover package
A cluster remains operational only when it is maintained by a team that understands the logic of primary node selection.
Therefore, along with a working installation, we provide a set of documents and settings that your on-call engineer can use to bring up the cluster from scratch, return a node after a failure, and confirm failover — without contacting us for every step.
- Cluster configuration — settings files for each node with explanations: replication parameters, trigger thresholds for communication loss, node priority during election of a new primary, and why these particular values were chosen.
- Network and port diagram — which ports serve group replication and MySQL Router, how addresses are separated between nodes and clients, where the bottlenecks and single points of failure are.
- Access policies and roles — separation of privileges between the administrator and the Router service account, the procedure for issuing and changing passwords, and what is prohibited from being done directly on the primary node.
- Failover procedure — a step-by-step scenario for primary node failure: how to check group status, when to wait for automatic election of a new leader, and when failover is done manually and who confirms it.
- Recovery instructions — returning a node after a failure, rebuilding from a backup, checking lag and consistency before bringing it back into the cluster.
- On-call procedure — which events and metrics count as an incident, whom to escalate to, what to do in the first minutes, and in what order.
- Placement map — which node runs on which server, where Router is, and which failure zones the design survives when one placement is lost.
Case study: failover without transaction loss on three nodes
Situation before: manual recovery and transaction loss
A three-node MySQL cluster held orders and payments. When the primary node failed, writes stopped: the application returned errors while the on-call engineer connected to the cluster, checked replication positions, and manually promoted one of the replicas.
Failover took from thirty minutes to an hour, and transactions that had not managed to be confirmed on a majority of nodes were lost — they were recovered from the nightly snapshot and logs, manually reconciling discrepancies in orders.
What was changed
We enabled group replication with confirmation on a majority of nodes and separated roles: writes go only through the primary node elected by the majority, reads are distributed across replicas.
Routing was given to Router with a "primary only" policy, orchestration to Shell: node promotion, recovery from network partition, and forced failover on quorum loss follow the procedure, not the on-call engineer's memory.
Metrics before and after
- Automatic failover: 20–40 seconds instead of 30–60 minutes of manual recovery.
- Loss of confirmed transactions: zero instead of some incomplete operations on every failure.
- Network partition: the minority of nodes stops accepting writes, dual primary is excluded.
- Drills: planned node shutdown, the on-call engineer sees the failover result instead of searching for it in logs.
Fault tolerance increased without expanding the server fleet: the same three nodes now survive the loss of one of them without stopping writes.
What to do if the database is already deployed and failover is done manually?
Yes, a running database can be migrated to a cluster without service downtime: new nodes are brought up alongside the existing server and first catch up with it via ordinary asynchronous replication.
Writes continue to go to the current write node, and only copies join the cluster. Downtime is reduced to one short application switchover — minutes, not hours.
Technically, the order is as follows: on the existing server, enable GTID and row-based logging, prepare two or three replicas with identical configuration, verify checksums, and only then switch them to group replication mode.
A mandatory condition is primary keys on all tables: without them, row-based copying runs into full scans, and replica lag grows and does not recover. At the same time, node roles become explicit: one accepts writes, the others serve reads and act as promotion candidates.
Next, the application stops going to a specific host and connects through Router to the group as a whole. This step removes the on-call engineer's manual operations — failover on failure, finding the current write node, editing the connection string after an incident.
A new leader is elected automatically by majority vote, and Router redirects traffic to it in a matter of seconds.
Some work remains manual, and it is more honest to name it in advance: schema migrations, changing the number of nodes, returning a node to service after a long outage, and handling a majority-loss situation.
The group's behavior when quorum is unavailable is configured before an incident — otherwise the cluster stops writes by itself and waits for an engineer. Implementing automation does not eliminate the procedure, but it removes the most nerve-racking part of on-call duty.
Common technical doubts: answers on the merits
Engineers' doubts rarely concern failover itself — it works. Questions are usually about non-standard modes: how quorum is calculated, what happens when communication between sites is lost, and whether the application will withstand a change of primary node. We address them on the merits.
What happens if more than half of the nodes lose connectivity?
Writes are accepted only with quorum — a majority of voting nodes. Of three nodes, one failure is survivable; if two are lost, the group stops electing a primary node, and the remaining ones switch to read-only mode.
This is a deliberate compromise: brief write unavailability instead of data divergence between nodes.
How does the cluster behave when communication between sites is lost?
Only the side that retains a majority continues to accept writes; the smaller part blocks writes and does not try to "come to an agreement." When the channel is restored, lagging nodes are not merged manually — they catch up with the change log or are rebuilt from a full snapshot.
Therefore nodes are placed across sites so that the majority does not depend on a single communication channel.
Does the application need to be reworked?
Yes, but selectively. Connections go through MySQL InnoDB Cluster in conjunction with Router — the application does not know the addresses of specific nodes and must correctly reconnect when the primary changes.
Writes are directed only to the primary node, reads to secondary ones; we remove custom replication schemes in the code. We separately check long transactions that hold a connection and interfere with failover.
How to combine backup and planned maintenance?
We take backups from a secondary node — the primary does not receive extra load, and we always test restoration on a separate test bench; otherwise the backup remains unverified.
Planned maintenance goes through manual role switchover: the node is removed from the group, upgraded, and returned, after which it catches up with changes on its own.
Let's discuss your task: what to clarify before starting
To make the assessment specific and the plan feasible, we need to understand the current database landscape and fault-tolerance requirements.
Describe the task in free form, and we will come back with an assessment and work plan: the composition of stages, timelines, and a list of checks that will confirm that automatic failover actually works. For an accurate assessment, answers to a few questions are usually enough.
- How many nodes are planned in the cluster and in how many availability zones they are placed — quorum and behavior on loss of one node depend on this.
- What write mode is needed: one primary node or several, and whether the applications are ready to read data from replicas.
- What exists now: a single database, a primary replica with manual failover, or custom administrator scripts.
- Target service recovery time and acceptable loss of recent transactions during an incident.
- How backup is organized and whether there is a tested rollback to the previous state.
- Constraints on the change window: whether nodes can be restarted during business hours and who approves the work.
- Monitoring requirements: who and how should learn about a primary node change and replication degradation.
If some data is not yet known, this does not block the start: we will discuss the task, record assumptions, and clarify them during the survey stage. We will come back with an assessment and work plan that explicitly state the composition, timelines, and result control points.