How to Upgrade from MariaDB 10.4 to MariaDB 10.5

MariaDB 10.5 was released as GA in June 2020. In the release, there has been added support for Amazon S3 or any third-party public or private cloud that supports S3 API. It also features sophisticated handling for privileges extending its granularity which enables a DBA for example to provide limited privileges on a particular database user for tight security of your database. 

MariaDB 10.5 also boasted its improvements with the InnoDB storage engine for its performance and some new variables are also presented but major deprecated variables have been marked deprecated or totally removed. For example, take note that in MariaDB 10.5, innodb_buffer_pool_instances has already been marked as deprecated while it is set to be removed in version 10.6. If you’re curious about whatever reason they say, please check out MDEV-15058. 

With all of these changes, it’s best to deliver this blog to provide a guide on how to upgrade MariaDB 10.4 to MariaDB 10.5. We’ll take it step-by-step on what are the things you need to consider for upgrading.

Things You Need Before Upgrading

It’s not always the best method to upgrade your database live in production without doing a test. This simple jargon defines the term we call SNAFU. You might hit Google to find the term but basically, it’s always best not to touch normal health especially the normally functioning systems. However, it’s not always that your system has to stay constant, it has to be upgraded to avail security patches, bug fixes, and advance features that are present on the newer version releases. So in this case, you always have a failback mechanism planned and setup ahead of the upgrade. In case upgrading the system caught up with issues that were left unobserve, then it can bring impact to your business.

Always Create a Backup of Your Database

In this case, always provide backup to your data. You can use tools such as mariabackup or mydumper or, if you are a ClusterControl user, then use the Database Backup Management tool. If you are not prepared yet on what type of backup you need, you might have to check the best practices when taking a backup.

Test…Test… and Test Again

While backup provides data to feed in case you need to restore to its primary state if unforeseen problems occur, upgrading to a major release has to be tested first into a development or staging machine. For large enterprise companies, it’s common practice to always do a regression test on a targeted QA environment or staging environment where the upgrading of the database servers to its major version has first to be applied. All systems from application and database have to proceed a regression test or series of QA testing until everything has passed. It’s not a good idea to simplify a test case of your application going to the database systems and just rule out that everything is fine as long as the database does not crash or it just has been proven for just a short amount of time where test is short, a very simple test which just covers a small percentage of your overall system. Testing your upgrade first on a staging or QA environment must be prioritized such that it has to achieve the perfectly good shape of your application without impacting the business side and also the users that are going to utilize your application, than realizing late that the database upgrade causes your system to behave abnormally due to changes you have not yet discovered.

Prepare A Restore Procedure

Everything has to be planned during the upgrade of your database. Whenever backup is available and testing reveals strong and promising results, it always feels secure and predictable in case unexpected challenges occur while upgrading your production MariaDB database servers. In this case, always write and prepare a procedure that makes things go back to normal smoothly and seamlessly. 

If your maintenance window is not too long, preparing a restore procedure using automated tools such as Ansible, Chef, Puppet, SaltStack, or Terraform can be a good choice for restore procedure. It minimizes human errors and provides speed and agility to perform vital tasks. Although it might damage in case a single error can be encountered if the automation script fails, then that also means you cannot ignore the possibility that this can happen. Therefore, this points also that restoring has to be seamless and has been tested properly that it shall be able to restore into a valid procedure.

MariaDB Upgrade Procedures

Upgrading your MariaDB version 10.4 to 10.5 is not a hassle yet a straightforward. Below are the steps you can follow to upgrade to the latest MariaDB 10.5 version.

Prepare Your Repository

It’s understandable that you have MariaDB 10.4, so it’s an assumption that there’s a present repository in your current MariaDB server nodes. Otherwise, you can add a repository anyway and that’s just simple. 

Ubuntu/Debian

For Ubuntu/Debian based systems, for an existing mariadb repository, you can edit the repository. You may be able to verify if the existing repositories are in your host or find if there’s an existing MariaDB repository somewhere. To do that, just,

$ grep ^[^#] /etc/apt/sources.list /etc/apt/sources.list.d/*

Typically, you have  a mariadb.list repository. In my setup in Ubuntu 18.0 (Bionic), this shows as the following:

root@debnode20:/vagrant# cat /etc/apt/sources.list.d/mariadb.list

deb [arch=amd64] http://ftp.osuosl.org/pub/mariadb/repo/10.4/ubuntu bionic main

Just run the following command line to add the MariaDB 10.5 repository,

 . /etc/os-release

sudo echo "deb [arch=amd64] http://ftp.osuosl.org/pub/mariadb/repo/10.5/${ID} ${VERSION_CODENAME}  main" >> /etc/apt/sources.list.d/mariadb.list

Before MariaDB packages can be installed,  it requires that the packages to be installed must be imported with GPG public key which is used to verify the digital signatures of the packages in their repository. You might check your apt keys with the following,

$ apt-key list |grep -C2 -i 'mariadb'

If keys aren’t imported, 

$ sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db

or for newer Ubuntu/Debian based versions i.e. starting with Debian 9 (Stretch), and Debian Unstable (Sid), and Ubuntu 16.04 LTS (Xenial),

$ sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8

Once done, just run

$ sudo apt update

CentOS/RHEL

For CentOS/RHEL, if you have an existing repository, you can just add or edit the file. Otherwise, adding the lines below for the MariaDB 10.5 repository will suffice the repository requirements (see mariadb.repo). For example, I have the following mariadb.repo in my CentOS 8.0 host.

[root@testnode30 ~]# cat /etc/yum.repos.d/mariadb.repo

[mariadb]

name = MariaDB Repository

baseurl = http://yum.mariadb.org/10.4/centos8-amd64

enabled = 1

gpgkey = https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck = 1



[mariadb_10.5]

name = MariaDB Repository For 10.5

baseurl = http://yum.mariadb.org/10.5/centos8-amd64

enabled = 1

gpgkey = https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck = 1

You can verify that if the MariaDB repository is enabled and works fine:

[root@testnode32 ~]# dnf --disablerepo=* --enablerepo=mariadb_10.5 repolist 

repo id                                repo name                                              status

mariadb_10.5                           MariaDB Repository For 10.5                            83

Upgrade Your MariaDB Packages

Upgrading with MariaDB is very straightforward. Make sure that you have shutdown the MariaDB server properly first.

For a busy and live production server, ensure that you have no incoming connections and that the dirty pages are properly flushed to disk. Before shutting down the server, you can set the flushing of dirty pages with your Innodb storage engine aggressively so that all dirty pages are all flushed and make shutdown process faster,

set global innodb_max_dirty_pages_pct = 0;

Then monitor the dirty pages with the following,

$ mysqladmin ext -i10 | grep dirty

| Innodb_buffer_pool_pages_dirty                         | 0                                                |

| Innodb_buffer_pool_bytes_dirty                         | 0                                                |

Once good, shutdown the MariaDB instance,

systemctl stop mariadb

For a master/replica database cluster, it’s a good practice to always start your upgrade on the replica(s). So before the upgrade and after the shutdown, make sure you have added the following in your my.cnf configuration file,

[mysqld]

….

skip-slave-start

This allows you to avoid automatically starting the replication threads when the MariaDB server is started. This gives you more safety and avoids further mistakes in the replication. Only start the replication threads manually once ready with the following statement,

START SLAVE;

Ubuntu/Debian

Upgrading with Ubuntu/Debian based systems is pretty straightforward,

sudo apt install --only-upgrade  mariadb-server mariadb-client mariadb-backup mariadb-common

Of course, do not supply with the -y option so you can review the following packages to be updated.

Centos/RHEL

Same as with Ubuntu/Debian based systems, the CentOS/RHEL also shows no hassle for upgrading your current MariaDB 10.4 version. You can run the following command below to suffice the process,

$ dnf --disablerepo=* --enablerepo=mariadb_10.5 upgrade Mariadb-server MariaDB-client MariaDB-backup MariaDB-common Mariadb-shared

Post Installation/Package Upgrade

Once the packages have been upgraded. SInce this is a major upgrade, do not forget to reload the daemon for systemd. Just run,

$ systemctl daemon-reload

Now that you’re set, start the mariadb service

$ systemctl start mariadb

 and run mysqld_upgrade,

$ mysql_upgrade 

While running the mysql_upgrade, always monitor the error log so you can catch any errors prior to running and starting everything for your normal operations:

tail -5f /var/log/mariadb/mariadb.log

Upgrade Tips for ClusterControl Users

Since ClusterControl does not provide major versions upgrade, when performing a package upgrade, always do not forget to turn off auto recovery modes for your MariaDB cluster. Set the nodes to maintenance mode so that alerts are silent and no false alerts to be notified.