Lab build, provisioned in a personal AWS account and torn down after the drills. Every figure below was measured during the runs shown in the screenshots — the AWS account identifier is redacted in three console captures.
The problem with most “high availability” builds
Almost everyone who builds this stops at two sentences: replication is running, and the backup script exits 0.
Neither is evidence. Replication threads report Yes right up until the moment they don’t. A backup script exits 0 when it uploads an empty file. Both statements describe a configuration, not a capability — and the gap between those two things is exactly where outages live.
So the goal here was not to build the architecture. The architecture is well understood and takes a weekend. The goal was to break it on purpose and write down what happened, in numbers, including the numbers that were not flattering.
server_id: 1 with both read-only flags false confirms it reached the primary, not the replica.The network shape, and why it is the whole argument
t3.micro bastion has an address. That single column is the entire private-subnet argument; everything else is commentary on it.
/healthz endpoint every 15 seconds — and that endpoint is deliberately exempt from rate limiting, because rate-limiting your own probes eventually deregisters your own healthy nodes.Replication, and proving the replica cannot silently diverge
SOURCE_AUTO_POSITION = 1 — no binlog filename, no offset to record, nothing to get wrong at 3 a.m.The interesting test is not that replication works. It is that the replica refuses to accept a write — because a replica that quietly takes one has already diverged, and you will not find out until the data disagrees.
"written": true.
sed against /etc/webapp.env, one service restart, and the same binary hits the replica instead — error 1290, refused. Note super_read_only: true, not just read_only: plain read_only still lets a SUPER account write, which is exactly how split-brain starts when someone connects as root to "fix one row."The edge, and the test that most WAF setups fail
403. Thirty login attempts → eight 200s then 429. And the one that matters most: curl straight at the ALB hostname times out — exit=28.That third command is the test I would ask about in an interview.
A WAF an attacker can walk around is decorative. If the origin answers anyone who discovers its hostname, every rule at the edge is optional. exit 28 is a timeout, not a rejection — packets from outside Cloudflare’s published ranges are dropped by the ALB security group rather than refused, so a scanner learns nothing at all.
error code: 1015 is Cloudflare's own rate limiter, which means the blocked request never reached AWS. Blocking at the edge costs nothing; blocking at the origin costs a request.Backups that were actually restored
./scripts/restore-verify.sh end to end: SHA-256 confirmed, the dump restored into a throwaway container, row counts checked, CHECK TABLE run across every table, and a measured recovery time. A backup you have never restored is a hypothesis. (S3 paths redacted.)The defect worth the whole project
Six defects surfaced by running the code rather than reading it. One of them is the reason this project exists.
After fixing a readiness race in the restore harness, the script completed — and printed All tables OK immediately after OCI runtime exec failed: exec: "mysqlcheck": executable file not found in $PATH.
mysqlcheck is absent from recent mysql:8.0 images. So the docker exec failed, the pipe carried nothing, grep -v 'OK$' exited non-zero on empty input, and the || log "All tables OK" fallback fired on that failure.
| What the script printed | What had happened | |
|---|---|---|
| Integrity check | All tables OK | The binary did not exist; nothing was checked |
This is precisely the failure mode the project was built to guard against: a verification step that is green while verifying nothing. A backup you have never restored is a hypothesis — and a restore whose integrity check silently no-ops is the same hypothesis wearing a tick mark.
Replaced with CHECK TABLE driven from information_schema, using the mysql client that is always present, failing loudly on empty output and printing how many tables it checked. “Checked nothing” can no longer render as “everything is fine.”
The replacement then failed the opposite way: it captured stderr deliberately, so it also captured mysql: [Warning] Using a password on the command line interface can be insecure — a line that does not end in OK, and was therefore reported as an integrity failure when the check had passed. Fixed by passing the password through MYSQL_PWD, suppressing the warning at source rather than filtering it afterwards.
A false negative followed by a false positive, both from the same mistake: pattern-matching mixed streams without controlling what is in them.
ERROR 2002 then ERROR 1045 — and that inconsistency was the diagnosis: the same input producing different errors is the signature of a race, not a misconfiguration. The readiness loop used mysqladmin ping, which exits 0 even on access-denied, and the official MySQL image runs a temporary server during initialisation that then shuts down. The probe was succeeding against a server about to disappear.This screenshot is here on purpose. A gallery containing only green output would contradict a write-up that claims six defects were found by running the code.
The failover drills
Reading “89.86% availability” honestly
That number undersells what happened, and a reader who sees only the percentage will draw the wrong conclusion.
There was never a second when the site was down. The failed probes interleave with successful ones. With one of two nodes dead, the ALB kept round-robining into the corpse until its health check caught up — so roughly half of requests failed during that window while the other half were served normally.
The correct characterisation is a 28-second degraded window at ~50% error rate, not a 28-second outage.
And the window is exactly the configured detection time. alb.tf sets interval = 15 with unhealthy_threshold = 2, so worst-case detection is 30 seconds. Both drills landed at 27–28s. That is the knob — shortening it buys faster failover and costs more probe traffic and more sensitivity to transient blips.
Two failure modes appeared, in the order you would predict. The first failures in each drill were 502 — the instance was still shutting down, so the ALB got a connection reset from a dying backend. The rest were 000, a client-side timeout at 2 seconds, once the instance was fully gone and its packets were being blackholed.
Measured results
| Drill | RTO measured | RPO measured | Failed requests |
|---|---|---|---|
Web node loss (web-1) | 0s full outage · 28s degraded | 0 | 7 of 69 |
Web node loss (web-2) | 0s full outage · 27s degraded | 0 | 7 of 68 |
| Restore from S3 backup | 1s restore · 24s end-to-end | ≤ 5h (nightly schedule) | n/a |
| DB primary loss → replica promotion | not performed | — | — |
| Control | Test | Result |
|---|---|---|
| GTID replication | SHOW REPLICA STATUS | Both threads Yes, Seconds_Behind_Source: 0 |
| Replica rejects writes | /write against a repointed app node | Error 1290, super_read_only: true |
| WAF blocks injection | ?id=1' OR '1'='1 | 403 |
| Login rate limiting | 30 × GET /login | 8 × 200, then 429 (Cloudflare 1015) |
| Origin cannot be reached directly | curl the ALB hostname | Timeout, exit 28 |
The database failover row is empty because the drill was not run, and an empty row is more useful than a guess. The manual procedure is written up in the repository’s runbook; automating and measuring it is the obvious next piece of work.
What the constraints actually were
Three things did not work as designed, and the write-up says so rather than quietly substituting.
The WAF block is a hand-written rule, not OWASP. The Cloudflare Managed and OWASP Core rulesets require Pro; deploying them on a Free zone fails with “not entitled to execute this managed ruleset.” The substitute matches four literal substrings after url_decode(). It stops the demo payload and naive scanners. It does not do grammar-aware detection, and an attacker who encodes or fragments the query walks past it. Claiming “Cloudflare WAF blocked SQLi” here would be overclaiming.
That replacement rule initially matched nothing. It returned 200 on its first test, because Cloudflare’s http.request.uri.query is the raw query string — contains "' or '" was being evaluated against '%20or%20' and could never fire. A WAF rule that silently matches nothing is worse than no rule at all, because the dashboard shows it deployed and green.
Rate limiting is looser than intended. The Free plan accepts only a 10-second counting period and a 10-second mitigation timeout; the design called for 10 requests per minute with a 10-minute lockout. What is deployed still stops a credential-stuffing loop, but a patient attacker gets ~60/minute rather than 10.
One request returned 200 in the middle of the blocked run. Cloudflare counts per ["ip.src", "cf.colo.id"] — the counter is per data centre and eventually consistent, so a request reaching a different colo, or arriving before the counter propagated, is not blocked. Expected behaviour for distributed edge rate limiting, not a misconfiguration.
The outage nobody scheduled
Partway through, the database primary became SSH-unreachable while the other four nodes were fine. Ansible reported Connection closed by UNKNOWN port 65535.
That specific message was the diagnosis. It means the ProxyJump tunnel was established — bastion and security groups healthy — but the far end never completed an SSH handshake. A timeout would have meant packets disappearing, which is a firewall or routing fault. Reading which failure had occurred ruled out the entire network layer in one step and pointed straight at the host.
A soft reboot recovered it. Replication then re-established itself across an unclean primary restart with no manual intervention — both threads Yes, zero lag.
That is an unplanned resilience test that did not have to be staged, and it is better evidence than any of the scripted drills.
Design decisions
One NAT gateway per availability zone, not one shared. A single shared NAT quietly undoes the point of multi-AZ: losing the zone holding it removes outbound connectivity for the surviving zone too. The cost difference is real, and paying it is the difference between multi-AZ and the appearance of multi-AZ.
Security groups reference security groups. The database tier admits 3306 from the web tier’s security group, and the primary and replica reach each other by a self-reference. No private IP is hardcoded anywhere, so replacing an instance changes nothing — and that is a capability no IP-based firewall has at all.
IMDSv2 required on every instance. One line, http_tokens = "required", closing the SSRF-to-credential-theft path behind the Capital One breach. The database nodes carry an IAM role for S3 access, so there is no long-lived access key on any host to steal.
Backups are a systemd timer with Persistent=true. The instances are stopped overnight for cost control, which would otherwise silently skip every scheduled backup. Confirmed working by catch-up runs firing at 11:30Z and 06:32Z against a 02:30Z schedule — worth verifying, because the cost-saving pattern would have quietly disabled the backups it was built alongside.
GTID rather than binlog coordinates. Position-based replication forces you to record a filename and offset, and to get them right precisely when you are failing over under pressure. GTID lets the replica ask for everything it has not seen.
What I would change for production
- Replace the bastion with SSM Session Manager. No SSH port open anywhere, no host to patch, no keys to distribute, every session logged in CloudTrail.
- Use RDS Multi-AZ unless there is a specific reason to run MySQL on EC2. Automated failover, patching and backups are not worth rebuilding by hand — building it manually was the point here, not the recommendation.
- Automate promotion. A human following a runbook at 3 a.m. is a plan, not a control.
- Replicate backups cross-region. Today a regional failure loses both the platform and its backups.
- Add alerting. Nothing pages anyone. Replication lag, backup age and target-group health should all alert, and none of them do — which, by this project’s own standard, means none of them are really monitored.
- Shorten the health check if 28 seconds of degraded service exceeds the SLO, and accept the extra probe traffic that buys.
Residual risks, stated
- Application-layer authorisation is out of scope; the demo application has no auth.
- The app trusts MySQL’s self-signed certificate — the connection is encrypted but the server is not authenticated. Acceptable inside a private subnet reachable only from the web security group; wrong in production, where you would distribute the CA and verify.
- Single region, single AWS account.
- WAF coverage and rate limiting are bounded by the Cloudflare Free plan, as detailed above.
- Database failover is manual and has not been drilled.