mysqldump exports a database as SQL statements that recreate it — tables, data, and optionally routines and triggers. It’s the simplest reliable backup for small and medium MySQL and MariaDB databases (up to a few tens of gigabytes). Here’s how to use it properly.

Back up one database

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

On Ubuntu with socket authentication, run sudo mysqldump … without -u/-p.

What the options do:

  • --single-transaction — takes a consistent snapshot of InnoDB tables without locking them. The site keeps running during the backup. (Doesn’t protect MyISAM tables; those are locked or inconsistent.)
  • --routines — include stored procedures and functions (not included by default).
  • --triggers — included by default, but explicit is clearer.
  • --events — add this if you use the MySQL event scheduler.

Back up all databases

sudo mysqldump --single-transaction --routines --triggers --events --all-databases > all.sql

This includes the mysql system database (users and grants). Restoring it onto a different MySQL version can cause trouble; for migrations, dump application databases individually and recreate users by hand.

Several specific databases:

sudo mysqldump --single-transaction --databases shop blog > shop-blog.sql

--databases adds CREATE DATABASE and USE statements to the dump, so it restores into databases of the same name.

Compress on the fly

SQL compresses extremely well:

sudo mysqldump --single-transaction --routines shop | gzip > shop-$(date +%F).sql.gz
sudo mysqldump --single-transaction --routines shop | zstd -T0 > shop-$(date +%F).sql.zst

zstd is faster than gzip at similar ratios.

Useful variations

# Structure only
mysqldump --no-data shop > shop-schema.sql

# Data only
mysqldump --no-create-info shop > shop-data.sql

# One table
mysqldump shop orders > orders.sql

# Skip a big log table
mysqldump --ignore-table=shop.sessions shop > shop.sql

# Rows matching a condition
mysqldump shop orders --where="created_at >= '2026-01-01'" > orders-2026.sql

Restore

mysql -u root -p -e "CREATE DATABASE shop CHARACTER SET utf8mb4"
mysql -u root -p shop < shop.sql

Compressed:

gunzip < shop-2026-11-01.sql.gz | sudo mysql shop
zstd -dc shop-2026-11-01.sql.zst | sudo mysql shop

A dump made with --databases or --all-databases restores without naming a database:

sudo mysql < all.sql

Restoring a large dump is slower than creating it. Install pv to watch progress: pv shop.sql | sudo mysql shop.

Automate nightly backups

Create a credentials file so scripts don’t need passwords on the command line (/root/.my.cnf, mode 600):

[client]
user=backup
password=the-backup-user-password

The backup user needs SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS — see MySQL users and privileges.

/usr/local/bin/mysql-backup.sh:

#!/bin/bash
set -euo pipefail
DEST=/var/backups/mysql
mkdir -p "$DEST"
for db in $(mysql -N -e "SHOW DATABASES" | grep -Ev '^(information_schema|performance_schema|mysql|sys)$'); do
  mysqldump --single-transaction --routines --triggers --events "$db" | gzip > "$DEST/$db-$(date +%F).sql.gz"
done
find "$DEST" -name '*.sql.gz' -mtime +14 -delete
chmod 700 /usr/local/bin/mysql-backup.sh
echo '30 2 * * * root /usr/local/bin/mysql-backup.sh' > /etc/cron.d/mysql-backup

set -o pipefail matters: without it, a failed mysqldump piped into gzip still “succeeds”, and you’d keep tiny broken backups without noticing.

Copy backups off the server

A backup on the same disk as the database doesn’t survive a disk failure, a deleted server or ransomware. Sync the backup directory to another location — another VPS, object storage, or a home NAS — with rsync, rclone, restic or Borg. See automated VPS backups with BorgBackup and backing up to cloud storage.

Test your restores

Once a month, restore the latest dump into a scratch database and check it:

sudo mysql -e "CREATE DATABASE restore_test"
gunzip < /var/backups/mysql/shop-$(date +%F).sql.gz | sudo mysql restore_test
sudo mysql -e "SELECT COUNT(*) FROM restore_test.orders"
sudo mysql -e "DROP DATABASE restore_test"

When mysqldump isn’t enough

  • Large databases (50 GB+): dumps and especially restores take hours. Use physical backups — Percona XtraBackup (MySQL) or mariadb-backup (MariaDB) — or the parallel tool mydumper.
  • Point-in-time recovery: combine dumps with binary logs to replay changes up to a specific moment.
  • MariaDB 11+: the commands are also available as mariadb-dump and mariadb; mysqldump remains as an alias.

Troubleshooting

  • Access denied; you need (at least one of) the PROCESS privilege(s) — grant PROCESS to the backup user, or add --no-tablespaces.
  • Got error: 2013: Lost connection to MySQL server during query — increase max_allowed_packet for both client and server (--max-allowed-packet=512M).
  • Restore fails with Unknown collation: 'utf8mb4_0900_ai_ci' — you’re restoring a MySQL 8 dump into MariaDB or MySQL 5.7. Replace the collation in the file with utf8mb4_unicode_ci.

New to MySQL? Start with installing MySQL on Ubuntu. For many backup files, a Storage VPS makes an affordable offsite target.

  • MySQL
  • MariaDB
  • Backups