How to Convert MariaDB to MYSQL?

MariaDB is a relational database management system RDBMS and is a fork of MySQL. Though MariaDB is considered as a drop-in replacement for MySQL, it has diverged significantly. Several organizations migrate from MariaDB to MySQL because of support contracts and licensing terms. In this article, we'll see how to move or convert data from MariaDB to MySQL.

Methods to Convert or Migrate MariaDB to MySQL

 If you want to migrate from MariaDB to MySQL, you need to move data by reading it from MariaDB and writing it in MySQL. This operation can be executed manually or using a dedicated database converter tool. Below, we will discuss both ways.

Migrate MariaDB to MySQL Manually

 To migrate from MariaDB to MySQL manually, follow the below steps:

• Perform Compatibility Test

A crucial step before proceeding is to look for incompatibility. In MariaDB, there are functions that are not available in MySQL. For example - the Data Types. MariaDB supports INET6, while this data type is not present in MySQL 8.0. It may also happen that a function may differ in name.

• Dumping Data Out of MariaDB

To dump data out of MariaDB, you can use the Dump loading utility. You can execute the following mysqldump command:

mysqldump -u user_name -p db_name > dumped_data.sql

Here, -u is the username and -p is the name of the MariaDB database.

This command creates the file dumped_data.sql that contains the data of the MariaDB database.

• Install MySQL 8.0

Install the MySQL version 8.0, if you don't have already.

• Create a New MySQL Database

Using the MySQL shell or the MySQL workbench (the official GUI of MySQL), create a new database. Use the following SQL command:

Create database /destination_database /destination_database

In the /destination_database, enter the name of the database you want create./destination_database

• Import Data into MySQL 8.0

To import the data into MySQL database, you can use the MySQL Shell. Execute the following SQL command:

       mysqldump -u user_name -p db_name < dumped_data.sql

Here, -u is the username and -p is the name of the new MySQL database.

       The db_name is filled with data from dumped_data.

Migrate MariaDB to MySQL using Database Converter Tool

 You can use a specialized database converter tool, such as Stellar Converter for Database to make the migration process easy, quick, and safe.

 Download, install, and launch the tool. When the tool starts, it asks for a source database to convert.

Select the format of the source database.

Click 'Browse' to select the database you want to convert.

To perform an offline migration, you need to specify the path of the MariaDB data file.

To perform a live migration, you must connect to the database instance using an ODBC driver and specify the username and password.

Here, we will select the offline option.

Now, enter the path and press the Scan button.

When the Scan process is complete, press the OK button.

On the left side of the interface, the list of the databases is shown.

Press the Convert button.

You cannot save data directly in MySQL format. You can make an intermediate step and save it in a different format. For example, MS Access.

Press the Browse button and enter a path.

At the end of the process, the data is saved in Access format.

Now, you can repeat the procedure to migrate data from Access to MySQL.

Press the Select Database button and choose the MS Access format.

Press the Browse button and select the Access (.mdb) file just created.

Press the Scan button.

When the Scanning process is completed, press the OK button.

Press the Convert button.

Choose MySQL as database type and enter the username, host name, and password.

Press the Save button.

When the process ends, press the OK button.

To Conclude

Migrating data from one database type to another is never an easy process. The process needs to be planned carefully. Different database types, even if related, such as MariaDB and MySQL may have different data types. Above, we have seen how to migrate data manually using SQL commands and SQL tools. We have also seen how to migrate or convert MariaDB to MySQL using dedicated software, such as Stellar Converter for Database.