Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Migrate MySQL to MariaDB on Linux?
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.
305 Views
