How to back up and restore MySQL and MariaDB with mysqldump

mysqldump or mariadb-dump?

MariaDB renamed its clients to mariadb-dump and mariadb. mysqldump still works as a symlink, but MariaDB deprecated that name in 11.0. Unless marked otherwise, every option in this guide works with both tools. Where you see mysql, MariaDB users can type mariadb.

Create a backup user and an option file

A password typed after -p shows up in ps output for other users and in your shell history. The MYSQL_PWD environment variable is worse: MySQL calls it extremely insecure and deprecated it in 8.4. Use a dedicated account and an option file instead.

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'your-password-here';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, PROCESS, EVENT ON *.* TO 'backup'@'localhost';

Those are the privileges the MySQL docs list for mysqldump, plus EVENT for dumping events. On MySQL with GTIDs enabled, --single-transaction also needs RELOAD or FLUSH_TABLES.

[client]
user=backup
password="your-password-here"

sudo chmod 600 /etc/mysql/backup.cnf

Quote the password if it contains #, which otherwise starts a comment. Pass the file with --defaults-extra-file, and put that option first on the command line or it does not work.

Back up one database

mysqldump --defaults-extra-file=/etc/mysql/backup.cnf --single-transaction --routines --events --triggers mydb > mydb.sql

• --single-transaction starts a transaction and dumps from one consistent read. InnoDB tables stay fully usable during the dump. While it runs, avoid ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE and TRUNCATE TABLE; they can make the dump invalid.
• --routines adds stored procedures and functions. It is off by default.
• --events adds scheduled events. It is off by default.
• --triggers adds triggers. It is on by default; listing it makes the intent clear.

A dump of one named database has no CREATE DATABASE or USE statements. That is useful: you choose the target database at restore time, and it can have a different name.

Several databases or the whole server

--databases treats every name as a database and writes CREATE DATABASE and USE statements for each. --all-databases dumps every database, including the mysql system schema with accounts and grants.

mysqldump --defaults-extra-file=/etc/mysql/backup.cnf --single-transaction --routines --events --databases shop blog > two-databases.sql

mysqldump --defaults-extra-file=/etc/mysql/backup.cnf --single-transaction --routines --events --all-databases > all-databases.sql

Compress the dump

SQL text compresses well. Pipe it through gzip:

mysqldump --defaults-extra-file=/etc/mysql/backup.cnf --single-transaction --routines --events mydb | gzip > mydb.sql.gz

MyISAM and other non-transactional tables

--single-transaction only gives a consistent view of transactional tables such as InnoDB. MyISAM tables can still change mid-dump. Find them first:

SELECT table_schema, table_name, engine FROM information_schema.tables
WHERE engine <> 'InnoDB'
  AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

If any matter to you, convert them to InnoDB or replace --single-transaction with --lock-all-tables (-x). That takes a global read lock for the whole dump: consistent across every engine, but writes wait until it finishes.

Restore

Restore as an account that can create databases and objects, such as root; the backup user is read-only. -p with no value prompts for the password. A single-database dump needs an existing database, so create one first:

mysql -u root -p -e "CREATE DATABASE mydb_restored"

gunzip < mydb.sql.gz | mysql -u root -p mydb_restored

Dumps made with --databases or --all-databases already contain CREATE DATABASE and USE, so load them with mysql -u root -p < all-databases.sql and no database name.

Restoring over an existing database replaces each table in the dump, because mysqldump writes a DROP TABLE before every CREATE TABLE by default. Tables that are not in the dump are left alone.

Large databases

• --quick is on by default. It streams rows one at a time instead of loading each table into memory. Do not turn it off.
• Restores are slow. The server replays every INSERT and rebuilds every index. The MySQL docs say plainly that mysqldump is not a fast or scalable way to back up large amounts of data; for those, look at physical backups or disk snapshots.
• Dump from a replica if you have one, so the extra reads land there.
• Skip huge tables you can rebuild with --ignore-table=mydb.logs. Repeat the option for each table.
• If a restore fails on a large row, raise max_allowed_packet on both the client (mysql --max_allowed_packet=1G) and the server. 1G is the maximum.

Verify the dump

A complete dump ends with a -- Dump completed on comment. A dump that stopped early does not:

gunzip -c mydb.sql.gz | tail -n 1

That proves the file is whole, not that it restores. Restore it into a scratch database as shown above, then compare row counts for your key tables against production. -N drops the column header:

mysql --defaults-extra-file=/etc/mysql/backup.cnf -N -e "SELECT COUNT(*) FROM mydb_restored.orders"

Run it every night with cron

The script writes to a temporary name, checks for the completion line, renames the file, and deletes dumps older than seven days.

#!/usr/bin/env bash
set -euo pipefail

DB="mydb"
BACKUP_DIR="/var/backups/mysql"
KEEP_DAYS=7
OUT="$BACKUP_DIR/$DB-$(date +%Y-%m-%d_%H%M).sql.gz"

mkdir -p "$BACKUP_DIR"
trap 'rm -f "$OUT.partial"' EXIT

mysqldump --defaults-extra-file=/etc/mysql/backup.cnf \
  --single-transaction --routines --events --triggers "$DB" \
  | gzip > "$OUT.partial"

gunzip -c "$OUT.partial" | tail -n 1 | grep -q "Dump completed"
mv "$OUT.partial" "$OUT"

find "$BACKUP_DIR" -name "$DB-*.sql.gz" -type f -mtime +"$KEEP_DAYS" -delete

15 2 * * * root /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

Make the script executable with chmod 755. Then copy the dumps off the server: a backup on the same disk goes down with it.

Common errors

Frequently asked questions

Does mysqldump lock tables?

By default it locks the tables of each database while dumping them. With --single-transaction, InnoDB tables are read from a consistent snapshot and stay writable.

Is mariadb-dump the same as mysqldump?

It is MariaDB's version of the same tool with the same core options. mysqldump remains as a deprecated alias on MariaDB.

How do I back up every MySQL database at once?

Use mysqldump --all-databases with --single-transaction --routines --events. Restore it with mysql < file.sql, without naming a database.

How do I dump only the table structure?

Add --no-data. mysqldump writes the CREATE statements without any rows.

How do I dump a single table?

Name it after the database: mysqldump mydb orders > orders.sql. List several tables to dump several.