☰
✕
✕

How to upgrade from MariaDB 5.5 to MariaDB 10.1

To upgrade from MariaDB 5.5 to MariaDB 10.1

In this article, we will learn how to Upgrade MariaDB 5.5 to MariaDB 10.1 on CentOS/RHEL 7 and Debian Systems.

Features of MariaDB

  • Plugins update.
  • Security fixes (Several vulnerabilities are addressed).
  • Galera, a multi-master cluster solution is included as a standard part of MariaDB.
  • Two new information schema tables have been added for examining wsrep information. The tables in question are WSREP_MEMBERSHIP and WSREP_STATUS.
  • Page compression is quiet similar to InnoDB COMPRESSED storage format.
  • Page compression for FusionIO.
  • Avoid creating .frm files for temporary tables.
  • Use the MAX_STATEMENT_TIME in order to exclude long running queries.
  • malloc() function is employed minimally and simple queries are executed quickly.
  • Webscale patches.

Dumping all MariaDB Databases

Use the below command to dump the databases

[user1@linuxhelp Desktop]$ mysqldump -u root -p --all-databases >  database-backup.sql

Or stop the MariaDB service and make a copy of the database directory

[root@linuxhelp Desktop]# /etc/init.d/mysql stop
Shutting down MySQL. SUCCESS!

And copy the databases directory in a separate folder.

[root@linuxhelp Desktop]# cp -ar /var/lib/mysql/ mysql-backup

2. To rename or move the old mariadb.repo file

After copying, now rename or remove the old mariadb.repo file if present.
Here I’ m renaming the mariadb.repo file.

[root@linuxhelp Desktop]# mv /etc/yum.repos.d/MariaDB.repo /etc/yum.repos.d/MariaDB5

3. To Add the MariaDB Repository

Check if your packages are up to date by doing the following:

On Debian/Ubuntu

# apt-get update

On RHEL/CentOS 7

[root@linuxhelp Desktop]# yum update

Wait for the installation to finish, if you have any old packages.

For Debian and Ubuntu
Run the below commands to add the MariaDB PPA on your machine:

# apt-get install software-properties-common
# apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
# add-apt-repository ' deb [arch=amd64,i386] http://kartolo.sby.datautama.net.id/mariadb/repo/10.1/ubuntu wily main'

Replace the Ubuntu wily with your system’ s distribution name and release.

On RHEL/CentOS

[root@linuxhelp Desktop]# vim /etc/yum.repos.d/MariaDB10.repo

Add the following text in it

# MariaDB 10.1 CentOS repository list &ndash  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

Then save and exit the file.

4. To Remove MariaDB 5.5

Run the below command for removing the existing MariaDB installation.

On Debian/Ubuntu

# apt-get purge mariadb-server mariadb mariadb-libs

On RHEL/CentOS 

[root@linuxhelp Desktop]# yum remove mariadb-server mariadb-libs mariadb-client 
Dependencies Resolved
=====================================================================================
Package                       Arch                  Version                            Repository                Size
=====================================================================================
Removing:
 MariaDB-server                x86_64                5.5.48-1.el7.centos                @mariadb                 171 M
 MariaDB-shared                x86_64                5.5.48-1.el7.centos                @mariadb                 5.1 M
Removing for dependencies:
 postfix                       x86_64                2:2.10.1-6.el7                     @anaconda                 12 M

Transaction Summary
=====================================================================================
Remove  2 Packages (+1 Dependent package)
Installed size: 188 M
Is this ok [y/N]: y
.
.
.
Verifying  : 2:postfix-2.10.1-6.el7.x86_64                                                                       3/3 
Removed:
  MariaDB-server.x86_64 0:5.5.48-1.el7.centos                MariaDB-shared.x86_64 0:5.5.48-1.el7.centos               
Dependency Removed:
  postfix.x86_64 2:2.10.1-6.el7                                                                                        
Complete!

5. To Clean the repository cache, execute the below command line

On Debian/Ubuntu

# apt-get clean all

On RHEL/CentOS 7

[root@linuxhelp Desktop]# yum clean all    
Loaded plugins: fastestmirror, langpacks
Cleaning repos: base extras mariadb updates
Cleaning up everything
Cleaning up list of fastest mirrors

To Install MariaDB 10.1

To run the following command to install the new version of MariaDB.

On Debian/Ubuntu

# apt-get install mariadb-server MariaDB-client

On RHEL/CentOS 7

[root@linuxhelp Desktop]# yum install MariaDB-server MariaDB-client  
Loaded plugins: fastestmirror, langpacks
base                                                                                            | 3.6 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/5): base/7/x86_64/group_gz                                                                   | 155 kB  00:00:01     
(2/5): mariadb/primary_db                                                                       |  18 kB  00:00:02     
(3/5): extras/7/x86_64/primary_db                                                               | 117 kB  00:00:06     
(4/5): base/7/x86_64/primary_db                                                                 | 5.3 MB  00:00:14     
(5/5): updates/7/x86_64/primary_db                                                              | 3.9 MB  00:00:35     
Determining fastest mirrors
 * base: centos.excellmedia.net
 * extras: centos.excellmedia.net
 * updates: centos.excellmedia.net
Resolving Dependencies
-->  Running transaction check
--->  Package MariaDB-client.x86_64 0:5.5.48-1.el7.centos will be updated
--->  Package MariaDB-client.x86_64 0:10.1.13-1.el7.centos will be an update.
.
.
.
  Verifying  : MariaDB-server-10.1.13-1.el7.centos.x86_64                                                     3/5 
  Verifying  : boost-program-options-1.53.0-25.el7.x86_64                                                     4/5 
  Verifying  : MariaDB-client-10.1.13-1.el7.centos.x86_64                                                     5/5 
Installed:
  MariaDB-client.x86_64 0:10.1.13-1.el7.centos            MariaDB-server.x86_64 0:10.1.13-1.el7.centos           
Dependency Installed:
  boost-program-options.x86_64 0:1.53.0-25.el7             galera.x86_64 0:25.3.15-1.rhel7.el7.centos            
  jemalloc.x86_64 0:3.6.0-1.el7                           
Complete!

7. To start the MariaDB service

[root@linuxhelp Desktop]# /etc/init.d/mysql start
Starting mysql (via systemctl):                            [  OK  ]

8. To upgrade MariaDB execute the upgrade command

Run the following command only if the MySQL password is set. Otherwise just give “ mysql_upgrade” .

[root@linuxhelp Desktop]# mysql_upgrade -u root -p
Enter password: 
Phase 1/6: 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
Phase 2/6: Fixing views
Phase 3/6: Running ' mysql_fix_privilege_tables' 
Phase 4/6: Fixing table and database names
Phase 5/6: Checking and upgrading tables
Processing databases
information_schema
performance_schema
Phase 6/6: Running ' FLUSH PRIVILEGES' 
OK

Execute the below command, to start MariaDB automatically after your computer boot.

[root@linuxhelp Desktop]# systemctl enable mariadb

9. To execute the below command to verify the upgrade

[root@linuxhelp Desktop]# mysql -V
mysql  Ver 15.1 Distrib 10.1.13-MariaDB, for Linux (x86_64) using readline 5.1

10. To Restore the database dump to the MariaDB10.1

First we have to check the present database,

[root@linuxhelp Desktop]# mysqlshow -u root -p
Enter password: 
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |

Here we are going to inject the database backup.

[root@linuxhelp Desktop]# mysql -u root -p <  database-backup.sql 
Enter password:
[root@linuxhelp Desktop]# 
Now check the database
[root@linuxhelp Desktop]# mysqlshow -u root -p
Enter password: 
+--------------------+
|     Databases      |
+--------------------+
| LINUXHELP_SUPPORT  |
| information_schema |
| linuxhelp          |
| mysql              |
| performance_schema |
+--------------------+
Tag : MariaDB
FAQ
Q
How to install "Galera cluster" on CentOS?
A
The following tutorial will explain the installation and configuration of the "Galera cluster" on CentOS as "https://www.linuxhelp.com/how-to-install-and-configure-galera-mysql-cluster-on-centos-7/"
Q
How can I Import Only a Table's Structure?
A
The easiest way to import only the structure of databases and tables is to export only that, and not the data. You can use mysqldump to export your database with --no-data option. Many GUI clients have similar options. Importing schema from other database systems, though, is more difficult and may not be possible.
Q
Why is the Software Called MariaDB?
A
The 'MySQL' name is trademarked by Oracle, and they have chosen to keep that trademark to themselves. The name MySQL (just like the MyISAM storage engine) comes from Monty's first daughter "My". MariaDB continues this tradition by being named after his younger daughter, Maria.
Q
How does the Password Prompt appear before or after establishing a connection?
A
You can refer the following official link of MariaDB to does the Password Prompt appear before or after establishing a connection as "https://mariadb.com/kb/en/library/does-the-password-prompt-appear-before-or-after-establishing-a-connection/"
Q
How can I Learn about Developing MariaDB?
A
If you want to learn about the developing MariaDB, please refer the following link as "https://mariadb.com/kb/en/library/development-articles/"