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.
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.
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.
Need an API based file storage system?
 |
 |
 |
|
|---|---|---|---|
| API Endpoints | 38 | 5 | 14 |
| Free Tier Storage | 100 GB | 5 GB | None |
| Free Tier Transfer | 200 GB | 15 GB | None |
| Free Tier API Requests | Unlimited | 20,000 | None |
| Web Interface | Yes | No | Yes |
| Live Documentation | Yes | No | No |
| File Size Limit | None | 5 TB | 5 GB |
| Inherit FTP Access | Yes | No | No |
Maria DB looks pretty interesting. I might have to look at this more in-depth in the future. I’ve never heard of a MySQL replacement that one can just “drop in’ before.
Nice post!
Thanks for the feedback. The reason it can be a drop-in replacement is because they forked MySQL before Oracle ran away with it. It’s basically MySQL renamed with more features.