FreeBSD is Fun

Practical recipes for FreeBSD

Switching from MySQL to MariaDB

Posted

by

Category

Many years ago Michael Widenius created MySQL, a free, open source implementation of the industry standard database query language SQL. Along with the also open source PHP and Apache, MySQL became the de facto standard for hobbyists to build their projects on in the nascent World Wide Web, a popularity which led Oracle to acquire MySQL in 2010.

After the acquisition, Michael Widenius forked the project to continue it on its own and naming it after his daughter – Maria.

Both MySQL and MariaDB, along with the less popular Percona, are almost 100% data compatible, meaning you can generally move your data between them without altering it.

So why should you use MariaDB instead of the MySQL you are used to?

MySQL is not maintained by the original developers. This means compatibility is not high in the list of priorities – hence the huge leap in version numbers. Where are MySQL 6 and 7? Oracle clearly wanted to signal a change of direction by jumping straight into MySQL 8. It may not be straightforward to move your data to MySQL 8. Moreover, Oracle is known for ruining every piece of Intellectual Property they lay their hands on, the most blatant case being OpenOffice.

What about performance? This is what Amazon Web Services has to say about it:

MariaDB is more scalable and offers a higher query speed when compared to MySQL. This makes it good for managing large-sized data. You will also find more features in MariaDB that MySQL doesn’t have, like sequence storage engines and virtual columns. You can also use multiple engines in one table.

However, MySQL has been around for much longer than MariaDB. Some organizations prefer the enterprise support that MySQL offers.

On the other hand and at the moment of writing this MariaDB 10.6 is the latest version ported to FreeBSD and it is extremely easy to replace your MySQL 5.x installation with MariaDB.

For the purposes of this tutorial, let’s say we have three databases called cats, dogs and rabbits.

The first step is to dump your data. Data is dumped separately for each Database as a SQL file – a list of instructions in MySQL language. This allows us to import this data in other versions of MySQL or MariaDB with minimal trouble. There are two ways to do this:

Dumping SQL with Navicat

For each database to dump, repeat this process:

  • Double click on it to open it.
  • Right click on its name.
  • Select “Structure and Data” from the “Dump SQL File” option in the drop down menu.
  • Choose a suitable name and location and press Ok.

Note you should not dump the system databases such as “sys”, “performance schema” or “mysql”. Instead, take note of the users and passwords and reproduce them in the new installation of MariaDB. Or, even better from the security perspective, change them.

Dumping SQL from the Command Line

For each database to dump, repeat this process:

mysqldump -e --single-transaction --quick cats > cats.sql
mysqldump -e --single-transaction --quick dogs > dogs.sql
mysqldump -e --single-transaction --quick rabbits > rabbits.sql

If your mysql user has a password you will need to add the -u switch, here is an example with root:

mysqldump -uroot -e --single-transaction --quick cats > cats.sql

Playing safe

First thing after backing up our data is to make a backup of our my.cnf file. It’s usually located either in /var/db/mysql or /usr/local/etc/mysql, depending on which version of MySQL we started with. Move this file somewhere else so MariaDB can overwrite it.

If the data we are moving is important to us, it is advisable to download the SQL files to your PC and, if space allows, to clone the mysql folder – just in case!:

df -h

If we have more than 50% of space left, then we should be good to go.

cp -R /var/db/mysql /var/db/mysql-bak

Installing MariaDB

Let’s get rid of MySQL and its data first. If we do not know our installed version, we can find out with the pkg info command.

pkg del mysql56-server mysql56-client
rm -r /var/db/mysql

Next, let’s install MariaDB 10.6:

pkg install mariadb106-server

And “onestart” the service in order to create the system tables:

service mysql-server onestart

Let’s try it:

mysql

If we get the welcome to MariaDB message, we should be good to go. Leave the MySQL console with “exit” and once back in the system shell set MariaDB to start at boot:

sysrc mysql_enable="YES"

Restoring our data

This part is easy. Note that MariaDB, by default, has no password for the root user. This is no security concern as this user is only available to the system root user. Let’s create the databases:

mysql
CREATE DATABASE 'cats':
CREATE DATABASE 'dogs':
CREATE DATABASE 'rabbits';

Exit from the MySQL console with “exit” and import the databases we dumped earlier:

mysql cats < cats.sql
mysql dogs < dogs.sql
mysql rabbits < rabbits.sql

Finally, we should recreate the User table, best done with Navicat’s help.
We may also want to copy some settings from our old my.cnf file, but I would rather advise to optimize our installation from scratch.

Not feeling confident? You can always hire me to perform this or any other of the administrative tasks described in this blog.


Leave a Reply

Your email address will not be published. Required fields are marked *