MariaDB

Migrate from MySQL to MariaDB with Zero Downtime

At SmartFile, there are many backend services that power our File Exchange Platform. Among these services, MySQL is used extensively to store customer data. Recently we migrated to MariaDB, which is a complete drop-in replacement for MySQL.

Why migrate from MySQL to MariaDB? Because MariaDB is open source and has some additional features, such as tracing transactions. It is also maintained by many of the original creators of MySQL.

In this post, I’ll walk through 2 methods of migrating from MySQL to MariaDB. To migrate from MySQL to MariaDB without downtime, check out method #2.

If you’re using MySQL 5.0 or older, you’ll need to need to read this guide from the MariaDB documentation:
Upgrading to MariaDB From MySQL 5.0 or Older

Method #1: Migrate from MySQL to MariaDB using Drop-in-replacement

1. Install the MariaDB software repo for your operating system
2. Install MariaDB with your package manager.
3. Restart the MySQL service.
4. Done. Continue using the database as if it were MySQL.

Caveats:

Restarting a production MySQL server can be problematic and cause downtime. To migrate from MySQL to MariaDB without downtime, refer to method #2.

Method 2: Migrate from MySQL to MariaDB using Master-Slave Replication

In this method, it will be possible to switch from MySQL to MariaDB without causing downtime. To seamlessly switch between databases, we use HAProxy as a load balancer for multiple MySQL instances.

1. First install MariaDB on a new database server. This new host will later become the master database server, so it is important to use sufficient hardware.

2. Configure master-slave MySQL replication such that the new database server becomes the slave.

Migrate from MySQL to MariaDB -- Master Slave Replication

3. (optional) If your final architecture requires database replication, add a replication slave to replicate the previously created slave.

4. HAProxy must be configured to failover to the MariaDB slave once we take the MySQL master offline.

5. Once there is data integrity between the master and the slave, the MySQL master can be taken offline. Percona has a free tool called ‘pt-table-checksum’ to verify SQL replication. Now that HAProxy is routing all MySQL traffic to the MariaDB machine, the slave becomes the master.

Migrate from MySQL to MariaDB -- Master Slave MariaDB

It is important NOT to bring the old MySQL server back online to avoid a split-brain scenario that can lead to data corruption.

I hope you enjoyed my article on migrating from MySQL to MariaDB. If you have any questions or comments, feel free to leave a comment below.

SmartFile is a business file mangement platform that gives you more control, compliance and security.

TO SIGN UP