Skip to main content

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
FlagPurpose
--single-transactionConsistent snapshot without locking tables (InnoDB only)
--source-data=2Records binlog position as a comment — required for PITR
--routinesInclude stored procedures and functions
--triggersInclude triggers (default on, but explicit is clear)
--eventsInclude 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:
  1. Restore the full backup:
mysql -u root -p < myapp_backup.sql
  1. 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;
  1. 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

MethodBackup sizeBackup speedRestore speedConsistencyMySQL compatibility
mysqldumpLarge (plain SQL)SlowSlow (re-executes SQL)Consistent snapshot (--single-transaction)Any MySQL version
mysqlpumpLarge (plain SQL)Faster (parallel dump)Slow (re-executes SQL)Per-table consistency onlyMySQL 5.7–8.0 (deprecated in 8.4)
MySQL Shell dumpSmall (zstd compressed)Fast (parallel, compressed)Fast (parallel load)Consistent snapshotMySQL 8.0+
Percona XtraBackupLarge (physical copy)Fast (hot copy, InnoDB-aware)Very fast (file copy)Full InnoDB consistencySame major version
Cold file copyLarge (physical copy)FastVery fast (file copy)Consistent only if server is stoppedSame 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

MethodUse whenAvoid when
mysqldumpPortability is required; migrating between MySQL versions; database is under ~10 GB; you want a human-readable backupDatabase is large and restore time is critical; you need sub-minute backup windows
MySQL Shell dumpDatabase is large (10 GB+); you’re on MySQL 8.0+; you want parallel load with compressionMySQL version is below 8.0; MySQL Shell isn’t installed or permitted
mysqlpump(Don’t use it — deprecated in MySQL 8.4. Use MySQL Shell dump instead.)—
Percona XtraBackupVery large InnoDB databases; you need hot physical backups with minimal performance impact; restore speed is criticalMixed storage engines (MyISAM); you can’t install third-party tools; smaller databases where complexity isn’t justified
Cold file copyDev/test environments; offline servers; a quick snapshot before a risky migrationProduction 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

ProblemSolution
mysqldump hangs on large tablesAdd --quick to stream rows one at a time instead of buffering the whole table in memory
Restore fails with ERROR 1005: Can't create tableForeign 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 largeUse compression: mysqldump ... | gzip > backup.sql.gz; restore with zcat backup.sql.gz | mysql ...
Point-in-time recovery missed changesBinary log files have a gap — confirm all log files in the range were replayed in sequence
Backup user can’t dump triggersGrant TRIGGER privilege: GRANT TRIGGER ON myapp.* TO 'backup_user'@'localhost'