MariaDB and MySQL are two widely popular relational databases that boast many of the largest enterprises as their clientele. Both MariaDB and MySQL are available in two versions – A community-driven version and an enterprise version. However, the distribution of features and development processes in the community and enterprise versions of MySQL and MariaDB differ from each other.

Even though MariaDB claims itself as a drop-in replacement for MySQL, because of the terms of licensing and enterprising support contracts, many organizations migrate between these two according to their policy changes. This blog post will cover the details of how to move data from MariaDB to MySQL.

What is MariaDB?

MariaDB is a RDBMS built on SQL, created by the professionals behind the development of MySQL intended to provide technical efficiency and versatility. You can use this database for many use cases, which include data warehousing, and managing your data. Its relational nature will be helpful for you. And, the open-source community will provide you with the resources required. 

What is MySQL?

MySQL is one of the renowned open source relational database management systems. You can store and arrange data in structured formats in tables with columns and rows. You can define, query, manage, and manipulate your data using SQL.

You can use MySQL to develop websites, and applications. Examples of companies who used this are Uber, Airbnb, Pinterest, and Shopify. They use MySQL for their database management requirements because of its versatility and capabilities to in manage large operations. 

Method 1: Using Hevo Data to Connect MariaDB to MySQL

The steps involved are,

Step 1: Configure MariaDB as Source

Configure MariaDB as a Source
Configure MariaDB as a Source

Step 2: Configure MySQL as Destination

Configure MySQL as Destination
Configure MySQL as Destination

Check out why Hevo is the Best:

  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.’
  • Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer.
  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
Get Started with Hevo for Free

Method 2: Using Custom Code to Connect MariaDB to MySQL

Since both databases provide the same underlying tools, it is very easy to copy data from MariaDB to MySQL. The following steps detail how to accomplish this.

  • Step 1: From the client machine, use the below command to create a complete dump of the database in MariaDB.
    mysqldump -u username -p database_name > source_dump.sql
    This command creates a source_dump.sql file.
  • Step 2: Move the file to a machine that can access the target MySQL database. If the same machine has access to the target database, this step is not relevant.
  • Step 3: Log in as root to the target MySQL database
    mysql -u root -p password
  • Step 4: In the MySQL shell, execute the below command to create a database.
    CREATE DATABASE target_database;

    Where target_database is the name of the database to which data is to be imported.

  • Step 5: Exit the MySQL shell and go to the location where the source_dump.sql is stored.
  • Step 6: Execute the below command to load the database from the dump file.
    mysql -u username -p new_database < source_dump.sql

That concludes the process. The target database is now ready for use and this can be verified by logging in to the MySQL shell and executing a SHOW TABLES command. Even though this approach provides a simple way for a one-off copy operation between the two databases, this method has a number of limitations. Let’s have a look at the limitations of this approach.

MariaDB to MySQL: Limitations of Custom Code Approach

  • 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 being executed, new data could come in resulting 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 versions as described here. This may lead to problems while migrating using the above approach.

Method 3: Using MySQL Workbench

  • In MySQL Workbench, navigate yourself to Database> Migrate to initiate the migration wizard.
  • Go to Overview page -> select Open ODBC Manager. This is done to make sure the ODBC drive for MySQL Server is installed. If not, useMySQL installer used to install MySQL Workbench for installing it. Select Start Migration.
  • Click and specify details on source database -> test the connection -> select Next.
  • Configure the target database details and verify connection.
  • Get the wizard extracting the schema list from the source server -> select the schema for migrating.
  • The migration will begin once you mention the objects you want to migrate on the Source Objects page.
  • Make edits in the generated SQL for all objects ->  edit migration issues, or change the name of the target object and columns on the View drop-down of Manual Edit.
  • Go to the next page ->  choose create schema in target RDBMS -> Give it sometime to finish the creation. And check the created objects on the Create Target Results page.
  • In the Data Transfer Settings page, configure data migration -> Select Next to move your data.
  • Check the migration report after the process -> select Finish to close the wizard.
  • You can check the consistency of source data and schema by logging into the target database.
  • Also, check if the table and row counts match.
SELECT COUNT (*) FROM table_name;
  • Get MySQL row count of tables in your 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;
  • Understand the size of the table.
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;

Limitations of using MySQL Workbench to Migrate MariaDB to MySQL:

  • Size Constraints: MySQL workbench has limitations on the size of migrations that it can handle effectively. It cannot be used for very large databases. 
  • Limited Functionality: It cannot deal with complex data structures efficiently. It requires manual interventions or additional tools to do so when using MySQL workbench. 

Use Cases of MariaDB to MySQL Migration

  • MySQL is suitable for heavily trafficked websites and mission-critical applications.  MySQL can handle terabyte-sized databases and also supports high-availability database clustering. When you migrate MariaDB to MySQL, you can manage databases of websites and applications with high traffic.  Popular applications that use the MySQL database include TYPO3, MODx, Joomla, WordPress, phpBB, MyBB, and Drupal. 
  • MySQL is one of the most popular transactional engines for eCommerce platforms. Thus, when you convert MariaDB to MySQL, it becomes easy to use to manage customer data, transactions, and product catalogs. 
  •  When you import MariaDB to MySQL, it assists you in fraud detection. MySQL helps to analyze transactions, claims etc. in real-time, along with trends or anomalous behavior to prevent fraudulent activities. 

Conclusion

This blog explained 2 methods using which you can import MariaDB to MySQL. The manual custom coding method provides a simple approach for a one-off migration between MariaDB and MySQL. Among the methods provided, determining which method is to be used depends on your use case. You can go for an automated data pipeline platform if you want continuous or periodic copying operations.

Visit our Website to Explore Hevo

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data replication process that are flexible to your needs. Hevo can help you move data from not just MariaDB, but a wide array of additional data sources.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite firsthand. Check out Hevo pricing to choose the best plan for your organization.

Share your understanding of the MariaDB to MySQL data transfer process in the comments section below!

Reference

  1. Migrating tips for MariaDB to Snowflake
mm
Former Director of Product Management, Hevo Data

Vivek Sinha has extensive experience in real-time analytics and cloud-native technologies. With a focus on Apache Pinot, he was a driving force in shaping innovation and defensible differentiators, including enhanced query processing, data mutability support, and cost-effective tiered storage solutions at Hevo. He also demonstrates a passion for exploring and implementing innovative trends within the dynamic data industry landscape.

No-code Data Pipeline for MySQL