MySQL Backup Best Practices: A Complete Guide

MySQL backups fail in predictable ways: dumps that lock production, copies of data files taken while the server writes, and nightly backups that cannot recover the transactions of the afternoon. This guide explains logical and physical backups, how binary logs enable point-in-time recovery, and how to build a routine you can trust.

Logical vs physical backups

Every MySQL backup method falls into one of two families.

A common rule of thumb: logical dumps are comfortable up to tens of gigabytes; beyond that, restore times measured in hours push most teams to physical backups. Measure on your own data.

Consistent logical backups with mysqldump

For InnoDB tables, --single-transaction opens a consistent snapshot so the dump reflects a single moment without blocking writes:

mysqldump --single-transaction --quick --routines --events --triggers \
  --source-data=2 --databases shop \
  -u backup -p | gzip > /srv/backups/shop-$(date +%F_%H%M).sql.gz

• --quick streams rows instead of buffering whole tables in memory.
• --routines, --events and --triggers include stored code (triggers are included by default, the others are not).
• --source-data=2 writes the binary log position as a comment, which you need for point-in-time recovery. On versions older than MySQL 8.0.26 the option is called --master-data.

Two caveats matter. First, --single-transaction only guarantees consistency for transactional engines: MyISAM tables can change during the dump. Second, DDL statements such as ALTER TABLE run during the dump can break the consistent snapshot, so avoid schema changes while backups run.

Use a dedicated account with the minimum privileges, for example SELECT, SHOW VIEW, TRIGGER, EVENT, LOCK TABLES and RELOAD (for --source-data), plus REPLICATION CLIENT to read the binary log position. If you see an error about the PROCESS privilege and tablespaces, add --no-tablespaces or grant PROCESS.

Note that mysqlpump was deprecated in MySQL 8.0.34 and removed in MySQL 8.4. For parallel logical dumps, use MySQL Shell's util.dumpInstance() and util.loadDump().

Physical backups with Percona XtraBackup

XtraBackup copies InnoDB files while the server runs and records the redo log generated during the copy. The backup must then be "prepared" to make it consistent:

# 1. Take the backup
xtrabackup --backup --user=backup --password=*** --target-dir=/srv/backups/full-2026-10-19

# 2. Prepare it (apply the redo log) before it can be restored
xtrabackup --prepare --target-dir=/srv/backups/full-2026-10-19

# 3. Restore: with MySQL stopped and an empty datadir
xtrabackup --copy-back --target-dir=/srv/backups/full-2026-10-19
chown -R mysql:mysql /var/lib/mysql

XtraBackup also supports incremental backups based on changed pages. Use a XtraBackup version that matches your MySQL major version (8.0 or 8.4); mismatched versions are a frequent cause of failed backups.

Binary logs and point-in-time recovery

A nightly backup alone means that an incident at 17:00 loses every transaction since the previous night. Binary logs close that gap: they record every change, so you can restore the last full backup and replay changes up to a chosen moment.

Binary logging is enabled by default since MySQL 8.0. Check and tune it:

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';            -- ROW is the default and recommended
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'; -- 2592000 (30 days) by default
SHOW BINARY LOGS;

Keep binary logs at least as long as the interval between full backups, and copy them offsite continuously or frequently: if the server's disk dies, local binary logs die with it.

A point-in-time recovery after an accidental DELETE at 14:32 looks like this:

# 1. Restore the last full backup (taken at 02:00) on a recovery server
gunzip < shop-2026-10-19_0200.sql.gz | mysql -u root -p

# 2. Find the binlog position written in the dump header
zgrep -m1 "CHANGE REPLICATION SOURCE TO\|CHANGE MASTER TO" shop-2026-10-19_0200.sql.gz

# 3. Replay changes from that file/position until just before the mistake
#    (POS is the SOURCE_LOG_POS / MASTER_LOG_POS value found in step 2)
mysqlbinlog --start-position="$POS" --stop-datetime="2026-10-19 14:31:59" \
  binlog.000412 binlog.000413 | mysql -u root -p

--stop-datetime is interpreted in the time zone of the machine running mysqlbinlog, so double-check it during an incident. With GTIDs enabled, you can also stop at a specific transaction using --exclude-gtids or --include-gtids.

Retention and storage

• Keep several generations: for example 7 daily, 4 weekly and 12 monthly backups, plus binary logs covering at least the daily interval.
• Store at least one copy offsite, encrypted, ideally on immutable storage. See the 3-2-1-1-0 rule.
• Back up configuration (my.cnf) and user grants, which are needed to rebuild the server.
• Estimate storage with the method in How much cloud backup storage does your business need?

Backing up replicas instead of the primary

Running backups on a replica keeps load away from the primary. Make sure the replica is healthy and not lagging (SHOW REPLICA STATUS), and record its position relative to the primary. A broken replica that stopped replicating last week will happily produce consistent backups of week-old data.

Test your restores

Schedule automated restore tests to a separate server:

1. restore the latest backup into an empty instance;
2. run sanity checks, such as row counts of key tables and the timestamp of the most recent order;
3. optionally run mysqlcheck --check --all-databases;
4. record the time it took, which is your real recovery time (see RPO vs RTO).

Common mistakes

• Copying /var/lib/mysql with cp or rsync while MySQL runs.
• Dumping without --single-transaction and locking tables in production, or using it with MyISAM tables and assuming consistency.
• Keeping binary logs only on the database server.
• Never restoring a backup, so nobody knows how long a restore takes.
• Storing backup credentials with excessive privileges in scripts readable by everyone.

Next steps

1. Check your engines: convert remaining MyISAM tables to InnoDB where possible.
2. Create a dedicated backup user and a scheduled dump or XtraBackup job.
3. Confirm binary log retention and start copying binary logs offsite.
4. Run a point-in-time recovery drill on a test server.

If you run MariaDB, read MariaDB backup strategies, since tools and options differ. The Backnix database backup page shows how Backnix plans to automate consistent MySQL backups and the status of that integration.

References

• MySQL 8.4 Reference Manual: mysqldump
• MySQL 8.4 Reference Manual: Point-in-Time Recovery Using Binary Logs
• MySQL Shell: Instance and Schema Dump Utilities
• Percona XtraBackup documentation

Topics: Binary logsMySQLmysqldumpPoint-in-time recovery