How to Migrate Data from MySQL to MariaDB?| 4 Easy Steps

With the data volumes increasing at an exponential rate, modern Database Systems have gained immense popularity owing to their ease of use and performance advantages. MariaDB and MySQL are popular Database Management Systems known for their robust nature and ability to process large amounts of data.

MySQL is an open-source Relational Database Management System from the house of Oracle. It is one of the pioneer database solutions, with many valuable features. MariaDB is a comparatively newer solution and comes out to be a lightweight version of MySQL with more features and better handling ability of complex data.

Organizations prefer MariaDB over MySQL in some cases and migrating data from MySQL to MariaDB allows them to explore and work faster on data.

This article provides a guide on MySQL, MariaDB, and steps to migrate MySQL to MariaDB in detail. The process to convert MySQL to MariaDB is a 4 step process.

What is MySQL?

MySQL is one of the most popular Database Management Systems available in the market. It was launched in the year 1995, and soon become the go-to solution for DBMS-related queries. MySQL utilizes SQL – Structured Query Language – to perform operations on data, monitor the data, and query the results from the data stored in the database. It is an Open-source solution that allows it to get updated on regular basis, adding newer support capabilities and stability fixes.

MySQL can be used for commercial aspects by paying for modelling capabilities. MySQL is a simple to use platform and has wide support for connections to different platforms. It supports operating systems like Linux, Unix, Windows, and more. It can be deployed on Cloud as well as on On-premise systems.

Key features of MySQL

• High Performance: MySQL is a powerful tool, that houses high processing power combined with an easy-to-use interface. It also supports multiple clients and can be accessed from anywhere around the world.
• Compatibility: MySQL is compatible with other platforms. The Open-source nature allows it to get constant updates and inclusion of newer integrations. It also ensures low latency for information transfer and transactions.
• Scalability: MySQL is highly scalable and supports both up-scaling and down-scaling, which is done dynamically. The support for different operating systems also improves scalability. 

Key Use Cases for MySQL

Some prominent use cases of MySQL:

• Elastic Replication: Where an environment requires the number of OS servers to grow and shrink dynamically.
• High Availability: Where Sharding is used for write scale-out (in which each shard maps to a replication group).
• Source-Replica Replication Alternative: Which allows using a single source server to make it a single point of contention.
• Autonomic Systems: This allows users to deploy MySQL Group Replication for the automation that is built into the replication protocol.

What is MariaDB?

MariaDB is an Open-source, Relational Database Management System that comes out as a replacement for the MySQL database. It was launched in the year 2009, as a lighter and improved version of MySQL. MariaDB is more reliable, provides better performance, and handles complex data much better.

MariaDB works on SQL and supports all the rules and functions of SQL. It offers better storage options than its competitors. MariaDB is ACID-compliant and SQL used for transactional, analytical and hybrid use cases. MariaDB is a one-stop solution to meet all the requirements.

Key Features of MariaDB

• Storage Engines in MariaDB: MariaDB has the provision of InnoDB, which is a storage engine that has high reliability and performance. It is ACID-compliant and supports the concepts of foreign keys. MariaDB also has provision for XtraDB which is a slightly older storage solution with a similar feature set. With the present generation of MariaDB InnoDB is a default storage.
• Compatibility: MariaDB uses SQL to query data. It connects to other programming languages Python, C++, and PHP, and can run on a variety of operating systems like Linux and Windows.  
• JSON Support: JSON is a go-to format for storing data. MariaDB has many options for storage and supports JSON formats for storing the functions.  

Steps to Migrate Data From MySQL to MariaDB

• Step 1: Create MySQL Database Backup
• Step 2: Uninstall MySQL Database Packages
• Step 3: Install MariaDB Database Packages
• Step 4: Load MySQL Backup File to MariaDB

Step 1: Create MySQL Database Backup

• The existing Databases of MySQL need to be backed up to be transferred from MySQL to MariaDB. 
• The command mysqldump can be used to export all the existing databases in the form of files. 
• Binary Logging is required for this command to work, and this can be enabled in the MySQL configurations by modifying my.cnf file and adding the following option under the [mysqld] section.

log-bin=mysql-bin

• Restart the MySQL by using the following command:

$ sudo service mysql restart

• After that enter the command to use mysqldump.

$ mysqldump --all-databases --user=root --password --master-data > backupdb.sql

Output:

• A backup of my.cnf can be created to be on the safer side, but this step is optional. To create the backup use the following command:

$ sudo cp /etc/mysql/my.cnf /opt/my.cnf.bak

Step 2: Uninstall MySQL Database Packages

• Stop the MySQL service by using the following command

$ sudo service mysql stop

or:

$ sudo systemctl stop mysql 

or:

$ sudo /etc/init.d/mysql stop

• Once the services have stopped, remove MySQL packages and configurations. You may leverage any of the following commands based on your operating system:

• On RPM-based system (e.g., CentOS, Fedora, or RHEL):

$ sudo yum remove mysql* mysql-server mysql-devel mysql-libs
$ sudo rm -rf /var/lib/mysql

• On Debian based system (e.g., Debian, Ubuntu or Mint):

$ sudo apt-get remove mysql-server mysql-client mysql-common
$ sudo apt-get autoremove
$ sudo apt-get autoclean
$ sudo deluser mysql
$ sudo rm -rf /var/lib/mysql

Step 3: Install MariaDB Database Packages

• The latest versions of CentOS and Ubuntu have the MariaDB package pre-installed in repositories. For older versions, you can visit the MariaDB website and download it based on the operating system version.

For a better understanding of this example, we will install MariaDB on Ubuntu and CentOS.

Ubuntu 14.04

$ sudo apt-get install software-properties-common
$ sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
$ sudo add-apt-repository 'deb http://mirror.mephi.ru/mariadb/repo/5.5/ubuntu trusty main'
$ sudo apt-get update
$ sudo apt-get install mariadb-server

CentOS 7

Create a custom yum repository file for MariaDB as follows.

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

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

$ sudo yum install MariaDB-server MariaDB-client

After all necessary packages have been installed, you may be asked to type a new password for the root user account. After configuring the root password, don’t forget to recover my.cnf backup file.

$ sudo cp /opt/my.cnf /etc/mysql/

Now, start the MariaDB service as follows:

$ sudo service mariadb start

or:

$ sudo systemctl start mariadb

or:

$ sudo /etc/init.d/mariadb start

Step 4: Load MySQL Backup File to MariaDB

Now import the MySQL backup file into MariaDB using the following command:

$ mysql -u root -p < backupdb.sql

Enter the password and the import process for MySQL to MariaDB migration will start. As soon as the import of files from MySQL to MariaDB is complete the cursor will return to the command prompt.

To verify the success of import from this MySQL to MariaDB converter process, run the following commands:

$ mysql -u root -p

MariaDB [(none)]> show databases;
MariaDB [(none)]> use test01;
MariaDB [test01]> select * from pet;

Limitations of using mysqldump to Load Data from MySQL to MariaDB

• MySQL to MariaDB is an easy process but when migrating from one release to another, mysql_upgrade needs to be run.
• Sometimes while migrating the data from MySQL to MariaDB, the schema of MySQL does not match with MariaDB, and privileges cause errors.

Conclusion

With advancements in Relational Database Management systems all around the world, newer and better solutions are now available to the end-user. MySQL was one of the first DBMS platforms and stayed at the top of the competition for a very long time, and even now it is very relevant. Small drawbacks such as inefficient complex data handling and slower processing make it slightly problematic for organizations that work on large and complex datasets. MariaDB was launched as an improved version working on top of MySQL, which addresses performance issues and improves compatibility. The organizations that previously used MySQL are moving towards the MariaDB.

This article gave a step-by-step guide to moving data from MySQL to MariaDB. There are various Data Sources that organizations leverage to capture a variety of valuable data points. But, transferring data from these sources into a Data Warehouse for a holistic analysis is a hectic task. It requires you to code and maintains complex functions that can help achieve a smooth flow of data. An Automated Data Pipeline helps in solving this issue and this is where Hevo comes into the picture. Hevo Data is a No-code Data Pipeline and has awesome 100+ pre-built Integrations such as MySQL, MariaDB, etc., that you can choose from.

visit our website to explore hevo

Hevo can help you integrate data from 100+ data sources and load them into a destination to analyze real-time data at an affordable price. It will make your life easier and Data Migration hassle-free. It is user-friendly, reliable, and secure.

SIGN UP for a 14-day free trial and see the difference!

Share your experience of learning about migrating data from MySQL to MariaDB in the comments section below.