> ## Documentation Index > Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # MySQL Backup Strategies > How to back up MySQL databases: mysqldump for logical backups, MySQL Shell's dump utilities, binary log-based point-in-time recovery, and backup best practices for production systems. > [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md) > > All examples on this page 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:** ```bash theme={null} mysqldump -u root -p myapp > myapp_backup.sql ``` **Back up all databases:** ```bash theme={null} mysqldump -u root -p --all-databases > all_databases.sql ``` **Recommended flags for InnoDB:** ```bash theme={null} 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:** ```bash theme={null} 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: ```bash theme={null} mysql -u root -p < myapp_backup.sql ``` 2. Find the binlog starting position from the dump file: ```bash theme={null} head -50 myapp_backup.sql | grep "SOURCE_LOG_FILE" -- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='mysql-bin.000003', SOURCE_LOG_POS=154; ``` 3. Replay binary logs up to the point just before the event you want to undo: ```bash theme={null} 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](https://villagesql.com/docs/en/guides/binary-logging.md) 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. ```bash theme={null} # 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 | Removed in 8.4 — the binary does not ship | | MySQL Shell dump | Small (zstd compressed) | Fast (parallel, compressed) | Fast (parallel load) | Consistent snapshot | MySQL 8.0+ | | 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` was removed in MySQL 8.4 — the binary is not in the build at all, so any script calling it fails outright. 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` | (Removed in MySQL 8.4 — the binary does not ship. 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: ```bash theme={null} # /etc/cron.d/mysql-backup 0 2 * * * mysql_user /usr/local/bin/mysql-backup.sh ``` ```bash theme={null} #!/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`, `RELOAD`, and `PROCESS` privileges — not `ALL PRIVILEGES`. Without `PROCESS`, `mysqldump` still completes but prints an "Access denied ... PROCESS privilege" error to stderr for every run while it tries to dump tablespace metadata. ## Verifying Backups A backup you haven't tested is not a backup. Verify regularly: ```bash theme={null} # 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 1824: Failed to open the referenced table` (or `ERROR 1452` on a row) | `mysqldump` already wraps its output in `SET FOREIGN_KEY_CHECKS=0` by default, so a full dump restored as-is won't hit this. It shows up when restoring a hand-edited or partial dump (only some tables extracted, or the FK-check lines removed) — add `SET FOREIGN_KEY_CHECKS=0;` at the top in that case | | 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'` | Your backup scripts survive a switch to [VillageSQL](https://villagesql.com/install) untouched. It is a drop-in replacement for MySQL, so the mysqldump commands, binary log settings, and restore runbooks in this guide work as-is. ## See also * [MySQL Binary Logging](https://villagesql.com/docs/en/guides/binary-logging.md) — binary logs power point-in-time recovery * [MySQL Replication Basics](https://villagesql.com/docs/en/guides/replication-basics.md) — replication depends on the same binary log infrastructure * [MySQL on Docker](https://villagesql.com/docs/en/guides/mysql-on-docker.md) — backups and volumes for containerized MySQL * [Upgrading from MySQL](https://villagesql.com/docs/en/guides/upgrade.md) — taking the backup an upgrade to VillageSQL starts with * [Upgrading MySQL 8.0 to 8.4 Before End of Life](https://villagesql.com/docs/en/guides/mysql-80-eol-upgrade.md) — the 8.0 to 8.4 upgrade these backups protect This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.