How to upgrade for MariaDB 5.5 to 10.1 and then to 10.3

You might encounter a situation (e.g. on AWS) where you have a new CentOS 7 server, but an old version of MariaDB 5.5 on the server. A couple of reasons for upgrading is bug fixes, performance improvements, ease of upgrading, and the fact that emoji and JSON support only really work nicely in later versions of MariaDB. This article explains how to first upgrade from MariaDB 5.5 to 10.1, and then references an article on how to upgrade from 10.1 to 10.3.

You will need the MariaDB root password to follow the procedure.

Disclaimer: Virtualmin Issues with Menus and Postfix

If you’re upgrading MariaDB on a Virtualmin server, see the note below because this procedure will remove some key components that could make Virtualmin hosted servers menu disappear. Also be sure to back up Postfix /etc/postfix/master.cf and /etc/postfix/main.cf because Postfix might loose it configuration. See comments by Carlos Miguel Hernandez Gil.

Procedure

First make a backup of your data using method 1 or method 2 described below.

Method 1 backup (best way as MariaDB stays online during the backup):

mysqldump -u root -p --all-databases > /tmp/all-database.sql

Method 2 backup (offline backup):

systemctl stop mariadb
cp -a /var/lib/mysql/ /var/lib/mysql.bak

Method 2 backup will leave a folder /var/lib/mysql.bak that you have to remember to delete later on.

Update packages

yum update

Add the 10.1 Repo

vi /etc/yum.repos.d/MariaDB10.repo

# MariaDB 10.1 CentOS repository list - created 2016-01-18 09:58 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Remove the old MariaDB and do yum cleanup. Don’t worry, you made a backup right?

yum remove mariadb-server mariadb mariadb-libs
yum clean all

Add MariaDB again, start it up, enable automatic startup, and finalise the upgrade. Check new version.

yum -y install MariaDB-server MariaDB-client
systemctl start mariadb
systemctl enable mariadb
mysql_upgrade -uroot -p
mysql -V

A critical command to watch above is the mysql_upgrade command as if you accidentally skip it then you will have (recoverable) problems later on.

Multiple references were followed to compile article article.

Reference #1:

How to Upgrade MariaDB 5.5 to MariaDB 10.1 on CentOS/RHEL 7 and Debian Systems

On you are on version 10.1, follow the instructions in Reference article #2 below:

Reference #2:
https://www.rootusers.com/how-to-upgrade-mariadb-version-10-1-to-10-3/

Summary of reference #2 instructions to go from 10.1 to 10.3 (without the BACK UP parts):

vi /etc/yum.repos.d/MariaDB10.repo
yum update
systemctl stop mariadb
yum remove MariaDB-server
yum install MariaDB-server
systemctl start mariadb
mysql_upgrade -u root -p
mysql -V

And if you’re running Virtualmin:

yum install perl-DBD-MySQL php-mysql postfix postgrey

Additional References:
https://mariadb.com/kb/en/library/upgrading/

Virtualmin Note:

When performing this procedure on a Virtualmin server, you will notice the following when removing MariaDB indicting key components for Virtualmin going away:

Dependencies Resolved

=========================================================================================================================================
Package Arch Version Repository Size
=========================================================================================================================================
Removing:
mariadb x86_64 1:5.5.60-1.el7_5 @base 49 M
mariadb-libs x86_64 1:5.5.60-1.el7_5 @base 4.4 M
mariadb-server x86_64 1:5.5.60-1.el7_5 @base 58 M
Removing for dependencies:
mariadb-devel x86_64 1:5.5.60-1.el7_5 @base 3.3 M
net-snmp x86_64 1:5.7.2-37.el7 @base 882 k
perl-DBD-MySQL x86_64 4.023-6.el7 @base 323 k
postfix x86_64 2:2.10.1-7.el7 @base 12 M
postgrey noarch 1.34-12.el7 @epel 104 k

Transaction Summary
=========================================================================================================================================
Remove 3 Packages (+5 Dependent packages)

The effects of removing Postfix will mean that Virtualmin’s menu list of your virtual servers will be blank. To get that back, reinstall the following packages:

yum install postfix
yum install postgrey
yum install perl-DBD-MySQL
yum install net-snmp (if you used it before)