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]>