MySQL backups for small teams: mysqldump, MySQL Shell and point-in-time recovery

Most small teams discover the state of their MySQL backups on the worst possible day: a migration dropped a column, a bug deleted rows, or a disk failed. At that point the questions are simple. Do we have a copy? How old is it? How long will a restore take? Can we get back to five minutes before the mistake rather than last night?

This guide answers those questions for a typical application database of a few gigabytes to a few hundred gigabytes running MySQL 8.4 LTS or 9.7 LTS. It compares the three tools that ship with MySQL Community (mysqldump, the MySQL Shell dump and load utilities, and the binary log), gives working commands for each, and ends with a checklist for testing restores, which is the part most backup plans skip. Commands and defaults were checked in the MySQL 9.7 Reference Manual and MySQL Shell manual in October 2026.

Decide what you need to recover from

Before choosing tools, write down two numbers for the database:

• Recovery point objective (RPO): how much data you can afford to lose. "Last night's backup" means up to 24 hours of orders, sign-ups or edits.
• Recovery time objective (RTO): how long the application can be down while you restore.

A nightly dump alone gives an RPO of a day. Adding the binary log lets you replay changes up to a chosen moment, which brings RPO down to minutes. RTO depends mostly on restore speed, which is where the tools differ most.

Physical backup tools (MySQL Enterprise Backup, file-system or volume snapshots, and managed-service snapshots) are faster for very large databases but are outside the scope of this guide. If you use a managed MySQL service, its automated backups and point-in-time restore are usually the first line of defence; a periodic logical dump you control is still a useful second copy.

Option 1: mysqldump for small databases

For InnoDB tables, the essential flag is --single-transaction. It starts a transaction before dumping, so you get a consistent snapshot without locking tables while the application keeps writing. The manual's one caveat: do not run DDL (ALTER, CREATE, DROP, RENAME, TRUNCATE) on the tables while the dump runs, so schedule dumps away from deployments that migrate the schema.

mysqldump \
  --single-transaction \
  --routines --triggers --events \
  --set-gtid-purged=AUTO \
  --source-data=2 \
  --databases app \
  | zstd -q -o /backups/app-$(date +%Y%m%d-%H%M).sql.zst

What each option contributes:

• --routines and --events include stored routines and scheduled events; triggers are included by default, and --triggers just makes that explicit.
• --source-data=2 writes the binary log coordinates as a comment at the top of the dump. You need them to start point-in-time recovery from the right position. (It replaces the deprecated --master-data.)
• --set-gtid-purged=AUTO includes the GTID state if GTIDs are enabled.

Use a dedicated backup account with only the privileges needed (SELECT, SHOW VIEW, TRIGGER, plus PROCESS unless you pass --no-tablespaces, and RELOAD or FLUSH_TABLES in some GTID configurations), and keep its password in an option file readable only by the backup user rather than on the command line. On Windows PowerShell, use --result-file instead of > redirection, which would produce a UTF-16 file MySQL cannot load.

To restore into an empty server:

zstd -dc /backups/app-20261007-0200.sql.zst | mysql --defaults-extra-file=/etc/mysql/restore.cnf

Option 2: MySQL Shell dump and load utilities

Once a dump takes more than a few minutes or a restore threatens your RTO, MySQL Shell's utilities are the better logical option. They split tables into chunks (about 64 MB each by default), dump and load them in parallel, compress with zstd by default and write a directory of DDL, data and metadata files. Use the MySQL Shell release that matches or is newer than your server; the current Shell manual is for 26.7.

// In mysqlsh, connected to the source server (JavaScript mode)
util.dumpSchemas(["app"], "/backups/app-20261007", {
  threads: 4,
  consistent: true,     // default: consistent snapshot across threads
  showProgress: true
})

// For every schema plus user accounts and grants:
util.dumpInstance("/backups/instance-20261007", { threads: 8 })

Run the dump with {dryRun: true} first to see what would be dumped and catch compatibility problems without writing anything.

Loading uses util.loadDump(). It relies on LOAD DATA LOCAL INFILE, so the target server's global local_infile setting must be ON for the duration of the import; switch it off again afterwards.

// On the target server
\sql SET GLOBAL local_infile = ON;
\js
util.loadDump("/backups/app-20261007", { threads: 8, dryRun: true })   // check first
util.loadDump("/backups/app-20261007", { threads: 8 })
\sql SET GLOBAL local_infile = OFF;

Two behaviours worth knowing. Loads are resumable: progress is recorded in a load-progress.<server_uuid>.json file in the dump directory, so rerunning the same command after an interruption skips completed steps. And users are not loaded by default; pass loadUsers: true when restoring an instance dump that should recreate accounts.

Option 3: the binary log for point-in-time recovery

Binary logging is enabled by default in current MySQL releases (log_bin is ON) and uses row-based format by default. The binary log records every change, so a full backup plus the binary logs written after it lets you replay the database to any point in between, typically to just before a destructive statement.

Two settings decide whether that works in practice:

• Retention. binlog_expire_logs_seconds defaults to 2,592,000 seconds (30 days). Binary logs must cover at least the interval back to your oldest full backup that you would restore from.
• Location. Logs that live only on the database server disappear with it. Copy closed binary log files to separate storage regularly, for example every few minutes, or stream them live from another host with mysqlbinlog --read-from-remote-server --host=db1 --raw --stop-never binlog.000130. That process does not reconnect on its own after a server restart or network outage, so run it under a supervisor that restarts it.

A point-in-time recovery walkthrough

Suppose a bad deployment ran DROP TABLE invoices at about 14:06. Restore last night's full backup to a new server first. Then find the exact position of the destructive event. The manual recommends using the date-time options only to locate the event, and positions to apply the log:

# 1. Locate the event (date-times only to narrow the search)
mysqlbinlog --start-datetime="2026-10-07 14:00:00" \
            --stop-datetime="2026-10-07 14:10:00" --verbose \
            /var/lib/mysql/binlog.000123 | grep -C 15 "DROP TABLE"

# 2. Replay from the dump's recorded position up to just before the DROP
mysqlbinlog --start-position=<pos_from_dump> --stop-position=<pos_before_drop> \
            /var/lib/mysql/binlog.000123 | mysql -u root -p

# 3. Optionally replay everything after the DROP event
mysqlbinlog --start-position=<pos_after_drop> /var/lib/mysql/binlog.000123 \
            | mysql -u root -p

When several binary log files are involved, pass them all to a single mysqlbinlog invocation piped into one mysql connection, as the manual shows, rather than one connection per file. Do not rely on the binary log position InnoDB prints after a restore; the manual warns it can miss DDL and non-InnoDB changes.

Storage, retention and security

• Keep copies off the server and off the account. A backup in the same cloud account as the database shares its failure and compromise risks. Store at least one copy in a separate account or provider, with deletion protection or object lock where available.
• Encrypt backups at rest and in transit; a dump contains every customer record and password hash in the database.
• Retain in layers, for example daily backups for two weeks, weekly for two months and monthly for a year, adjusted to your legal and contractual obligations.
• Back up configuration too: the server option file, user accounts and grants (util.dumpInstance includes users by default), and the exact server version.
• Monitor the job: alert when a backup fails, when its size changes sharply, or when the newest backup is older than expected.

Restore-testing checklist

A backup that has never been restored is a hope, not a backup. Run this at least monthly, and after any change to the backup job:

1. Provision a clean server with the same MySQL version and configuration as production.
2. Restore the most recent full backup and record how long it took. Compare it with your RTO.
3. Replay binary logs to a chosen timestamp and confirm a known recent change is present.
4. Check row counts on key tables against production at the backup's time.
5. Run CHECK TABLE on the largest tables, and start the application against the restored copy to confirm it works.
6. Confirm users, grants, routines, triggers and events came back.
7. Write down the exact commands used, so the person on call during an incident does not have to work them out.
8. Destroy the test server and its data afterwards; it holds production data.

Summary

For a small database, a nightly mysqldump --single-transaction plus copied binary logs gives you minutes of RPO with very little machinery. As data grows, move the full backup to MySQL Shell's parallel dump and load utilities to keep restores within your RTO. Whatever you choose, keep copies off the server, encrypt them and, above all, rehearse the restore on a schedule.