How to Use mysqldump to Backup All Databases (September 2026)

If you need to know how to use mysqldump to backup all databases, the fastest answer is one command. From any Linux, macOS, or Windows shell with the MySQL client installed, run:

mysqldump -u root -p --single-transaction --quick --routines --triggers --events --all-databases > all_databases.sql

That single line writes every database on your server into one SQL file you can restore on the same host, a new server, or a staging environment. I have shipped this exact pattern on production MySQL 5.7, 8.0, and MariaDB 10.x clusters, and it remains the workhorse of logical MySQL backups.

This guide goes deeper than the one-liner. I will walk you through the prerequisites, the production-ready flag combinations, how to handle the items --all-databases silently skips (MySQL users and grants), how to exclude system schemas, how to verify the dump is restorable, how to restore a single database from a full dump, and how to automate everything with cron. I will also flag the failure modes that bite people in real life: lost connections, max_allowed_packet errors, disk full mid-dump, and the surprising behaviour of --all-databases on managed databases like Amazon RDS and Linode.

What Is mysqldump and Why Use It for All-Databases Backups

mysqldump is a built-in MySQL command-line client that produces a logical backup — a plain-text SQL file containing the CREATE DATABASE, CREATE TABLE, and INSERT statements needed to recreate your databases on any MySQL-compatible server.

A logical backup is different from a physical backup. A physical backup copies the raw InnoDB data files (/var/lib/mysql on Linux); it is fast but tightly coupled to the server version, storage engine layout, and sometimes the host filesystem. A logical backup is portable: a dump from MySQL 5.7 will restore on MySQL 8.0, and a dump from MySQL will restore on MariaDB (and vice versa, in most cases).

For small to medium databases (the rule of thumb I use is anything under about 100 GB), mysqldump is the right tool because:

• It needs no extra software — it ships with every MySQL and MariaDB install.
• The output is a single file you can copy, gzip, ship to S3, or check into artefact storage.
• Restores are simple: pipe the file back into mysql.
• You can restore a single database from the full dump with --one-database — very handy for “I just need that one schema back.”

For very large databases (hundreds of GB to TB scale) mysqldump becomes slow and memory-hungry, and a physical backup tool like Percona XtraBackup or mariabackup is a better fit. I cover that choice later in the article so you can self-select by database size.

Prerequisites: mysqldump, Privileges, and Storage

Before you run any backup, work through this checklist. Most failed backups come from skipping one of these steps.

1. Confirm mysqldump is installed and on your PATH.

mysqldump --version

You should see output like mysqldump Ver 8.0.36 for MySQL 8 or a MariaDB version string. On Debian/Ubuntu, install with sudo apt install mysql-client. On RHEL/Rocky/Alma, use sudo dnf install mysql. On Windows, add the MySQL bin directory to your PATH.

2. Confirm you have the required privileges.

For a full --all-databases backup with routines, triggers, and events, the user needs at minimum:

• SELECT
• SHOW VIEW
• LOCK TABLES
• RELOAD (required when using --single-transaction with --master-data)
• PROCESS
• REPLICATION CLIENT (only if you plan to use --master-data)

To grant these to a dedicated backup user called backup_admin:

CREATE USER 'backup_admin'@'localhost' IDENTIFIED BY 'strong-password';
GRANT SELECT, SHOW VIEW, LOCK TABLES, RELOAD, PROCESS, REPLICATION CLIENT ON *.*
  TO 'backup_admin'@'localhost';
FLUSH PRIVILEGES;

Using a dedicated user (not root) is a good security habit and makes automation easier because the credentials are narrowly scoped.

3. Check your free disk space.

An uncompressed SQL dump is usually 1.5 to 3 times the size of the raw data, because every row becomes a separate INSERT statement. If your databases are 20 GB on disk, expect a 30–60 GB dump file. Run df -h first and make sure you have headroom.

4. Store credentials so you are not prompted for a password.

The cleanest option is mysql_config_editor:

mysql_config_editor set --login-path=backup --host=localhost --user=backup_admin --password

After that, any client can use --login-path=backup instead of -u and -p. If you prefer a plain file, create ~/.my.cnf with mode 600:

[client]
user=backup_admin
password=strong-password

chmod 600 ~/.my.cnf

Backing Up All Databases With mysqldump — Basic Command

The simplest possible command that captures every database on the server is:

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

You will be prompted for the password, the dump streams to standard output, and the shell redirect writes it to all_databases.sql. The file starts with comments and CREATE DATABASE statements, then CREATE TABLE and INSERT statements for every table in every database, and ends with a line that looks like -- Dump completed on 2026-09-11 14:32:10.

The difference between --all-databases and --databases trips up a lot of people:

• --all-databases dumps every database on the server and adds CREATE DATABASE IF NOT EXISTS statements so the restore can recreate each schema.
• --databases db1 db2 db3 dumps only the named databases and adds USE db1; and CREATE DATABASE IF NOT EXISTS db1; statements for each.

For a full-server backup you almost always want --all-databases.

For filename hygiene, I recommend a timestamped filename so multiple backups do not overwrite each other:

mysqldump -u root -p --all-databases > all_databases_$(date +%Y%m%d_%H%M%S).sql

Creating a Consistent InnoDB Backup

For production InnoDB workloads you want a consistent snapshot without locking the whole server for the duration of the dump. Three flags work together to make that happen:

• --single-transaction opens a single transaction and uses InnoDB’s MVCC to read a consistent snapshot. Reads do not block writes, and writes do not block reads.
• --quick streams rows one at a time instead of buffering the whole result set in memory. Critical for large tables.
• --lock-tables=false tells mysqldump not to take table locks. Safe only when all your tables are InnoDB.

The combined production-ready command is:

mysqldump -u root -p 
  --single-transaction 
  --quick 
  --lock-tables=false 
  --all-databases > all_databases.sql

If you still have MyISAM tables, drop the --lock-tables=false flag so mysqldump can lock each table during read. MyISAM does not support MVCC, so consistency requires locking. There is no clean way to get a fully consistent MyISAM snapshot without a brief global lock.

For older MySQL versions (5.5 and earlier) or for very busy servers, also consider adding --max-allowed-packet=512M to defend against max_allowed_packet errors on large BLOB columns.

Including Stored Procedures, Triggers, and Events

Stored procedures, functions, triggers, and scheduled events are easy to forget, and a backup without them is incomplete the moment someone runs SHOW PROCEDURE STATUS on the restored server.

• --routines dumps stored procedures and functions.
• --events dumps scheduled events (the jobs created with CREATE EVENT).
• Triggers are included by default; you can disable them with --skip-triggers if needed.

For a complete backup, include all three:

mysqldump -u root -p 
  --single-transaction --quick --lock-tables=false 
  --routines --events 
  --all-databases > all_databases.sql

If you forget --routines, your stored procedures come back as empty schemas. If you forget --events, your nightly aggregations silently stop running after a restore.

Backing Up MySQL Users and Grants Separately

This is the gap that catches everyone once. --all-databases does NOT cleanly include MySQL user accounts and grants on modern MySQL versions. The mysql system schema is excluded from --all-databases in MySQL 8.0 by default, and on managed databases (Amazon RDS, Aurora, Linode Managed Databases, DigitalOcean Managed MySQL) the mysql schema is always off-limits.

You need a separate dump for users. On a self-hosted MySQL server, the most portable method is:

mysqldump -u root -p --skip-lock-tables --tables mysql.user mysql.db 
  --where="1=1" > mysql_users.sql

On MySQL 8 and MariaDB, you can also dump mysql.global_priv (the table that actually stores credentials on those versions):

mysqldump -u root -p --skip-lock-tables mysql global_priv > mysql_global_priv.sql

For managed databases, use the provider’s tooling: RDS stored procedures such as mysql.rds_show_configuration or mysqldump run from a bastion. For Percona Toolkit users, pt-show-grants is the cleanest way to dump grants as executable SQL:

pt-show-grants --host=localhost --user=root --ask-pass

Back up user grants on the same schedule as your data dump. Restoring them after a server crash is the difference between “we are back online” and “we are online but nobody can log in.”

Excluding System Databases From the Dump

The information_schema and performance_schema databases are virtual — their contents are derived from server state at query time and cannot meaningfully be backed up or restored. Dumping them produces noise at best and harmless errors at worst. The sys schema is a read-only view layer; you can usually skip it. The mysql schema holds users and grants and is excluded by default in modern MySQL.

To back up only the user databases, use a bash loop that lists databases and skips the system ones:

#!/bin/bash
USER="backup_admin"
MYSQL="mysql -u $USER --login-path=backup -BNe"
DUMP="mysqldump -u $USER --login-path=backup --single-transaction --quick --routines --events --triggers"

OUTPUT="/var/backups/mysql/all_databases_$(date +%Y%m%d_%H%M%S).sql"

# Get list of non-system databases
DATABASES=$($MYSQL "SHOW DATABASES;" | grep -Ev '^(information_schema|performance_schema|sys|mysql)$')

# Build the mysqldump command with --databases and the filtered list
$DUMP --databases $DATABASES > "$OUTPUT"

echo "Dump written to $OUTPUT"
tail -n 5 "$OUTPUT"

The tail -n 5 at the end is the verification step. A successful dump always ends with a line similar to:

-- Dump completed on 2026-09-11 14:32:10

If you do not see that line, the dump is incomplete. Do not trust the file size — a truncated dump can still be many gigabytes.

Compressing the Backup With gzip

An SQL dump compresses extremely well because INSERT statements contain a lot of repetition. Expect a 5–10x reduction with gzip. Pipe mysqldump straight into gzip instead of writing to a file first:

mysqldump -u root -p --all-databases | gzip > all_databases.sql.gz

Restore with the inverse:

gunzip < all_databases.sql.gz | mysql -u root -p

On multi-core servers, use pigz (parallel gzip) for faster compression and decompression:

mysqldump -u root -p --all-databases | pigz > all_databases.sql.gz
unpigz -c all_databases.sql.gz | mysql -u root -p

Compressing on the fly means your backup script never writes the uncompressed SQL to disk, which doubles your effective storage headroom.

Capturing Binary Log Coordinates With –master-data

If you are backing up a primary server that needs to seed a replica, you also need the binary log coordinates at the moment the snapshot was taken. The --master-data flag writes a CHANGE MASTER TO statement into the dump so the replica knows where to start replicating from.

mysqldump -u root -p 
  --single-transaction 
  --master-data=2 
  --all-databases > all_databases.sql

With =2, the coordinates are written as a comment (safe for restores). With =1, they are written as an executable statement (intended for direct pipe-to-replica use). For GTID-based replication, add --set-gtid-purged=COMMENTED on the source and =OFF on the replica to avoid GTID conflicts.

If you do not need to seed a replica, skip this flag. It is only relevant when the backup is feeding into a replication topology.

Verifying the Backup Is Restorable

An untested backup is not a backup — it is a hope. Three checks, in order of effort:

1. Quick sanity check: tail the file.

tail -n 10 all_databases.sql

You should see -- Dump completed on YYYY-MM-DD HH:MM:SS as the last meaningful line. No error messages, no truncation notices.

2. Grep for table counts.

grep -c "^CREATE TABLE" all_databases.sql

The number should match SELECT COUNT(*) FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys'); on the source server.

3. Test-restore into a throwaway server.

This is the only check that proves the dump is actually usable. Spin up a fresh MySQL instance (Docker works: docker run --rm -d --name mysql-test -e MYSQL_ROOT_PASSWORD=test mysql:8.0), pipe the dump in, then run a few representative queries. If anything fails, you find out before the disaster, not during it.

Restoring From an All-Databases Dump File

Restoring is the inverse of backing up, with a couple of caveats.

Full restore from uncompressed dump:

mysql -u root -p < all_databases.sql

Full restore from gzipped dump:

gunzip < all_databases.sql.gz | mysql -u root -p

Restore a single database from an –all-databases dump:

mysql -u root -p --one-database target_db_name < all_databases.sql

The --one-database flag tells the mysql client to apply statements only when the default database matches. This is the technique that forum threads on dba.stackexchange and Reddit r/mysql keep rediscovering.

Warning. A full --all-databases restore will overwrite existing data on the target server. If you are restoring to a server that already has databases with the same names, expect them to be replaced. Make sure you are pointing at the right host before you run the command.

Automating All-Databases Backups With cron

Once the manual command works, automate it. A production pattern looks like this:

Step 1: Create a backup directory.

sudo mkdir -p /var/backups/mysql
sudo chown backup_admin:backup_admin /var/backups/mysql
chmod 750 /var.backups/mysql

Step 2: Write the backup script.

Save this as /usr/local/bin/mysql-backup.sh:

#!/bin/bash
set -euo pipefail

BACKUP_DIR="/var/backups/mysql"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
FILENAME="all_databases_${TIMESTAMP}.sql.gz"
LOGFILE="/var/log/mysql-backup.log"

mysqldump 
  --login-path=backup 
  --single-transaction 
  --quick 
  --lock-tables=false 
  --routines --events --triggers 
  --master-data=2 
  --set-gtid-purged=COMMENTED 
  --all-databases 
  | gzip > "${BACKUP_DIR}/${FILENAME}"

echo "Backup finished: ${FILENAME}" >> "$LOGFILE"

# Retain 7 daily backups
find "$BACKUP_DIR" -name "all_databases_*.sql.gz" -mtime +7 -delete

# Ship off-site (example with aws s3)
# aws s3 cp "${BACKUP_DIR}/${FILENAME}" s3://my-backups/mysql/

Make it executable: chmod 700 /usr/local/bin/mysql-backup.sh.

Step 3: Schedule it in cron.

crontab -e -u backup_admin

Add a nightly run at 02:30:

30 2 * * * /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

Step 4: Verify the first scheduled run.

After the first night, check the log file and confirm a backup file landed in the backup directory. cron does not email failures by default on most systems; the log file is your only signal.

The retention policy (7 daily, with older files deleted) is the minimum. Adjust to your recovery objectives and disk budget.

When mysqldump Is Not Enough: Percona XtraBackup and mysqlbackup

mysqldump streams every row as SQL. For a 500 GB database, that means hours of CPU on the source server, hours of network bandwidth, and a multi-hundred-GB output file. At that scale, you want a physical hot backup:

• Percona XtraBackup copies the InnoDB data files while the server is running and applies the redo log at restore time. Near-zero lock on the source, fast restore, supports encrypted and compressed backups.
• mariabackup is MariaDB’s fork of XtraBackup, with full MariaDB compatibility.
• MySQL Enterprise Backup (mysqlbackup) is Oracle’s commercial option, included with MySQL Enterprise subscriptions.
• LVM/ZFS/Btrfs snapshots — take a filesystem snapshot, copy the data files, drop the snapshot. Fast for very large InnoDB clusters on local storage.

Quick self-selection: if your total database size is under 50 GB, mysqldump is fine. From 50 GB to 500 GB, consider XtraBackup. Beyond 500 GB, you almost certainly need a physical backup strategy.

Troubleshooting Common mysqldump Errors

mysqldump: Error 2013: Lost connection to MySQL server during query.

Usually caused by net_read_timeout or max_allowed_packet being too small for a large BLOB or long-running table. Increase both on the server side and retry:

SET GLOBAL max_allowed_packet = 1024 * 1024 * 512;  -- 512 MB
SET GLOBAL net_read_timeout = 600;

mysqldump: Error 2020: Got packet bigger than ‘max_allowed_packet’ bytes.

Same fix: bump max_allowed_packet on the server. The packet is read on the server side, so the client-side flag --max-allowed-packet alone is not enough.

mysqldump: Error 1045: Access denied for user.

The user lacks the privileges listed in the Prerequisites section. Grant them and retry.

mysqldump: Got error 1146: Table ‘foo.bar’ doesn’t exist.

Usually a corrupted or renamed table mid-dump. Add --force to continue past errors (the rest of the dump will still be written). Investigate the table separately.

Dump file grows huge and the disk fills up.

Run df -h before each backup. Compress on the fly with gzip. Ship to off-box storage (S3, NFS, rsync to a backup host) before the next backup starts. Never write a full uncompressed SQL dump to the same disk as the live databases.

Restore fails: “ERROR 1049 (42000): Unknown database ‘foo'”.

The dump file references a database that does not exist on the target server, often because it was renamed. Either restore the missing database manually first, or pass --one-database with the database you actually want to load.

Managed database restored fine but users are gone.

This is the gap from earlier. --all-databases on RDS, Aurora, Linode Managed DBs, and DigitalOcean Managed MySQL skips the mysql schema. Use the provider’s user-export tooling or pt-show-grants.

Windows and PowerShell Notes

The shell redirection operator works on PowerShell, but the encoding it produces sometimes trips up MySQL. Use the --result-file flag instead, which writes a clean UTF-8 file:

mysqldump -u root -p --all-databases --result-file=all_databases.sql

Two more Windows-specific gotchas worth flagging:

• Add the MySQL bin directory to your system PATH, or call mysqldump with its full path. PowerShell does not pick it up by default.
• If you see weird characters in the dump, the console encoding is the culprit. Run chcp 65001 at the start of the session to switch to UTF-8.

Frequently Asked Questions

Final Thoughts on Backing Up All Databases With mysqldump

That is the full picture of how to use mysqldump to backup all databases, from the single command on the command line to a fully automated, verified, retention-managed nightly job. The three commands worth memorising are mysqldump --all-databases for the dump, mysql < dump.sql for the restore, and tail to confirm a successful run. Start with the prerequisite checklist, run the production flag combination on a quiet server first, and automate only once the manual run is verified. The MySQL reference manual at dev.mysql.com/doc/en/mysqldump.html is the canonical source for flag details if you need to dig deeper.