mysqldump is the command-line utility shipped with MySQL and MariaDB that creates a logical backup of one or more databases by writing out the SQL statements needed to recreate them: CREATE TABLE definitions followed by INSERT statements for the rows. In Backup & recovery for databases, it is the classic tool for portable, human-readable dumps that can be restored on another server, another version or even another storage engine.
Because a dump contains statements rather than data files, restoring it means replaying every statement, which rebuilds tables and indexes from scratch. This makes mysqldump simple and flexible but slow for large databases, where physical backup tools are usually preferred. It remains the standard choice for small to medium databases, migrations, schema exports and selective table backups.
How a logical dump is produced
mysqldump connects to the server as an ordinary client, reads the table definitions from the data dictionary and then selects every row of each table, converting the results into SQL text. Output goes to standard output, so it is usually redirected to a file or piped into a compression tool such as gzip or zstd.
A typical full backup command looks like mysqldump --single-transaction --routines --triggers --events --all-databases | gzip > backup.sql.gz. The resulting file can be inspected with a text viewer, edited if necessary and restored with the `mysql` client.
By default, mysqldump uses extended inserts, which combine many rows into one INSERT statement, and wraps tables in LOCK TABLES statements during restore to speed up loading. These defaults make output compact and restores considerably faster than one statement per row.
Restoring is the reverse operation: the mysql client reads the file and executes each statement in order, for example gunzip < backup.sql.gz | mysql. Because the dump is plain SQL, it can also be loaded into a newer server version, which makes mysqldump a common tool for upgrades and migrations between hosts.
Consistency options and locking behavior
The most important decision is how the dump stays consistent while the server keeps accepting writes. A dump taken without the right option can contain tables from different moments, which breaks referential integrity after restore.
| Option | What it does | When to use it |
|---|---|---|
| –single-transaction | Reads all tables in one consistent snapshot | InnoDB tables, no blocking of writes |
| –lock-tables | Locks tables per database while dumping | Non-transactional engines such as MyISAM |
| –lock-all-tables | Global read lock for the whole dump | Mixed engines needing full consistency |
| –source-data / –master-data | Records the binary log position | Seeding replicas, point-in-time recovery |
| –quick | Streams rows instead of buffering tables in memory | Large tables, enabled by default |
With --single-transaction, InnoDB provides a consistent snapshot through MVCC, so applications keep writing while the dump runs. The snapshot does not protect against DDL changes such as ALTER TABLE during the dump, which can cause errors or an inconsistent result, so schema changes should be paused.
Useful parameters for everyday backups
Beyond consistency, a handful of options decides what ends up in the dump. Missing them is a common reason why restored databases lack stored procedures or scheduled events.
| Option | Effect | Note |
|---|---|---|
| –routines | Includes stored procedures and functions | Not included by default |
| –events | Includes scheduled events | Not included by default |
| –triggers | Includes triggers | Enabled by default |
| –no-data | Dumps only the schema | Useful for schema comparisons |
| –where | Dumps only rows matching a condition | Partial or sample exports |
| –hex-blob | Writes binary columns in hexadecimal | Avoids character set corruption |
Character sets deserve attention: dumps should be created and restored with utf8mb4 as the connection character set, otherwise multibyte text can be silently damaged during the round trip.
Comparison with other backup approaches
mysqldump is one of several ways to protect a MySQL or MariaDB database. Its strengths are portability and selectivity; its weaknesses are speed and the lack of incremental backups.
| Method | Backup type | Restore speed on large data |
|---|---|---|
| mysqldump | Logical, single-threaded SQL text | Slow, rebuilds indexes |
| mysqlpump and MySQL Shell dump utilities | Logical, parallel | Faster, parallel loading |
| Physical hot backup tools | Copies of data files while running | Fast, file copy and recovery |
| Storage snapshots | Volume-level point-in-time image | Very fast, depends on storage |
| pg_dump | Logical dump for PostgreSQL | Comparable role in another system |
For point-in-time recovery, a dump is combined with the binary log: the dump restores the state at a known log position, and replaying the binary log brings the database forward to just before an error, such as an accidental DELETE.
Limitations and common mistakes
The main limitation is restore time. A dump of a few gigabytes usually restores within minutes, while a dump of several hundred gigabytes can take many hours, because every index is rebuilt and every row is inserted again.
Restores can be accelerated by temporarily relaxing durability settings on the target server, for example lowering innodb_flush_log_at_trx_commit and disabling the binary log for the import session, and restoring them afterwards. Splitting a large dump into one file per table also allows several tables to be loaded in parallel, which mysqldump itself does not do.
- Never assume a dump works: test restores regularly on a separate server.
- Avoid passwords on the command line; use an option file with restricted permissions instead.
- Watch the replica impact, because long dumps on a replica increase its replication lag.
- Check exit codes and file size, since a failed dump can leave a truncated file that looks valid.
- Store dumps off the server, encrypted, under a defined backup retention policy.
In everyday language
Imagine writing down a set of instructions that would let someone rebuild your whole bookshelf: which shelves to build, then which book goes where. mysqldump writes exactly such instructions for a database: first the empty tables, then every row of data.
The instructions are plain text, so they can be read, copied and used on another computer. The downside is that following them takes time, because everything is rebuilt piece by piece. For small and medium databases it is simple and reliable; for very large ones, faster methods exist.
Seen in the real world
A web agency hosts about thirty client websites, each with a MySQL database between 200 MB and 3 GB. Every night at 2:00, a scheduled script runs mysqldump --single-transaction --routines --events for each database, compresses the output and copies it to off-site storage, keeping 30 daily and 12 monthly copies.
One morning, a client’s editor accidentally deletes hundreds of product pages. The administrator restores the previous night’s dump into a temporary database on a staging server, extracts only the affected rows and inserts them back into production.
The whole recovery takes under an hour, the rest of the shop keeps running, and because the agency practices test restores every month, the procedure works on the first attempt.