astutesnoot

I just had a failed upgrade from MariaDB 10.6 to 11.1 and it was only my backup that saved me. I have a service that runs mariadb-dump every day and tars the resulting .sql file with a dated name (keeping only the previous 10 backups), so I was able to just start fresh with an empty 11.1 db and restore from yesterday's dump.

Here's my backup setup:

# /etc/systemd/system/backupdb.service
[Unit]
Description=auto backup mariadb daily

[Service]
Type=simple
ExecStart=/usr/bin/docker exec db /config/backupdb

# /etc/systemd/system/backupdb.timer
# enable with 'systemctl enable --now backupdb.timer'
[Unit]
Description=Periodically runs the backupdb service

[Timer]
OnCalendar=Daily
Persistent=true

[Install]
WantedBy=timers.target

#!/bin/bash

# /pool/config/db/backupdb
# runs within the context of the container
# /pool/config/db/ is bind mounted into /config in the container 
# backs up to /config/backup within container
# requires files /config/db_user and /config/db_pw to exist and be populated

mkdir -p /config/backup
cd /config/backup
BASENAME=db.backup.$(date "+%Y.%m.%d-%H.%M.%S")
/usr/bin/mariadb-dump -u $(cat /config/db_user) -p$(cat /config/db_pw) -x -A > $BASENAME.sql
/usr/bin/tar -czvf $BASENAME.tar.gz $BASENAME.sql
rm $BASENAME.sql
while (($(ls | wc -l) > 10))
do
rm $(ls -t | tail -1)
done

And this was how I restored with that setup.

cd /pool/config/mariadb/backup
tar xvf db.backup.2023.09.22-00.00.02.tar.gz
docker exec -it db bash
root@db:/# mariadb -u root -p < /config/backup/db.backup.2023.09.22-00.00.02.sql