mysqldump backup and restore: flags, large dumps, errors

For a MySQL database up to a few tens of gigabytes, mysqldump is the right backup tool, and one command does it properly:

$ mysqldump -h db.example.net -P 30412 -u backup -p \    --single-transaction --routines --events --triggers \    --set-gtid-purged=OFF --no-tablespaces \    appdb | gzip > appdb-$(date +%F).sql.gz

--single-transaction gives you a consistent snapshot of every InnoDB table without locking the application out. --routines and --events include stored procedures and scheduled events, which are left out by default. --set-gtid-purged=OFF and --no-tablespaces stop the two errors that most often break dumps taken by an ordinary user. Restoring is gunzip -c file.sql.gz | mysql appdb. The rest of this guide explains each flag, how to make large dumps and imports fast, and how to read the errors - because a dump is only a backup once you have restored it.

Everything here is for MySQL 8.4 LTS and its mysqldump. mysqlpump, the parallel tool introduced in 5.7, was removed in 8.4; its job is now done by MySQL Shell's dump utilities, covered below.

What mysqldump produces#

mysqldump is a logical backup: it reads the data through ordinary queries and writes SQL statements that rebuild it - CREATE TABLE, then multi-row INSERTs, then indexes and triggers. The output is a text file you can read, grep, edit and load into any compatible server, including a newer version or a different host.

The trade is speed. A dump of 10 GB of data writes quickly, but loading it back means executing every INSERT and rebuilding every index, which can take several times longer than the dump did. If the database is large enough that a restore takes hours, you need to know that before the day you need it.

The flags that matter#

mysqldump turns on --opt by default, a bundle of sensible behaviour: --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert (many rows per INSERT), --lock-tables, --quick (stream rows instead of buffering the whole table) and --set-charset. You add to that, rather than build from nothing.

Why --single-transaction, and its limits

Without it, --lock-tables locks each database's tables for reading while they are dumped, so the application cannot write for the duration. With --single-transaction, mysqldump starts a transaction with a consistent snapshot and reads everything from that point in time, while the application carries on writing. On InnoDB, which is every table you should have, this is consistent and non-blocking.

It has two limits worth knowing. It only covers transactional tables: a MyISAM table in the same dump is read at whatever moment the dump reaches it. And it does not protect against schema changes - an ALTER TABLE, RENAME TABLE, TRUNCATE or DROP on a table during the dump can make that table's data missing or inconsistent in the output. Do not run migrations while a backup is in progress, and schedule the two apart.

A long dump holds an old snapshot open, so InnoDB has to keep old row versions around for it. On a busy database a multi-hour dump makes the undo log grow and purge fall behind. That is usually tolerable at night; it is a reason not to dump a large, busy database every hour.

Routines, events and triggers

The defaults are inconsistent and catch people out: triggers are included, routines and events are not. An application that relies on a stored procedure restored from a dump without --routines fails with "PROCEDURE appdb.close_month does not exist" weeks later, when the procedure is first called. Add -R -E to every full backup.

Every view, routine, trigger and event carries a DEFINER clause. Restoring as a user who is not the definer, and who lacks SET_ANY_DEFINER (8.4) or SUPER (older), fails. More on that in the errors below.

The privileges a backup user needs#

Back up with a dedicated account rather than root. For one database dumped with the command at the top of this post:

CREATE USER 'backup'@'%' IDENTIFIED BY RANDOM PASSWORD;GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT ON appdb.* TO 'backup'@'%';

SELECT reads the rows. Dumping stored routines with --routines needs more: either the global SELECT privilege or SHOW_ROUTINE, a dynamic privilege added in 8.0.20, unless the backup account is itself the routines' definer - GRANT SHOW_ROUTINE ON *.* TO 'backup'@'%' is the narrow option. SHOW VIEW dumps view definitions, TRIGGER dumps triggers, EVENT dumps events, and LOCK TABLES is needed for non-transactional tables. Dumping without --no-tablespaces needs the global PROCESS privilege, and --source-data needs RELOAD and REPLICATION CLIENT. MySQL users and privileges covers creating accounts like this one.

Restoring a dump#

# Into an existing, empty database$ mysql -h db.example.net -P 30412 -u app -p appdb < appdb-2026-10-08.sql# From a compressed file, with a progress bar (pv is optional)$ pv appdb-2026-10-08.sql.gz | gunzip | mysql -h db.example.net -P 30412 -u app -p appdb# A dump made with --databases names its own database; give mysql no database$ mysql -h db.example.net -P 30412 -u root -p < all-databases.sql

From inside the client, SOURCE /path/to/appdb.sql does the same thing with per-statement output. On Windows PowerShell, < redirection does not exist; use Get-Content dump.sql | mysql ... for small files or, better, mysql ... -e "source C:/backups/appdb.sql", because PowerShell's pipeline can re-encode the text.

A dump without --add-drop-table will fail on the first table that already exists, and one with it (the default) drops and recreates each table it contains - while leaving any table the dump does not mention untouched. Restoring "over" a live database therefore gives you a mix: tables from the dump, plus any tables created since. For a clean restore, drop and recreate the database first, or restore into a new database and switch the application over.

Restoring one table

Because the dump is text, one table is recoverable without loading the rest. Each table section begins with a comment -- Table structure for table followed by its name:

$ zcat appdb.sql.gz | sed -n '/^-- Table structure for table `orders`/,/^-- Table structure for table/p' \    > orders.sql

Inspect the result before loading it, ideally into a scratch database, and copy the rows you need from there. For a database where single-table restores happen often, dump each table to its own file instead.

Large dumps: compression, time and MySQL Shell#

A mysqldump text file compresses extremely well, often to a tenth of its size. gzip is everywhere; zstd is faster at a similar ratio, and zstd -T0 uses every core:

$ mysqldump ... appdb | zstd -T0 -3 > appdb.sql.zst$ zstd -dc appdb.sql.zst | mysql ... appdb

Pipe straight into the compressor rather than writing an uncompressed file first, both for disk space and because the disk write is often the bottleneck. When dumping across the internet, --compression-algorithms=zstd compresses the client-server protocol as well, which helps on a slow link.

mysqldump is single-threaded in both directions. Past roughly 20 to 50 GB, or when a restore needs to fit in a maintenance window, MySQL Shell's dump utilities are the better tool. They dump tables in parallel chunks to a directory of compressed files and load them back in parallel:

$ mysqlsh app@db.example.net:30412 -- util dump-schemas appdb \    --outputUrl=/backups/appdb-2026-10-08 --threads=4$ mysqlsh root@new-host:3306 -- util load-dump /backups/appdb-2026-10-08 --threads=4

util.loadDump requires local_infile=ON on the target server, because it loads data with LOAD DATA LOCAL INFILE. That needs global privileges to change, so on a hosted server check it with SELECT @@local_infile; before you depend on it. Shell's dumps also check compatibility, and can strip definers and fix other issues when moving to a server where you are not a superuser - the ocimds and compatibility options.

Making a restore fast#

The dump file already turns off the cheapest checks at the top: it sets UNIQUE_CHECKS=0 and FOREIGN_KEY_CHECKS=0 for the session and wraps each table's rows in ALTER TABLE ... DISABLE KEYS (which only affects MyISAM). What remains is the cost of committing and logging every row. If you hold the root account, three things help:

-- Before the import, on the target serverSET GLOBAL innodb_flush_log_at_trx_commit = 2;   -- flush the redo log once a secondSET GLOBAL sync_binlog = 0;                      -- let the OS flush the binary log-- After the import, put them backSET GLOBAL innodb_flush_log_at_trx_commit = 1;SET GLOBAL sync_binlog = 1;

Both trade durability for speed - a crash during the import loses the last second or so - which is fine for a restore you can rerun from the start. Raising innodb_buffer_pool_size and innodb_redo_log_capacity for the duration also helps a large load; both are dynamic in 8.x. The setting that disables the redo log entirely, ALTER INSTANCE DISABLE INNODB REDO_LOG, makes loading into a fresh instance much faster and leaves the whole instance unrecoverable if it crashes before you re-enable it. Use it only on a new server you can rebuild, never one holding other data.

Scheduling dumps and keeping copies#

A dump nobody restores is a hypothesis. A dump that sits on the same disk as the database is a hypothesis with one point of failure. A workable routine for a small database:

#!/bin/bashset -euo pipefailSTAMP=$(date +%F-%H%M)DEST=/backups/mysqlmysqldump --login-path=backup --single-transaction -R -E --triggers \  --set-gtid-purged=OFF --no-tablespaces appdb | zstd -q -T0 > "$DEST/appdb-$STAMP.sql.zst"# keep 14 daysfind "$DEST" -name 'appdb-*.sql.zst' -mtime +14 -delete

--login-path reads credentials from ~/.mylogin.cnf, created once with mysql_config_editor, so the password is not in the script or the process list. set -euo pipefail stops the script if mysqldump fails. The pipefail part matters: without it a pipeline's exit status is the compressor's, so a dump that died halfway still produces a small, valid-looking compressed file, and you find out at restore time. Check the size of the newest file against yesterday's as a cheap alarm, and copy the files somewhere else: database dumps to S3 on a schedule shows the upload and retention side.

On RE:NODE, MySQL plans come with one to four backup slots depending on the tier. Backups run on demand or on a schedule, restore with a button, can be downloaded, can be locked against rotation, and are stored off the machine they protect. A logical dump alongside them is still worth having: it is the format you can load into a different server, a newer version, or a local copy for debugging, and it is the one you can restore a single table from. Database backups and restores compares the approaches across engines.

Point-in-time recovery with the binary log#

A nightly dump means that a mistake at 17:00 costs you everything since the night before. The binary log closes that gap: it records every change after the dump, and mysqlbinlog can replay them up to the moment before the mistake. It needs three things - binary logging enabled (the default in MySQL 8), the dump taken with --source-data=2 so it records the log file and position it corresponds to, and the binary log files kept at least as long as the gap between dumps.

# The dump's header names its starting point$ zgrep -m1 'CHANGE REPLICATION SOURCE TO' appdb.sql.gz-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000042', SOURCE_LOG_POS=157;# Restore the dump, then replay changes up to just before the bad statement$ mysqlbinlog --start-position=157 --stop-datetime="2026-10-08 16:59:00" \    binlog.000042 binlog.000043 | mysql -u root -p

In practice this needs the root account, RELOAD and REPLICATION CLIENT for the dump, and access to the binary log files, which a hosted server may or may not give you. If it does not, the realistic alternative is more frequent dumps of the tables that change most. Either way, find out which you have before the incident, not during it.

Errors, and what to do about each#

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s)for this operation' when trying to dump tablespaces

Since 8.0.21, dumping tablespace information needs global PROCESS. Add --no-tablespaces; you almost certainly do not need tablespace statements in an application dump.

ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER,SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation

At restore time, usually the SET @@GLOBAL.GTID_PURGED or SET @@SESSION.SQL_LOG_BIN= 0 lines a dump from a GTID-enabled server contains. Re-dump with --set-gtid-purged=OFF, or delete those lines from the top of the file.

ERROR 1449 (HY000): The user specified as a definer ('root'@'localhost') does not exist

The dump contains views or routines whose definer is missing on the new server, or you are restoring as a user who cannot assign that definer. Strip the definers before loading:

$ zcat appdb.sql.gz | sed -E 's/DEFINER=`[^`]+`@`[^`]+`//g' | mysql ... appdb

mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, ...)':Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

An 8.x mysqldump talking to an older or non-Oracle server that has no COLUMN_STATISTICS table. Add --column-statistics=0.

ERROR 2006 (HY000) at line 4127: MySQL server has gone awayERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes

One INSERT in the dump is larger than the server accepts. Raise max_allowed_packet on the server (64 MB by default in 8.x, up to 1 GB) and pass --max-allowed-packet=512M to mysql, or re-dump with --net-buffer-length lowered so extended inserts are smaller.

ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'

A dump from MySQL 8 loaded into MySQL 5.7 or a server from another family, which lacks the 8.0 default collation. Replace it with utf8mb4_unicode_ci in the file, or better, restore into MySQL 8. See utf8mb4 and collations.

FAQ#

Does mysqldump lock the database?

With --single-transaction and InnoDB tables, no: reads and writes continue throughout, and the dump sees a consistent snapshot. Without it, the default --lock-tables blocks writes to each database while it is dumped. Schema changes during a dump can still break consistency either way.

How long does restoring a mysqldump file take?

Longer than the dump, often three to five times, because every index has to be rebuilt row by row. Measure it once with a real file on a scratch server. If the answer is longer than you can be down, move to MySQL Shell's parallel dump and load, or to physical backups.

Can I restore a MySQL 8.0 dump into MySQL 8.4?

Yes. Logical dumps load into the same or newer versions without trouble in nearly every case. Going backwards - an 8.4 dump into 8.0, or any 8.x dump into 5.7 - can fail on collations and syntax that the older server does not know.

Is a phpMyAdmin export the same as mysqldump?

Close enough for small databases: phpMyAdmin's SQL export produces similar statements. It runs inside a web request, though, so large exports hit PHP time and memory limits, and it is easy to forget routines and events in its options. phpMyAdmin import and export covers its settings.

How do I back up every database on a server?

mysqldump --all-databases includes the mysql system schema, which is not a good way to move accounts between 8.x servers. Dump application databases with --databases db1 db2, and copy users separately with SHOW CREATE USER and SHOW GRANTS. Migrating MySQL to a new host has the full procedure.