Fix RDS MySQL read replica write errors after failover

You're not alone — this bites everyone at least once

Nothing like watching your app throw 'Cannot execute write query on a read-only server' errors right when your RDS master decides to fail over. Happened to a client last month who lost 20 minutes of orders because their connection pool didn't know the master had swapped. Let's fix this so it doesn't happen to you.

The fix: stop treating read replicas like they're writable

First, understand this: RDS read replicas are read-only by design. When your master fails over, the old master goes away, and a new master spins up. But your app's write connections are still pointed at the old master's endpoint — or worse, they're hitting a read replica because you didn't separate read and write traffic.

Always use the RDS writer endpoint for writes. It's a CNAME that points to the current master. Never hardcode the instance endpoint for writes. In your app config, set write_host to the writer endpoint (something like mydb.cluster-xxxxx.us-east-1.rds.amazonaws.com).
Separate read and write connections in your app. Use the reader endpoint (the cluster endpoint that load-balances across replicas) for reads. Your ORM or connection pool library (HikariCP, Prisma, SQLAlchemy) can handle this. Create two data sources: one for writes (writer endpoint), one for reads (reader endpoint).
Set a connection timeout and retry logic. RDS failover takes 30–90 seconds. Your app should not crash during that window. Set connectTimeout to 5000ms and socketTimeout to 120000ms. Then add a retry loop that retries the write up to 3 times with exponential backoff.

# Example using Python SQLAlchemy
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool

write_engine = create_engine(
    "mysql+pymysql://user:pass@mycluster.cluster-xxxxx.us-east-1.rds.amazonaws.com/mydb",
    connect_args={"connect_timeout": 5},
    poolclass=NullPool  # avoid stale connections
)
read_engine = create_engine(
    "mysql+pymysql://user:pass@mycluster.cluster-ro-xxxxx.us-east-1.rds.amazonaws.com/mydb",
    connect_args={"connect_timeout": 5},
    poolclass=NullPool
)

Why the read replica throws write errors during failover

Here's what's happening under the hood. Your master fails — maybe due to a zone outage, hardware failure, or a manual failover during maintenance. RDS promotes a read replica to be the new master. During that promotion, the promoted replica goes from read-only to writable. But here's the catch: the DNS change for the writer endpoint isn't instant. Cloudflare or Route 53 TTLs may still point to the old master for up to 60 seconds.

Meanwhile, your app tries to write via the writer endpoint. If DNS hasn't updated, it hits the dead old master (timeout) or, worse, a remaining read replica that's still read-only. The replica gets your write query and replies: ERROR 1290 (HY000): The MySQL server is running with the --read-only option.

The real fix is to never assume any endpoint is writable. Always check the server's read_only variable before executing a write, or rely on DNS propagation with a low TTL (like 5 seconds). RDS sets the reader endpoint TTL to 60 seconds by default — bump it down to 5 seconds in Route 53 if you can (RDS does this automatically for cluster endpoints in Aurora, but for standard RDS you need to manage your own Route 53 CNAME with a low TTL).

Less common variations of this issue

Not every case is the same. Here are a few I've seen:

Scenario	What happens	Fix
Multi-AZ failover (not read replica)	RDS flips the standby to master. Your writer endpoint still works but the DNS may lag. No read replica involved.	Same DNS TTL fix. Use the writer endpoint, not the instance endpoint.
Aurora cluster failover	Aurora's reader endpoint handles failover differently — it may temporarily become writable. Your app might get a different error like 'replication lag too high'.	Use the cluster endpoint for writes, and set `aurora_read_replica_read_committed` ON for the reader to avoid stale reads.
Application uses a custom proxy like ProxySQL or HAProxy	The proxy has its own health checks. If it marks the old master as healthy, writes still go there.	Configure the proxy to use RDS's writer endpoint and set a low check interval (1 second).
Connection pool holds stale connections	Even after DNS updates, your pool may have open connections to the dead master. Those connections fail with 'Can't connect to MySQL server on ...'.	Set `maxLifetime` in your pool to 60 seconds (HikariCP default is 30 minutes — too long). Add a validation query like `SELECT 1`.

Prevention: don't let it happen again

You can't stop failovers, but you can stop them from hurting you. Do this:

Never hardcode instance endpoints. Always use the writer and reader cluster endpoints. Script your config deployment to pull these from RDS API if needed.
Set DNS TTL to 5 seconds for your Route 53 records pointing to RDS endpoints. RDS doesn't let you change the default cluster endpoint TTL, but you can create your own Route 53 alias with a low TTL.
Test failover in staging. Trigger a manual failover (RDS console → Reboot with failover) and watch your app logs. If you see any write errors, fix them before they hit production.
Use a retry library like tenacity (Python) or resilience4j (Java). Retry with backoff and jitter. Three retries with 1s, 2s, 4s waits usually covers the DNS propagation window.

Had a client who ignored this for three years until a major AZ outage took them down for four hours. Don't be that person. Set it up right once and forget it.