How to Export All MySQL Databases with mysqldump

mysqldump --all-databases > all.sql writes every database on the server to a single .sql file, schema and data both included. That's the entire one-liner you need for a basic backup. The flags that matter beyond that are --single-transaction (for InnoDB consistency without locking writes), --routines and --events (stored programs are not dumped by default), --hex-blob (for binary-safe BLOB columns), --quick (for large tables that don't fit in RAM), and --source-data (for replication-ready dumps). Below is the full reference with the production-grade command, the restore process, gzip compression, a cron-friendly script, and notes on when to reach for MySQL Shell or Percona XtraBackup instead.

What does mysqldump do and when should I use it?

mysqldump is the official command-line tool for logical backups of MySQL — it generates a .sql file containing CREATE TABLE, INSERT, and (with flags) CREATE PROCEDURE, CREATE EVENT, and CREATE TRIGGER statements that recreate every database when replayed. It's the right tool for moves between servers, point-in-time exports, schema-only dumps, and developer-machine seeding. It's the wrong tool for very large databases (>100GB), production hot backups, or anything that needs to be fast. For those, reach for MySQL Shell's dump utilities (util.dumpInstance(), which does parallel, compressed logical dumps and is the supported successor to the now-removed mysqlpump) or Percona XtraBackup (physical block-level backups, no read lock on InnoDB). MySQL Enterprise Backup and cloud-provider volume snapshots are the other production-grade options. For most installations under ~50GB, mysqldump with --single-transaction is the safest, most portable choice.

A note on mysqlpump: older guides recommend it as the parallel alternative to mysqldump. Do not. Oracle deprecated mysqlpump in MySQL 8.0.34 and removed it entirely in MySQL 8.4. On a current server it is simply not there. MySQL Shell's dump utilities are its replacement.

Jump to:

• Basic command: back up all databases
• Production command: InnoDB-consistent + complete
• Essential mysqldump flags
• Compress the dump as you write it
• Restoring from a mysqldump backup
• Excluding specific databases or tables
• Cron-friendly nightly backup script
• When mysqldump is the wrong tool
• FAQ

Basic command: back up all databases

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

That writes every user database plus the mysql system database (accounts, grants, time zones) to all-databases.sql. It does not include information_schema, performance_schema, or sys: mysqldump skips those by default, which is correct, because they are server-generated and not meant to be restored. You'll be prompted for the password. Same syntax works on Linux, macOS, and Windows (PowerShell or cmd).

To target a remote server, add -h hostname and -P port:

mysqldump -h db.example.com -P 3306 -u admin -p --all-databases > all.sql

Don't put the password on the command line. mysqldump -u root -p'mysecret' leaks the password to anyone running ps or reading shell history. Use the prompt (-p with no value), or a ~/.my.cnf credentials file:

# ~/.my.cnf — chmod 600
[client]
user=backup_user
password=...
host=db.example.com

Then just mysqldump --all-databases > all.sql.

Production command: InnoDB-consistent + complete

The basic command misses several things you almost always want. The production-grade form:

mysqldump \
  --single-transaction \
  --routines \
  --events \
  --triggers \
  --hex-blob \
  --quick \
  --all-databases \
  > all-databases.sql

What each flag does:

• --single-transaction — wraps the dump in a single repeatable-read transaction. Gives you a consistent snapshot of all InnoDB tables without locking writes. Critical for production. Does NOT work for MyISAM (which is uncommon in 2026). One caveat: the snapshot only isolates DML. If another connection runs DDL (ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE) on a table while the dump is in progress, the dump can become inconsistent or fail outright. Schedule dumps away from migrations, or pause schema changes for the dump window.
• --routines — include stored procedures and functions. Not the default. Forgetting this is the most common backup gap.
• --events — include scheduled events. Not the default.
• --triggers — include triggers. Actually the default, but I keep it explicit so the command is self-documenting.
• --hex-blob — dump BLOB and BINARY columns as hex literals. Survives charset and line-ending mangling that pure binary doesn't.
• --quick — stream rows from the server directly to the output instead of buffering an entire table in RAM. Required for very large tables.

This is the command I run on every production server.

Essential mysqldump flags

Run mysqldump --help for the full list.

Compress the dump as you write it

A .sql dump from mysqldump is plain text and compresses well — typically 5-10× with gzip, more with zstd. Pipe directly instead of writing then compressing:

# gzip (universally available)
mysqldump --single-transaction --routines --events --all-databases \
  | gzip > all-databases.sql.gz

# zstd (faster, better ratio — install with apt/brew install zstd)
mysqldump --single-transaction --routines --events --all-databases \
  | zstd -T0 > all-databases.sql.zst

zstd -T0 uses all CPU cores for compression and is typically 5-10× faster than gzip at the same ratio.

Restoring from a mysqldump backup

The dump is a sequence of SQL statements. Replay it with the mysql client:

# Uncompressed
mysql -u root -p < all-databases.sql

# gzip
gunzip -c all-databases.sql.gz | mysql -u root -p

# zstd
zstdcat all-databases.sql.zst | mysql -u root -p

To pull a single database out of an --all-databases dump, the --one-database flag restores only statements for the named database:

mysql -u root -p --one-database myapp < all-databases.sql

Treat this as a convenience, not a precise extractor. --one-database filters statements by tracking the current USE database, so it handles ordinary table data well but is unreliable for stored routines, events, triggers, and any cross-database statements, which can be missed or leak through. If you know in advance that you will need to restore one database at a time, the dependable answer is to take per-database dumps in the first place (mysqldump --databases db1 db2), not to carve them out of a full dump afterward.

For a faster restore, disable a few InnoDB checks during the import (re-enable after):

mysql -u root -p <<EOF
SET unique_checks=0;
SET foreign_key_checks=0;
source all-databases.sql;
SET unique_checks=1;
SET foreign_key_checks=1;
EOF

This is 3-10× faster on data-heavy restores. Note that this does not make the restore atomic: a full dump is full of DDL (CREATE TABLE and friends), and DDL triggers an implicit commit in MySQL, so the import cannot be wrapped in a single transaction and rolled back as one unit. Treat the speed-up purely as relaxed integrity checking during the load. Only do it when you trust the dump (you generated it yourself with consistent flags); skipping foreign_key_checks on a hand-edited dump can leave the database in an inconsistent state.

Excluding specific databases or tables

--all-databases is all-or-nothing. To skip the system databases or a particular table, use a custom list:

# Dump only a specific set of databases
mysqldump -u root -p --databases myapp myapp_logs > selected.sql

# Or build a list dynamically, skipping system databases
DBS=$(mysql -u root -p -e "SHOW DATABASES;" | grep -Ev "^(Database|information_schema|performance_schema|mysql|sys)$")
mysqldump -u root -p --single-transaction --routines --events --databases $DBS > user-databases.sql

To skip a single huge table while including its database:

mysqldump -u root -p --single-transaction --routines --events \
  --ignore-table=myapp.event_log \
  --databases myapp > myapp.sql

Cron-friendly nightly backup script

#!/usr/bin/env bash
# mysql-backup-all.sh, dump every MySQL database to a timestamped, gzipped file,
# then prune anything older than KEEP_DAYS.
# Source: https://techearl.com/export-or-backup-all-mysql-databases
# Site:   https://techearl.com/
set -euo pipefail

BACKUP_DIR="/var/backups/mysql"
TIMESTAMP=$(date +%Y%m%d-%H%M%S)
KEEP_DAYS=14

mkdir -p "$BACKUP_DIR"

# Dump + compress in one pipe
mysqldump \
  --defaults-file=/root/.my.cnf \
  --single-transaction \
  --routines \
  --events \
  --triggers \
  --hex-blob \
  --quick \
  --all-databases \
  | zstd -T0 > "$BACKUP_DIR/mysql-$TIMESTAMP.sql.zst"

# Prune backups older than KEEP_DAYS
find "$BACKUP_DIR" -name "mysql-*.sql.zst" -mtime "+$KEEP_DAYS" -delete

echo "$(date -Iseconds) Backup complete: $BACKUP_DIR/mysql-$TIMESTAMP.sql.zst"

Save as /usr/local/bin/mysql-backup.sh, chmod +x, and schedule:

# /etc/cron.d/mysql-backup
30 2 * * * root /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

That runs at 2:30 AM nightly, writes a timestamped compressed dump, and prunes anything older than 14 days. Add an S3/R2 upload step at the end for off-server backups:

aws s3 cp "$BACKUP_DIR/mysql-$TIMESTAMP.sql.zst" "s3://backups/mysql/mysql-$TIMESTAMP.sql.zst"

When mysqldump is the wrong tool

For databases over ~100GB, or production servers where a full-table-scan dump would dominate I/O for hours, switch to:

• MySQL Shell dump utilities (mysqlsh with util.dumpInstance(), util.dumpSchemas(), util.dumpTables()). The modern logical-backup tooling: parallel, chunked, compressed dumps that are dramatically faster than mysqldump on multi-database servers, restored with util.loadDump(). This is the supported replacement for the old mysqlpump, which Oracle removed in MySQL 8.4.
• Percona XtraBackup (free, open source). Physical block-level backup of the InnoDB data files. Doesn't read every row — it copies the data files and replays the redo log. Production-grade hot backup for large InnoDB installations.
• MySQL Enterprise Backup (commercial). Oracle's equivalent of XtraBackup, requires a MySQL Enterprise license.
• Cloud-provider snapshots. RDS automated snapshots, Cloud SQL exports, Azure Database for MySQL backups — block-level, point-in-time, often the easiest answer.

For schema migration without data, mysqldump --no-data --routines --events --triggers --all-databases produces a structure-only file in seconds even on huge databases.

What to do next

• For day-to-day MySQL command-line reference (CREATE, ALTER, indexes, joins), see the MySQL Cheat Sheet.
• For the full data-type reference (exact byte sizes per column type — relevant when planning a restore target), MySQL Data Types and Sizes.
• For migrating data INTO MySQL from a CSV (the inverse of a dump-and-restore), How to Import a CSV into MySQL.

FAQ