Kurt Keller's Blog

I have a strictly internal database server running MariaDB v10.5.20 on Fedora 38. This is an old installation and has not been updated in a long time. Both, the DB and Fedora have moved a few versions up.

So finally I’m moving to MariaDB v10.11.11 on Fedora 42. Since I have daily dumps of all databases created with

/usr/bin/mysqldump -A -Y \
    --add-drop-database --add-drop-table --add-drop-trigger \
    --comments --flush-logs --flush-privileges --lock-all-tables \
    --routines --events --dump-date 
        >/var/lib/mysqldump/$(date +%a)

that should be a piece of cake, or so I thought. During the restore of the databases on the new server, it complains that the table innodb_index_stats does not exist. Bah! I’m by no means a database specialist, whenever I have to do something with databases, I’m utterly lost without google. So a quick search brings me to a page which details how to do a full backup and restore with MariaDB. Let’s try this backup procedure on the old server:

mariadb-backup --backup \
   --target-dir=<backup_directory> \
   --user=<mariadb-backup> --password=<mypassword>

After copying the whole <destination directory> to the new server, a few steps are required there. First of all a preparation step.

mariadb-backup --prepare \
   --target-dir=<copied_backup_directory>

An error again (since I didn’t read the fine print). mariadb-backup complains that the backup has been made by an older version of mariadb-backup and this newer version can not process the files.

Turns out, I need to copy and use the mariadb-backup from the old server for this step. Crossing my fingers that this works and will not complain about things like missing libraries etc. I try this step with the old version of mariadb-backup and, yeah, this works. The remaining steps can be performed with mariadb-backup from the new server.

• stop MariaDB
• remove everything in the data directory of MariaDB (the place where the database files are kept; in my case /var/lib/mysql/)
• the actual restore needs to be run

  mariadb-backup --copy-back --target-dir=<copied_backup_directory>

• unless you ran the last command as the mysql user, permissions on everything in the data directory need to be fixed

  chown -R mysql:mysql /var/lib/mysql/

• now everything is restored and MariaDB can be started on the new server

Out of this experience I have added another weekly mysql cronjob which creates this type of backup and saves the required version of mariadb-backup together with the backup. My additional weekly cronjob looks like this:

/usr/bin/mariadb-backup --backup \
    --target-dir /var/lib/mysqldump/full_backup_$(date +%Y-%m-%d)/ \
    --user=<dump_user> --password=<dump_user_password> \
        2>>/var/log/mysqlfull_backup.log >&2
if [ "${?}" -ne "0" ]; then
    echo "error while running mariadb-backup"
else
    cp -p /usr/bin/mariadb-backup \
          /var/lib/mysqldump/full_backup_$(date +%Y-%m-%d)
    find /var/lib/mysqldump/ -type d \
                             -name full_backup_\* \
                             -mtime +1 | xargs -r rm -rf
fi

However, not all is good yet, unfortunately. My regular daily backups using mysqldump fail with

Couldn't execute 'show events': Cannot proceed, because event scheduler is disabled (1577)

Even though I found instructions on how to enable the scheduler, that did not help in any way. Turns out, after the update to the new mariadb version and the load of the data, I still have to tell mariadb to update the data to the new version with

mysql_upgrade --verbose

Finally everything working fine now?