MariaDB and MySQL Backups on AlmaLinux 10: mysqldump, mariadb-backup and Point-in-Time Recovery

November 11, 2026 · by Zwiegnet Consulting · Database, Linux

MySQL/MariaDB on AlmaLinux series, part 3. Part 1 installed and secured the server, and part 2, users, grants and remote access, created the shopdb schema and its accounts. This part makes sure you can get that data back.

A database without a tested restore is a database you are hoping about. MariaDB gives you two complementary backup tools: mariadb-dump (the tool many people still call mysqldump), which writes portable SQL, and mariadb-backup, which copies the InnoDB files while the server runs and restores a large server in minutes instead of hours. Add the binary log and you can also replay changes up to the second before someone ran the wrong DELETE. This guide sets up a dedicated backup account, both kinds of backup, a systemd timer that runs them every night, and full, single-database and point-in-time restores, with verification and troubleshooting for each step.

Applies to: AlmaLinux 10.x with MariaDB 10.11 (package mariadb-server) or 11.8 (mariadb11.8-server) from AppStream. A section near the end covers what changes on MySQL 8.4. The examples use the server db1.example.lan from the earlier parts and keep backups in /srv/backup/mysql.

Prerequisites

  • A MariaDB server installed as in part 1, with the local settings file /etc/my.cnf.d/zz-local.cnf, and root access through sudo mariadb.
  • Enough free disk for at least two full copies of the data directory plus compressed dumps. Check with sudo du -sh /var/lib/mysql and df -h /srv; ideally /srv/backup is a separate filesystem or volume, and the backups are copied off the host afterwards.
  • The physical backup tool, which matches the server package you installed, and zstd for compression:
# MariaDB 10.11 (mariadb-server)
sudo dnf install -y mariadb-backup zstd
# MariaDB 11.8 (mariadb11.8-server)
sudo dnf install -y mariadb11.8-backup zstd

mariadb-backup --version
mariadb --version          # the two major.minor versions must match

Step 1: Choose logical, physical or both

Logical: mariadb-dumpPhysical: mariadb-backup
OutputSQL text (CREATE TABLE, INSERT)Copy of the InnoDB data files and redo log
Server impactReads every row; one short global lock with --single-transactionCopies files; short lock at the end for non-InnoDB tables
Restore speedSlow: every row is re-inserted and every index rebuiltFast: files are copied back into place
GranularityOne table, one database or everythingThe whole server (single tables are possible but fiddly)
PortabilityLoads into other versions and other hosts, even MySQLSame MariaDB major version only
Best forSmall to medium databases, migrations, restoring one databaseLarge servers, fast full recovery, seeding replicas

The usual answer is both: a nightly logical dump for flexibility and a weekly or nightly physical backup for fast full recovery, plus the binary log to fill the gap between backups. This guide builds exactly that.

Step 2: Create a backup account and a credentials file

Do not run backups as root with a password on the command line. Create one account with only the privileges both tools need, following the least-privilege approach from part 2:

sudo mariadb <<'SQL'
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'Paste-A-Long-Random-Password';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT,
      RELOAD, PROCESS, BINLOG MONITOR
  ON *.* TO 'backup'@'localhost';
SHOW GRANTS FOR 'backup'@'localhost';
SQL

SELECT, SHOW VIEW, TRIGGER, LOCK TABLES and EVENT let mariadb-dump read tables, views, triggers and events; RELOAD allows the FLUSH TABLES WITH READ LOCK and FLUSH LOGS both tools use; PROCESS and BINLOG MONITOR are what the MariaDB documentation lists for mariadb-backup (together with RELOAD and LOCK TABLES), and BINLOG MONITOR also lets mariadb-dump record the binary log position. The account can read everything but change nothing.

Store the password in a root-only option file. The [client] group is read by mariadb-dump; the [mariadb-backup] group is read by mariadb-backup:

sudo install -m 600 -o root -g root /dev/null /root/.db-backup.cnf
sudo tee /root/.db-backup.cnf >/dev/null <<'EOF'
[client]
user = backup
password = Paste-A-Long-Random-Password

[mariadb-backup]
user = backup
password = Paste-A-Long-Random-Password
EOF
sudo mariadb --defaults-extra-file=/root/.db-backup.cnf -e "SELECT CURRENT_USER();"

--defaults-extra-file must be the first option on the command line, or the client ignores it. The test should print backup@localhost.

Step 3: Turn on the binary log for point-in-time recovery

A backup only takes you back to the moment it was made. The binary log records every change after that, so you can replay it on top of a restored backup. MariaDB on AlmaLinux ships with it off. Put the settings in their own file so they are easy to find:

sudo tee /etc/my.cnf.d/zz-binlog.cnf >/dev/null <<'EOF'
[mariadb]
server_id                  = 1
log_bin                    = mariadb-bin
binlog_format              = ROW
binlog_expire_logs_seconds = 1209600   # 14 days, keep longer than your oldest full backup chain
EOF
sudo systemctl restart mariadb
sudo mariadb -e "SHOW VARIABLES LIKE 'log_bin'; SHOW BINARY LOGS;"

A relative log_bin name puts the files (mariadb-bin.000001 and the index mariadb-bin.index) in the data directory /var/lib/mysql, which already has the right SELinux label. ROW format logs the actual row changes, which is the safest format to replay. Size the expiry to your backup schedule: you need every binary log written since the oldest backup you might restore. Part 4 of this series reuses the binary log for replication.

Step 4: Logical backups with mariadb-dump

Create the backup directories, readable by root only, then take a full logical backup of every database:

sudo install -d -m 700 /srv/backup/mysql /srv/backup/mysql/dump /srv/backup/mysql/phys
STAMP=$(date +%F_%H%M)
sudo mariadb-dump --defaults-extra-file=/root/.db-backup.cnf \
    --all-databases --single-transaction --routines --events \
    --master-data=2 --gtid --flush-logs \
  | zstd -q -T0 | sudo tee /srv/backup/mysql/dump/all-$STAMP.sql.zst >/dev/null

What the options do:

  • --single-transaction dumps every InnoDB table from one consistent snapshot without blocking writes. It only takes a brief global read lock at the start to record the binary log position. Tables in other engines (MyISAM, Aria) are not protected by the snapshot; convert them to InnoDB if their consistency matters.
  • --routines and --events add stored procedures, functions and scheduled events, which are not dumped by default. Triggers are included by default.
  • --master-data=2 writes the binary log file and position as a commented-out CHANGE MASTER TO line near the top of the dump, and --gtid adds the GTID position too. That is your starting point for point-in-time recovery (Step 9) and for seeding a replica in part 4.
  • --flush-logs starts a new binary log file at the moment of the dump, so the logs you would replay begin cleanly at the start of a file.

Dumping a single database (without --databases) produces a file you can load into a database of any name, which is ideal for restore tests and for copying data to a test server:

sudo mariadb-dump --defaults-extra-file=/root/.db-backup.cnf \
    --single-transaction --routines --events shopdb \
  | zstd -q | sudo tee /srv/backup/mysql/dump/shopdb-$(date +%F).sql.zst >/dev/null

Accounts and grants live in the mysql system database. --all-databases includes it, but a raw dump of the grant tables is version-specific. MariaDB can also write accounts as portable CREATE USER and GRANT statements, which is what you want when rebuilding on a newer server:

sudo mariadb-dump --defaults-extra-file=/root/.db-backup.cnf --system=users \
  | sudo tee /srv/backup/mysql/dump/users-$(date +%F).sql >/dev/null
sudo grep -c '^CREATE USER' /srv/backup/mysql/dump/users-$(date +%F).sql

Check the dump right away. A dump that stopped half way still looks like a valid file, so look for the trailer mariadb-dump writes at the very end, and test the compression:

f=$(ls -t /srv/backup/mysql/dump/all-*.sql.zst | head -1)
sudo zstd -t "$f"
sudo zstd -dc "$f" | tail -n 1                  # expect: -- Dump completed on 2026-...
sudo zstd -dc "$f" | grep -m2 -E 'CHANGE MASTER|gtid_slave_pos'
ls -lh "$f"

Step 5: Physical backups with mariadb-backup

mariadb-backup copies the data files while the server keeps running, records the redo log written during the copy, and takes only a short lock at the end to copy non-InnoDB tables and record the binary log position. The target directory must be empty or not exist yet:

FULL=/srv/backup/mysql/phys/full-$(date +%F)
sudo mariadb-backup --defaults-extra-file=/root/.db-backup.cnf \
    --backup --target-dir="$FULL"
sudo ls "$FULL"

The run ends with completed OK!. The directory looks like a data directory plus a few metadata files: the checkpoint file with the backup’s log sequence numbers (xtrabackup_checkpoints on 10.11, mariadb_backup_checkpoints on 11.x) and the binary log position (xtrabackup_binlog_info or mariadb_backup_binlog_info).

The copied files are not consistent yet, because pages changed during the copy. Preparing the backup applies the saved redo log so the files are crash-consistent and ready to copy back. It needs no database connection:

sudo mariadb-backup --prepare --target-dir="$FULL"     # ends with "completed OK!"

Preparing right after the backup confirms that the backup is usable and saves time during an outage. For large servers, incremental backups copy only the pages changed since a previous backup, identified by their log sequence number:

# incremental on top of the full backup (use the previous incremental as the base for the next one)
INC1=/srv/backup/mysql/phys/inc1-$(date +%F_%H%M)
sudo mariadb-backup --defaults-extra-file=/root/.db-backup.cnf \
    --backup --target-dir="$INC1" --incremental-basedir="$FULL"

# at restore time: prepare the full backup, then apply each incremental in order
sudo mariadb-backup --prepare --target-dir="$FULL"
sudo mariadb-backup --prepare --target-dir="$FULL" --incremental-dir="$INC1"

Applying an incremental changes the full backup directory for good, so work on a copy (cp -a) if you might need to stop at an earlier point. A physical backup is only usable with the same major MariaDB version: always back up and prepare with the mariadb-backup that matches the running server, and take a new full backup after every major upgrade.

Step 6: Automate the backups with a systemd timer

A small script runs a nightly logical dump, a physical full backup on Sundays, and deletes old files. set -o pipefail matters here: without it, a failed mariadb-dump piped into zstd would still count as success.

sudo tee /usr/local/sbin/db-backup.sh >/dev/null <<'EOF'
#!/bin/bash
# Nightly MariaDB backup: logical dump every day, physical full backup on Sunday
set -euo pipefail
umask 077
CNF=/root/.db-backup.cnf
BASE=/srv/backup/mysql
STAMP=$(date +%F_%H%M)
DUMP_KEEP_DAYS=14
PHYS_KEEP_DAYS=35

mariadb-dump --defaults-extra-file=$CNF --all-databases --single-transaction \
    --routines --events --master-data=2 --gtid --flush-logs \
  | zstd -q -T0 -o "$BASE/dump/all-$STAMP.sql.zst"
mariadb-dump --defaults-extra-file=$CNF --system=users > "$BASE/dump/users-$STAMP.sql"
zstd -dc "$BASE/dump/all-$STAMP.sql.zst" | tail -n 1 | grep -q '^-- Dump completed'

if [ "$(date +%u)" = 7 ]; then
    mariadb-backup --defaults-extra-file=$CNF --backup --target-dir="$BASE/phys/full-$STAMP"
    mariadb-backup --prepare --target-dir="$BASE/phys/full-$STAMP"
fi

find "$BASE/dump" -type f -mtime +$DUMP_KEEP_DAYS -delete
find "$BASE/phys" -mindepth 1 -maxdepth 1 -type d -mtime +$PHYS_KEEP_DAYS -exec rm -rf {} +
echo "backup $STAMP OK"
EOF
sudo chmod 700 /usr/local/sbin/db-backup.sh

Now the service and timer. Persistent=true runs a missed backup at the next boot, and the I/O and CPU settings keep the job from starving the database:

sudo tee /etc/systemd/system/db-backup.service >/dev/null <<'EOF'
[Unit]
Description=MariaDB backup (mariadb-dump and mariadb-backup)
After=mariadb.service
Requires=mariadb.service

[Service]
Type=oneshot
ExecStart=/usr/local/sbin/db-backup.sh
Nice=10
IOSchedulingClass=best-effort
IOSchedulingPriority=7
EOF

sudo tee /etc/systemd/system/db-backup.timer >/dev/null <<'EOF'
[Unit]
Description=Nightly MariaDB backup

[Timer]
OnCalendar=*-*-* 01:30:00
RandomizedDelaySec=10min
Persistent=true

[Install]
WantedBy=timers.target
EOF

sudo systemctl daemon-reload
sudo systemctl enable --now db-backup.timer
sudo systemctl start db-backup.service          # run once now to test
systemctl list-timers db-backup.timer
journalctl -u db-backup.service -n 20 --no-pager

The journal should end with backup ... OK. If you use mariadb11.8-server, the service is still called mariadb.service. Copy /srv/backup/mysql to another machine afterwards (rsync over SSH, or a backup system such as Bacula): a backup on the same disk as the database does not survive the loss of that disk.

Step 7: Restore from a logical dump

Before restoring over anything, stop the applications that write to the database. Then pick the case that fits.

Restore a whole server

f=/srv/backup/mysql/dump/all-2026-11-11_0130.sql.zst
sudo zstd -dc "$f" | sudo mariadb

A full dump contains DROP TABLE/CREATE TABLE for every table and replaces the mysql system database, so accounts and passwords return to their state at backup time. On a newly installed server of a newer version, load only the application databases and the portable users-*.sql file instead of the raw mysql schema, then run sudo mariadb-upgrade.

Restore one database from a full dump

sudo mariadb -e "CREATE DATABASE IF NOT EXISTS shopdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
sudo zstd -dc "$f" | sudo mariadb --one-database shopdb

--one-database makes the client skip every statement that is not for shopdb, so the rest of the server is left alone.

Restore test into a scratch database

This is the restore you should run regularly, because it proves a backup works without touching production. Load the single-database dump into a new database and compare it with the live one:

sudo mariadb -e "CREATE DATABASE shopdb_restoretest;"
sudo zstd -dc /srv/backup/mysql/dump/shopdb-2026-11-11.sql.zst | sudo mariadb shopdb_restoretest
sudo mariadb -e "SELECT COUNT(*) FROM shopdb.orders; SELECT COUNT(*) FROM shopdb_restoretest.orders;"
sudo mariadb -e "CHECKSUM TABLE shopdb.customers, shopdb_restoretest.customers;"
sudo mariadb -e "DROP DATABASE shopdb_restoretest;"

Row counts can differ slightly if the application wrote rows since the dump; checksums of a table that has not changed should be identical.

Step 8: Restore from a physical backup

A physical restore replaces the whole data directory, so the server must be stopped and the directory empty. Move the old one aside instead of deleting it: it still contains the binary logs you need for point-in-time recovery.

FULL=/srv/backup/mysql/phys/full-2026-11-08_0130     # already prepared (Step 5)
sudo systemctl stop mariadb
sudo mv /var/lib/mysql /var/lib/mysql.old-$(date +%F)
sudo install -d -m 755 -o mysql -g mysql /var/lib/mysql
sudo mariadb-backup --copy-back --target-dir="$FULL"
sudo chown -R mysql:mysql /var/lib/mysql
sudo restorecon -Rv /var/lib/mysql
sudo systemctl start mariadb
sudo mariadb -e "SELECT NOW(); SHOW DATABASES;"

--copy-back reads the data directory location from the server configuration and keeps the backup intact; --move-back is faster but consumes the backup. The chown is required because the files are written as root, and restorecon resets the mysqld_db_t SELinux label in case files were created with the wrong context. Without them, MariaDB fails to start.

Step 9: Point-in-time recovery with the binary log

Suppose someone ran DELETE FROM shopdb.orders; without a WHERE clause at about 14:02. Restore the last backup (Step 7 or 8), then replay the binary log from the backup’s position up to just before the mistake. First, find the starting position:

# physical backup
sudo cat /srv/backup/mysql/phys/full-2026-11-08_0130/xtrabackup_binlog_info
#   mariadb-bin.000012   385   0-1-48211        (file, position, GTID)

# logical dump
sudo zstd -dc /srv/backup/mysql/dump/all-2026-11-11_0130.sql.zst | grep -m1 'CHANGE MASTER'
#   -- CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000015', MASTER_LOG_POS=385;

Next, find exactly where the bad statement is. With ROW format, decode the row events so you can read them, and search around the time of the incident:

cd /var/lib/mysql.old-2026-11-11                       # or /var/lib/mysql if you restored a dump
sudo mariadb-binlog --base64-output=decode-rows -vv \
    --start-datetime="2026-11-11 13:55:00" --stop-datetime="2026-11-11 14:05:00" \
    mariadb-bin.000015 | grep -n -B4 -A6 'DELETE FROM `shopdb`.`orders`' | less

Note the # at NNN position of the event before the transaction that contains the bad delete (the BEGIN of that transaction). Then replay from the backup position up to that point:

sudo mariadb-binlog --start-position=385 --stop-position=918274 \
    mariadb-bin.000015 | sudo mariadb

# when the logs span several files, --start-position applies to the first file only
# and --stop-position to the last one:
sudo mariadb-binlog --start-position=385 --stop-datetime="2026-11-11 14:01:59" \
    mariadb-bin.000015 mariadb-bin.000016 | sudo mariadb

Replay all files in a single mariadb-binlog command rather than one command per file, so temporary tables and transactions that cross a file boundary are handled correctly. Check the result (SELECT COUNT(*) FROM shopdb.orders;), then take a fresh full backup before letting the applications back in, because the replayed server now has a new history.

Step 10: What changes on MySQL 8.4

  • Logical dumps: use mysqldump with --source-data=2; --master-data is the deprecated old name. A typical command is mysqldump --defaults-extra-file=/root/.db-backup.cnf --all-databases --single-transaction --routines --events --source-data=2 --flush-logs. There is no --system=users; save accounts with SHOW CREATE USER and SHOW GRANTS per account, or use MySQL Shell’s util.dumpInstance(), which includes users.
  • Backup account: grant REPLICATION CLIENT instead of MariaDB’s BINLOG MONITOR; the rest of the list is the same. PROCESS is needed for the tablespace information mysqldump reads (or add --no-tablespaces).
  • Binary log: enabled by default in MySQL 8.4 (files binlog.000001 in the data directory, 30-day expiry). SHOW MASTER STATUS was removed in 8.4; use SHOW BINARY LOG STATUS. Replay with mysqlbinlog using the same options as Step 9.
  • Physical backups: mariadb-backup does not support MySQL 8.x data files. Use Percona XtraBackup 8.4 (from Percona’s own repository, not AppStream), MySQL Enterprise Backup, or the clone plugin to copy a server.

Verification checklist

  • SHOW GRANTS FOR 'backup'@'localhost'; shows only read and backup privileges, and /root/.db-backup.cnf is mode 600 owned by root.
  • SHOW BINARY LOGS; lists files, and the oldest is older than your oldest backup you plan to restore.
  • systemctl list-timers db-backup.timer shows the next run, and journalctl -u db-backup.service shows backup ... OK for the last one.
  • Every recent dump passes zstd -t and ends with -- Dump completed; the latest physical backup ended with completed OK! for both backup and prepare.
  • A restore test into a scratch database (Step 7) has been run recently, and a copy of the backups exists on another machine.

Troubleshooting tips

  • mariadb-dump: Error: Binlogging on server not active: --master-data needs the binary log. Enable it as in Step 3, or drop --master-data and --gtid if you do not want point-in-time recovery.
  • Access denied; you need (at least one of) the RELOAD privilege(s) (or PROCESS, BINLOG MONITOR): the backup account is missing a grant. Compare SHOW GRANTS FOR 'backup'@'localhost' with Step 2. If CURRENT_USER() is not backup@localhost, the credentials file was not read; put --defaults-extra-file first.
  • ERROR at line 1: Unknown command '\-' when loading a dump: recent MariaDB versions of mariadb-dump start the file with a sandbox-mode line that the MySQL client and older clients do not understand. Load it with the MariaDB mariadb client, or skip the first line: zstd -dc file.sql.zst | tail -n +2 | mysql ....
  • Table definition has changed, please retry transaction during a dump: a DDL statement (ALTER, TRUNCATE, RENAME) ran on a table while --single-transaction was reading it. Schedule the backup away from deployments and migrations; --order-by-size helps when small tables are truncated often.
  • mariadb-backup --copy-back says the original data directory is not empty: move or empty /var/lib/mysql first, as in Step 8. Check for hidden files and leftover lost+found on a dedicated filesystem.
  • MariaDB will not start after a physical restore: check journalctl -u mariadb and /var/log/mariadb/mariadb.log. Wrong ownership (Permission denied) is fixed with chown -R mysql:mysql /var/lib/mysql; SELinux denials (ausearch -m avc -ts recent) with restorecon -Rv /var/lib/mysql. A backup that was never prepared, or prepared with a different mariadb-backup version, can also fail here: prepare it with the matching version.
  • mariadb-backup fails reading the redo log or reports an unsupported format: the tool and the server are different versions, typically after dnf update moved one of them or after switching between mariadb and mariadb11.8. Install the matching backup package and take a new full backup.
  • /var/lib/mysql or /srv fills up: binary logs are the usual cause. Check SHOW BINARY LOGS;, shorten binlog_expire_logs_seconds, or purge manually after a successful backup with PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;. Never delete binary log files with rm, because the index file still lists them.
  • The timer never ran: systemctl list-timers --all | grep db-backup shows whether it is enabled; systemctl status db-backup.service and the journal show why a run failed, including the exact command that returned non-zero.

Quick reference

TaskCommand
Full logical dumpmariadb-dump --all-databases --single-transaction --routines --events --master-data=2 --gtid
One databasemariadb-dump --single-transaction --routines --events shopdb
Accounts as SQLmariadb-dump --system=users
Physical full backupmariadb-backup --backup --target-dir=DIR
Incremental backupmariadb-backup --backup --target-dir=INC --incremental-basedir=DIR
Preparemariadb-backup --prepare --target-dir=DIR [--incremental-dir=INC]
Physical restoremariadb-backup --copy-back --target-dir=DIR, then chown and restorecon
Restore one databasezstd -dc all.sql.zst | mariadb --one-database shopdb
Read the binary logmariadb-binlog --base64-output=decode-rows -vv FILE
Replay to a pointmariadb-binlog --start-position=N --stop-datetime="..." FILES | mariadb

Next in the series

Part 4 sets up primary/replica replication with GTID, using the binary log enabled here and a backup from this guide to seed the replica. Earlier: part 1, install and secure MariaDB/MySQL and part 2, users, grants and remote access. If you also run PostgreSQL, the equivalent guide is PostgreSQL backup and restore with pg_dump and pg_basebackup.

Official references: MariaDB: mariadb-dump, MariaDB: mariadb-backup overview, full backup and restore, incremental backup and restore, MySQL 8.4: mysqldump and MySQL 8.4: point-in-time recovery.

, , , , , , , , , , , , , , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *