Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 40 ยท Replication, Backup and Security

Backup and Restore in MariaDB

Back up and restore MariaDB with mariadb-dump, mariadb-backup and the binary log for point-in-time recovery, plus consistency options, scheduling, encryption and restore testing.

A backup is only real once you have restored from it. Disks fail, people run DROP TABLE on the wrong server, applications write bad data for an hour before anyone notices. This lesson covers the two kinds of MariaDB backup (logical and physical), how to take them without stopping the server, how to recover to an exact moment with the binary log, and how to build a routine you can trust.

The tools run on a server, so the commands are read-only here. The two runnable examples model the ideas in SQL: what a logical dump contains, and how point-in-time recovery replays a log on top of a snapshot.

Logical versus physical backups

Logical (mariadb-dump) Physical (mariadb-backup)
Output SQL statements (CREATE, INSERT) copy of the data files
Speed on big data slow to dump and much slower to restore fast both ways
Size readable, compresses well close to the data size
Portability across versions, engines, even to other servers same major version family, same architecture
Selective restore easy (one table, edit the text) harder (needs prepare, then copy files)
Typical use small and medium databases, migrations, schema export large production databases

Many teams use both: nightly physical backups for fast recovery, plus logical dumps of important schemas for flexibility.

mariadb-dump

Terminal
# one database, consistent snapshot without locking InnoDB tables
mariadb-dump --single-transaction --routines --triggers --events \
             --default-character-set=utf8mb4 shop > shop.sql

# all databases, compressed, with the binlog position for building a replica
mariadb-dump --all-databases --single-transaction --routines --events \
             --master-data=2 --flush-logs | gzip > full-$(date +%F).sql.gz

# only structure, only data, selected tables
mariadb-dump --no-data shop > schema.sql
mariadb-dump --no-create-info shop orders order_lines > data.sql

Options worth memorizing:

  • --single-transaction: reads InnoDB tables inside one consistent-snapshot transaction, so the dump is consistent without blocking writers. It does not protect MyISAM tables; for those use --lock-all-tables (blocks writes).
  • --routines, --triggers (on by default), --events: stored programs are not dumped unless you ask (--routines, --events).
  • --master-data=2 (or --source-data=2 in newer versions) records the binlog coordinates in a comment, the starting point for point-in-time recovery or a new replica. --gtid records the GTID position.
  • --quick (default) streams rows without buffering the full table; --extended-insert (default) writes multi-row INSERTs, faster to load.
  • --hex-blob, --default-character-set=utf8mb4 avoid binary and encoding corruption.
  • Pass credentials through an option file (~/.my.cnf with [client] and chmod 600), not on the command line where ps can show them.

Restore:

Terminal
mariadb -u root -p shop < shop.sql                    # into an existing, empty database
gunzip < full-2026-03-15.sql.gz | mariadb -u root -p  # a full dump
mariadb -u root -p -e "CREATE DATABASE shop_test CHARACTER SET utf8mb4"
mariadb -u root -p shop_test < shop.sql               # always test into a scratch database first

To speed up a big restore, the dump already wraps tables in SET FOREIGN_KEY_CHECKS=0 and UNIQUE_CHECKS=0 sections and locks tables during the load. Using innodb_flush_log_at_trx_commit=2 temporarily on the restore target also helps.

A dump is just text: statements that recreate the schema followed by INSERTs. You can generate the same kind of text with SQL itself, using quote() (MariaDB: QUOTE()) to escape values:

SQL What a logical dump looks like

Press Run (or Ctrl + Enter) to see the result here.

Replaying those lines on an empty server rebuilds the table, which is why logical backups are portable and why restore is as slow as re-inserting every row.

mariadb-backup (physical, hot)

mariadb-backup (a fork of Percona XtraBackup) copies InnoDB files while the server runs, then uses the redo log to make the copy consistent.

Terminal
# 1. full backup
mariadb-backup --backup --target-dir=/backups/full-2026-03-15 --user=backup --password-file=...

# 2. prepare (apply the redo log so the copy is consistent)
mariadb-backup --prepare --target-dir=/backups/full-2026-03-15

# 3. restore: server stopped, datadir EMPTY
systemctl stop mariadb
mariadb-backup --copy-back --target-dir=/backups/full-2026-03-15
chown -R mysql:mysql /var/lib/mysql
systemctl start mariadb

Incremental backups store only changed pages since a base: --backup --target-dir=/backups/inc1 --incremental-basedir=/backups/full; at restore time --prepare --incremental-dir=... merges them into the full backup. Useful extras: --stream=mbstream to pipe to compression, encryption or another host, and --compress. The backup account needs RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR (10.5+: BINLOG MONITOR; older: REPLICATION CLIENT). This same tool is what Galera nodes use for state transfer.

File-system or LVM/cloud-volume snapshots are another physical method: FLUSH TABLES WITH READ LOCK, snapshot, UNLOCK TABLES, then copy from the snapshot. Never copy the data directory of a running server with cp.

Point-in-time recovery

A nightly backup at 02:00 and a disaster at 14:37 means 12 hours of lost changes, unless you also keep the binary log. Recovery then has two steps: restore the last backup, then replay the binlog up to just before the mistake.

Terminal
# find the position/time of the mistake
mariadb-binlog --start-datetime="2026-03-15 02:00:00" --stop-datetime="2026-03-15 14:36:59" \
               /var/lib/mysql/mariadb-bin.000123 | mariadb -u root -p

# or by position taken from the backup's recorded coordinates
mariadb-binlog --start-position=4 --stop-position=987654 mariadb-bin.000123 | mariadb -u root -p

Keep binlogs as long as your backup interval plus a safety margin (binlog_expire_logs_seconds or expire_logs_days), and copy them off the server, because losing the disk loses the logs too.

SQL Point-in-time recovery model

Press Run (or Ctrl + Enter) to see the result here.

Cy keeps the 75 because the log was replayed only up to the mistake. (Event 15:05 is after the stop time and would need to be re-applied selectively; that is a business decision, not a technical one.)

Strategy: how to build a routine

  1. Define RPO and RTO. Recovery Point Objective: how much data loss you can accept (minutes? a day?). Recovery Time Objective: how long may recovery take. They decide the tools: tight RPO needs binlog shipping or replicas; tight RTO needs physical backups.
  2. 3-2-1 rule: 3 copies, on 2 kinds of storage, 1 off-site (and ideally immutable, so ransomware cannot erase it).
  3. Automate and monitor. Cron or a systemd timer that backs up, verifies the exit code, checks the size, and alerts on failure. A backup that silently stopped three weeks ago is the classic disaster.
  4. Encrypt backups at rest and in transit (openssl, gpg, --stream into an encrypting pipeline, or encrypted object storage), and protect the keys separately.
  5. Restore regularly. Restore into a test server, run CHECK TABLE and a row-count or checksum comparison, and time the process. That is your real RTO.
  6. Backup from a replica to avoid loading the primary; remember a replica's backup is consistent with its own position.
  7. Back up configuration (/etc/mysql), users (a mariadb-dump mysql or SHOW GRANTS export) and routines, not only data.

Handy single-table export/import for data exchange: SELECT ... INTO OUTFILE '/var/lib/mysql-files/x.csv' and LOAD DATA INFILE, subject to secure_file_priv.

Common mistakes

  • Never testing a restore.
  • Backing up only on the same server or disk as the data.
  • Forgetting --routines and --events.
  • --single-transaction on MyISAM tables (not consistent).
  • Copying data files of a running server.
  • Dumping with the wrong character set, corrupting accents.
  • Keeping the binlog on the same disk with no off-site copy.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which mariadb-dump option gives a consistent InnoDB dump without blocking writes?

    It runs the dump inside one consistent-snapshot transaction. It does not help MyISAM tables.
  2. 2. What is the purpose of mariadb-backup --prepare?

    The files are copied while the server runs, so they must be brought to a consistent state before copy-back.
  3. 3. What do you need besides a full backup to recover to 14:36 on the same day?

    Point-in-time recovery = restore the backup, then replay binlog events with --stop-datetime.
  4. 4. Why is a replica not a backup?

    Replication copies mistakes too. A delayed replica or real backups protect against them.

Summary

  • Logical dumps (mariadb-dump) are portable SQL; physical backups (mariadb-backup) are fast for large data.
  • Use --single-transaction for InnoDB, add --routines --events, and record the binlog position.
  • Binary logs plus a backup give point-in-time recovery.
  • Define RPO/RTO, follow 3-2-1, encrypt, monitor, and rehearse restores.

Next: Users, Grants and Roles