mysql_purgatory

Binary logs pile up on the master like unread emails. This little one-shot tool asks every replica how far it has applied the log, finds the slowest one, and sends everything older to purgatory (PURGE BINARY LOGS TO ...). Nothing a replica still needs gets touched. Run it from cron, then forget about it.

MySQL 8.0.14 and up (that's where BINLOG_ADMIN appeared). Servers older than 8.0.22 are asked with the old SHOW SLAVE STATUS / SHOW SLAVE HOSTS dialect, newer ones with SHOW REPLICA STATUS / SHOW REPLICAS, chosen by each server's @@version.

Quick start

Put the settings in a .env file. Values go in unquoted, since docker's --env-file would keep the quotes:

MASTER_DATABASE_URL=mysql://purgatory:secret@db1
SLAVE_DATABASE_URLS=mysql://purgatory:secret@db2,mysql://purgatory:secret@db3

Then dry-run it and eyeball what it would do:

docker run --rm -t --env-file .env ghcr.io/hacker/mysql_purgatory --dry-run
# would purge files=12 size=3.2G target="binlog.000123"
# would execute: PURGE BINARY LOGS TO 'binlog.000123'

If you like what you see, drop --dry-run.

With compose

services:
  purgatory:
    image: ghcr.io/hacker/mysql_purgatory
    env_file: .env
    tty: true

docker compose run --rm purgatory --dry-run

tty: true gets you colored logs. Without a terminal the logs are plain, which is what you want in cron mail: docker compose run --rm -T purgatory in a crontab.

How it decides

1. Connects to the master and every replica. If any of them is unreachable, it gives up. Purging on partial information is how data gets lost.
2. Asks the master which replicas have registered with it. If there are more than you configured, it refuses to run: a replica you forgot about would lose its logs. Only replicas with report_host set register themselves, so this is a safety net, not a guarantee.
3. Asks each configured replica for its status and insists on:
   • exactly one replication channel (multi-source is not supported, so it bails),
   • both the IO and SQL threads running,
   • Source_UUID matching the master's @@server_uuid, so a wrong pairing is caught.
4. Takes Relay_Source_Log_File from each replica. That is the file the SQL thread has applied, not merely fetched. The oldest one across all replicas wins.
5. Steps back --keep more files (default 1) as a safety margin, then purges everything before that.

The file name used in PURGE always comes from the master's own SHOW BINARY LOGS, never straight from a replica's answer. If a replica names a file the master has never heard of, that's an error, not a guess.

Configuration

Configuration comes from the environment, or a .env file in the working directory (/app in the image). Every setting also has a flag, and flags win.

Env var	Flag	Notes
MASTER_DATABASE_URL	--master-url	mysql://user:pass@host:3306
SLAVE_DATABASE_URLS	--slave-urls	comma-separated, at least one
KEEP_BINLOGS	--keep N	extra files to keep before the target, def. 1
(none)	--dry-run	log the statement it would run, don't run it
RUST_LOG		log filter, defaults to info

SLAVE_DATABASE_URLS is split on commas, so a literal comma in a URL (e.g. in the password) must be written as %2C.

Everything is logged to stderr, the dry run's statement included. Failures name the node and statement that tripped, e.g. slave #1 db3:3306: SHOW REPLICA STATUS. URLs, passwords included, are never logged. Any failure exits non-zero, which is what cron needs to email you about.

Privileges

A dedicated user keeps this boring and auditable. CREATE USER and GRANT are binlogged, so the user replicates to the replicas on its own, and replicas only need REPLICATION CLIENT. The master needs two more, and REPLICATION SLAVE is the kind you don't want lying around on every node (it lets a user stream the binlog), so that part is kept off the replication stream:

-- on the master only
CREATE USER 'purgatory'@'%' IDENTIFIED BY '...';
GRANT REPLICATION CLIENT ON *.* TO 'purgatory'@'%';

SET SESSION sql_log_bin = 0;
GRANT REPLICATION SLAVE, BINLOG_ADMIN ON *.* TO 'purgatory'@'%';

Check with SHOW GRANTS FOR 'purgatory'@'%' on a replica. If the user isn't there, your replicas filter the mysql schema (replicate-ignore-db or replicate-wild-ignore-table), and you'll have to create it on each of them. After a failover, re-run the second GRANT on the new master.

Cron

17 3 * * *  cd /etc/mysql_purgatory && docker compose run --rm -T purgatory

Build

cargo build --release

Requires Rust 1.98 or newer. Or docker build -t mysql_purgatory ..