MySQL dump: back up and restore with mysqldump

Quick answer

Back up one database:

mysqldump -u root -p --single-transaction --routines --events shop > shop.sql

Restore it into a database that already exists:

mysql -u root -p shop < shop.sql

That covers most MySQL dump jobs. The flags matter more than they look: without --routines and --events, stored procedures and scheduled events are left out of the file, and the dump still finishes without complaint. Every command in this guide was run against MySQL 8.4 with the mysqldump 8.4 client, and the error text is copied from those runs.

How to dump a MySQL database with mysqldump

mysqldump connects to a running server and writes SQL: CREATE TABLE statements followed by INSERT statements for the rows. Restoring means running that SQL again.

mysqldump -u root -p shop > shop.sql

The top of the file looks like this:

-- MySQL dump 10.13  Distrib 8.4.11, for Linux (aarch64)
--
-- Host: localhost    Database: shop
-- ------------------------------------------------------
-- Server version	8.4.11

Some variations you will use:

# Only some tables
mysqldump -u root -p shop products customers > tables.sql

# Schema only, no rows
mysqldump -u root -p --no-data shop > schema.sql

# Rows matching a condition
mysqldump -u root -p shop orders --where="status = 'priority'" > priority_orders.sql

# Several databases, with CREATE DATABASE and USE statements
mysqldump -u root -p --databases shop crm > two_dbs.sql

# Every database on the server
mysqldump -u root -p --single-transaction --routines --events --all-databases > all.sql

--all-databases includes the mysql system schema, which holds user accounts and privileges. Keep that in mind before restoring an all-databases dump onto a server that already has its own users.

The password warning

Passing the password as -psecret works but prints this on every run:

mysqldump: [Warning] Using a password on the command line interface can be insecure.

It goes to stderr, so it does not end up in your dump file. For scripts, use -p with no value to get a prompt, set the MYSQL_PWD environment variable, or put the credentials in an option file such as ~/.my.cnf.

--single-transaction: a consistent dump without locking

By default mysqldump runs with --lock-tables, which takes a read lock on the tables of each database while it dumps them. Writes to InnoDB tables wait until the lock is released.

On InnoDB tables, --single-transaction is the better choice. It starts a transaction with a consistent snapshot, reads every table from that snapshot, and turns --lock-tables off. Your application keeps writing, and the dump reflects a single point in time.

The limits, from the mysqldump --help text in 8.4:

Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them.

So: use it for InnoDB, and do not run migrations while the dump is in progress.

Routines, triggers, and events

These three are handled differently, which is how people end up with incomplete backups.

To check, create a trigger, a procedure, and an event in the same database, then dump with and without the flags:

mysqldump -u root -p shop > plain.sql
mysqldump -u root -p --single-transaction --routines --events --triggers shop > full.sql
grep -c -E 'CREATE.*PROCEDURE|CREATE.*EVENT|CREATE.*TRIGGER' plain.sql full.sql

plain.sql:1
full.sql:3

The plain dump has the trigger and nothing else. If your application relies on a stored procedure, a restore from plain.sql will look successful and then fail the first time that procedure is called.

Compressing a MySQL dump with gzip

SQL text compresses well. Pipe through gzip rather than writing an uncompressed file first:

mysqldump -u root -p --single-transaction --routines --events shop | gzip > shop.sql.gz

To restore, decompress into mysql:

gunzip < shop.sql.gz | mysql -u root -p shop

Or zcat shop.sql.gz | mysql -u root -p shop, which does the same thing on Linux.

Restoring a dump

A single-database dump made without --databases has no CREATE DATABASE or USE statement. It restores into whichever database you name, and that database has to exist:

mysql -u root -p shop_copy < shop.sql

ERROR 1049 (42000): Unknown database 'shop_copy'

Create it first:

mysql -u root -p -e "CREATE DATABASE shop_copy"
mysql -u root -p shop_copy < shop.sql

This is also how you restore under a different name. A dump made with --databases or --all-databases contains its own CREATE DATABASE and USE lines, so it always restores into the original database name:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `shop` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `shop`;

You can also restore from inside the mysql client with source shop.sql, which prints the result of each statement as it goes.

DB Pro

Work With Your Databases Like A Pro

Query, explore, and manage your databases with a beautiful desktop app and built-in AI.

Download Now

mysqldump with Docker

When MySQL runs in a container, run mysqldump inside it with docker exec and redirect the output on the host:

docker exec my-mysql mysqldump -u root -psecret --single-transaction shop > shop.sql

The password warning goes to stderr, so the file on the host is clean.

Restoring needs -i. Without it, Docker does not forward your shell's stdin into the container, and mysql runs with no input:

# Restores nothing, exits 0
docker exec my-mysql mysql -u root -psecret shop < shop.sql

# Works
docker exec -i my-mysql mysql -u root -psecret shop < shop.sql

Both commands exit with status 0. Only the second one creates any tables, so check the result rather than trusting the exit code.

Common mysqldump errors

Access denied; you need the PROCESS privilege

A user without the global PROCESS privilege gets this:

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

The dump file is still written and the exit code is 0. mysqldump is trying to read tablespace information, which needs the global PROCESS privilege. Unless you use general tablespaces, add --no-tablespaces:

mysqldump -u app -p --no-tablespaces --single-transaction shop > shop.sql

Access denied when using LOCK TABLES (1044)

mysqldump: Got error: 1044: Access denied for user 'bk'@'%' to database 'shop' when using LOCK TABLES

The default --lock-tables behaviour needs the LOCK TABLES privilege. For InnoDB tables, add --single-transaction, which turns table locking off and needs no extra privilege. Otherwise grant LOCK TABLES on the database.

Couldn't execute 'show events' (1044)

mysqldump: Couldn't execute 'show events': Access denied for user 'app'@'%' to database 'shop' (1044)

--events needs the EVENT privilege on the database. Grant it, or drop --events if the database has none.

Stored procedures missing, no error at all

This one prints nothing. A user without the SHOW_ROUTINE privilege (or global SELECT) can run mysqldump --routines, get exit code 0, and produce a file with no procedures in it. In testing, a user with SELECT on the database got zero CREATE PROCEDURE statements. After GRANT SHOW_ROUTINE ON *.* TO 'app'@'%', the procedure appeared. A separate user with global SELECT also got it.

If you back up with a limited user, grep the dump for your routines once to confirm they are there.

Can't connect to local MySQL server through socket (2002)

mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when trying to connect

With no -h, or with -h localhost, the client uses a Unix socket, and there is no server on it. Either the server is not running, or it is somewhere else, such as a Docker container. Pass -h 127.0.0.1 -P 3306 to force TCP, or run mysqldump inside the container as shown above.

Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

Running the MySQL 8.4 mysqldump against a MariaDB server fails with:

mysqldump: Couldn't execute 'SELECT COLUMN_NAME,                       JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')                FROM information_schema.COLUMN_STATISTICS                WHERE SCHEMA_NAME = 'm' AND TABLE_NAME = 't';': Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

MySQL 8's mysqldump has --column-statistics on by default and queries a table that MariaDB does not have. Add --skip-column-statistics, or use MariaDB's own mariadb-dump.

Restore fails on DEFINER: errors 1227 and 1419

Dumps of triggers, procedures, events, and views record who created them:

CREATE DEFINER=`root`@`localhost` PROCEDURE `restock`(IN p_id INT, IN qty INT)

Restoring that file as a non-root user fails. On a server with binary logging enabled (the default in MySQL 8.4), the first error is usually this one, raised on the trigger:

ERROR 1419 (HY000) at line 133: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

With log_bin_trust_function_creators turned on, the next error is about the definer itself:

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

You see these often on managed services, where nobody gets SUPER. The usual fix is to strip the DEFINER clauses so the objects are created as the restoring user:

sed -E 's/DEFINER=`[^`]+`@`[^`]+`//g' shop.sql > shop_nodefiner.sql

That removes the 1227 error. The 1419 error still needs log_bin_trust_function_creators = 1 on the target server. With both in place, the restore completes and the trigger's definer becomes the restoring user.

mysqlpump is gone

mysqlpump was deprecated in MySQL 8.0.34 and removed in MySQL 8.4, along with its lz4_decompress and zlib_decompress helpers. The MySQL 8.4 manual points to mysqldump or MySQL Shell's dump utilities instead. Scripts that call mysqlpump will fail on an 8.4 client.

MySQL Shell dump utilities for large databases

mysqldump writes one SQL stream from one thread, and restoring it replays every INSERT in order. On large databases, both ends get slow. MySQL Shell's util.dumpSchemas(), util.dumpInstance(), and util.loadDump() dump with multiple threads (4 by default), write zstd-compressed chunks, and load in parallel.

mysqlsh --uri root@localhost:3306 --js -e 'util.dumpSchemas(["shop"], "/backups/shop")'

Schemas dumped: 1
Tables dumped: 4
Uncompressed data size: 436 bytes
Compressed data size: 321 bytes
Compression ratio: 1.4
Rows written: 12

The output is a directory of metadata files and .tsv.zst data chunks, not a single .sql file, so you restore it with MySQL Shell rather than mysql. loadDump also needs local_infile enabled on the target, and says so if it is not:

ERROR: The 'local_infile' global system variable must be set to ON in the target server, after the server is verified to be trusted.
Util.loadDump: local_infile disabled in server (MYSQLSH 53025)

For small and medium databases, mysqldump is simpler: one file you can read, grep, edit, and restore with the standard client.

Quick reference

Related: MySQL CREATE DATABASE for creating the restore target, MySQL CREATE USER for setting up a dedicated backup user, and MySQL list databases to confirm what the restore created.