How to migrate from MySQL to MariaDB on Linux in five steps

You’ve decided to move from MySQL to MariaDB, but you’re a little nervous. The last thing you want is to become mired in a slough of brokenness. Don’t worry, because MariaDB is designed to be a drop-in replacement for MySQL. You should be able to install MariaDB over your MySQL server, and then go about your business without drama.

The simplest method follows these steps:

1. Update your software repositories list with the MariaDB repos
2. Update your Linux package manager with the new repos
3. Stop MySQL
4. Install MariaDB with your package manager
5. Go back to work because you’re done

Before you do anything, make sure you have current backups (which you should have anyway), and do the migration during a quiet time when you can monkey around with minimal upset to users. A good confidence-builder is to replicate your production server on a testing sandbox, and then practice on it. You’ll want MariaDB to be the same or higher version than your MySQL server. MySQL major releases are 5.0, 5.1, 5.5, and 5.6. MariaDB versions are 5.1, 5.2, 5.3, 5.5, and 10. You can reliably switch to MariaDB and then switch back to MySQL, if you wish, up to 5.5, but after that they diverge enough that I consider moving to MariaDB a one-way trip.

The nice people at MariaDB have created a copy-and-paste installation guide and wizard for the major Linux distributions. Choose your distro, MariaDB version, and download mirror, then copy and run the command created by the wizard to install the repository signing key, and copy the repository configuration into your sources list. Run your package manager’s update command, stop your MySQL server, and then install MariaDB with your package manager. The installation guide thoughtfully includes all the relevant commands for you. Your package manager should automatically run mysql_upgrade, which is a necessary step to check all of your tables for compatibility with the current version of MariaDB, and fix anything that isn’t right. If it doesn’t, run mysql_upgrade manually. Then restart MariaDB, check your logfiles, and if no errors appear, you’re done.

Another, and more labor-intensive, option is to install the new MariaDB server alongside your existing MySQL installation, then migrate databases and applications one at a time. To do this, download the MariaDB source tarball; compile and install the binary; create users, directories, and the init file; set permissions; start MariaDB; check that both MySQL and MariaDB are running; and then start moving stuff. This is also a good exercise for a refresher in the nuts and bolts of installation and configuration, which is always good to know and could save the day when something doesn’t work right.

If you’re running a more complex setup with tons of tables and databases and clustering and masters and slaves, take a look at this detailed guide by Sheeri K. Cabral of the Mozilla IT and Operations team. These people are so proficient with MySQL and MariaDB that they switch between them to take advantage of specific features in new releases. They start by making a logical backup of the database on a slave server, then import this backup into a new empty server installation. Before starting the export/import they use checksums to verify that the data are consistent between master and slave, issue maintenance notices, prevent anyone but root from logging in, take the server out of the load balancer, make sure the slave does not get overwritten by the master during the export, and perform a number of other checks and tests. It’s a lot of steps, but it’s a good safe method that allows you to take your time without taking everything offline.

Resources

• Moving from MySQL
• MariaDB vs. MySQL white paper (registration required)
• Installing MariaDB alongside MySQL
• Replication
• MySQL to MariaDB migration: Handling privilege table differences when using mysqldump