mysql-ha

#Introduction

The mysql-ha Package Skill creates three identical droplets in one region's default VPC and runs MySQL 8.0 across them as a single-primary Group Replication group. The three servers are the quorum: failure detection, majority agreement and primary election are one mechanism rather than three that have to agree with each other. There is no orchestrator, no keepalived, no etcd and no fourth machine.

#Quick start

npx skills add getcolors/mysql-ha
cp .agents/skills/package-mysql-ha-green/green ./green
./green build
./green create --dry-run
# after approval:
./green create
./green health

create does not return until it has converged the group, published a snapshot, archived binary logs, rebuilt a scratch server from both, and asserted the result. A backup chain that is only tested once is a backup chain that is not tested.

#Workflow architecture

create  start ─ infrastructure ─┬─ dns ──┐
                                └─ base ─┴─ cluster ─ backup ─ health

delete  start ─ load state ─ cleanup ─ dns ─ infrastructure

health  start ─ load state ─ health

dns and base have nothing to say to each other — one talks to Cloudflare, the other to apt — so they fork and join at cluster. Joining DNS there rather than leaving it dangling means a bad zone surfaces before any data-plane work starts.

Delete and health both begin by reading node addresses out of remote state, because neither can re-derive them.

#Replication and failover

Single-primary Group Replication over GTIDs. Every ONLINE member applies and logs every transaction under the same GTID, which is what makes both the failover and the archive design work.

#The client endpoint

The endpoint is a DigitalOcean reserved IP. The Cloudflare A record for the cluster host points at that address and never changes — failover moves an address between droplets instead of moving a record between addresses.

A ten-second timer on each member asks the local server whether this member is ONLINE, PRIMARY and not super_read_only. All but one exit in three lines. The one that does not calls the DigitalOcean API and claims the address.

A TCP load balancer was considered and rejected: every member accepts connections and only one accepts writes, so a health check on the MySQL port cannot tell them apart.

#Backups and point-in-time recovery

snapshot/<UTC ts>/dump.sql.zst    daily GTID-stamped logical snapshot
snapshot/<UTC ts>/meta.json       gtid_executed, sha256, size, schemas, node
snapshot/latest.json              written last — never points at a partial upload
binlog/<member>/binlog.NNNNNN     point-in-time material, one prefix per member
restore-check/<UTC ts>.json       the verdict of each verified restore
restore-check/latest.json

The snapshot is mysqldump --single-transaction --set-gtid-purged=ON, compressed with zstd. It runs on an ONLINE secondary, chosen by the members themselves with no lease and no coordinator, so the primary never holds the consistent-read snapshot open. Only user schemas are dumped: restoring mysql would overwrite the accounts of the server being restored into.

Point-in-time material is mysqlbinlog --read-from-remote-server --stop-never --raw — MySQL's own documented binary-log backup — held open by systemd on every member, with a one-minute timer pushing the spool to R2. That interval, not binary-log rotation, is the recovery-point objective.

Every member archives its own log into its own prefix. Any one prefix is a complete recovery source, so losing a member does not lose the archive, and the archiver needs no leader election.

#The verified restore

Daily, on the same self-elected member, the package builds a real server from the published backup and asks it three questions a merely well-formed dump could not answer:

1. does its gtid_executed cover the snapshot's?
2. did replaying the archived binary logs add heartbeat rows the snapshot did not already have?
3. is the newest heartbeat in the restored copy inside the configured lag budget?

Question two is the one that matters: it is the only assertion that distinguishes the dump restored from the dump restored and the point-in-time material on top of it was real. It is why the cluster keeps a heartbeat at all.

The scratch instance is the host's own mysqld binary — so the restore is verified against exactly the server version that produced the snapshot — on its own datadir, its own socket, no network, and a 128 MB buffer pool. Ubuntu's AppArmor profile is extended rather than disabled to permit it. The verdict is published whether it passes or fails, so a silent failure of the job is itself detectable: latest.json stops being fresh.

#Desired state

Validation reports every problem at once and exits 2. See the configuration reference.

#Command reference

#State and output

.colors/<profile>/
├── mysql-ha-infrastructure/   backend.tf.json  main.tf
├── mysql-ha-dns/              backend.tf.json  main.tf
└── mysql-ha-ansible/          playbooks, inventory.json, files/

Remote state is <profile>/mysql-ha-infrastructure.tfstate and <profile>/mysql-ha-dns.tfstate. Nothing under the work directory ever holds a credential.

#Credentials and safety

• DigitalOcean: COLORS_PAR_DO_TOKEN — droplets, firewall, and the reserved-IP claim.
• Cloudflare: COLORS_PAR_CLOUDFLARE_API_TOKEN.
• State bucket and backup bucket use separate R2 key pairs — different blast radii.
• Database: COLORS_PAR_MYSQL_ADMIN_PASSWORD and COLORS_PAR_MYSQL_REPLICATION_PASSWORD, and nothing else. Agents on a member authenticate to their own server as root@localhost over the unix socket, so the admin password is never written to a member.
• The three files on a member that hold a credential are built by Ansible from the process environment under no_log; none is ever rendered.
• Never export COLORS_PAR_PROFILE. Keep compute-prevent-destroy: true.
• The MySQL port is a genuinely public port. Keep digitalocean-client-sources narrow.

#Recovery procedure

The snapshot carries SET @@GLOBAL.GTID_PURGED, so archived logs replay idempotently on top of it: transactions the dump already contained are skipped by GTID and there is no position arithmetic anywhere.

rclone ... cat  r2:$BUCKET/$PREFIX/snapshot/latest.json
rclone ... copyto r2:$BUCKET/$PREFIX/snapshot/$TS/dump.sql.zst ./dump.sql.zst
sha256sum ./dump.sql.zst        # must match meta.json
rclone ... copy r2:$BUCKET/$PREFIX/binlog/$MEMBER/ ./binlog/

zstd -dc ./dump.sql.zst | mysql --socket=$TARGET
mysqlbinlog --stop-datetime='2026-08-16 09:14:00' ./binlog/binlog.* | mysql --socket=$TARGET

Every member runs with default_time_zone = '+00:00' precisely so that a --stop-datetime means the same thing everywhere. mysql_ha.beat_log is a ten-second heartbeat carrying the writing member's name, which is the quickest way to confirm you landed where you meant to.

To rebuild one member, stop MySQL on it, clear its datadir and run create again: it is treated as fresh and rejoins by distributed recovery. If every member is down at once, create bootstraps from member one, which then becomes the source of truth — recover anything it was missing from the archive first.