Restoring to a scratch database on a schedule, automatically

The restore is the part that fails, and finding that out during an incident is the worst possible moment to discover the dump has been missing a table since March.

#!/usr/bin/env bash
set -euo pipefail

latest=$(ls -t /backup/shop-*.sql.gz | head -1)

mysql -e 'DROP DATABASE IF EXISTS restore_check; CREATE DATABASE restore_check;'
zcat "$latest" | mysql restore_check

tables=$(mysql -Nse "SELECT COUNT(*) FROM information_schema.tables
                     WHERE table_schema='restore_check'")
orders=$(mysql -Nse 'SELECT COUNT(*) FROM restore_check.orders')

[ "$tables" -ge 42 ] && [ "$orders" -gt 0 ] || exit 1

Asserting on a table count and a row count turns the restore into a test rather than a ritual — a dump that restores but contains an empty orders table fails loudly. Running it weekly on a spare machine also produces a number nobody otherwise has: how long a restore actually takes, which is the input to every recovery-time conversation. The number is invariably larger than everyone assumed.