MySQL Backup Strategies

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples in this guide work on VillageSQL. Install Now →

A MySQL backup strategy has two parts: taking a consistent snapshot of your data (the full backup) and having a way to replay changes made after that snapshot (point-in-time recovery). The right tools depend on database size, acceptable recovery time, and how much data you can afford to lose.

mysqldump — Logical Backups

mysqldump exports data as SQL statements. It’s the most portable format and works for databases of any size, though it’s slower to restore than binary backups for large datasets. Back up a single database:

mysqldump -u root -p myapp > myapp_backup.sql

Back up all databases:

mysqldump -u root -p --all-databases > all_databases.sql

Recommended flags for InnoDB:

mysqldump \
  --single-transaction \
  --source-data=2 \
  --routines \
  --triggers \
  --events \
  -u root -p myapp > myapp_backup.sql

Flag	Purpose
`--single-transaction`	Consistent snapshot without locking tables (InnoDB only)
`--source-data=2`	Records binlog position as a comment — required for PITR
`--routines`	Include stored procedures and functions
`--triggers`	Include triggers (default on, but explicit is clear)
`--events`	Include scheduled events

Without --single-transaction, mysqldump locks tables with LOCK TABLES, which blocks writes for the duration of the dump. Restore from a dump:

mysql -u root -p myapp < myapp_backup.sql

To restore all databases: mysql -u root -p < all_databases.sql

Point-in-Time Recovery with Binary Logs

Full backups only protect you to the point of the last backup. Binary logging lets you replay every change made after the backup was taken. Requirements:

Binary logging enabled (log_bin = ON)
--source-data=2 used when taking the backup (records the starting binlog position)

Workflow:

Restore the full backup:

mysql -u root -p < myapp_backup.sql

Find the binlog starting position from the dump file:

head -50 myapp_backup.sql | grep "SOURCE_LOG_FILE"
-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='mysql-bin.000003', SOURCE_LOG_POS=154;

Replay binary logs up to the point just before the event you want to undo:

mysqlbinlog \
  --start-position=154 \
  --stop-datetime="2024-06-15 11:59:00" \
  mysql-bin.000003 mysql-bin.000004 | mysql -u root -p

See MySQL Binary Logging for full details on mysqlbinlog options.

MySQL Shell Dump Utilities

MySQL Shell (mysqlsh) provides faster dump and load utilities than mysqldump. They use parallel threads and produce compressed output, making them practical for large databases.

# Dump a single schema
mysqlsh root@localhost -- util dump-schemas '["myapp"]' --outputUrl=/backups/myapp

# Load it back
mysqlsh root@localhost -- util load-dump /backups/myapp

MySQL Shell dump utilities compress output by default (zstd) and can parallelize both dump and load across multiple CPU cores. For databases larger than a few GB, the speed difference over mysqldump is significant.

Backup Methods Compared

Method	Backup size	Backup speed	Restore speed	Consistency	MySQL compatibility
`mysqldump`	Large (plain SQL)	Slow	Slow (re-executes SQL)	Consistent snapshot (`--single-transaction`)	Any MySQL version
`mysqlpump`	Large (plain SQL)	Faster (parallel dump)	Slow (re-executes SQL)	Per-table consistency only	Deprecated in 8.4 — avoid
MySQL Shell dump	Small (zstd compressed)	Fast (parallel, compressed)	Fast (parallel load)	Consistent snapshot	MySQL 8.4+
Percona XtraBackup	Large (physical copy)	Fast (hot copy, InnoDB-aware)	Very fast (file copy)	Full InnoDB consistency	Same major version
Cold file copy	Large (physical copy)	Fast	Very fast (file copy)	Consistent only if server is stopped	Same MySQL version

mysqldump is still the go-to for portability and simplicity. MySQL Shell dump has effectively replaced mysqlpump for large databases — it’s faster, produces smaller files, and supports MySQL 8.0+. mysqlpump is deprecated in MySQL 8.4 and should not be used for new backup scripts. For production databases over ~50 GB where restore time matters, Percona XtraBackup or MySQL Shell dump are the practical choices.

When to use each method

Method	Use when	Avoid when
`mysqldump`	Portability is required; migrating between MySQL versions; database is under ~10 GB; you want a human-readable backup	Database is large and restore time is critical; you need sub-minute backup windows
MySQL Shell dump	Database is large (10 GB+); you want parallel load with compression	MySQL Shell isn’t installed or permitted
`mysqlpump`	(Don’t use it — deprecated in MySQL 8.4. Use MySQL Shell dump instead.)	—
Percona XtraBackup	Very large InnoDB databases; you need hot physical backups with minimal performance impact; restore speed is critical	Mixed storage engines (MyISAM); you can’t install third-party tools; smaller databases where complexity isn’t justified
Cold file copy	Dev/test environments; offline servers; a quick snapshot before a risky migration	Production servers that can’t tolerate downtime; any situation where the server must stay up

Scheduling Backups

A cron job running a nightly dump is the most common production setup:

# /etc/cron.d/mysql-backup
0 2 * * * mysql_user /usr/local/bin/mysql-backup.sh

#!/bin/bash
# mysql-backup.sh
DATE=$(date +%Y-%m-%d)
BACKUP_DIR=/backups/mysql

mysqldump \
  --single-transaction \
  --source-data=2 \
  --all-databases \
  -u backup_user -p"$MYSQL_BACKUP_PASSWORD" \
  | gzip > "$BACKUP_DIR/all_databases_$DATE.sql.gz"

# Keep 14 days of backups
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +14 -delete

The backup user needs only SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, EVENT, and RELOAD privileges — not ALL PRIVILEGES.

Verifying Backups

A backup you haven’t tested is not a backup. Verify regularly:

# Test restore to a separate instance
mysql -u root -p --host=test-server < all_databases.sql

# Quick sanity check — verify row counts
mysql -u root -p -e "SELECT COUNT(*) FROM myapp.orders;" test-server
mysql -u root -p -e "SELECT COUNT(*) FROM myapp.orders;" production-server

Automated restore testing at least monthly catches corruption, permission issues, and disk space problems before they matter.

Frequently Asked Questions

Does `--single-transaction` work with all storage engines?

No. --single-transaction uses a consistent read snapshot, which only InnoDB supports. MyISAM tables in the same database are still locked with LOCK TABLES during the dump. If you have mixed engines, --single-transaction alone isn’t sufficient for a fully consistent dump.

How long should I retain backups?

At minimum: daily backups for 7–14 days, weekly backups for 4 weeks, monthly backups for a year. Retention depends on regulatory requirements and how far back you might need to recover. Binary logs should be retained long enough to cover the gap between your oldest retained backup and now.

Is copying the data directory a valid backup?

Only if the server is shut down first (cold copy). A running MySQL server writes data files in a way that’s not safe to copy directly — you’ll get a corrupt backup. For hot backups without stopping the server, use MySQL Shell’s dump utility or Percona XtraBackup.

Troubleshooting

Problem	Solution
`mysqldump` hangs on large tables	Add `--quick` to stream rows one at a time instead of buffering the whole table in memory
Restore fails with `ERROR 1005: Can't create table`	Foreign key constraint violation during import — add `SET FOREIGN_KEY_CHECKS=0;` at the top of the dump, or use `--disable-keys`
Dump file is too large	Use compression: `mysqldump ... \| gzip > backup.sql.gz`; restore with `zcat backup.sql.gz \| mysql ...`
Point-in-time recovery missed changes	Binary log files have a gap — confirm all log files in the range were replayed in sequence
Backup user can’t dump triggers	Grant `TRIGGER` privilege: `GRANT TRIGGER ON myapp.* TO 'backup_user'@'localhost'`

Getting Started

Server Development

Extensions

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

mysqldump — Logical Backups

Point-in-Time Recovery with Binary Logs

MySQL Shell Dump Utilities

Backup Methods Compared

When to use each method

Scheduling Backups

Verifying Backups

Frequently Asked Questions

Does `--single-transaction` work with all storage engines?

How long should I retain backups?

Is copying the data directory a valid backup?

Troubleshooting

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​mysqldump — Logical Backups

​Point-in-Time Recovery with Binary Logs

​MySQL Shell Dump Utilities

​Backup Methods Compared

​When to use each method

​Scheduling Backups

​Verifying Backups

​Frequently Asked Questions

​Does --single-transaction work with all storage engines?

​How long should I retain backups?

​Is copying the data directory a valid backup?

​Troubleshooting

​See also

mysqldump — Logical Backups

Point-in-Time Recovery with Binary Logs

MySQL Shell Dump Utilities

Backup Methods Compared

When to use each method

Scheduling Backups

Verifying Backups

Frequently Asked Questions

Does `--single-transaction` work with all storage engines?

How long should I retain backups?

Is copying the data directory a valid backup?

Troubleshooting

See also