mysqldump basics: back up and restore a MySQL database, and test that it works

#The goal

You need a copy of a MySQL database before a risky change, or you want to move one to another server. mysqldump writes a database out as SQL statements. It's simple and it works almost everywhere. By the end of this post, you'll have a backup, and you'll have proven that it restores.

A backup you haven't restored is only a hope.

#When mysqldump is the right tool

#Step 1: check what you're backing up

SELECT table_schema AS db,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS size_mb,
       COUNT(*) AS tables_count
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')
GROUP BY table_schema;

-- Non-InnoDB tables can't be dumped consistently with --single-transaction
SELECT table_schema, table_name, engine
FROM information_schema.tables
WHERE table_schema = 'shop' AND engine <> 'InnoDB';

Check that you have enough disk space for the output. For help getting connected, see users, grants and connections.

#Step 2: take the backup

mysqldump -h db01 -u backup_user -p \
  --single-transaction \
  --routines --triggers --events \
  --set-gtid-purged=OFF \
  --databases shop \
  | gzip > shop-$(date +%F).sql.gz

What each option does:

Keep --set-gtid-purged=OFF for copies, but not if you're building a replica. In that case, read about replication first.

Warning

Avoid changing table structure while the dump is running. --single-transaction doesn't protect against ALTER TABLE or TRUNCATE, and the dump can fail or be inconsistent. A long dump also holds an old snapshot open, which can cause purge lag on a busy server. Run it during a quiet period, or from a replica.

#Step 3: check the file

Confirm that the dump finished. A complete file ends with a line beginning -- Dump completed:

ls -lh shop-*.sql.gz
gunzip -t shop-$(date +%F).sql.gz && echo "gzip is OK"
zcat shop-$(date +%F).sql.gz | tail -1

If the last line isn't -- Dump completed on ..., the dump was cut short. Also note that piping into gzip can hide a mysqldump failure. In bash, run set -o pipefail first in scripts, so the failure shows up in the exit code.

#Step 4: restore it to a test database

Never test a restore on top of your original database.

Caution

A dump created with --databases contains USE shop; and restores into shop, wherever you run it. On a server that holds the live database, this replaces its tables, since dumps include DROP TABLE IF EXISTS by default. Restore to a separate test server, or dump without --databases as shown below.

To restore under a different name, make a dump without --databases:

mysqldump -h db01 -u backup_user -p \
  --single-transaction --routines --triggers --events \
  --set-gtid-purged=OFF \
  shop | gzip > shop-nodb.sql.gz

Then restore into a new database:

mysql -h test01 -u admin -p -e "CREATE DATABASE shop_restore CHARACTER SET utf8mb4;"

gunzip -c shop-nodb.sql.gz | mysql -h test01 -u admin -p shop_restore

For a large restore, watch its progress from another session with SHOW PROCESSLIST.

#Step 5: verify the restore

"No errors" isn't enough. Compare the source and the copy:

-- Run on each side
SELECT COUNT(*) FROM shop.orders;
SELECT MAX(order_id), MAX(created_at) FROM shop.orders;

-- Objects that restored
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'shop_restore';
SELECT routine_type, COUNT(*) FROM information_schema.routines
WHERE routine_schema = 'shop_restore' GROUP BY routine_type;

For a stronger check on key tables, use CHECKSUM TABLE orders; on both sides. It only matches if the rows and table format are the same, and it reads the whole table.

#Back up only part of a database

# Specific tables
mysqldump -h db01 -u backup_user -p --single-transaction shop orders customers > two-tables.sql

# Structure only, no data
mysqldump -h db01 -u backup_user -p --no-data --routines shop > schema.sql

# Selected rows
mysqldump -h db01 -u backup_user -p --single-transaction \
  --where="created_at >= '2026-01-01'" shop orders > recent-orders.sql

Handy before a risky update: back up just the table that you're about to change.

#Common errors

Managed services don't allow SUPER, so definer and GTID errors are common on them. Check your provider's documentation.

The privileges that a backup user usually needs:

CREATE USER 'backup_user'@'%' IDENTIFIED BY 'use-a-strong-secret';
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, LOCK TABLES ON shop.* TO 'backup_user'@'%';

Restrict the host to your backup server, instead of %, if you can.

#Don't type passwords in scripts

Store them with the login path tool, not in the command line:

mysql_config_editor set --login-path=backup --host=db01 --user=backup_user --password
mysqldump --login-path=backup --single-transaction shop | gzip > shop.sql.gz

Remember that the backup file holds all your data. Encrypt it, and limit who can read it.

#Prevent it next time

• Schedule backups, copy them off the server and alert if a job fails.
• Test a restore regularly, and record how long it takes.
• Add binlog retention if you need point-in-time recovery.
• Decide on an RPO and RTO first. See the backup strategy matrix.