How a Bareos Physical Backup Made a Simple DB Restore Into a Six-Incremental Chain

I nuked the Kanboard MariaDB containers and had to rebuild them from scratch… which is when I found out the hard way that our Bareos backups were physical mariadb-backup snapshots, not logical SQL dumps. What followed was a six-incremental chain restore, a temporary MariaDB sidecar, and a careful dance to preserve the fresh containers’ user accounts.

What Happened

I was rebuilding hub-andrewcz-com-kanboard-mariadb-ct0 and hub-andrewcz-com-kanboard-mariadb-ct1 from scratch. Both are Proxmox LXC containers running NixOS 25.05 with MariaDB 10.11.13, configured in a primary/replica setup with GTID replication. ct0 is the primary (server_id=1, keepalived MASTER), ct1 is the replica (server_id=2, keepalived BACKUP). Applications connect through a keepalived VRRP virtual IP at 172.16.8.9.

The containers were freshly rebuilt and running, but the kanboard database was empty… just the default admin user and 24 settings from NixOS provisioning. The real data… 6,596 tasks across 11 projects, 6 users, 3,120 comments, 22,285 subtasks… was sitting in Bareos backups on hub-andrewcz-net-bareos-ct0.

This is the time to figure out how to restore the latest backup. The job name is job-andrewcz-com-kanboard-mariadb, and it backs up hub-andrewcz-com-kanboard-mariadb-ct1-fd (the replica) using the Bareos mariabackup plugin.

The second discovery was bigger: the backup format wasn’t a mysqldump logical dump. It was a raw physical backup created with mariadb-backup --backup --stream=xbstream. That means the backup contains InnoDB data files, not SQL statements. You can’t just pipe it into mysql… you have to extract it, prepare it, and either copy it back over the data directory or start a separate MariaDB instance from it.

Root Cause

The Bareos bareos-fd-mariabackup plugin runs mariadb-backup --defaults-file=/etc/bareos/my.cnf --backup --stream=xbstream --extra-lsndir=<tempdir> on the client. This produces an xbstream file… a streaming format for physical backups that can be extracted with the mbstream tool.

Physical backups have advantages: they’re faster to create and restore than logical dumps, and they preserve the exact on-disk state of InnoDB. But they also have a critical constraint: a mariadb-backup --copy-back replaces the entire data directory, including the mysql system database. That means all user accounts, passwords, and grants get overwritten with whatever was in the backup.

I explicitly didn’t want that. The fresh containers had been provisioned by NixOS with specific user passwords for kanboard, replicator, bareos, and lgtm… and I didn’t want to change those. I just needed the kanboard database itself.

The backup schedule was weekly full, daily incremental: full backups every Saturday at 02:00, incrementals daily at 02:00. The most recent full was job 4690 from July 4. The most recent incremental was job 4852 from July 10. I wanted the latest point-in-time, so I needed both.

The Fix

Step 1: Restore from Bareos via bconsole

The first step was getting the backup data out of Bareos and onto ct1. Using bconsole on the Bareos server:

restore
3  (Enter list of comma separated JobIds)
4690,4852
mark *
done
4  (client: hub-andrewcz-com-kanboard-mariadb-ct1-fd)
mod
10  (Where parameter)
/tmp/bareos-restore
yes

This restored the xbstream files to ct1. But here’s the thing about mariadb-backup incremental backups: they chain off each other using LSN (Log Sequence Number) tracking. Each incremental only contains pages changed since the previous backup in the chain. They are not self-contained.

Step 2: The incremental chain surprise

I initially tried to apply only the final incremental (job 4852, July 10) to the full backup (job 4690, July 4). That failed with:

error: This incremental backup seems not to be proper for the target

The full backup’s to_lsn was 30981765434, but job 4852’s from_lsn was 31171391631. They didn’t match because there were five intermediate incrementals in between (jobs 4727, 4752, 4777, 4802, 4827… one per day from July 5 through July 9). Each one chains off the previous, so I had to restore all of them and apply them in sequence:

The prepare sequence:

# Prepare the base full backup
mariadb-backup --prepare --target-dir=$FULL_DIR

# Apply each incremental in order
mariadb-backup --prepare --target-dir=$FULL_DIR --incremental-dir=$INC_4727
mariadb-backup --prepare --target-dir=$FULL_DIR --incremental-dir=$INC_4752
mariadb-backup --prepare --target-dir=$FULL_DIR --incremental-dir=$INC_4777
mariadb-backup --prepare --target-dir=$FULL_DIR --incremental-dir=$INC_4802
mariadb-backup --prepare --target-dir=$FULL_DIR --incremental-dir=$INC_4827
mariadb-backup --prepare --target-dir=$FULL_DIR --incremental-dir=$INC_4852

# Final prepare to make the merged backup consistent
mariadb-backup --prepare --target-dir=$FULL_DIR

The --prepare step is mandatory… without it, InnoDB detects inconsistency and crashes on startup to protect against corruption. It replays the redo log against the copied data files to bring them to a consistent state.

Step 3: The temp MariaDB sidecar

Here’s where it got interesting. I couldn’t just mariadb-backup --copy-back because that replaces the entire data directory, including the mysql system database with all its user accounts and passwords. I needed to preserve the fresh containers’ NixOS-provisioned users.

The solution: start a temporary MariaDB instance from the prepared backup directory, on a different port, and dump just the kanboard database from it.

# Start a temp MariaDB from the prepared backup
sudo -u mysql mariadbd --datadir=/tmp/restore --port=3307 \
  --socket=/tmp/restore.sock --skip-networking --skip-grant-tables &

# Wait for it to be ready
sleep 5
mysqladmin --socket=/tmp/restore.sock ping

# Dump just the kanboard database
mysqldump --socket=/tmp/restore.sock --single-transaction kanboard > /tmp/kanboard_restore.sql

The dump came out to 116MB, 580,735 lines, with 57 CREATE TABLE statements (55 tables + 2 views).

Step 4: Import to ct0 and let converge handle the rest

With the logical dump in hand, the rest was straightforward. I imported it into ct0 (the primary):

mysql -u root -e "DROP DATABASE IF EXISTS kanboard; CREATE DATABASE kanboard CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
mysql -u root kanboard < /tmp/kanboard_restore.sql

Then I let the mariadb-converge script do its thing. This is a systemd timer that runs every 20 seconds on both containers… it handles primary election, replication setup via CHANGE MASTER TO ... MASTER_USE_GTID=slave_pos, and even split-brain detection. ct0 is deterministically elected as primary (hostname contains “ct0”), and ct1 configures itself as a replica.

Within 20 seconds, ct1 had picked up the replication and was syncing the kanboard database from ct0. No manual replication setup needed.

Step 5: Verification

What We Learned

• Physical backups and logical backups require completely different restore strategies. A mariadb-backup --copy-back replaces the entire data directory, including system tables. If you need to preserve existing user accounts, you can’t use copy-back directly… you need a sidecar approach: start a temp MariaDB from the prepared backup, mysqldump the specific database, and import that.
• Incremental backup chains must be applied in full sequence. You can’t skip intermediate incrementals and jump straight to the latest one. The LSN chain is strict: each incremental’s from_lsn must match the previous backup’s to_lsn. If you get error: This incremental backup seems not to be proper for the target, you’re missing intermediate backups in the chain.
• The mariadb-converge script is a lifesaver for replication setup. Once the primary has data, the replica picks it up automatically within 20 seconds via GTID. No manual CHANGE MASTER TO needed… the converge script handles primary election, replication configuration, and even split-brain detection.
• Bareos mariabackup plugin restores go to systemd private temp directories. The restored files landed in /tmp/systemd-private-*/tmp/bareos-restore/, which the mysql user couldn’t access. Had to copy the prepared backup to a standard temp directory before starting the sidecar MariaDB.
• Direct SCP between containers may not work depending on network isolation. The dump had to be relayed through the local machine (ct1 to local, local to ct0). Worth knowing if your container network segments don’t allow direct container-to-container file transfer.
• mysqld refuses to run as root. The temp MariaDB instance had to be started with sudo -u mysql mariadbd instead of just mysqld. Small thing, but it’ll trip you up if you’re not expecting it.