How to Migrate MySQL to MariaDB on Linux?

LinuxMySQLMaria DBDatabase

This article will help you to migrate the database from MySQL to MariaDB as the binary compatibility of MySQL-to-MariaDB in the migration process is very much straightforward.

After the Oracle’s acquisition of MySQL, the community has driven an outcome of such movement and developed a new database called MariaDB. MariaDB is the open-source and its compatibility with MySQL. Most of the Linux distributions (RH, CentOS, Fedora) have already started to use the support of MariaDB as a drop-in replacement of MySQL.

If we want to migrate the database from MySQL to MariaDB then this article will help.

Preparing a MySQL Database and Tables

We create a test MySQL database and totality table for demo purpose. We can skip this if we have a database and tables in the environment.

Log into the MySQL from terminal using MySQL root user and root user password.

# mysql -uroot -p

Creating one Database and Tables

mysql> Create database test1;
mysql> use test1;
mysql> create table tab1(name varchar(30), owner varchar(30), species varchar(20), sex char(1));
Query OK, 0 rows affected (0.02 sec)

Also, we will add some records to test the data

mysql> insert into tab1 values('Chandra','Prakash','Kadarla','m'),('Srinvas','Kadarla','Chary','m');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
Then quit from the MySQL.

Backup of MySQL Database

We needed to take a backup of MySQL Databases using the command – MySQL dump

# mysqldump --all-databases --user=root --password --master-data > backupDB.sql

For security reason we will take the configuration file for MySQL some, where else in the system

# cp /etc/mysql/my.cnf /opt/my.cnf.bak

Uninstall MySQL Packages

We will uninstall the MySQL using below command –

# service mysqld stop
# yum remove mysql-server mysql-client mysql-common

Install MariaDB Packages

MariaDB has been replaced with MySQL on all the major Linux distributions official repositories. Even if you are using an old version of Linux can add official repository.

Create a file in yum repos directory

# sudo vi /etc/yum.repos.d/MariaDB.repo

Add the below to the file and save it

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

To install Maria-DB Server & Client run the below command.

# yum install MariaDB-server MariaDB-client
Loaded plugins: fastestmirror, langpacks
base                                  | 3.6 kB 00:00
extras                                | 3.4 kB 00:00
mariadb                               | 2.9 kB 00:00
updates                               | 3.4 kB 00:00
   --> Restarting Dependency Resolution with new changes.
   --> Running transaction check
   ---> Package MariaDB-shared.x86_64 0:5.5.49-1.el7.centos will be obsoleting
   ---> Package mariadb-libs.x86_64 1:5.5.35-3.el7 will be obsoleted
   --> Finished Dependency Resolution
Dependencies Resolved
========================================================================================
Package                         Arch          Version                      Repository    Size
========================================================================================
Installing:
   MariaDB-client             x86_64          5.5.49-1.el7.centos          mariadb          8.6 M
   MariaDB-server             x86_64          5.5.49-1.el7.centos          mariadb          40 M
   MariaDB-shared             x86_64          5.5.49-1.el7.centos          mariadb          1.0 M
      replacing mariadb-libs.x86_64 1:5.5.35-3.el7
Installing for dependencies:
   MariaDB-common             x86_64          5.5.49-1.el7.centos          mariadb          23 k
   perl-Compress-Raw-Bzip2    x86_64          2.061-3.el7                  base             32 k
   perl-Compress-Raw-Zlib     x86_64          1:2.061-4.el7                base             57 k
   perl-DBI                   x86_64          1.627-4.el7                  base             802 k
   perl-Data-Dumper           x86_64          2.145-3.el7                  base             47 k
   perl-IO-Compress           noarch          2.061-2.el7                  base             260 k
   perl-Net-Daemon            noarch          0.48-5.el7                   base             51 k
   perl-PlRPC                 noarch          0.2020-14.el7                base             36 k
Transaction Summary
========================================================================================
Install 3 Packages (+8 Dependent packages)
Total download size: 51 M
Is this ok [y/d/N]: y
Downloading packages:
warning: /var/cache/yum/x86_64/7/mariadb/packages/MariaDB-5.5.49-centos7-x86_64-common.rpm: Header V4 DSA/SHA1 Signature, key ID 1bb943db: NOKEY
Public key for MariaDB-5.5.49-centos7-x86_64-common.rpm is not installed
(1/11): MariaDB-5.5.49-centos7-x86_64-common.rpm             | 23 kB 00:00:01
(2/11): MariaDB-5.5.49-centos7-x86_64-client.rpm             | 8.6 MB 00:00:12
warning: /var/cache/yum/x86_64/7/base/packages/perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Public key for perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm is not installed
(3/11): perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm       | 32 kB 00:00:00
(4/11): perl-Compress-Raw-Zlib-2.061-4.el7.x86_64.rpm        | 57 kB 00:00:00
(5/11): perl-Data-Dumper-2.145-3.el7.x86_64.rpm              | 47 kB 00:00:00
(6/11): perl-IO-Compress-2.061-2.el7.noarch.rpm              | 260 kB 00:00:00
(7/11): perl-Net-Daemon-0.48-5.el7.noarch.rpm                | 51 kB 00:00:00
(8/11): perl-DBI-1.627-4.el7.x86_64.rpm                      | 802 kB 00:00:00
(9/11): perl-PlRPC-0.2020-14.el7.noarch.rpm                  | 36 kB 00:00:00
(10/11): MariaDB-5.5.49-centos7-x86_64-shared.rpm            | 1.0 MB 00:00:01
Total download size: 51 M
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
   Installing : MariaDB-common-5.5.49-1.el7.centos.x86_64          1/12
   Installing : perl-Data-Dumper-2.145-3.el7.x86_64                2/12
   Installing : MariaDB-client-5.5.49-1.el7.centos.x86_64          3/12
   Installing : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64        4/12
   Installing : perl-Net-Daemon-0.48-5.el7.noarch                  5/12
   Installing : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64         6/12
   Installing : perl-IO-Compress-2.061-2.el7.noarch                7/12
   Installing : perl-PlRPC-0.2020-14.el7.noarch                    8/12
   Installing : perl-DBI-1.627-4.el7.x86_64                        9/12
Installed:
   MariaDB-client.x86_64 0:5.5.49-1.el7.centos       MariaDB-server.x86_64 0:5.5.49-1.el7.centos
   MariaDB-shared.x86_64 0:5.5.49-1.el7.centos
Dependency Installed:
   MariaDB-common.x86_64 0:5.5.49-1.el7.centos       perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7
   perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7       perl-DBI.x86_64 0:1.627-4.el7
   perl-Data-Dumper.x86_64 0:2.145-3.el7             perl-IO-Compress.noarch 0:2.061-2.el7
   perl-Net-Daemon.noarch 0:0.48-5.el7               perl-PlRPC.noarch 0:0.2020-14.el7
Replaced:
   mariadb-libs.x86_64 1:5.5.35-3.el7
Complete!

After all the necessary packages installed, we needed to setup password for the root user account. The root password recovers the configuration file which takes a backup from MySQL.

# cp /opt/my.cnf /etc/mysql/

Now, we needed to restart MariaDB service run the below command.

# service mariadb start

Importing MySQL Databases

We have to import the database which we took a backup from MySQL to MariaDB

# mysql -u root -p < backupDB.sql

Enter the password of the root user, and then the database will be imported to the MariaDB

We needed to check the database if it was restored correctly or not.

Please follow the bellow commands –

# mysql -u root -p
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test1              |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> use test1;
MariaDB [test01]> select * from tab1;
select * from tab1;
+---------+---------+---------+------+
| name    | owner   | species | sex  |
+---------+---------+---------+------+
| Chandra | Prakash | Kadarla |    m |
| Srinvas | Kadarla | Chary   |    m |
+---------+---------+---------+------+
2 rows in set (0.00 sec)

Now the migration from MySQL to MariaDB has completed successfully.

After we run the command and follow the tutorial, we can migrate the database from MySQL-to-MariaDB with easy steps, where MariaDB has a lot of new features compared to MySQL. In the above tutorial I have used a simple scenario to test the migration configuration.

raja
Published on 22-Jan-2020 08:21:42
Advertisements