With the data volumes increasing exponentially, 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 is 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 to the steps to migrate MySQL to MariaDB in detail. The process to convert MySQL to MariaDB is a 4 step process.

Steps to Migrate Data From MySQL to MariaDB

Replicate Data From MySQL in Minutes Using Hevo’s No-Code Data Pipeline

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.

GET STARTED WITH HEVO FOR FREE

Hevo is the fastest, easiest, and most reliable data replication platform that will save your engineering bandwidth and time multifold. Try our 14-day full access free trial today to experience an entirely automated hassle-free Data Replication!

Step 1: Create MySQL Database Backup

  • Check for incompatibilities between MySQL and MariaDB.
  • 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:

MySQL to MariaDB: output of mysqldump
Image Source: www.xmodulo.compg
  • 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.
MySQL to MariaDB: MariaDB download page
Image Source: www.xmodulo.com

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
Solve your data replication problems with Hevo’s reliable, no-code, automated pipelines with 150+ connectors.
Get your free trial right away!

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. When 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;
MySQL to MariaDB: verifying the migration of databse
Image Source: www.xmodulo.com

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.

Incompatibilities between MariaDB and MySQL

It’s essential to check the versions before you get started with the migration process. This is especially important since all MySQL versions aren’t compatible with every MariaDB version. Owing to the associated restrictions, here’s a table of compatibility you can refer to before starting with the migration.

Table To Check Version Compatibility

MySQLMariaDB
5.15.1, 5.2, 5.3
5.55.5
5.610.0, 10.1
5.710.2, 10.3, 10.4, 10.5
8.0
  • If the MySQL source database utilizes a SHA256 password hash, ensure to reset passwords for users with SHA256 hashing before establishing a connection to the MariaDB database. Below is a code snippet demonstrating how to reset a password hashed with SHA256.

SET old_passwords = 0;
UPDATE mysql.user SET plugin = 'mysql_native_password',
Password = PASSWORD('new_password')
WHERE (User, Host) = ('master_user_name', %);
FLUSH PRIVILEGES;

Why Import from MySQL to MariaDB?

Migrating from MySQL to MariaDB can be beneficial when dealing with modern data structures and handling complex queries. You can leverage MariaDB’s upgraded capabilities to enhance your current database infrastructure significantly.

If you want to improve your database performance, MariaDB offers enhanced performance and efficiency in handling complex data compared to MySQL.

Conclusion

With advancements in Relational Database Management systems worldwide, 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 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 working on large 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 solve this issue, and this is where Hevo comes into the picture.

visit our website to explore hevo

SIGN UP for a 14-day free trial and see the difference! Check out our affordable prices and choose the best plan for your organization.

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

mm
Former Research Analyst, Hevo Data

Arsalan is a data science enthusiast with a keen interest towards data analysis and architecture and is interested in writing highly technical content. He has experience writing around 100 articles on various topics related to data industry.

mm
Customer Experience Engineer, Hevo Data

Rohit, proficient in Python, SQL, and diverse database technologies, specializes in comprehensive support for database integration, optimizing performance, and implementing robust solutions to meet clients' unique integration needs. His expertise ensures seamless connectivity and efficient data transfer across various platforms, enhancing client satisfaction and operational efficiency.

No-code Data Pipeline For Integrating MySQL Data