A primary database can fail at 02:00 and still look healthy in your runbook. The replica promotes, but writes keep targeting the dead node, the connection pool recycles stale sockets, and your stated 60-second RTO becomes a customer-facing outage. This guide shows how to make that failure path observable, repeatable, and measurable for MySQL, PostgreSQL, and MongoDB.

The outage you have not tested yet

A documented failover plan is not proof that failover works. Proof is a scheduled test that kills the primary, promotes a valid replica, restores application writes, and records the elapsed time against a defined RTO.

If any of these answers is unclear, your failover path is still unproven:

• Which component decides the primary is actually down?
• How many failed checks are required before promotion starts?
• Where do application writes go during the switch?
• How much data can be lost when the primary fails?
• Can you show the last five recovery times to an auditor or incident commander?

For teams running managed database services, automated testing turns those questions into a scheduled control rather than a once-a-quarter fire drill.

The operational rule: a green database health check does not prove that your application can recover from a primary failure.

Build the test around the recovery path

Treat failover testing as one complete transaction, not a database-only check. The test starts when the primary becomes unavailable and ends only when an application transaction succeeds on the new primary.

• Detection: The cluster manager identifies a failed primary after the configured failed-check threshold.
• Election: A safe replica becomes the new writer only when quorum is intact and one writable primary remains.
• Routing: Proxy, DNS, and connection pools direct new writes away from the old primary.
• Recovery: The application resumes normal work inside the defined RTO.
• Evidence: Detection, promotion, and application recovery times are logged for comparison across tests.

This structure exposes the gap that generic DR drills miss: the database can recover while the application remains unavailable.

What you need before the first test

• A staging or replica environment that mirrors production topology, engine version, replication mode, and node count.
• A defined recovery time objective and recovery point objective. An RTO of 60 seconds and an RPO of zero require different replication choices from a three-minute RTO with accepted data loss.
• An orchestration layer: Orchestrator or MySQL InnoDB Cluster for MySQL, Patroni for PostgreSQL, or native replica-set elections for MongoDB.
• A routing layer such as ProxySQL, HAProxy, or a cloud load balancer with health checks.
• A scheduler: cron, Kubernetes CronJob, or a CI pipeline.
• Monitoring that records the exact test timestamps and sends an alert when the RTO is missed.
• A rollback runbook that restores the original topology after every test.

Do not substitute a single-node sandbox for a production-like cluster. One replica cannot expose the quorum and majority-vote failures that appear in a three-node InnoDB Cluster or five-member MongoDB replica set.

The automated failover test runbook

1. Set a hard pass or fail line

Define the RTO and RPO before scripting a single test. For example, a 60-second RTO means the full chain, detection, promotion, routing, and application recovery, must finish within 60 seconds. A zero-data-loss RPO requires synchronous or semi-synchronous replication rather than asynchronous replication.

Expected outcome: every run has an unambiguous pass or fail result.

Common mistake: approving an RTO described as “a few minutes.” A vague recovery target makes every result debatable after an incident.

2. Mirror the topology that actually carries risk

Build the test environment with the same database version, replication configuration, node count, proxy behavior, and connection-pool settings as production. MySQL 8, PostgreSQL 15 or 16, and MongoDB replica sets all fail differently when election, quorum, and routing layers diverge.

Expected outcome: the test exercises the same failure decisions used by production.

Common mistake: using one standby node in staging when production requires three-node quorum. The smaller environment hides the failure mode you need to validate.

3. Check replication state, not just open ports

Poll every 2 to 5 seconds and test process liveness, replication health, and writer eligibility. Orchestrator and Patroni support this model natively; ProxySQL can evaluate read_only state and replication-lag thresholds.

Expected outcome: the automation can distinguish a live process from a usable primary.

Common mistake: treating an open port on 3306 or 5432 as a healthy database. The process can respond while replication is broken or the node is no longer safe to promote.

4. Require quorum before promotion

Configure the trigger to promote a replica only after the cluster agrees the primary is unavailable. For MySQL, Orchestrator recovery settings define safe promotion behavior; for PostgreSQL, Patroni relies on a distributed configuration store such as etcd, Consul, or ZooKeeper.

Use three consecutive failed checks, roughly 10 to 15 seconds at a 2 to 5 second polling interval, as the baseline for a controlled test.

Expected outcome: one valid primary emerges and transient network jitter does not trigger a false failover.

Common mistake: promoting after one missed health check. A short network interruption can then create the split-brain event the test was meant to prevent.

5. Inject a controlled primary failure

On a fixed cadence, stop the primary in the test environment. Use a controlled process termination such as SIGKILL for mysqld, a test-instance termination through your infrastructure controls, or rs.stepDown() for a MongoDB replica set.

Measure detection time, promotion time, and total application recovery time separately. Weekly testing suits compliance-heavy systems; monthly is the minimum for other production databases.

Expected outcome: the test creates a repeatable failure without relying on a human to decide when to promote.

Common mistake: testing only during low traffic. Connection-pool and routing failures often appear when the application is carrying real concurrent demand.

6. Prove that the application can write again

Run a synthetic transaction through the same DNS, proxy, driver, and connection-pool path used by the application. Confirm it reaches the new primary and verify that read/write routing behaves as expected after promotion.

Expected outcome: the first successful application write marks the true end of the outage.

Common mistake: declaring success when the cluster reports a new primary. A database promotion in 8 seconds is not an 8-second outage if the application continues to return errors for 45 seconds.

7. Store the result and look for drift

Write each run to a dashboard or log store: test date, time to detect, time to promote, time to application recovery, RTO result, and rollback status. Compare every run with the prior five.

Expected outcome: a gradual slowdown becomes visible before it becomes an incident.

Common mistake: retaining only a pass or fail flag. A 12-second recovery last month and a 40-second recovery this month is a configuration-drift signal, even when both results technically pass.

When the test fails, use the failure signature

The promoted replica has stale data

Check whether semi-synchronous replication is active rather than silently falling back to asynchronous replication. In MySQL, review rpl_semi_sync_master_enabled and the conditions that allow degraded replication.

ProxySQL still sends writes to the old primary

Review mysql_replication_hostgroups and its read_only monitoring interval. A 30-second polling interval conflicts with a sub-minute RTO; the routing check must react inside the recovery budget.

Two nodes consider themselves primary

Treat this as a fencing or quorum failure, not a retry issue. Validate Patroni fencing and distributed-configuration-store behavior, or define safe recovery restrictions in Orchestrator before running another test.

The application throws errors after promotion

Inspect DNS caching, connection-pool lifetime, validation queries, and retry behavior. Lowering a long TTL to 5 to 10 seconds and validating connections before reuse often removes stale-socket delays.

Staging passes while production does not

The test environment is not representative. Compare node count, database version, replication mode, proxy configuration, and application connection settings before trusting the next test result.

The tooling map

• Orchestrator: MySQL topology management and automated recovery for standard asynchronous, semi-synchronous, and InnoDB Cluster environments.
• Patroni: PostgreSQL high availability with distributed leader election and fencing controls.
• ProxySQL: MySQL query routing and health-check-driven read/write splitting.
• MongoDB replica sets: Native election protocol with controlled primary-switch testing through rs.stepDown().
• Prometheus and Grafana: Test-result storage, drift detection, and alerting.

For PostgreSQL environments, review managed PostgreSQL services for the operating model around Patroni, streaming replication, and resilience testing. For multi-engine environments, open source database management covers high availability, backup, recovery, and observability across MySQL, PostgreSQL, MongoDB, MariaDB, and TiDB.

What to do after the first five tests

Do not stop at a successful node failover. Once the test shows stable recovery times across five runs, test the next failure domain: loss of an availability zone, a broken replication channel, or a regional recovery scenario.

A single-node failover proves that a standby can become primary. Disaster recovery proves that the business can restore service when the infrastructure around every node fails.

FAQ

How often should automated database failover testing run?

Run automated database failover testing weekly for compliance-heavy production databases and monthly at minimum for other production systems. Scheduled runs catch replication and routing drift before a real incident.

What is the difference between failover testing and disaster recovery testing?

Failover testing validates automatic promotion after one primary node fails. Disaster recovery testing validates recovery from a full availability-zone or regional outage, including backup restoration and topology rebuilds.

Can MongoDB replica-set failover testing be automated?

Yes. MongoDB replica sets support controlled primary transitions with rs.stepDown(), which can be scripted and measured like MySQL or PostgreSQL failover tests.

Does automated failover testing cause production downtime?

It should not when it runs in a production-like staging or replica environment. Running uncontrolled failure tests against a live primary creates avoidable risk and is not an acceptable substitute for a mirrored test path.

What RTO is realistic for MySQL failover?

A correctly configured MySQL InnoDB Cluster or Orchestrator-managed topology can target recovery under 30 seconds when routing and application connection handling are included. The only defensible RTO is the result of your own repeatable end-to-end tests.

Is ProxySQL required for automated MySQL failover testing?

No. ProxySQL is not required, but a routing layer gives the test one place to validate writer discovery and read/write behavior. Without it, the application must perform and prove the same routing work.

What normally causes an automated failover test to fail?

The most common failure signatures are replication degrading to async, missing quorum or fencing controls, stale proxy routing, and application pools holding dead connections. Each failure needs a targeted fix rather than a generic restart.

Measure recovery before an outage does

The useful metric is not “failover passed.” It is the trend in end-to-end recovery time. A recovery that expands from 12 seconds to 40 seconds has already told you that configuration drift is eating your RTO, even if users have not noticed yet.

Related guides

• High availability with MariaDB Galera Cluster
• Database failover lessons for crypto exchanges

Get a DBA-led failover review

Mydbops validates failover paths and disaster-recovery controls for MySQL, PostgreSQL, and MongoDB environments.