How to Upgrade MariaDB 5.5 to MariaDB 10.1 on CentOS 7 Linux Systems

In this post, we will show you how to Upgrade MariaDB 5.5 to MariaDB 10.1 on Linux systems (RPM Family “Redhat /CentOS /Scientific Linux 7 and newer releases of Fedora ”). This tutorial will help you to upgrade the default  shipped MariaDB 5.5 with CentOS/RHEL  7 to The latest stable MariaDB version at the time of writing this article “which is 10.1.23” on CentOS, RHEL and Fedora Systems.

Why I needed to do this upgrade?

I was creating a new table in my existing database on MariaDB 5.5, this table has two columns that causes  errors and prevented the new table creation. In short, I needed to use DATETIME data type with CURRENT_TIMESTAMP in my new table that uses InnoDB engine,  this feature is supported at MariaDB 10.0.1 and higher. Also, one column uses a FULLTEXT index on a TEXT column, which is supported at MariaDB 10.0.5 and higher.

So I decided to upgrade my MariaDB 5.5 To the latest stable version “MariaDB 10.1.23” and not MariaDB 10.0.5 as the latest version will has more new features.

In a simple steps I’ll show you how to safely do this upgrade, let’s start

Step 1: Take a full database backup for all databases

This is the first step you must do, for your safety you must take a backup of all databases on your server or at least the live databases, run the following command to take a backup of all databases:

# mysqldump -uroot -p --all-databases | gzip  > Server_Databases.sql.gz

As you see, the above command will save me from any errors during the upgrade process.

For more info about other backup and restore techniques check this post:

Backup and Restore MySQL/MariaDB Databases

Step 2 : Install the MariadDB repo

Now, we’ll install MariaDB 10.1 on our CentOS 7 machine, run the following command:

# cat << EOL > /etc/yum.repos.d/mariadb-10.1-centos.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOL

Now, we check on our enabled repo, run the following command:

# yum repolist
Loaded plugins: fastestmirror
base | 3.6 kB 00:00:00 
epel/x86_64/metalink | 20 kB 00:00:00 
epel | 4.3 kB 00:00:00 
extras | 3.4 kB 00:00:00 
mariadb | 2.9 kB 00:00:00 
updates | 3.4 kB 00:00:00 
(1/4): epel/x86_64/updateinfo | 791 kB 00:00:00 
(2/4): epel/x86_64/primary_db | 4.7 MB 00:00:00 
(3/4): updates/7/x86_64/primary_db | 4.8 MB 00:00:00 
(4/4): mariadb/primary_db | 19 kB 00:00:00 
Determining fastest mirrors
 * base: centos.datente.com
 * epel: mirrors.n-ix.net
 * extras: mirror.tobias-wollmann.com
 * updates: centosmirror.netcup.net
repo id repo name status
base/7/x86_64 CentOS-7 - Base 9,363
epel/x86_64 Extra Packages for Enterprise Linux 7 - x86_64 11,585
extras/7/x86_64 CentOS-7 - Extras 337
mariadb MariaDB 16
updates/7/x86_64 CentOS-7 - Updates 1,577
repolist: 22,878

Good, our new repo is enabled.

Step 3: Stop your Current Database Server

We must stop the running MariaDB server before installing the new one, run the following command:

# systemctl stop mariadb

Now, it’s the time for the upgrading step.

Step 4: Install MariaDB 10.1 from the new repo

Run the following command to install the new MariaDB version and remove the old one “5.5 in our case”:

# yum -y install MariaDB-server MariaDB-client

Here’s the sample output:

Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: centos.datente.com
 * epel: mirrors.n-ix.net
 * extras: mirror.tobias-wollmann.com
 * updates: centosmirror.netcup.net
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-client.x86_64 0:10.1.23-1.el7.centos will be obsoleting
--> Processing Dependency: MariaDB-common for package: MariaDB-client-10.1.23-1.el7.centos.x86_64
---> Package MariaDB-server.x86_64 0:10.1.23-1.el7.centos will be obsoleting
--> Processing Dependency: galera for package: MariaDB-server-10.1.23-1.el7.centos.x86_64
--> Processing Dependency: lsof for package: MariaDB-server-10.1.23-1.el7.centos.x86_64
--> Processing Dependency: libjemalloc.so.1()(64bit) for package: MariaDB-server-10.1.23-1.el7.centos.x86_64
---> Package mariadb.x86_64 1:5.5.52-1.el7 will be obsoleted
---> Package mariadb-server.x86_64 1:5.5.52-1.el7 will be obsoleted
--> Running transaction check
---> Package MariaDB-common.x86_64 0:10.1.23-1.el7.centos will be installed
---> Package galera.x86_64 0:25.3.20-1.rhel7.el7.centos will be installed
--> Processing Dependency: openssl for package: galera-25.3.20-1.rhel7.el7.centos.x86_64
--> Processing Dependency: libboost_program_options.so.1.53.0()(64bit) for package: galera-25.3.20-1.rhel7.el7.centos.x86_64
---> Package jemalloc.x86_64 0:3.6.0-1.el7 will be installed
---> Package lsof.x86_64 0:4.87-4.el7 will be installed
--> Running transaction check
---> Package boost-program-options.x86_64 0:1.53.0-26.el7 will be installed
---> Package openssl.x86_64 1:1.0.1e-60.el7_3.1 will be installed
--> Processing Dependency: make for package: 1:openssl-1.0.1e-60.el7_3.1.x86_64
--> Running transaction check
---> Package make.x86_64 1:3.82-23.el7 will be installed
--> Processing Conflict: MariaDB-common-10.1.23-1.el7.centos.x86_64 conflicts mariadb-libs < 1:10.1.23-1.el7.centos
--> Restarting Dependency Resolution with new changes.
--> Running transaction check
---> Package MariaDB-shared.x86_64 0:10.1.23-1.el7.centos will be obsoleting
---> Package mariadb-libs.x86_64 1:5.5.52-1.el7 will be obsoleted
--> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================================================================
 Package Arch Version Repository Size
=======================================================================================================================================
Installing:
 MariaDB-client x86_64 10.1.23-1.el7.centos mariadb 39 M
 replacing mariadb.x86_64 1:5.5.52-1.el7
 MariaDB-server x86_64 10.1.23-1.el7.centos mariadb 101 M
 replacing mariadb-server.x86_64 1:5.5.52-1.el7
 MariaDB-shared x86_64 10.1.23-1.el7.centos mariadb 1.3 M
 replacing mariadb-libs.x86_64 1:5.5.52-1.el7
Installing for dependencies:
 MariaDB-common x86_64 10.1.23-1.el7.centos mariadb 123 k
 boost-program-options x86_64 1.53.0-26.el7 base 156 k
 galera x86_64 25.3.20-1.rhel7.el7.centos mariadb 8.0 M
 jemalloc x86_64 3.6.0-1.el7 epel 105 k
 lsof x86_64 4.87-4.el7 base 331 k
 make x86_64 1:3.82-23.el7 base 420 k
 openssl x86_64 1:1.0.1e-60.el7_3.1 updates 713 k

Transaction Summary
=======================================================================================================================================
Install 3 Packages (+7 Dependent packages)

Total download size: 152 M
Downloading packages:
warning: /var/cache/yum/x86_64/7/mariadb/packages/MariaDB-10.1.23-centos7-x86_64-common.rpm: Header V4 DSA/SHA1 Signature, key ID 1bb943db: NOKEY
Public key for MariaDB-10.1.23-centos7-x86_64-common.rpm is not installed
(1/10): MariaDB-10.1.23-centos7-x86_64-common.rpm | 123 kB 00:00:00 
(2/10): MariaDB-10.1.23-centos7-x86_64-client.rpm | 39 MB 00:00:05 
(3/10): MariaDB-10.1.23-centos7-x86_64-shared.rpm | 1.3 MB 00:00:00 
(4/10): boost-program-options-1.53.0-26.el7.x86_64.rpm | 156 kB 00:00:00 
(5/10): make-3.82-23.el7.x86_64.rpm | 420 kB 00:00:00 
(6/10): lsof-4.87-4.el7.x86_64.rpm | 331 kB 00:00:00 
(7/10): openssl-1.0.1e-60.el7_3.1.x86_64.rpm | 713 kB 00:00:00 
(8/10): MariaDB-10.1.23-centos7-x86_64-server.rpm | 101 MB 00:00:06 
(9/10): galera-25.3.20-1.rhel7.el7.centos.x86_64.rpm | 8.0 MB 00:00:01 
(10/10): jemalloc-3.6.0-1.el7.x86_64.rpm | 105 kB 00:00:01 
---------------------------------------------------------------------------------------------------------------------------------------
Total 20 MB/s | 152 MB 00:00:07 
Retrieving key from https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
Importing GPG key 0x1BB943DB:
 Userid : "MariaDB Package Signing Key <package-signing-key@mariadb.org>"
 Fingerprint: 1993 69e5 404b d5fc 7d2f e43b cbcb 082a 1bb9 43db
 From : https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
 Installing : MariaDB-common-10.1.23-1.el7.centos.x86_64 1/13 
 Installing : MariaDB-client-10.1.23-1.el7.centos.x86_64 2/13 
 Installing : boost-program-options-1.53.0-26.el7.x86_64 3/13 
 Installing : lsof-4.87-4.el7.x86_64 4/13 
 Installing : jemalloc-3.6.0-1.el7.x86_64 5/13 
 Installing : 1:make-3.82-23.el7.x86_64 6/13 
 Installing : 1:openssl-1.0.1e-60.el7_3.1.x86_64 7/13 
 Installing : galera-25.3.20-1.rhel7.el7.centos.x86_64 8/13 
 Installing : MariaDB-server-10.1.23-1.el7.centos.x86_64 9/13 
warning: /etc/my.cnf.d/server.cnf created as /etc/my.cnf.d/server.cnf.rpmnew
 Installing : MariaDB-shared-10.1.23-1.el7.centos.x86_64 10/13 
 Erasing : 1:mariadb-server-5.5.52-1.el7.x86_64 11/13 
warning: /var/log/mariadb/mariadb.log saved as /var/log/mariadb/mariadb.log.rpmsave
 Erasing : 1:mariadb-5.5.52-1.el7.x86_64 12/13 
 Erasing : 1:mariadb-libs-5.5.52-1.el7.x86_64 13/13 
 Verifying : 1:make-3.82-23.el7.x86_64 1/13 
 Verifying : 1:openssl-1.0.1e-60.el7_3.1.x86_64 2/13 
 Verifying : jemalloc-3.6.0-1.el7.x86_64 3/13 
 Verifying : MariaDB-common-10.1.23-1.el7.centos.x86_64 4/13 
 Verifying : lsof-4.87-4.el7.x86_64 5/13 
 Verifying : MariaDB-client-10.1.23-1.el7.centos.x86_64 6/13 
 Verifying : boost-program-options-1.53.0-26.el7.x86_64 7/13 
 Verifying : MariaDB-shared-10.1.23-1.el7.centos.x86_64 8/13 
 Verifying : galera-25.3.20-1.rhel7.el7.centos.x86_64 9/13 
 Verifying : MariaDB-server-10.1.23-1.el7.centos.x86_64 10/13 
 Verifying : 1:mariadb-5.5.52-1.el7.x86_64 11/13 
 Verifying : 1:mariadb-server-5.5.52-1.el7.x86_64 12/13 
 Verifying : 1:mariadb-libs-5.5.52-1.el7.x86_64 13/13

Installed:
 MariaDB-client.x86_64 0:10.1.23-1.el7.centos MariaDB-server.x86_64 0:10.1.23-1.el7.centos 
 MariaDB-shared.x86_64 0:10.1.23-1.el7.centos

Dependency Installed:
 MariaDB-common.x86_64 0:10.1.23-1.el7.centos boost-program-options.x86_64 0:1.53.0-26.el7 galera.x86_64 0:25.3.20-1.rhel7.el7.centos
 jemalloc.x86_64 0:3.6.0-1.el7 lsof.x86_64 0:4.87-4.el7 make.x86_64 1:3.82-23.el7 
 openssl.x86_64 1:1.0.1e-60.el7_3.1

Replaced:
 mariadb.x86_64 1:5.5.52-1.el7 mariadb-libs.x86_64 1:5.5.52-1.el7 mariadb-server.x86_64 1:5.5.52-1.el7

Complete!

As you see, old MariaDB version 5.5 replaced with version 10.1

Step 5: Enable and Start your New Database Server

we need to enable the MariaDB10.1, run the following commands:

# systemctl enable mariadb
Created symlink from /etc/systemd/system/mysql.service to /usr/lib/systemd/system/mariadb.service.
Created symlink from /etc/systemd/system/mysqld.service to /usr/lib/systemd/system/mariadb.service.
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

And run the following command to start the MariaDB server:

# systemctl restart mariadb

Now, I’ll check on the status of the new running MariaDB server, run the following command:

# systemctl status mariadb
● mariadb.service - MariaDB database server
 Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
 Drop-In: /etc/systemd/system/mariadb.service.d
 └─migrated-from-my.cnf-settings.conf
 Active: active (running) since Sat 2017-05-06 13:27:47 EET; 5s ago
 Process: 14192 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Process: 14134 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
 Process: 14132 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 14164 (mysqld)
 Status: "Taking your SQL requests now..."
 CGroup: /system.slice/mariadb.service
 └─14164 /usr/sbin/mysqld

May 06 13:27:47 mimastech_vps_cx10 mysqld[14164]: 2017-05-06 13:27:47 140230461757696 [Note] InnoDB: Waiting for purge to start
May 06 13:27:47 mimastech_vps_cx10 mysqld[14164]: 2017-05-06 13:27:47 140230461757696 [Note] InnoDB: Percona XtraDB (http://w...739309
May 06 13:27:47 mimastech_vps_cx10 mysqld[14164]: 2017-05-06 13:27:47 140230461757696 [Note] Plugin 'FEEDBACK' is disabled.
May 06 13:27:47 mimastech_vps_cx10 mysqld[14164]: 2017-05-06 13:27:47 140229902042880 [Note] InnoDB: Dumping buffer pool(s) no...tarted
May 06 13:27:47 mimastech_vps_cx10 mysqld[14164]: 2017-05-06 13:27:47 140230461757696 [Note] Server socket created on IP: '127.0.0.1'.
May 06 13:27:47 mimastech_vps_cx10 mysqld[14164]: 2017-05-06 13:27:47 140230461757696 [ERROR] Missing system table mysql.roles...ate it
May 06 13:27:47 mimastech_vps_cx10 mysqld[14164]: 2017-05-06 13:27:47 140230461303552 [Warning] Failed to load slave replicati... exist
May 06 13:27:47 mimastech_vps_cx10 mysqld[14164]: 2017-05-06 13:27:47 140230461757696 [Note] /usr/sbin/mysqld: ready for connections.
May 06 13:27:47 mimastech_vps_cx10 mysqld[14164]: Version: '10.1.23-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 ...Server
May 06 13:27:47 mimastech_vps_cx10 systemd[1]: Started MariaDB database server.
Hint: Some lines were ellipsized, use -l to show in full.

From the above output, we see that we’ve an error and a warning. Simply our server health not good, we missing one final step to remove this error.

Step 6: Run mysql_upgrade command:

We need to run mysql_upgrade command on CentOS/RHEL Linux, as  Red Hat, CentOS, and Fedora packages do not upgrade the existing files in the installation process.
mysql_upgrade does two things:

• Upgrades the permission tables in the mysql database with some new fields.
• Does a very quick check of all tables and marks them as compatible with MariaDB 10.0

In most cases this should be a fast operation (depending of course on the number of tables)

Run the following command “you’ll need the old version root password”:

# mysql_upgrade -uroot -p
Enter password:

Here’s a sample output:

Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Upgrading from a version before MariaDB-10.1
Phase 2/7: Installing used storage engines
Checking for tables with unknown storage engine
Phase 3/7: Fixing views
Phase 4/7: Running 'mysql_fix_privilege_tables'
Phase 5/7: Fixing table and database names
Phase 6/7: Checking and upgrading tables
Processing databases
information_schema
mimastech.com

......

performance_schema
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK

Step 7: Final check on our server status

For the above command to take effect, we must restart our MariaDB server, run the following command:

# systemctl restart mariadb

Now. we re-check on the status of the server to see if the previous error has been removed, run the following command:

# systemctl status mariadb
● mariadb.service - MariaDB database server
 Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
 Drop-In: /etc/systemd/system/mariadb.service.d
 └─migrated-from-my.cnf-settings.conf
 Active: active (running) since Sat 2017-05-06 13:33:08 EET; 3s ago
 Process: 14510 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Process: 14452 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
 Process: 14450 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 14482 (mysqld)
 Status: "Taking your SQL requests now..."
 CGroup: /system.slice/mariadb.service
 └─14482 /usr/sbin/mysqld

May 06 13:33:08 mimastech_vps_cx10 mysqld[14482]: 2017-05-06 13:33:08 139686031268096 [Note] InnoDB: Highest supported file fo...acuda.
May 06 13:33:08 mimastech_vps_cx10 mysqld[14482]: 2017-05-06 13:33:08 139686031268096 [Note] InnoDB: 128 rollback segment(s) a...ctive.
May 06 13:33:08 mimastech_vps_cx10 mysqld[14482]: 2017-05-06 13:33:08 139686031268096 [Note] InnoDB: Waiting for purge to start
May 06 13:33:08 mimastech_vps_cx10 mysqld[14482]: 2017-05-06 13:33:08 139686031268096 [Note] InnoDB: Percona XtraDB (http://w...889436
May 06 13:33:08 mimastech_vps_cx10 mysqld[14482]: 2017-05-06 13:33:08 139686031268096 [Note] Plugin 'FEEDBACK' is disabled.
May 06 13:33:08 mimastech_vps_cx10 mysqld[14482]: 2017-05-06 13:33:08 139685472995072 [Note] InnoDB: Dumping buffer pool(s) no...tarted
May 06 13:33:08 mimastech_vps_cx10 mysqld[14482]: 2017-05-06 13:33:08 139686031268096 [Note] Server socket created on IP: '127.0.0.1'.
May 06 13:33:08 mimastech_vps_cx10 mysqld[14482]: 2017-05-06 13:33:08 139686031268096 [Note] /usr/sbin/mysqld: ready for connections.
May 06 13:33:08 mimastech_vps_cx10 mysqld[14482]: Version: '10.1.23-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 ...Server
May 06 13:33:08 mimastech_vps_cx10 systemd[1]: Started MariaDB database server.
Hint: Some lines were ellipsized, use -l to show in full.

Great; all error and warning are removed now after restart our server. Now enjoy using the latest version of MariaDB which satisfy our needs for the DATETIME and FULLTEXT search.

Hint:

1.  If you added any setting on the old MariaDB version, you'll find those settings exists on the new version unless the directive removed or renamed, which will cause error on restarting the new server, this didn't happen with us her.