Should i update my selfhosted database? MariaDB

back everything up and do your update

simple

Database version upgrades can be tricky. Always make a proper backup before! Ideally you would make a db dump plus a copy of the db container data.

Typically smaller version upgrades are not a problem, for example updating from 10.9.4 to 10.9.5 shouldnt be a issue.

But major upgrades, like going from 9.6 to 10.2 can cause more problems and you should always pay attention to the release notes of a new version and also make sure that the software that makes use of that db is also still compatible with that new db version. For example it could be that Nextcloud says to stay on 10.9 and 11.0 is not yet supported.

Since databases are usually not configured so that anyone can connect to it, let alone the open internet, it is not too bad when you lag behind a few versions. Ideally when using Docker networking for example you would have a closed network that only connects the db with the actual app that needs the app, and nothing else can connect to that db.

You could:

Backup your database. Run a second docker container with new version database. Restore/import backed up database from old version into the new version.

And then try out if all works without errors.

If errors, you can just revert back to your initial docker container with the old version.

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