How to Migrate MariaDB to MySQL in 2 Ways Easily?

MariaDB is an open-source database that was developed in 2009 as a variant of MySQL. It is considered a drop-in replacement and compatible with previous versions of MySQL. Some may prefer to stick with a more established user community or product, so they decide to return to MySQL from MariaDB.

Anyhow, migration requests from MariaDB to MySQL increase, and here in this article, we will learn 2 specific data migration methods for the two databases.

Way 1: Export MariaDB Dump and Import into MySQL

Mysqldump is a backup tool that creates logical backups of databases, allows for replication of database objects and table data, and facilitates transfer or backup to another SQL server. One can also use it to generate CSV/XML or more delimited text formats. You can use it to migrate PostgreSQL to MySQL, or backup and restore MySQL.

Before Migration:

• Environment: MariaDB 10.6, and MySQL 8.0.

• Backup MariaDB prior to the migration in case of risks.

• Check for incompatibilities between MariaDB and MySQL.

Transfer non-InnoDB storage engine in MariaDB into InnoDB used in MySQL.

1.     Verify the storage engines on your MariaDB database.

mysql> SELECT * FROM INFORMATION_SCHEMA.TABLES;

2.     Find out the tables that are incompatible with MySQL 8.0.

3.     Modify the engine on the MariaDB server or change it on MySQL when load data.

ALTER TABLE table_schema_and_table-name ENGINE=InnoDB;

Check and modify the inappropriate functions.

1.     See the differences between MariaDB 10.6 and MySQL 8.0.

2.     Check whether you have the function for some columns.

SELECT TABLE_NAME, COLUMN_NAME

FROM information_schema.COLUMNS

WHERE COLUMN_DEFAULT LIKE ' %add_months%';

Empty set

3.     Create a table with the function as default.

ALTER TABLE table_name ADD COLUMN column_name DATETIME DEFAULT (ADD_MONTHS(NOW(), 2));

Check data types of MariaDB and MySQL.

1.     Find the data type list in the database.

SELECT DATA_TYPE from INFORMATION_SCHEMA.COLUMNS where

TABLE_SCHEMA = ’database_name’ and table_name =’ table_name’.

2.     Understand the different names of data types in the two databases.

3.     Change the data type to the one MySQL supports accordingly.

ALTER TABLE table_name MODIFY address data_type;

During Migration:

1.     Create a dump in MariaDB.

mysqldump -u username-p database_name > source_dump.sql

2.     Force the unsupported storage engines to be InnoDB.

util.dumpInstance("/tmp/dump_mariadb_10_6_force",

            {users: false, compatibility: ["force_innodb"]}

3.     Stop and uninstall MariaDB.

# service mysql stop

# apt-get –purge remove “mysql*”

4.     Remove or move configuration file.

5.     Use the MySQL shell to create a new database with the same name as the one in MariaDB.

CREATE DATABASE target_database;

6.     Log in to MySQL as root.

mysql -u root -p password

7.     Exit MySQL shell and navigate to the dump file.

8.     Load the dump file into MySQL:

mysql -u username -p new_database < source_dump.sql

After Migration:

1.     Verify the migration:

mysql -u root -p mydatabase -e "show tables"

Note that while MariaDB and MySQL are very similar, there may be some differences in features or syntax that could cause issues during the migration process. It's a good idea to test the migrated database thoroughly before using it in a production environment.

Way 2: Migrate MariaDB to MySQL Using MySQL Workbench

MySQL Workbench is an integrated tool that provides a visual interface for designing databases, developing SQL code, and administering MySQL databases, allowing users to perform multiple tasks related to MySQL database management from a single interface. One can also use the tool to migrate multiple databases to MySQL.

1.       Go to Database> Migrate to start the migration wizard in MySQL Workbench.

2.       On the Overview page, click Open ODBC Manager to see if the ODBC drive for MySQL Server is installed. If not, install it via the MySQL installer used to install MySQL Workbench. After that, click Start Migration.

3.       Select and specify the source database information, test the connection and click Next.

4.       Configure the connection details for the target database and verify its connection.

5.       Wait for the wizard extracting the schema list from the source server and choose the schema to migrate.

6.       Specify the objects to migrate on the Source Objects page. The migration starts.

7.       Edit the generated SQL for all objects, show migration issues, or modify the target object’s name and columns on the View drop-down of Manual Edit.

8.       Select create schema in target RDBMS on the next page. Wait for the creation to finish. And check or edit the created objects on the Create Target Results page.

9.       Configure data migration on the Data Transfer Settings page. Select Next to start transferring the data.

10.    See the migration report after the process and click Finish to close the wizard.

11.    Log into the target database to verify whether the migrated schema and tables are consistent with the source.

12.    See if the table and row counts match.

SELECT COUNT (*) FROM table_name;

13.    Get MySQL row count of all tables in a database.

SELECT

  table_name,

    table_rows

FROM

    information_schema.tables

WHERE

    table_schema = 'classicmodels'

ORDER BY table_name;

14.    Check the database size.

SELECT TABLE_SCHEMA AS `Database`,

TABLE_NAME AS `Table`,

ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`

FROM information_schema.TABLES

GROUP BY table_schema;

15.    Check the table size.

SELECT table_name AS "Table",

ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"

FROM information_schema.TABLES

WHERE table_schema = "database_name"

ORDER BY (data_length + index_length) DESC;

Avert Migration Risks in Advance

Various migration risks are associated with the process such as sematic errors, data corruption, or data loss, one of the best and recommended practices before any risky operations is backup.

Vinchin Backup & Recovery performs frequent data backups and effective recovery for 12 virtualizations, NAS, physical servers, and 6 databases like Oracle DB, MySQL, SQL Server, PostgreSQL, Postgres Pro, and MariaDB. 

With it, you can:

• Automate scheduled database backup under encrypted transfer.

• Reduce 50% of backup size using data deduplication and compression.

• Choose from full, differential, incremental, and transaction log backup that change with databases.

• Perform MySQL hot backup without affect on the production DB.

• Encrypt the backups with the AES-256 standard.

• Restore a database to the original or a new path.

Download the 60-day free trial of the Enterprise version for agentless V2V migration across 10+ virtual platforms, anti-ransomware protection, and GDPR compliance.

Final Thoughts

You can migrate MariaDB to MySQL using the manual export and import, or the migration wizard of MySQL GUI MySQL Workbench easily. Or the migration from MySQL to MariaDB is also feasible. But note that data migration risks lurk everywhere, and you must back up your critical data before the operation. Vinchin Backup & Recovery streamlines the backup and recovery for 6 databases including MariaDB and MySQL, and users can manage all backups and tasks in one web console centrally.