How to upgrade mariadb on cyberpanel from 10.3 to 10.6 on ubuntu 20.04

In this guide, we shall indicate two methods of upgrading a database

Method 1: Automated upgrade script. The script will handle everything for you including backups.

Using the convenience script.

wget -qO- https://raw.githubusercontent.com/dannydev77/database_upgrade_utility_script/main/database_upgrade.sh | bash

Method 2: Manual – This section provide step by step actions.

Before you begin the upgrade ensure to take a backup of your data in case something goes wrong.

Steps:

1. Confirm Your current mariadb installation version by typing

mariadb --version
Output:

mariadb Ver 15.1 Distrib 10.3.39-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

2. Backup.
   Take a full database backup using either of the commands below.

Command one:

Create a directory to store the backups.

For my case
mkdir -p /home/dbs/databases

Backup using command below

mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" > /home/dbs/databases/"$dbname".sql; done
or
Command two:
mysqldump --events --all-databases | gzip > /home/alldatabases.sql.gz

3. Get your mariadb root password : Will be needed for Later Step.
   cat /etc/cyberpanel/mysqlPassword

4. Configure mariadb for proper shutdown by running command below within mariadb cli

SET GLOBAL innodb_fast_shutdown = 1;
XA RECOVER;
exit;

5. Stop the database server and Remove MariaDB
   

systemctl stop mariadb

confirm it is stopped with

systemctl status mariadb

The output is convoluted, but look for following lines
Active: inactive (dead) since Thu 2024-08-01 12:45:15 CEST; 11s ago
Status: “MariaDB server is down”

To confirm which packages will be removed, run

apt list --installed | grep -i -E "mariadb|galera"

To remove run,

apt remove "*mariadb*" "galera*" -y

To confirm all those packages were removed

apt list --installed | grep -i -E "mariadb|galera"

Output should be empty.

6. Install New Version of MariaDB
   run the following command, changing 10.6 for whichever version you would like to install. 10.11 is the latest LTS version.

curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-10.6"

Update the system

apt update -y

You should see an Output similar to the one below showing a repository for your new database has been added and updated.

Get:6 https://dlm.mariadb.com/repo/mariadb-server/10.6/repo/ubuntu focal InRelease [7,767 B]

Install mariadb

apt install mariadb-server libmariadb-dev -y

when prompted with this, enter N
Configuration file ‘/etc/mysql/mariadb.conf.d/50-server.cnf’
==> Modified (by you or by a script) since installation.
==> Package distributor has shipped an updated version.
What would you like to do about it ? Your options are:
Y or I : install the package maintainer’s version
N or O : keep your currently-installed version
D : show the differences between the versions
Z : start a shell to examine the situation
The default action is to keep your current version.
*** 50-server.cnf (Y/I/N/O/D/Z) [default=N] ?

run apt update && apt upgrade -y to update all packages again

apt update && apt upgrade -y

run apt list –installed | grep -i -E “mariadb|galera” to confirm which packages were installed

apt list --installed | grep -i -E "mariadb|galera"

enable the mariadb service

systemctl enable mariadb

Start the database server

systemctl start mariadb.service

7. Complete the upgrade.
   Run the mariadb-upgrade script utility Learn more here

mariadb-upgrade -u root -p --force
Enter the root password earlier copied.

You should see an output like below.

root@srv:~# mysql_upgrade -u root -p –force
Enter password:
Major version upgrade detected from 10.3.39-MariaDB to 10.6.18-MariaDB. Check required!
Phase 1/8: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK

Confirm by typing
systemctl status mariadb

If you still see some errors such as “Access denied for ‘root’@’localhost’, run the force upgrade command.

mariadb-upgrade --force

All checks must be OK with NO ERRORS!!!

Database upgrade completed successfully.
Database upgraded from version 10.3 to 10.6

Confirm you can now access the database by typing mysql or mariadb command.

You can confirm the installed version by either of the commands below.

SHOW GLOBAL VARIABLES LIKE 'version';

SELECT VERSION();

Done.