Backup and Recovery

Replication is not a backup#

The replication page in this section covers how a replica keeps a live, continuously up-to-date copy of a primary's data — which makes it tempting to treat a replica as "the backup." It isn't one: replication faithfully propagates every change, including an accidental DROP TABLE customers; or a bad DELETE missing its WHERE clause, to every replica just as quickly as it propagates a legitimate write. A real backup strategy needs a copy of the data that isn't automatically corrupted by the same mistake that corrupted the primary — which means a point-in-time snapshot, kept separately, that can be rolled back to.

Logical backups with mysqldump#

mysqldump produces a logical backup — a plain text file of SQL statements (CREATE TABLE, INSERT) that reconstructs the data by re-running them, rather than a raw copy of MySQL's on-disk files.

# Back up one database
mysqldump -u root -p --single-transaction --routines --triggers shop > shop_backup.sql

# Back up a single table
mysqldump -u root -p shop orders > orders_backup.sql

# Back up every database on the server
mysqldump -u root -p --all-databases --single-transaction > full_backup.sql

--single-transaction is essential for InnoDB tables: it wraps the entire dump in one transaction, taking a consistent snapshot via MVCC-like read consistency rather than locking every table for the duration of the dump — without it, mysqldump falls back to table locks that block writes on a live production database for as long as the dump takes. --routines and --triggers capture stored procedures/functions and triggers, which aren't included by default.

Restoring is just replaying the file:

mysql -u root -p shop < shop_backup.sql

Logical backups are portable (the same .sql file restores cleanly onto a different MySQL version, or even a different storage engine), human-readable, and easy to filter down to one table — but they're slow for large databases, since restoring means fully re-running every INSERT and rebuilding every index from scratch rather than just copying already-built data files into place.

Physical backups, conceptually#

A physical backup copies MySQL's actual on-disk data files directly, rather than re-deriving them from SQL statements. Tools like Percona XtraBackup (or MySQL Enterprise Backup) can do this against a live, running InnoDB server without taking it offline, by combining a snapshot with a replay of changes made during the copy — conceptually similar to how the binlog replay described below works, just applied at the storage-engine level instead of across a network to a replica.

The trade-off runs opposite to logical backups: a physical backup restores dramatically faster on a large database, since it's just putting pre-built files back in place rather than re-executing millions of statements — but the resulting files are tied to the specific MySQL version and storage engine they came from, and can't be filtered down to "just this one table" the way a mysqldump file naturally can.

Point-in-time recovery using the binlog#

A full backup — logical or physical — only captures data as of the moment it ran. If a full backup was taken at 2 AM and a bad DELETE runs at 10:15 AM, restoring the 2 AM backup alone would also throw away eight hours of perfectly good, legitimate writes made between 2 AM and the mistake. Point-in-time recovery (PITR) solves this by replaying the binary log — the same durable, ordered record of every change that replication is built on — from the moment of the backup up to just before the mistake.

Worked example#

A full backup runs nightly at 2 AM. At 10:15 AM, someone runs DELETE FROM orders; with no WHERE clause. It's caught at 11 AM. Recovery:

# 1. Restore the last known-good full backup
mysql -u root -p shop < shop_backup_2am.sql

# 2. Find which binlog file and position correspond to just before the bad statement.
#    mysqlbinlog can print the binlog as readable SQL with timestamps to locate it:
mysqlbinlog --start-datetime="2026-08-25 02:00:00" binlog.000045 | less

# 3. Replay everything from the backup's binlog position up to (but not including)
#    the moment the bad DELETE ran
mysqlbinlog --start-datetime="2026-08-25 02:00:01" \
            --stop-datetime="2026-08-25 10:14:59" \
            binlog.000045 binlog.000046 | mysql -u root -p shop

This restores the 2 AM snapshot, then replays every legitimate write between 2 AM and 10:14:59 AM — recovering everything except the mistake itself, rather than losing eight hours of real data along with it. Precisely targeting a stop point can also be done by binlog position (--stop-position) instead of a timestamp, which is more exact when several statements ran within the same second.

Common mistakes#

• Relying on a replica as the only "backup" — it faithfully replicates mistakes just as fast as legitimate writes, and offers no way to go back to a point before the mistake happened.
• Running mysqldump without --single-transaction against InnoDB tables on a live server, either getting an inconsistent snapshot (rows changed mid-dump) or unexpectedly locking production tables for the duration of the backup.
• Never actually testing a restore — a backup file nobody has successfully restored from is an unverified assumption, not a working recovery plan; the only way to know a backup is good is to have restored it at least once.
• Configuring binlogs to expire (binlog_expire_logs_seconds) sooner than the gap between backups, silently making point-in-time recovery impossible for anything that happened between the last successful full backup and the binlog's retention cutoff.