MySQL and MariaDB backups: logical dumps, physical backups and point-in-time recovery

There are two ways to back up a MySQL or MariaDB server. A logical backup exports SQL statements that recreate your data; a physical backup copies the database files themselves. Both have a place, and neither on its own lets you recover to the minute before a bad query. This guide shows how to take each kind correctly on a Debian or Ubuntu server, how to restore them, and how the binary log fills the gap between backups.

Know which server you run

Debian ships MariaDB as its MySQL-compatible server: MariaDB 10.11 in Debian 12 (bookworm) and MariaDB 11.8 in Debian 13 (trixie). Oracle's MySQL 8.4 LTS comes from MySQL's own repositories or other distributions. The tools differ in name and in a few options, so check first:

mariadb --version || mysql --version

On MariaDB the client tools are called mariadb, mariadb-dump and mariadb-binlog. The old mysqldump name still works through a compatibility link, but MariaDB's documentation marks it as deprecated from MariaDB 11.0, so use the new names in new scripts.

Logical backups with mariadb-dump or mysqldump

A dump is portable across versions and machines, easy to inspect and easy to restore a single database from. For InnoDB tables, --single-transaction takes a consistent snapshot without blocking the application:

# MariaDB on Debian: root connects through the local socket
sudo mariadb-dump --single-transaction --quick --routines --events \
  --databases shop > /var/backups/db/shop-$(date +%F).sql

# MySQL 8.4
mysqldump --single-transaction --quick --routines --events \
  --databases shop > /var/backups/db/shop-$(date +%F).sql

Details that matter:

• Only InnoDB is consistent. --single-transaction gives a consistent view of transactional tables only; MyISAM or MEMORY tables can still change during the dump.
• No schema changes during the dump. The MariaDB documentation warns that ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE or TRUNCATE TABLE from another connection can make the dump incorrect or fail. Schedule dumps away from deployments that run migrations.
• Routines and events are opt-in. Triggers are dumped with their tables, but stored procedures, functions and scheduled events need --routines and --events.
• Check for failure. If you pipe the dump through gzip, run the script with set -o pipefail so a failed dump is not hidden behind a successful compression step.

Restoring is a matter of feeding the file back to the client, ideally into a test server first:

sudo mariadb < /var/backups/db/shop-2026-10-31.sql
# or for a compressed dump
zcat /var/backups/db/shop-2026-10-31.sql.gz | sudo mariadb

The MySQL manual is candid about the limits: mysqldump is not intended as a fast or scalable solution for large amounts of data, and restoring can be very slow because every row and index is rebuilt by replaying SQL. When a restore would take longer than your business can wait, move to physical backups.

Physical backups with mariadb-backup or Percona XtraBackup

Physical tools copy InnoDB data files while the server keeps running, then make the copy consistent in a separate prepare step. Restores are much faster because nothing is rebuilt.

For MariaDB, use mariadb-backup (formerly mariabackup, originally forked from Percona XtraBackup 2.3.8). On Debian it is in the mariadb-backup package. The backup user needs the RELOAD, PROCESS, LOCK TABLES and BINLOG MONITOR privileges.

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

The files are not consistent until you run --prepare, so never copy an unprepared backup into a data directory. To restore: stop the server, make sure the data directory is empty, copy the backup back and fix ownership:

sudo systemctl stop mariadb
sudo mariadb-backup --copy-back --target-dir=/var/backups/mariadb/full
sudo chown -R mysql:mysql /var/lib/mysql/
sudo systemctl start mariadb

Use a mariadb-backup version that matches the server it backs up; the documentation is explicit about this when you plan an upgrade.

For Oracle MySQL, Percona XtraBackup plays the same role with xtrabackup --backup, --prepare and --copy-back. Percona's rule is to use an XtraBackup version equal to your server's major version, for example XtraBackup 8.4 for MySQL 8.4. Oracle's own physical tool, MySQL Enterprise Backup, is part of the commercial edition.

Point-in-time recovery with the binary log

A nightly backup alone means that a bad DELETE at 4 p.m. costs you everything since last night. The binary log records every change after the backup, so you can restore the backup and replay changes up to just before the mistake.

• Turn it on. Binary logging is on by default in MySQL 8.4. On MariaDB, enable it with log_bin in the server configuration and confirm with SHOW VARIABLES LIKE 'log_bin';.
• Record where each backup starts. --master-data=2 (MariaDB) or --source-data=2 (MySQL 8.4) writes the binary log file and position into the dump as a comment. mariadb-backup writes them to mariadb_backup_binlog_info (xtrabackup_binlog_info in older releases).
• Keep and copy the logs. Retain binary logs at least as long as the gap between full backups, and copy them off the server with the backups.

Recovery then looks like this: restore the last full backup, then replay the logs from the recorded position up to the moment before the incident.

mariadb-binlog --start-position=321 \
  --stop-datetime="2026-10-31 15:59:00" \
  /var/lib/mysql/mariadb-bin.000042 > replay.sql
# review replay.sql, then apply it
sudo mariadb < replay.sql

On MySQL the tool is mysqlbinlog; when replaying several log files, the MySQL manual recommends sending them through a single connection.

Which one should you use?

On a managed hosting plan the platform may already do part of this. Skyline's hosting plans, for example, include nightly backups of websites and databases kept for 30 days with one-click restore. On a self-managed VPS, all of it is yours to set up.

What you should do

1. Identify your server and version, and use the matching tools (mariadb-* on MariaDB).
2. Take a nightly logical dump with --single-transaction --routines --events, check its exit status and copy it off the server.
3. If restores from dumps would be too slow, add mariadb-backup or XtraBackup at the version that matches your server.
4. Enable and retain binary logs, and record the log position with every backup.
5. Restore into a test server on a schedule and time it; that number is your real recovery time.

Sources

• mariadb.com/docs/server/clients-and-utilities/backup-restore-and-import-clients/mariadb...
• mariadb.com/docs/server/server-usage/backup-and-restore/mariadb-backup/mariadb-backup-o...
• mariadb.com/docs/server/server-usage/backup-and-restore/mariadb-backup/full-backup-and-...
• mariadb.com/docs/server/server-usage/backup-and-restore/mariadb-backup/point-in-time-re...
• mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log/activating-...
• mariadb.com/docs/server/clients-and-utilities/logging-tools/mariadb-binlog/mariadb-binl...
• mariadb.com/docs/server/reference/plugins/authentication-plugins/authentication-plugin-...
• dev.mysql.com/doc/refman/8.4/en/mysqldump.html
• dev.mysql.com/doc/refman/8.4/en/backup-methods.html
• dev.mysql.com/doc/refman/8.4/en/point-in-time-recovery-binlog.html
• docs.percona.com/percona-xtrabackup/8.4/server-backup-version-comparison.html
• docs.percona.com/percona-xtrabackup/8.4/create-full-backup.html
• docs.percona.com/percona-xtrabackup/8.4/restore-a-backup.html
• qa.debian.org/madison.php?package=mariadb
• packages.debian.org/trixie/amd64/mariadb-client/filelist
• cloud.alskyline.com/hosting