Migrating From MySQL 5.7 to MariaDB 10.2 on Ubuntu 16.04

Choose Maria

For those unfamiliar with MariaDB, it's a fork of MySQL that is dedicated to remaining free to use. It was created the day Oracle bought out Sun Microsystems in 2010 who had previously acquired MySQL in 2008. MariaDB was built by the very developers who worked on MySQL and for most users the transition to MariaDB should be fairly painless. All of the clients and tools that work with MySQL work with MariaDB as well.

This guide is for those like me who have servers running Ubuntu's latest long-term release 16.04. I found some guides migrating from older versions of MySQL and Ubuntu, but nothing current. The following steps worked on a server housing several customized WordPress sites. I tested this out on a VM with snapshots which allowed me to rollback any changes when problems arose. If this is being done in production, I highly recommend at the very least taking full backups of all of your databases before the migration if not taking full images of your server. And with that warning out of the way, let's begin.

*UPDATE (1/14/19): I saw that someone linked to this article from the Ubuntu Forums, but decided to use MariaDB 10.0 instead of 10.2. His migration failed because he was using MySQL 5.7, which is not compatible with MariaDB 10.0. To complete this migration successfully, you need to install version 10.2 or above. MariaDB provides a MySQL/MariaDB Compatibility Chart to help eliminate confusion. Please read this chart before attempting the migration. As of this update, the latest stable version is MariaDB 10.3. You can opt for this version instead of 10.2 and still complete the rest of this guide without issue.


Inviting Maria In

You'll need to migrate to the latest stable version of MariaDB which is 10.2. The Ubuntu repositories only include 10.0 so we'll need to add the MariaDB 10.2 repository.

*NOTE: MySQL 5.7, which is the default package on Ubuntu 16.04 & 18.04, is not comptible with MariaDB 10.0 & 10.1, the default packages on those Ubuntu releases. You need to use MariaDB 10.2 or above for the migration to complete successfully. Please reference this MySQL/MariaDB Compatibility Chart for more info.

Open a Terminal or SSH into your server and run the following.

sudo apt-key adv --recv-keys --keyserver hkp:// 0xF1656F24C74CD1D8
echo -e 'deb [arch=amd64,i386] xenial main\ndeb-src xenial main' | sudo tee /etc/apt/sources.list.d/mariadb.list > /dev/null
sudo apt update

Saying Farewell to My

Most guides I've found have various ways to uninstall MySQL before installing MariaDB, but I've found that Ubuntu is smart enough to uninstall the unnecessary MySQL packages during the MariaDB installation.

sudo apt install mariadb-server

During the installation, you'll be prompted twice to setup the root password for MariaDB. Just leave this blank and it will use the existing MySQL root password.

Cleaning Up After Maria

Now that MariaDB is installed and MySQL is no more, we should check that MariaDB installed correctly and is running.

sudo systemctl status mariadb

MariaDB Post-Install Status
MariaDB Post-Install Status

As you can see, even though MariaDB is running, there are a few errors showing. This is common during a MySQL/MariaDB upgrade or migration. We need to run the post upgrade script to clear up these errors.

sudo mysql_upgrade -u root -p

Don't be thrown by the name of the command. As stated earlier, pretty much all of the commands, clients & tools written for MySQL work with MariaDB. When prompted, enter the old MySQL root password which is now your MariaDB root password then restart MariaDB.

sudo systemctl restart mariadb
sudo systemctl status mariadb

MariaDB Post-Cleanup Status
MariaDB Post-Cleanup Status

The errors you saw before should all be gone now and we're one last step away from MariaDB goodness

Sprucing up Ubuntu

There are a few packages that need to be updated now that we've added the MariaDB repository. We'll take care of that now.

sudo apt upgrade

That's it. For most, this will probably be the easiest migration you'll ever do. You'll definitely want to do a check of your databases and any applications or websites that rely on backend databases. I luckily didn't run into any issues after migrating.

Hope this guide is helpful to those who are ready to make the leap to MariaDB or at least got you curious enough to check her out. I mean how could you say no to that face.

Please Share Me, I'm Lonely

2 Responses to Migrating From MySQL 5.7 to MariaDB 10.2 on Ubuntu 16.04

  1. Thank you for the best guide on how to update to mariadb!