mysqldump: How to Back Up & Restore MySQL Databases

What mysqldump does, and when to use something else

mysqldump performs logical backups: it produces SQL statements that reproduce the original object definitions and table data, and can also write CSV, other delimited text or XML. It is a client program, so it runs wherever the MySQL client tools are installed (with MySQL Server, MySQL Shell installs or the mysql Docker image) and connects to the server over the network like any other client. It is documented in both current LTS manuals, MySQL 8.4 and 9.7.

MySQL is clear about its limits. Its strengths are that you can read or edit the output before restoring and use it to clone databases for development, but it is not intended as a fast or scalable way to back up substantial amounts of data, and restoring a large dump can be very slow because every statement is replayed. For large-scale backup and restore the manual recommends a physical backup, and for logical dumps it suggests the MySQL Shell dump utilities, which add parallel threads, compression and progress output.

MariaDB users: the equivalent client is mariadb-dump. It was previously called mysqldump and the old name still works through a symlink on Linux, but from MariaDB 11.0 that symlink is deprecated and removed from the mariadb Docker image.

mysqldump command syntax and examples

There are three invocation forms: a database with optional table names, --databases followed by one or more database names, or --all-databases. The difference matters on restore. With --databases or --all-databases, the dump includes CREATE DATABASE and USE statements, so it reloads into the same database names. Without them, the dump has no database statements, so you must create the target database first and can load it under a different name.

# one database, consistent InnoDB snapshot, with routines and events
mysqldump -u backup -p --single-transaction --routines --events mydb --result-file=mydb.sql

# several databases, with CREATE DATABASE and USE statements
mysqldump -u backup -p --single-transaction --databases db1 db2 --result-file=db1-db2.sql

# all databases (routines and events must be requested explicitly in 8.4+)
mysqldump -u root -p --single-transaction --routines --events --all-databases --result-file=all-databases.sql

# selected tables only
mysqldump -u backup -p mydb customers orders --result-file=mydb-tables.sql

# structure only, then data only
mysqldump -u backup -p --no-data --routines --events mydb --result-file=mydb-schema.sql
mysqldump -u backup -p --no-create-info mydb --result-file=mydb-data.sql

mysqldump with a remote host, and keeping passwords safe

Because mysqldump is a client, dumping a remote server only needs connection options: --host (-h, default localhost), --port (-P, default 3306) and --user (-u). The account needs read access to everything it dumps, plus the privileges listed above for routines and events.

Do not put the password on the command line: the manual calls that insecure. Use -p with no value and mysqldump prompts for it, or store the credentials once with mysql_config_editor, which writes an obfuscated .mylogin.cnf file (in %APPDATA%\MySQL on Windows, the home directory elsewhere). The manual notes the obfuscation will not stop a determined attacker with access to your files, so protect the account and the file.

mysql_config_editor set --login-path=backup --host=db.example.com --user=backup --password
mysqldump --login-path=backup --port=3306 --single-transaction --routines --events mydb --result-file=mydb.sql

docker exec some-mysql sh -c 'exec mysqldump --all-databases -uroot -p"$MYSQL_ROOT_PASSWORD"' > all-databases.sql

How to restore a mysqldump file

A SQL-format dump is restored by feeding it to the mysql client. If the dump was made with --databases or --all-databases, no target database is needed. Otherwise, create the database and name it on the command line, or use source from inside the client.

On Windows PowerShell, < is reserved for future use, so MySQL documents running the restore through cmd.exe /c "mysql < dump.sql" or using source. To copy a database under a new name on the same server, dump it without --databases (which would add USE db1 and override the new name) and load it into the new database.

To move a database to another server, the manual describes the same two routes: dump with --databases and run mysql < dump.sql on the second server, which recreates the database under its original name, or dump without it, create the database on the target with mysqladmin create, and load the file into it. The second route is the one to use when the target database must have a different name.

# dump made with --databases or --all-databases
mysql -u root -p < all-databases.sql

# single-database dump into an existing or new database
mysqladmin -u root -p create mydb_copy
mysql -u root -p mydb_copy < mydb.sql

CREATE DATABASE IF NOT EXISTS mydb;
USE mydb;
source mydb.sql

Large databases, compression and scheduled backups

Large databases. --quick is already on through --opt, so mysqldump streams rows instead of buffering tables. Beyond that, mysqldump runs single-threaded and has no built-in file compression. MySQL Shell's util.dumpInstance(), util.dumpSchemas() and util.dumpTables() provide parallel dumping with multiple threads and file compression (zstd by default), and load back with util.loadDump(). Compression of a mysqldump file is therefore done outside MySQL, for example by piping the output through gzip; the official mysql Docker image can load .sql.gz files placed in its init folder.

Linux and macOS (cron). A crontab line has five time fields followed by the command. In a crontab, an unescaped % becomes a newline, so escape it as \% if you add a date to the file name. Use a login path so the job needs no password.

Windows (Task Scheduler). Put the command in a .cmd file and register it with schtasks /create; /sc daily with /st sets a daily start time in 24-hour HH:mm format. Run the task as the Windows user whose mysql_config_editor login path you created.

mysqldump --login-path=backup --single-transaction --routines --events mydb | gzip > mydb.sql.gz

30 2 * * * mysqldump --login-path=backup --single-transaction --routines --events mydb --result-file=/var/backups/mysql/mydb.sql

schtasks /create /tn "MySQL backup" /tr C:\scripts\mysql-backup.cmd /sc daily /st 02:30

Common mysqldump errors and how to fix them

These are the problems the MySQL manual documents for dumping and reloading, with the fix it gives.

Frequently asked questions

Sources

• MySQL 8.4 Reference Manual: mysqldump
• MySQL 9.7 Reference Manual: mysqldump
• MySQL 8.4 Reference Manual: Using mysqldump for Backups
• MySQL 8.4 Reference Manual: Dumping Data in SQL Format
• MySQL 8.4 Reference Manual: Reloading SQL-Format Backups
• MySQL 8.4 Reference Manual: Making a Copy of a Database
• MySQL 8.4 Reference Manual: Copy a Database from one Server to Another
• MySQL 8.4 Reference Manual: Dumping Stored Programs
• MySQL 8.4 Reference Manual: Dumping Table Definitions and Content Separately
• MySQL 8.4 Reference Manual: mysql_config_editor
• MySQL Shell 8.4: Instance, Schema and Table Dump Utilities
• MySQL 8.4 Reference Manual: Troubleshooting problems connecting
• MySQL Community Server downloads
• mysql Docker Official Image documentation
• MariaDB documentation: mariadb-dump
• Microsoft Learn: schtasks create
• Ubuntu manpage: crontab(5)

Checked 8 October 2026.

How we research guides: our editorial method. We link only to official downloads and never host installers.