A restore on a schedule, asserting on row counts

A nightly dump that has exited zero for two years proves the dump command works, which is a different claim from the data being recoverable.

#!/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 ]

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