Logical vs physical backups
| Type | Tools | Pros | Cons |
|---|---|---|---|
| Logical – SQL statements | mysqldump, mariadb-dump, MySQL Shell | simple, portable between versions, readable | slow to restore for large databases |
| Physical – copy of the data files | Percona XtraBackup (MySQL), mariadb-backup (MariaDB) | fast backup and restore for large data | same major version needed to restore |
For databases up to a few GB, mysqldump is all you need.
Never just copy /var/lib/mysql while the server runs – the copy will be inconsistent.
mysqldump
# one database
mysqldump --single-transaction --routines --triggers --events shop > shop.sql
# one database, compressed, with date
mysqldump --single-transaction --routines --triggers --events shop | gzip > shop_$(date +%F).sql.gz
# several databases / everything
mysqldump --single-transaction --databases shop blog > shop_blog.sql
mysqldump --single-transaction --all-databases --routines --events > all.sql
# only the structure / only the data
mysqldump --no-data shop > shop_schema.sql
mysqldump --no-create-info shop > shop_data.sql
# single tables
mysqldump --single-transaction shop customers orders > shop_tables.sql| Option | Why |
|---|---|
--single-transaction | consistent snapshot of InnoDB tables without locking them – always use it |
--routines, --triggers, --events | include stored procedures, triggers, events (triggers are included by default) |
--databases | adds CREATE DATABASE and USE to the dump |
--all-databases | every database including users in mysql |
--no-tablespaces | avoids the "PROCESS privilege" error for non-root users on MySQL 8 |
On MariaDB the tool is also called mariadb-dump.
A backup user
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'long-password';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS, RELOAD ON *.* TO 'backup'@'localhost';Store its credentials in /root/.my.cnf (chmod 600) so no password appears in scripts.
Automating with cron
/usr/local/bin/mysql-backup.sh:
#!/bin/bash
set -euo pipefail
DIR=/var/backups/mysql
KEEP_DAYS=14
mkdir -p "$DIR"
for DB in $(mysql -N -B -e "SHOW DATABASES" | grep -Ev '^(information_schema|performance_schema|sys)$'); do
mysqldump --single-transaction --routines --triggers --events "$DB" \
| gzip > "$DIR/${DB}_$(date +%F_%H%M).sql.gz"
done
find "$DIR" -name '*.sql.gz' -mtime +$KEEP_DAYS -deletesudo chmod 700 /usr/local/bin/mysql-backup.sh
sudo crontab -e
# every night at 02:30
30 2 * * * /usr/local/bin/mysql-backup.shA backup on the same disk is not a backup: copy the files to another machine or storage (rsync, restic, borg, object storage).
Restoring
# create the empty database first if the dump has no CREATE DATABASE
mysql -e "CREATE DATABASE shop CHARACTER SET utf8mb4"
mysql shop < shop.sql
# compressed
gunzip < shop_2026-11-15.sql.gz | mysql shop
# dump made with --databases / --all-databases
mysql < all.sqlRestore into a different database name to inspect old data without touching the live one:
mysql -e "CREATE DATABASE shop_restore"
gunzip < shop_2026-11-15.sql.gz | mysql shop_restoreLarge imports run faster with pv to show progress: pv shop.sql.gz | gunzip | mysql shop.
Point-in-time recovery with binary logs
The binary log records every change. With a nightly dump plus the binary logs, you can restore to any moment – for example just before an accidental DELETE.
Binary logging is on by default in MySQL 8; in MariaDB enable it:
[mysqld]
log_bin = /var/log/mysql/mysql-bin
server_id = 1
binlog_expire_logs_seconds = 1209600 # 14 days (MariaDB: expire_logs_days = 14)Recovery steps:
- Restore the last full dump.
- Find the position or time of the mistake:
mysqlbinlog --start-datetime="2026-11-15 02:30:00" /var/log/mysql/mysql-bin.000042 | less- Replay everything up to just before it:
mysqlbinlog --start-datetime="2026-11-15 02:30:00" \
--stop-datetime="2026-11-15 10:14:59" \
/var/log/mysql/mysql-bin.000042 | mysqlAdd --source-data=2 (MySQL 8.0.26+, older: --master-data=2) to mysqldump so the dump records the matching binary log position.
Physical backups for large databases
# MariaDB
sudo mariadb-backup --backup --target-dir=/var/backups/full
sudo mariadb-backup --prepare --target-dir=/var/backups/full
# MySQL with Percona XtraBackup
sudo xtrabackup --backup --target-dir=/var/backups/full
sudo xtrabackup --prepare --target-dir=/var/backups/fullTo restore, stop the server, empty the data directory, run --copy-back, fix ownership with chown -R mysql:mysql /var/lib/mysql, and start the server.
MySQL Shell also offers fast parallel logical dumps: util.dumpInstance() and util.loadDump().
Test your restores
A backup you have never restored is only a hope. Regularly restore a backup to a test machine or a separate database and check that the data is complete.
Put together with AI as a quick reference I keep for my own work. Entries like this are usually not reviewed in detail by a person.