MySQL backups you can actually restore

Here's an uncomfortable question for anyone running MySQL or MariaDB in production: when did you last restore one of your backups?

Not "check that the cron job ran." Not "confirm the .sql file landed in the bucket." Actually restore it, into a real database, and query the data.

If the answer is "never," you don't have backups. You have a folder of files you hope are backups. Restore failures are almost never discovered during backups; they're discovered during outages, which is the one moment you can't afford them.

There's a MySQL-specific trap on top of that. A mysqldump run without the right flags can produce a dump that's internally inconsistent (rows from different points in time, because writers kept going mid-dump) or that silently drops your stored procedures and triggers. It restores without error and looks fine, right up until the application hits a missing routine.

This guide builds a MySQL backup pipeline where consistency and restore are part of the pipeline, not an afterthought:

1. Dump consistently (the flags that actually matter)
2. Verify the dump is complete
3. Prove it restores (the step everyone skips)
4. Ship it off-site: encrypted, deduplicated, versioned
5. Apply retention automatically
6. Put the whole thing on a schedule

Total time: about 10 minutes. Total cost: free up to 5 GB.

1. Dump consistently

For InnoDB tables (the default engine), the single most important flag is --single-transaction. It takes the dump inside one transaction, so you get a consistent snapshot without locking out writers:

mkdir -p ./db-dumps

mysqldump \
  --host=127.0.0.1 \
  --port=3306 \
  --user=root \
  --single-transaction \
  --quick \
  --routines \
  --triggers \
  app_db > ./db-dumps/app.sql

What each flag buys you, and why leaving it off bites later:

Two habits worth building in now:

• Keep the file path stable. Overwrite ./db-dumps/app.sql on every run. That stable path is what makes deduplicated, incremental uploads effective later: only the changed blocks ship.
• Keep the password out of shell history. Put credentials in a ~/.my.cnf under a [client] section and drop --password from the command, so it isn't visible in ps or your history file.

Note on MyISAM: --single-transaction only gives consistency for transactional engines. If you still run MyISAM tables, they can change mid-dump; the durable fix is migrating them to InnoDB.

Need roles, events, and every schema? Dump the whole server:

mysqldump --host=127.0.0.1 --user=root \
  --single-transaction --quick --routines --triggers \
  --all-databases > ./db-dumps/all.sql

2. Verify the dump is complete

Thirty seconds, and it catches the two most common failures: a truncated file, or a dump that died halfway. A complete mysqldump ends with a specific marker line:

ls -lh ./db-dumps/app.sql
tail -n 1 ./db-dumps/app.sql   # a complete dump ends with: -- Dump completed on ...

If that last line isn't there, the dump was cut short (disk full, killed process, broken pipe to the bucket) and everything downstream is worthless. A file that exists is not a file that's complete.

3. Prove it restores

This is the step that separates a backup strategy from a folder of files. Restore into a throwaway database, never over production:

mysql --host=127.0.0.1 --user=root -e "CREATE DATABASE app_db_restore"

mysql --host=127.0.0.1 --user=root app_db_restore < ./db-dumps/app.sql

# Then look at the data. Row counts, latest records, whatever proves it's real:
mysql --host=127.0.0.1 --user=root app_db_restore -e "SELECT COUNT(*) FROM users;"

Time this step with a stopwatch. That wall-clock number is your real RTO, the minimum downtime you'd eat in a genuine recovery. Large SQL dumps replay row by row, so restore is often much slower than the dump was. If the number surprises you, better to learn it now than during an incident.

While you're here, spot-check that the routines came across: SHOW PROCEDURE STATUS WHERE Db = 'app_db_restore'; should list what you expect. This is exactly where a --routines-less dump reveals itself.

4. Ship it off-site

A verified dump sitting next to the database dies with the database. The classic 3-2-1 rule: three copies, two media, one off-site.

You could aws s3 cp the .sql into a bucket, but a bucket is storage, not backup logic. No versioned snapshots, no integrity verification on restore, no retention policy, no deduplication (you pay full price to store 30 nearly-identical dumps), and encryption is on you.

This is what Backup Data handles. Sign in, claim the free 5 GB workspace, mint an API key scoped backup:write, backup:read, snapshots:read, then:

npm install @lighthouse-web3/baas-js-sdk
export LH_API_KEY="lh_xxxxxxxxxxxxxxxxxxxxxxxx"
export LH_WORKSPACE_ID="your-workspace-uuid"

import { BackupClient } from "@lighthouse-web3/baas-js-sdk";

const client = new BackupClient({
  apiKey: process.env.LH_API_KEY,
  workspaceId: process.env.LH_WORKSPACE_ID,
});

const snapshot = await client.backup(["./db-dumps"], {
  description: "nightly database backup",
  tags: { type: "db-backup", env: "prod", db: "app_db" },
});

console.log(`snapshotId=${snapshot.snapshotId} totalSize=${snapshot.totalSize}`);

(There's a Go SDK with the same surface if that's your stack.)

Each run creates an immutable, point-in-time snapshot. Because uploads are content-addressed and chunked (FastCDC), a nightly SQL dump that's 2% different from yesterday's uploads roughly 2% of the bytes. That's the stable file path from step 1 paying off, and SQL dumps of a slowly-changing database dedupe especially well because most of the text is identical night to night.

If the dump contains anything sensitive (it does), enable client-side encryption. Data is AES-GCM-encrypted on your machine before upload; the server only ever stores ciphertext:

import { generateKeyfile } from "@lighthouse-web3/baas-js-sdk";

// once:
generateKeyfile("/secure/lh.keyfile", process.env.LH_KEYFILE_PASSPHRASE);

// then on every backup:
const encrypted = await client.backup(["./db-dumps"], {
  description: "nightly-encrypted",
  encryption: {
    keyfilePath: "/secure/lh.keyfile",
    passphrase: process.env.LH_KEYFILE_PASSPHRASE,
  },
});

One warning that deserves bold: you hold the keys. Lose the keyfile and passphrase and the data is unrecoverable. There is no server-side reset. Store the keyfile like you store any root credential.

5. Retention, so storage doesn't grow forever

Nightly snapshots for two years is 700+ snapshots you'll never use. Encode a retention policy, and dry-run it before letting it delete anything:

const keepLatest = 14;
const before = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000).toISOString();

// Preview: what would be deleted?
const preview = await client.pruneSnapshots({ keepLatest, before, dryRun: true });
console.log("would delete:", preview.snapshotIds);

// Then for real:
await client.pruneSnapshots({ keepLatest, before, dryRun: false });

A snapshot is deleted only when it's both outside the newest 14 and older than 30 days. Chunks still referenced by surviving snapshots are kept automatically; deleting a snapshot never corrupts its neighbors.

6. Schedule it, including the restore drill

Wire dump + upload into cron or CI (mint a dedicated API key for the job):

# /etc/cron.d/mysql-backup (nightly at 02:15)
15 2 * * * backup /opt/backup/run-backup.sh >> /var/log/mysql-backup.log 2>&1

Where run-backup.sh is the mysqldump command from step 1 followed by node upload.mjs with the backup() call from step 4.

And schedule the drill, because step 3 wasn't a one-time ceremony. Monthly, restore the latest snapshot into a scratch directory and load it into a throwaway database:

import { mkdtempSync } from "node:fs";
import { tmpdir } from "node:os";
import { join } from "node:path";

const target = mkdtempSync(join(tmpdir(), "lh-restore-drill-"));
await client.restore(snapshot.snapshotId, target, {
  onProgress: (e) => console.log(`[${e.phase}] ${e.current}/${e.total}`),
});
// then: mysql ... app_db_restore < ${target}/db-dumps/app.sql

Every restore verifies chunk integrity against expected checksums before reassembly. A silently corrupted backup fails loudly at drill time, not at incident time.

The drill checklist:

• ✅ Restore into a fresh, empty database. Never over live data
• ✅ Leave checksum verification on
• ✅ Confirm the dump's trailing -- Dump completed marker survived the round trip
• ✅ Spot-check row counts and that routines/triggers are present
• ✅ Record wall-clock time (that's your RTO, keep a log of it)
• ✅ Drop the scratch database and directory when done

The pipeline, complete

The difference between this and the mysqldump | aws s3 cp script in most repos isn't the dump. It's that consistency flags, restore, verification, encryption, dedupe, and retention are part of the loop instead of TODOs.

Start with the free tier: backupdata.io has 5 GB free, no card, and the 10-minute quickstart gets you to a first verified snapshot today.