How to Migrate MariaDB to MySQL: 2 Easy Methods

MariaDB and MySQL are two of the leading Relational Database Management Systems (RDBMS). While both are open-source, they also offer commercial features available through subscriptions. 

The features and development process of both databases are similar. However, their open-source communities, enterprise versions, and use cases differ broadly. With these differences, there can be situations where you may need to migrate data from MariaDB to MySQL. 

In this article, we’ll discuss two methods to connect MariaDB to MySQL:

• A manual method using custom code and mysqldump
• A real-time, no-code method using Estuary

Additionally, if you're interested in migrating data in the opposite direction, be sure to check out our detailed guide on migrating MySQL to MariaDB.

MariaDB Overview

Built by some of the original developers of MySQL, MariaDB is an RDBMS based on SQL, offering technical efficiency and versatility. The database is used for many purposes, including data warehousing and retrieving and managing data. Leveraging its relational nature and open-source community, MariaDB has been shown to perform better than MySQL. Walmart, Alibaba, and Accenture are just a few names on the list of the major organizations that use MariaDB in their tech stacks. 

Here are some of the key features of MariaDB: 

• MySQL Compatibility: MariaDB was originally designed to be a drop-in replacement for MySQL. This means that most MySQL code can work seamlessly with MariaDB. Leveraging this compatibility, you can use MySQL’s syntax, commands, and APIs in its ecosystem without making significant changes.
  
• Multiple Storage Engines: In MariaDB, a storage engine functions as a distinct plugin tailored to specific use cases. Each storage engine is meticulously optimized for different types of data workloads, ranging from transactional to analytical. MariaDB supports multiple storage engines for various data and workloads, including InnoDB, Aria, TokuDB, and others. 
  
• Security: MariaDB comes equipped with robust security features as a default, ensuring the protection of your database. This includes connections through TLS/SSL encryption between the client and database server, preventing unauthorized access. Additionally, frequent security feature updates on MariaDB make it a reliable choice if you are dealing with sensitive data. 

MySQL Overview

Originally developed by a Swedish company MySQL AB, which was then acquired by Oracle in 2010, MySQL is one of the most popular open source RDBMSs. It allows you to store and organize data in structured collections within tables characterized by columns and rows. With MySQL, you can define, query, control, and manipulate data using Structured Query Language (SQL).

As a part of the widely used tech stack LAMP (Linux, Apache, MySQL, and PHP), MySQL is used to build many popular services, websites, and applications. Major organizations such as Uber, Airbnb, Pinterest, and Shopify rely on MySQL for their database management needs, showcasing its versatility and robustness in handling large-scale operations. 

Below are the key features of MySQL: 

• ACID Compliance: MySQL can provide strong ACID-style transactional guarantees when using the InnoDB storage engine, which is the default in most modern MySQL deployments. Atomicity ensures that every operation contained with a transaction is handled as an individual unit. Consistency makes sure that data is valid before and after a transaction. Isolation prevents multiple concurrent transactions from interfering with each other. Durability ensures that transaction modifications are saved permanently, even in system failure. 
  
• IAM Policy: If you run MySQL HeatWave on Oracle Cloud Infrastructure (OCI), it integrates with OCI IAM for authentication and authorization across interfaces like the Console, SDK/CLI, and REST APIs
  
• Free to Use: MySQL is open-source, so you can download, install, and use MySQL without any licensing cost. This feature allows you to leverage all the functionalities of a robust database management system without many barriers. However, some commercial versions of this platform, like MySQL Cluster Carrier Grade Edition and MySQL Enterprise Edition, are also available. 

Two Methods to Migrate Data From MariaDB to MySQL

You can migrate your data from MariaDB to MySQL using one of the two methods mentioned below: 

• Method 1: Using Estuary to Connect MariaDB to MySQL
• Method 2: Manually Connecting MariaDB to MySQL Using Custom Code

Method 1: Using Estuary to Connect MariaDB to MySQL

SaaS tools like Estuary streamline the process of replicating data from MariaDB to MySQL. Follow this step-by-step guide to efficiently connect and replicate data between the two databases:

Prerequisites 

• MariaDB source (CDC): MariaDB must have row-based binary logging enabled (binlog_format=ROW), retain binlogs long enough to recover from interruptions (at least ~7 days), and you’ll need a capture user with replication permissions plus read access to the tables (and optionally information_schema for auto-discovery).
• MySQL destination: MySQL 5.7+ is supported, the destination user must be able to create tables, and local_infile must be enabled on the MySQL server (for example: SET GLOBAL local_infile = true).
• At least one collection in Estuary to materialize into MySQL.

Step 1: Connect to MariaDB as a Source

• Log in to your Estuary account, or sign up for free.
   
• Once you see the main dashboard, click Sources on the left.

• Click the + NEW CAPTURE button on the top left of the Source page. 

• In the Search connectors box, type MariaDB, and you’ll find the MariaDB connector with the Capture button on the bottom. Click Capture. 

• On the Create Capture page, fill in the details like Name, Server Address, Login username, and Login password. Now, click Next > SAVE and PUBLISH.

Estuary will perform an initial backfill of the selected tables and then continue capturing ongoing changes from MariaDB using CDC.

Step 2: Connect to MySQL as a Destination

• After setting up the source, click on the Destinations option on the left side of the dashboard. 
  
• On the Destination page, click the + NEW MATERIALIZATION button. 
  
• Type MySQL in the Search connector box, and you'll find the MySQL connector with the Materialization button at the bottom. Click Materialization. 

• You’ll be redirected to the Create Materialization page. Enter the MySQL host (or host and port, typically 3306), database name, user, and password. If connection tests fail with load-related errors, confirm that the MySQL server has local_infile enabled, since the connector relies on it for ingestion.

Once published, Estuary will continuously materialize MariaDB changes into MySQL tables, keeping the two systems in sync.

The migration between MariaDB and MySQL is now complete. For a deeper understanding of Estuary, read the following documentation: 

• Set Up a Data Flow
• MariaDB Source Connector
• MySQL Destination Connector

Why Estuary Is the Best Way to Migrate MariaDB to MySQL

Here are some of the benefits of using Estuary: 

• Extensive Connectors Library: Estuary has a wide variety of pre-built connectors. With these connectors, you can quickly integrate data from various sources to the destination without writing a single line of code. 
  
• Data Cleansing: Estuary provides data cleansing capabilities, which allow you to clean, filter, and validate data during the transformation step. This is crucial for data integrity and quality, ensuring that only accurate data is transferred to the target database. 
  
• Minimal Technical Expertise: Estuary simplifies the migration process between MariaDB and MySQL, allowing you to complete the process with just a few clicks. This ease of use means that professionals of varying technical backgrounds can easily utilize Estuary for their data migration tasks. 

Method 2: Manually Connecting MariaDB to MySQL Using Custom Code

In this method, you’ll learn how to migrate MariaDB to MySQL manually using custom code. Follow this step-by-step guide to replicate data between the two databases:

⚠️ Manual migration works for simple, one-time transfers but lacks real-time sync and may require ongoing maintenance if schemas change or new tables are added.

Step 1: Create a Dump of MariaDB 

Begin by generating a dump of the MariaDB database you want to migrate.

• Open the terminal and type the following command: 

plaintext
mysqldump -u yourusername -p yourdatabasename > maria_dump.sql

• Replace yourusername with your MariaDB database username, and yourdatabasename with the database name you want to migrate. The file maria_dump.sql is where your data will be exported.

Step 2: Log in to MySQL as a Root User

• Using the terminal, navigate to the SQL directory "C:\Program Files\MySQL\MySQL Server X.X\bin" (replace X.X with your actual version number) and enter the following command:

plaintext
mysql -u root -p password

• Provide your MySQL password when prompted.

Step 3: Create a MySQL Database

• To create a new database in MySQL, enter the following code:

plaintext
CREATE DATABASE yoursqldatabasename;

• Replace yoursqldatabasename with your preferred MySQL database name. Check that the database was created and exit the MySQL session. 

Step 4: Restore the Data 

• Now that you have your MariaDB data file and the MySQL database set up, it’s time to restore the data into the new database. To import the MariaDB data into MySQL, enter the following command into your terminal: 

plaintext
mysql -u yourusername -p yoursqldatabasename < maria_dump.sql

Step 5: Verify and Test

Once the migration is completed, ensure that the MariaDB data is successfully migrated into the MySQL table. Run SQL queries or test your application to make sure everything functions as it should.

Limitations of Using Custom Code

The following are some challenges you might encounter when manually connecting MariaDB to MySQL using custom code: 

• Time and Resource Requirements: Developing, testing, and maintaining custom scripts can be time-consuming and require more development expenses than ETL tools. 
  
• Maintenance Challenges: Custom scripts need regular updates to accommodate changes in database schemas or system upgrades. This ongoing requirement demands constant effort. 
  
• Expertise Requirements: A deep understanding of MariaDB and MySQL databases is required to write custom code for migration. Mistakes or inefficiencies in custom code can result in performance problems, data loss, and more. 
  
• Latency in Real-Time Updates: Executing custom scripts may result in delays and, in some cases, cause a lack of real-time synchronization between databases. This is a notable drawback if you want real-time updates across systems and applications. 

The Takeaway

In conclusion, while migrating MariaDB to MySQL can be complex, Estuary simplifies the process significantly. By following these streamlined steps, you can achieve a seamless migration:

• Connect to MariaDB: Establish MariaDB as your source within Estuary.
• Configure MySQL: Set up MySQL as your destination in Estuary.
• Initiate Migration: Launch the migration process with a few clicks in Estuary's intuitive interface.

This user-friendly approach eliminates the need for intricate custom code, minimizing errors and ensuring a smooth transition to MySQL.

We recommend using Estuary to connect MariaDB to MySQL. It automates most of the process with its comprehensive features and user-friendly interface. Sign up or log in to Estuary today!

FAQs

Can I replace MariaDB with MySQL?

Yes, you can replace MariaDB with MySQL; MariaDB is considered a drop-in replacement for the corresponding MySQL version. However, it is essential to review feature differences between MariaDB and MySQL to ensure the switch doesn’t affect applications using a database server.

Can I run MariaDB and MySQL at the same time?

Yes, you can run MariaDB and MySQL on the same system simultaneously without conflicts; the two platforms use different default ports and directories, operating independently of each other.

Can I import MySQL to MariaDB?

Yes, you can import MySQL to MariaDB. First, back up your MySQL database into an SQL file. Then, log in to your MariaDB server and import the backup file to recreate a database.