Debian Stable: Upgrade MariaDB to Mysql8

So there are a other posts showing how to upgrade MariaDB to MySQL8 on Ubuntu etc.

This one is focused on upgrading from MariaDB on Debian Stable - aka Bullseye. June 2022.

Hopefully you found MySQL :: A Quick Guide to Using the MySQL APT Repository and understand how to get MySQL8 onto a Debian host. Basically, you install the community mysql-apt-config package, which will update your sources.list to find the MySQL8 Community repo with packages and security updates etc. This is way better than manually installing a specific version in a .deb file.

Before you Start

Don’t forget to catalogue everything - remember you have to upgrade the entire server, not just your Ghost databases. This includes all databases and users

echo "select concat(user,'@',host) from mysql.user" | sudo mysql
# users that will need recreation

alldb=$(echo "show databases" | sudo mysql | grep -Ev "^(Database|mysql|performance_schema|information_schema|sys)$");echo $alldb
# non-system databases to backup and re-create

Backup all Databases

Quiesce all your apps before you backup - including all instances of ghost - for me this is:

cd /var/www; for d in */; do echo $d; cd $d; ghost stop; cd ..; done; ghost ls

Backup commands that I used:

cd;alldb=$(echo "show databases" | sudo mysql | grep -Ev "^(Database|mysql|performance_schema|information_schema|sys)$")
sudo mysqldump --opt --dump-date --result-file=alldb-`date +%Y%m%d-%H%M%S`.sql --routines --events --single-transaction --verbose --databases $alldb

# check the content of the resulting .sql - esp last line, size etc.
tail alldb-20220625-151537.sql
grep 'CREATE DATABASE .* EXISTS' alldb-20220625-151537.sql

Remove MariaDB

I tried the in-place updates - there were issues that were hard to recover, including conflicting packages like mysql-common - and purge the config until your are ready to re-tune your new database server.

sudo dpkg -l | grep -Ei 'maria|mysql'
sudo dpkg --purge           \
 mariadb-client-10.5        \
 mariadb-common             \
 mariadb-server             \
 mariadb-server-10.5        \
 mariadb-server-core-10.5   \
 mysql-common
sudo mv /etc/mysql /etc/mysql.`date +%F`

# move the existing databases out of the way - hopefully you have enough space
sudo mv /var/lib/mysql /var/lib/mysql.`date +%F`

# This is critical - the purge above does NOT remove the systemd symlinks, 
# and the mysql-server install will fail at the last step
sudo rm /etc/systemd/system/mysql.service /etc/systemd/system/mysqld.service

Install MySQL 8

wget https://dev.mysql.com/get/mysql-apt-config_0.8.22-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.22-1_all.deb
sudo apt-get update
sudo apt-get install mysql-server

If you get this error: Failed to preset unit: File mysql.service: Link has been severed it’s because the MariaDB installation didn’t clean up the systemd symlinks.

# Only needed if your symlink has been severed
cd /etc/systemd/system
sudo rm mysql.service mysqld.service
sudo ln -s /lib/systemd/system/mysql.service
sudo ln -s /lib/systemd/system/mysql.service mysqld.service
sudo systemctl enable mysql
sudo systemctl start mysql
sudo systemctl status mysql

Restore Databases

First - make sure you can sudo mysql to get a root mysql prompt. MariaDB by default authenticated root by UID and socket. MySQL defaults to using a root password.
You can create a ~/.my.cnf that is only readable by root containing your password - google.

# restore databases
cat alldb-20220625-151537.sql | sudo mysql

# Did it work ok?
alldb=$(echo "show databases" | sudo mysql | grep -Ev "^(Database|mysql|performance_schema|information_schema|sys)$");echo $alldb

# recreate ALL your users e.g.
echo "create user ghost_hfp@localhost identified by'passwd-from-ghost-config';grant all on ghost_hfp.* to ghost_hfp@localhost" | sudo mysql

Restart services

For me, Ghost restarts are:

cd /var/www; for d in */; do echo $d; cd $d; ghost start; cd ..; done; ghost ls

Follow-up

You’re now ready to do the latest Ghost security updates ;)

The thing that seems to be missing from most other posts and searches is that MariaDB leaves the mysql systemd symlinks in place, and so the MySQL8 install fails to install the systemd unit.

Oh - and when it’s all stable - you have two folders you can rm - /etc/mysql.$DATE and /var/lib/mysql.$SDATE

Ciao.

1 Like