Migrate MariaDB to MySQL

This post is about Migrate MariaDB to MySQL

Introduction

MySQL first appeared in 1995 as an open-source project and was later acquired by Sun microsystems. Finally, Oracle acquired sun, leading to MySQL becoming an Oracle product. Many of the biggest names in the industry, like Facebook, Github, etc., use MySQL for their day-to-day operations. MySQL provides a comprehensive SQL shell that can even interpret Python and Javascript. In addition, MySQL supports JSON as table fields and allows users to query based on JSON keys.

It provides a secure authentication layer with support for very granular field-level controls for reading and writing. MySQL supports table encryption as well as binary log encryption. Development processes in MySQL are more closed when compared to MariaDB and the issue trackers refer to many oracle internal sources that are closed for the public.

Use the below command to create a complete dump of the database in MariaDB.

# mysqldump -u root -p UnixCop > UnixCop_dump.sql

Transfer UnixCop_dump.sql to a machine that can access the target MySQL database. This step is irrelevant if the same device has access to the target database.

Log in as root to the target MySQL database

# mysql -u root -p password

MySQL shell, execute the below command to create a database.

> CREATE DATABASE UnixCop;

Exit the MySQL shell and go to the location where the UnixCop_dump.sql is stored.

Execute the below command to load the database from the dump file.

# mysqldump -u root -p UnixCop -d UnixCop < UnixCop_dump.sql

The target database is now ready for use and can be verified by logging in to the MySQL shell and executing a SHOW TABLES command. However, even though this approach provides a simple way for a one-off copy operation between the two databases, this method has several limitations.

• In most cases, the original database will be online while the customer attempts to copy the data. mysqldump command is a costly execution and can lead to the primary database being unavailable or slow during the process.
• While the mysqldump command is executed, new data could result in some leftover data. This data needs to be handled separately.
• This approach works fine if the copying operation is a one-off process. In some cases, organizations may want to maintain an exact running replica of MariaDB in MySQL and then migrate. This will need a complex script that can use the binary logs to create a replica.
• Even though MariaDB claims itself as a drop-in replacement, the development has been diverging now, and there are many incompatibilities between the versions described here. This may lead to problems while migrating using the above approach.