SafeGrd / MySQL backup

MySQL and MariaDB backups that are restored on a schedule

SafeGrd backs up MySQL 8.0 and 8.4 and MariaDB 10.6 and 11.4 with the dump tool from the server's own project, run with --single-transaction. The dump streams into an archive that is compressed and encrypted on your host, written to S3-compatible storage under Object Lock, and loaded into a scratch database on a schedule to check every table's rows.

Run your first Fire Drill Read the install guide

curl -fsSL https://safegrd.dev/install.sh | sh
safegrd backup --database-url "mysql://backup:$DB_PASSWORD@db.internal:3306/shop" --retention-days 14
safegrd verify --snapshot <newest> --sandbox-target "mysql://drill:$DRILL_PASSWORD@localhost:3306/scratch"

No server to run it on

SafeGrd can take the backup itself, on a machine it starts for each run: Back up on SafeGrd under Surfaces in the console, engine MySQL or MariaDB, and the connection string, such as mysql://backup:PASSWORD@db.example.com:3306/shop?tls=true. A server that still uses MySQL's own self-signed certificate needs tls=skip-verify instead (TLS options). The database has to accept connections from the internet. Where SafeGrd's machines connect from fixed addresses, the console lists them beside Back up on SafeGrd. Until it does, a database that admits only listed addresses has to admit any address for these backups. How it runs.

What each backup holds

What a Fire Drill checks

An in-memory drill confirms the dump reached its final marker and that each table's rows match the manifest. A sandbox drill loads the dump into a scratch database, counts the tables and rows there, and drops the database when it is done. That is the check that catches a dump that is complete but will not load, such as a trigger whose trailing semicolon mysqldump writes in a form the server rejects. SafeGrd marks those triggers when it takes the backup.

Both kinds of Fire Drill read the locked, compressed and encrypted backup. The in-memory drill checks that the dump reached its end and that rows match the manifest. The sandbox drill loads it into a scratch database and counts tables and rows there. Each ends in a signed drill record. locked backup compressed, encrypted on your host in memory dump reached its end rows match manifest sandbox loaded into a scratch DB tables and rows counted signed drill record
Both drills read the locked backup. Only the sandbox drill loads it into a database, which is the check that catches a dump that is complete but will not load.

Restoring into a managed server

Views, triggers and routines keep the account that created them, and recreating another account's objects needs SET_USER_ID, which RDS and most managed servers do not grant. When SafeGrd restores, it rewrites each definer to the restoring account and reports how many it changed. It backs up with --set-gtid-purged=OFF, so neither the backup nor the restore needs SUPER.

Questions

Does it lock my tables?

InnoDB tables are read from a snapshot without a lock. MyISAM tables have no transactions, so mysqldump reads them as they are while the dump runs. If writes to them must line up with the rest, move them to InnoDB.

Can I back up from a replica?

SafeGrd only reads, so point it at an RDS, Aurora or self-hosted read replica to keep the table scans off the primary.

What does the restore target need?

An empty database, and a max_allowed_packet at least as high as the source's, so the largest rows load. MySQL applies DDL outside transactions, so a failed restore can leave tables behind: drop and recreate the target before you try again.

Who holds the decryption key?

You choose when the key is made. With a SafeGrd-managed key, SafeGrd keeps your key sealed and releases it only to your enrolled hosts, so you can restore even after losing a host. With a customer-managed key, only you can decrypt these backups.

Related