Log in Sign up

Logical vs physical backups

TypeToolsProsCons
Logical – SQL statementsmysqldump, mariadb-dump, MySQL Shellsimple, portable between versions, readableslow to restore for large databases
Physical – copy of the data filesPercona XtraBackup (MySQL), mariadb-backup (MariaDB)fast backup and restore for large datasame 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
OptionWhy
--single-transactionconsistent snapshot of InnoDB tables without locking them – always use it
--routines, --triggers, --eventsinclude stored procedures, triggers, events (triggers are included by default)
--databasesadds CREATE DATABASE and USE to the dump
--all-databasesevery database including users in mysql
--no-tablespacesavoids 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 -delete
sudo chmod 700 /usr/local/bin/mysql-backup.sh
sudo crontab -e
# every night at 02:30
30 2 * * * /usr/local/bin/mysql-backup.sh

A 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.sql

Restore 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_restore

Large 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:

  1. Restore the last full dump.
  2. Find the position or time of the mistake:
mysqlbinlog --start-datetime="2026-11-15 02:30:00" /var/log/mysql/mysql-bin.000042 | less
  1. 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 | mysql

Add --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/full

To 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.

Next: MySQL Configuration, Logs and Troubleshooting

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.