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 https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/ 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.