MySQL backup: back up and restore a database with mysqldump

Short answer: back up a MySQL database with mysqldump --single-transaction --routines --events app | gzip > app.sql.gz (mariadb-dump on MariaDB). That takes a consistent copy of InnoDB tables without locking the site. Restore with zcat app.sql.gz | mysql app. Run it nightly from cron, copy the files to another machine, and test a restore regularly.

# back up one database
sudo mysqldump --single-transaction --routines --events app | gzip > app-$(date +%F).sql.gz

# restore it into an empty database
sudo mysql -e "CREATE DATABASE app_restored"
zcat app-2026-10-22.sql.gz | sudo mysql app_restored

Option behaviour on this page comes from the mariadb-dump man page of MariaDB 10.11 (the version Debian 12 ships, read on a Debian 12 server) and the MySQL 8.4 Reference Manual. The backup script further down was run on Debian 12 with stub commands in place of the database clients, to check its error handling; no database was touched.

Cheat sheet

mysqldump, mariadb-dump and mysqlpump

mysqldump writes the database out as SQL: CREATE TABLE statements followed by INSERTs. That file usually restores on another server, a newer version and even the other fork, and you can read it with less. On MariaDB the tool is called mariadb-dump; on Debian 12’s MariaDB 10.11 the man page says mysqldump is now a symlink to it, so both names work. The examples here use mysqldump and mysql; on MariaDB read mariadb-dump and mariadb.

mysqlpump, the parallel variant some older guides use, is gone: MySQL deprecated it in 8.0.34 and removed it in 8.4, according to the 8.4 manual’s “What Is New” page, which points to mysqldump or MySQL Shell’s dump utilities instead. MariaDB never shipped it. For databases of a few gigabytes, plain mysqldump is the tool; for much bigger ones, see physical backups below.

The options that matter

• --single-transaction starts a transaction before dumping, so InnoDB tables are copied as they were at that moment while the application keeps writing. It does nothing for MyISAM or MEMORY tables, which can still change mid-dump, and the manual warns against ALTER, DROP, RENAME or TRUNCATE TABLE on the dumped tables while it runs. It replaces --lock-tables; the two exclude each other.
• --routines and --events add stored procedures, functions and scheduled events. Both are off by default, so a dump without them restores an application with its procedures missing. --triggers is on by default.
• --quick fetches rows one at a time instead of buffering whole tables in memory. It is part of --opt, which is on by default, so you do not need to type it.
• --databases and --all-databases write CREATE DATABASE and USE into the file, so it restores under the original name. A plain mysqldump app does not, which lets you restore into any database name you pick.

Because --single-transaction protects only transactional tables, check once that nothing important still uses MyISAM:

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

User accounts and grants

A per-database dump does not contain the accounts that use it. Write them down as well, or a restore onto a new server ends in Access denied for the application:

• MariaDB: mariadb-dump --system=users > users.sql writes every account, role and grant as CREATE USER and GRANT statements.
• MySQL: save the output of SHOW CREATE USER and SHOW GRANTS for each application account, or use MySQL Shell’s util.dumpInstance(), which includes accounts. An --all-databases dump contains the mysql system schema too, but loading that into a different MySQL version is asking for trouble; per the 8.4 manual, combining it with --add-drop-database even produces a dump that errors on reload.

Keep the password off the command line

On Debian and Ubuntu, the database root account logs in through the Unix socket, so sudo mysqldump or a cron job running as root needs no password at all. That is the simplest setup on a single server. Where you want a separate backup account (another server, or no root login), give it read-only rights:

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

Then put its credentials in an option file instead of typing -p… on the command line, where they show up in ps and in your shell history:

[client]
user=backup
password=a-long-random-password

chmod 600 /etc/mysql/backup.cnf
mysqldump --defaults-extra-file=/etc/mysql/backup.cnf --single-transaction app | gzip > app.sql.gz

--defaults-extra-file must come first on the command line. A file that other users can write to is ignored with a World-writable config file ... is ignored warning, and the dump then fails to log in. The same [client] lines in /root/.my.cnf would be read without any option, but then every mysql call by root, restores included, logs in as the read-only account. In the script below, add the option as the first argument of both client calls if you use a separate account.

How do I restore a MySQL backup?

# a dump of one database: create the target first, then feed the file in
mysql -e "CREATE DATABASE app"
mysql app < app.sql
zcat app.sql.gz | mysql app

# a dump made with --databases or --all-databases: no name needed
zcat all.sql.gz | mysql

# only one database out of an --all-databases dump
zcat all.sql.gz | mysql --one-database app

--one-database filters only on the USE statements in the file, as the man page warns, so the target database has to exist and the result is worth checking. Restoring is single-threaded and usually takes longer than the dump did, because every index is rebuilt; know that number before you need it.

Automate it: script, cron and rotation

1. 1A script with one file per database

   #!/bin/bash
   # one compressed dump per database, kept 14 days
   set -euo pipefail
   umask 077
   
   DIR=/var/backups/mysql
   KEEP_DAYS=14
   STAMP=$(date +%F_%H%M)
   DUMP=mysqldump        # mariadb-dump on MariaDB
   CLIENT=mysql          # mariadb on MariaDB
   
   mkdir -p "$DIR"
   
   # a failed login stops the script here (inside a for list it would go unnoticed)
   DBS=$("$CLIENT" -N -e 'SHOW DATABASES' | { grep -Ev '^(information_schema|performance_schema|mysql|sys)$' || true; })
   
   for db in $DBS; do
     "$DUMP" --single-transaction --routines --events "$db" | gzip > "$DIR/$db-$STAMP.sql.gz.part"
     mv "$DIR/$db-$STAMP.sql.gz.part" "$DIR/$db-$STAMP.sql.gz"
   done
   
   find "$DIR" -name '*.sql.gz' -mtime +"$KEEP_DAYS" -delete

   set -o pipefail is the line that matters most. Without it a pipeline returns the exit code of its last command, and gzip succeeds even when the dump in front of it died: on Debian 12 (bash 5.2.15, gzip 1.12), a failing command piped into gzip returned exit code 0 and left a small, perfectly valid .gz file that gzip -t accepts. With pipefail the script stops, the half-written file keeps its .part name, and cron reports the failure. That is also why the script needs bash: Debian’s /bin/sh (dash 0.5.12) rejects set -o pipefail as an illegal option. The database list is read into a variable first for the same reason: with the query written straight into the for line, a failed login ended the loop silently and the script still exited with 0 (checked with a stub client under the same bash), while the assignment stops it with an error.

2. 2Make it executable and run it once by hand

   sudo chmod 700 /usr/local/sbin/mysql-backup
   sudo /usr/local/sbin/mysql-backup && ls -lh /var/backups/mysql
   zcat /var/backups/mysql/app-*.sql.gz | tail -n 1    # "-- Dump completed on ..."

   A dump that finished writes -- Dump completed on and the date as its last line. A missing line means the file is cut short.

3. 3Schedule it

   MAILTO=you@example.com
   30 3 * * * root /usr/local/sbin/mysql-backup

   A script keeps the % signs of date out of the crontab, where they would have to be escaped. The format and the usual traps are in the crontab guide. If no mail arrives, the machine needs a working mail setup, or have the script report failures some other way.

Fourteen nightly files is a starting point, not a rule. Keeping a weekly copy for a few months and a monthly one for a year costs little disk for most databases, and saves you when a problem is noticed late. How full, incremental and differential schemes trade disk for restore effort is explained in incremental vs differential backup.

Copy it off the server

A backup on the same disk as the database does not survive losing the server, a deleted account or an attacker with root. Copy the files to another machine, ideally by pulling them from the backup side, so the database server holds no credentials that could delete the copies:

rsync -a backup@203.0.113.10:/var/backups/mysql/ /srv/backups/db1/
find /srv/backups/db1 -name '*.sql.gz' -mtime +90 -delete      # longer retention here

The script writes files that only root can read (umask 077). For a pull by a separate account, give that account a group, change the script to umask 027, and run chgrp with that group and chmod 2750 on /var/backups/mysql so new files inherit the group. Leave out --delete: without it, files the server rotates away stay on the backup machine until its own retention removes them. The options, SSH keys and trailing-slash rules are in the rsync guide. Encrypt the files before they leave the server if the backup machine is not fully yours.

Physical backups for big databases

mysqldump gets slow once a database reaches tens of gigabytes, and the restore, which rebuilds every index, gets slower still. Physical tools copy the InnoDB data files while the server runs and apply the changes made during the copy, so the restore is a file copy:

• mariadb-backup (package mariadb-backup, formerly mariabackup) for MariaDB.
• Percona XtraBackup, open source, for MySQL; its major version follows the MySQL series it supports.
• MySQL Enterprise Backup, part of Oracle’s commercial Enterprise Edition.

sudo mariadb-backup --backup --target-dir=/var/backups/full
sudo mariadb-backup --prepare --target-dir=/var/backups/full

# restore: server stopped, data directory empty
sudo systemctl stop mariadb
sudo mariadb-backup --copy-back --target-dir=/var/backups/full
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mariadb

A physical backup is restored onto the same server version it came from, then upgraded as usual if needed. For recovery to a point in time, keep the binary logs (log_bin) as well: restore the last full backup, then replay the binlogs with mysqlbinlog up to the moment before the mistake.

Test your restores

A backup you have never restored is a guess. Once a month, or after any change to the script, restore the newest file into a scratch database (or onto a second, small server) and look at it:

mysql -e "CREATE DATABASE restore_test"
zcat /var/backups/mysql/app-2026-10-22_0330.sql.gz | mysql restore_test
mysql -e "SELECT COUNT(*) FROM restore_test.orders; SELECT COUNT(*) FROM app.orders;"
mysql -e "DROP DATABASE restore_test"

Row counts that match (allowing for writes since 03:30), procedures that exist and an application that starts against the copy are the evidence you want. Note how long the restore took: that is your recovery time.

Common mysqldump errors

If the root login itself fails, see Access denied for user ‘root’@‘localhost’. Installing the server and tuning its memory are covered in installing MariaDB or MySQL on Ubuntu and Debian.

Dumps and snapshots on a VPS

Our VPS panel takes snapshots of the whole server, by hand or on a daily, weekly or monthly schedule, and that is a good net before an upgrade. For a database it is not a replacement for dumps. A snapshot of a running server captures the disk the way a power cut would leave it; InnoDB is built to recover from that, but you get the whole machine back, not one database or one table, and the snapshot stays with the same provider. Keep both: snapshots for fast rollbacks of the server, nightly dumps copied elsewhere for the data.

Frequently asked