mysqldump: Syntax, Examples & Restore

Our MySQL database support team uses mysqldump as the standard logical backup tool on every managed server. This page documents the command: syntax, the dump examples that cover normal use, the options that determine whether the dump is consistent, the restore procedure, and the errors that stop a dump or a restore.

What mysqldump does

mysqldump is a logical backup program. Per the official reference, it connects to the server as a client and writes out the SQL statements — CREATE TABLE, INSERT, and so on — needed to reproduce the databases. The output is a plain text file. That has three consequences:

• The dump is portable across MySQL versions, platforms, and storage engines, and can be edited before restore.
• Restore is slow relative to a physical (file-level) backup, because the server re-executes every statement and rebuilds every index.
• The dump runs through a normal client connection, so it competes with application traffic for server resources.

For databases up to the tens of gigabytes, mysqldump is the standard tool. Beyond that, the alternatives at the end of this page apply.

Basic syntax and authentication

mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases

The three invocation forms differ in what the output contains: the first form does not write CREATE DATABASE or USE statements; the other two do. That difference matters at restore time, as covered below.

Authentication uses the standard client options. Do not put the password on the command line — it is visible in the process list and shell history. Use -p without a value to be prompted, or store credentials with mysql_config_editor:

# Prompted for password
mysqldump -u backupuser -p mydb > mydb.sql

# Stored credentials (login path)
mysql_config_editor set --login-path=backup --user=backupuser --password
mysqldump --login-path=backup mydb > mydb.sql

The account needs at least SELECT on the dumped tables, SHOW VIEW for views, TRIGGER for triggers, PROCESS if --no-tablespaces is not used, and LOCK TABLES unless the dump runs with --single-transaction.

mysqldump examples

Two of these need explanation. First, --triggers is on by default but --routines (stored procedures and functions) and --events are off by default. A dump taken without --routines --events silently omits every stored procedure, function, and scheduled event in the database, and the omission is usually discovered at restore time. Add both to every backup job. Second, piping through gzip is the standard way to compress; SQL text compresses at roughly 5:1 to 10:1. Restore reads the pipe in reverse: gunzip < mydb.sql.gz | mysql mydb.

Consistency options

A dump taken while the application is writing is inconsistent unless one of these options makes it consistent. Which one applies depends on the storage engine.

--single-transaction — InnoDB. Issues START TRANSACTION with a consistent snapshot before dumping, so every table is read as of the same instant, without locking anything. This is the correct option for InnoDB, which is the default engine, and it is why --single-transaction appears in nearly every production backup command. Two caveats from the reference: the snapshot only guarantees consistency for transactional tables, and a concurrent ALTER TABLE or other DDL during the dump can break it, because DDL is not transactional.

--lock-tables — MyISAM. Locks all tables in each database with READ LOCAL before dumping that database. This is the fallback for non-transactional engines, and it blocks writes for the duration. Note the scope: tables are locked per database, so a dump spanning multiple databases is only consistent within each database, not across them. --lock-all-tables takes a global read lock for cross-database consistency, at the cost of blocking all writes server-wide. --single-transaction and --lock-tables are mutually exclusive; specifying --single-transaction turns table locking off.

--source-data — replication. Writes the source's binary log file name and position into the dump as a CHANGE REPLICATION SOURCE TO statement, which is what a new replica needs to start replicating from the exact point the dump represents. --source-data=1 writes the statement active; --source-data=2 writes it commented out for reference. The old name --master-data still works in 8.4 but is a deprecated alias; use --source-data in new scripts. Combined with --single-transaction, it produces a consistent InnoDB dump plus the matching binlog coordinates in one pass. On servers using GTIDs, --set-gtid-purged controls whether GTID state is written to the dump.

The standard production backup command for an InnoDB server:

mysqldump --single-transaction --routines --triggers --events   --source-data=2 --all-databases | gzip > /backup/full-$(date +%F).sql.gz

Restore procedure

A dump file is restored by feeding it to the mysql client. There is no separate restore program.

# Restore a single-database dump into an existing database
mysql -u root -p mydb < mydb.sql

# Restore an --all-databases or --databases dump
# (the file contains CREATE DATABASE / USE, so no db argument)
mysql -u root -p < all.sql

# Restore ONE database out of an --all-databases dump
mysql -u root -p --one-database mydb < all.sql

Points that matter in practice:

• A dump made as mysqldump mydb (first syntax form) contains no CREATE DATABASE statement. Create the target database first (CREATE DATABASE mydb;) and name it on the restore command line. A dump made with --databases or --all-databases creates its databases itself.
• --one-database filters an all-databases dump down to one database at restore time. It is a blunt instrument — it works by ignoring statements outside the named database — and the documentation notes it misbehaves if the dump contains USE statements for other databases interleaved unusually. Extracting the wanted database with sed or restoring to a scratch server are the alternatives.
• A restore replays every INSERT and rebuilds every index, so it takes longer than the dump did. For large restores, disabling binary logging on the session (SET sql_log_bin=0; where appropriate) and increasing max_allowed_packet reduce the time and the failure rate.

A backup is verified by restoring it. Restore each backup to a scratch server or a scratch database on a schedule and run row-count or checksum comparisons against production. A dump file that has never been restored is untested, and dump files fail restore for reasons — truncation, packet limits, definer errors — that only appear when the restore runs.

Large databases

mysqldump is single-threaded: one connection, one table at a time, plain SQL text out. On databases in the hundreds of gigabytes, the dump takes hours and the restore takes several times longer than the dump. The current alternatives:

• MySQL Shell dump utilities — util.dumpInstance(), util.dumpSchemas(), util.dumpTables(), restored with util.loadDump(). These dump in parallel threads (default 4), compress with zstd by default, chunk large tables into multiple files, and load in parallel. This is the tool the mysqldump manual page itself points to for large datasets, and it is the modern replacement for most bulk dump work.
• mysqlpump — the earlier parallel-dump attempt. It is deprecated as of MySQL 8.0.34 and is not the answer for new work; use MySQL Shell instead.
• Physical backups — Percona XtraBackup (free, InnoDB hot backup) and MySQL Enterprise Backup (commercial) copy the data files directly. Backup and restore speed scale with disk throughput rather than SQL replay speed, which is why physical backup is the standard above a few hundred gigabytes.

Scheduling with cron

The standard automation is a nightly cron job. Syntax and pitfalls of the scheduler itself are covered in crontab; the mysqldump-specific points are: use a login path instead of a password in the crontab line, write to a dated filename, check the exit code, and delete old dumps explicitly.

# /etc/cron.d/mysql-backup — 01:30 nightly
30 1 * * * backup mysqldump --login-path=backup --single-transaction   --routines --triggers --events --all-databases 2>>/var/log/mysqldump.err   | gzip > /backup/full-$(date +%F).sql.gz && find /backup -name 'full-*.sql.gz' -mtime +14 -delete

The && matters: the retention cleanup only runs if the dump pipeline succeeded, so a failing backup job stops deleting old backups instead of silently rotating them away. Percent signs are escaped because cron treats a bare % as a newline.

Common errors

Version differences also matter when moving dumps between servers — a dump from a newer major version does not always restore cleanly to an older one. Supported versions and upgrade paths are listed in MySQL versions.

When to hand it over

Backup jobs fail quietly: the cron line stops running, the disk fills, the dump omits routines, or the file has never been test-restored. Our MySQL support team sets up and monitors dump schedules, verifies restores on a schedule, and handles large-database backup design — Shell utilities, XtraBackup, and replication-based approaches — as standard work. A DBA is available on live chat 24/7.