How to upgrade Mariadb 5.5 to Mariadb 10.0 on CentOS 7 / RHEL 7

In this tutorial we will learn, how to upgrade Mariadb 5.5 to Mariadb 10.0 on CentOS 7 / RHEL 7 . We will use yum command in this practical. We will also include preliminary exercises should be done while upgrading MariaDB 5.5 to MariaDB 10.0 .

MariaDB is a fork of MySQL Database. And now it is default database shipped with RHEL 7 / CentOS 7. At the time of writing this post, the CentOS 7 is providing MariaDB 5.5 version from its repo.

IMPORTANT! Recommended Reading before starting this practical : There are incompatible parameters has been listed in MariaDB site . Check the my.cnf and related configuration file in current MariaDB server 5.5.x before upgrading.

Description of Practical

Operating System : CentOS 7
Arch : x86_64
MariaDB Server : Version 5.5.40 upgraded to 10.0.15

We expect you have already installed MariaDB Server version 5.5 on CentOS 7 . We already written post on installing MariaDB 5.5 on CentOS 7

Take database backup before MariaDB upgrade from version 5.5.x to 10.0.x

1. Take the full backup of all databases.

mysqldump -u root -p --all-databases > AllDatabases.sql

2. We also recommend you to take backup of each databases. Replace database_name with actual database name.

mysqldump -u root -p database_name > database_name.sql

3. Take the backup of my.cnf file and my.cnf.d directory.
To take this backup you must be login as system root user.

Here, first we are taking backup of my.cnf file in same /etc path. You can select any other path for backup (Do not use /tmp directory for any backup).

cp -p  /etc/my.cnf /etc/my.cnf.5.5.x.orig

Take the backup of /etc/my.cnf.d/

cd ~
tar -cvzf my.cnf.d.tar.gz /etc/my.cnf.d/

4. Take backup of data directory of MariaDB. Default path is /var/lib/mysql/ . (Check correct datadir path in my.cnf file and replace in below given command. Help: grep datadir /etc/my.cnf)

cd ~
tar -cvzf lib_mysql.tar.gz /var/lib/mysql/

Now we have taken almost all important backups related to MariaDB Database.

Upgrading MariaDB from 5.5 to 10.0 version

Upgrading MariaDB 5.5.x version to 10.x version ,includes basic steps. Lets have a look on below given overview of steps.

1. Remove MariaDB 5.5.x packages by using yum command.
2. Installing MariaDB 10.x by using yum command.
3. Running mysql_upgrade command (Final step)

Now we will discuss more in below given sections step by step

Step 1 : Remove MariaDB 5.5.x packages by yum command

First check what are the MariaDB packages installed on your system. We will remove only those package with the help of yum.
In some system, the number of MariaDB packages vary depend upon how system admin earlier installed the MariaDB server.

Find out the MariaDB packages installed on your system and which will be removed by using yum command.

rpm -qa|grep  -i mariadb

Below given is reference from our system, we have only 3 packages installed related to MariaDB hence we will remove only these 3 packages.

[root@localhost ~]# rpm -qa|grep  -i mariadb
mariadb-server-5.5.40-1.el7_0.x86_64
mariadb-libs-5.5.40-1.el7_0.x86_64
mariadb-5.5.40-1.el7_0.x86_64
[root@localhost ~]#

Stop the MariaDB service

systemctl stop mariadb.service

Removing these packages by yum command. If you find more packages prefix with MariaDB, add the package name in below command.

yum remove mariadb-server mariadb-libs mariadb

When we were removing MariaDB 5.5.x version, postfix was also removed. We will later install postfix.

Below given is reference from our system

[root@localhost ~]# yum remove mariadb-server mariadb-libs mariadb
Loaded plugins: fastestmirror
Resolving Dependencies
--> Running transaction check
---> Package mariadb.x86_64 1:5.5.40-1.el7_0 will be erased
---> Package mariadb-libs.x86_64 1:5.5.40-1.el7_0 will be erased
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: perl-DBD-MySQL-4.023-5.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-6.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: perl-DBD-MySQL-4.023-5.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-6.el7.x86_64
---> Package mariadb-server.x86_64 1:5.5.40-1.el7_0 will be erased
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:4.023-5.el7 will be erased
---> Package postfix.x86_64 2:2.10.1-6.el7 will be erased
--> Finished Dependency Resolution

Dependencies Resolved

=================================================================================================================================================
 Package                              Arch                         Version                                 Repository                       Size
=================================================================================================================================================
Removing:
 mariadb                              x86_64                       1:5.5.40-1.el7_0                        @updates                         49 M
 mariadb-libs                         x86_64                       1:5.5.40-1.el7_0                        @updates                        4.4 M
 mariadb-server                       x86_64                       1:5.5.40-1.el7_0                        @updates                         55 M
Removing for dependencies:
 perl-DBD-MySQL                       x86_64                       4.023-5.el7                             @base                           323 k
 postfix                              x86_64                       2:2.10.1-6.el7                          @anaconda                        12 M

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

Installed size: 121 M
Is this ok [y/N]:

Step 2 : Installing MariaDB Server 10.x

In this section now we will install latest stable MariaDB version 10.x .
Check latest stable package from this MariaDB download page.

Create a yum repo file called mariadb.repo in /etc/yum.repos.d/ directory

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

copy-paste below given MariaDB repo information and save it.

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/rhel7-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Now run below given command to remove yum cached data. (Important)

yum clean all

Importing MariaDB signing key

rpm --import https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

Install MariaDB Server and client (version 10.x)

With the help of yum command install MariaDB server and client (version 10.x)

yum install MariaDB-server MariaDB-client MariaDB-devel MariaDB-shared

You can verify the MariaDB packages installed on system.

rpm -qa|grep -i maria

Below given is reference from our system.

[root@localhost ~]# rpm -qa|grep -i maria
MariaDB-common-10.0.15-1.el7.centos.x86_64
MariaDB-server-10.0.15-1.el7.centos.x86_64
MariaDB-devel-10.0.15-1.el7.centos.x86_64
MariaDB-client-10.0.15-1.el7.centos.x86_64
MariaDB-shared-10.0.15-1.el7.centos.x86_64
[root@localhost ~]#

Install postfix which was removed in Step 1

Installing postfix. (If not require, you can skip this step)

yum install postfix

Start MariaDB Service

Now start the MariaDB service.(Yes! service name is mysql)

systemctl start mysql

Run mysql_upgrade

This is a final command to upgrade. It require mysql root password.

mysql_upgrade -u root -p

The given below is reference from our system

[root@localhost ~]# mysql_upgrade -u root -p 
Enter password: 
Phase 1/5: Checking mysql database
Processing databases
mysql
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.gtid_slave_pos                               OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.index_stats                                  OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.table_stats                                  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
Phase 2/5: Running 'mysql_fix_privilege_tables'...
Phase 3/5: Fixing table and database names
Phase 4/5: Checking and upgrading tables
Processing databases
information_schema
linux
linux.Persons                                      OK
performance_schema
Phase 5/5: Running 'FLUSH PRIVILEGES'...
OK
[root@localhost ~]# 

We are also able to login successfully on MariaDB server. Given below is our system reference.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 16
Server version: 10.0.15-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux              |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> use linux;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [linux]> show tables;
+-----------------+
| Tables_in_linux |
+-----------------+
| Persons         |
+-----------------+
1 row in set (0.00 sec)

MariaDB [linux]> 


Posted

in

by

Comments

5 responses to “How to upgrade Mariadb 5.5 to Mariadb 10.0 on CentOS 7 / RHEL 7”

  1. Kaushik Singh Avatar
    Kaushik Singh

    Require latest regular update to my Emailid

    1. Rudra Avatar
      Rudra

      Hi Muhaimin,
      I have gone through the same frustration as you have, when I had worked with MariaDB 10 on CentOS 6 and CentOS 7. And I have failed to find a solution for the same. Now this issue with MariaDB seems to be present with RHEL/CentOS. Because, I do not see this on Debian ( at least I have not till now.) I replicated a site running on Centos 6 with mysql 5.x version in two machines in test lab. One was running CentOS 7 and other with Debian 7. When i upgraded to mariadb 10.0 on CentOS7. I faced almost similar errors as you have. But nothing as such happened in Debian 7. Also with respect to a fresh Mariadb Installation optimization, all the memory related parameters are defined in my.cnf of mariaDB 10.0 in debian7. But the same is not there in MariaDB 10.0 CentOS 7.

    2. Rudra Avatar
      Rudra

      Hello Again,
      As a workaround, I had did a fresh CentOS 7 Install on a seperate server. Installed MariaDB 10.0. After that I took a backup of the required database from the main server.
      “mysqldump -u root -p -QqeR –add-drop-table — database database_name > database_name.sql”
      And moved it to the new server
      “mysql -u root -p < database_name.sql"
      After that, I granted all privileges on the database to the database user.
      It has worked that way….

      Again I have to say that, On the first attempt mariaDB failed to start… similar errors to what you are talking… but on the second atttempt… it worked for me…. still not able to understand why it failed in the first attempt…

      Having said that… In Open Source World.. we live with these broken stuff at times…

      1. sharad chhetri Avatar

        Thanks Rudra for giving time and effort.
        I also reproduced the issue and successfully able to upgrade.
        I am waiting from Muhaimin to check the parameter he is using in MariaDB 5. Because some of parameter are incompatible.
        Hence, without his confirmation and sharing these parameter ,we can assure why this upgradation is giving issue.

        Regards
        Sharad

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: