Replace MySQL by MariaDB

By | 14th December 2015

MariaDB is the open source replacement of MySQL (now owned by Oracle). If you are starting a new project and thinking to use MySQL, you should seriously consider using MariaDB instead as it is virtually identical functionality and there are reports suggesting MariaDB has better performance benchmark results. If your MySQL instance is currently working fine in production, there is no reason why it needs to be replaced immediately. The general trend in the community is to replace MySQL with MariaDB at a time suit. In some cases, it may be a still be a good idea to stay with MySQL, for example, if your company requires all production software having full commerical support with SLA etc.

In my case, I am starting a new project and decided to use MariaDB. The Debian server already has MySQL installed as backend DB for another application. There are two options available:

  1. Install MariaDB side by side with MySQL
  2. Replace MySQL by MariaDB

If you are not 100% sure about MariaDB and would like to try it out first, you can do side by side installation first – see MariaDB alongside MySQL (for SME Server)

WARNING: If you plan to have MariaDB running side by side with MySQL, do not use “apt-get install mariadb-server” (Debian) or equivalent command on other Linux variants – that will do a drop in replacement i.e. MySQL will be overwritten.

Please note that one potential “complication” is that both MySQL and MariaDB are listening on the same TCP/IP port – 3306. MariaDB will have to use a different port number, say 3307 (can configured in /etc/maria-db/my.cnf for example) if you do not want to “disturb” existing applications.

In my case, I do not really have any major concern on MariaDB, always prefer simple solution and the existing application is not too critical hence I decided to go with drop in replacement option.

MariaDb as drop in replacement for MySQL

It is always a good idea to run a full DB Backup and/or take a image backup before you start. Anyway, you should test this on a test / staging server before roll it out to production.

From command prompt, run

>apt-get install mariadb-server

This removed MySQL server from the Debian server and install MariaDB – see output below

...
The following packages will be REMOVED:
mysql-client-5.5 mysql-server mysql-server-5.5 mysql-server-core-5.5
The following NEW packages will be installed:
libreadline5 mariadb-client-10.0 mariadb-client-core-10.0 mariadb-common mariadb-server mariadb-server-10.0 mariadb-server-core-10.0
0 upgraded, 7 newly installed, 4 to remove and 0 not upgraded.
...
Removing mysql-server (5.5.46-0+deb8u1) ...
Removing mysql-server-5.5 (5.5.46-0+deb8u1) ...
Removing mysql-client-5.5 (5.5.46-0+deb8u1) ...
Removing mysql-server-core-5.5 (5.5.46-0+deb8u1) ...
...
151213  7:48:12 [Note] /usr/sbin/mysqld (mysqld 10.0.22-MariaDB-0+deb8u1-log) starting as process 16304 ...

It can be quite confusing as there is no obvious sign of MariaDB on file system. That is because MariaDB is a branch of MySQL and the filenames are kept the same for compatibility – for example, configuration is /etc/mysql/my.cnf – exactly the same as MySQL. To check the installation, after server is restarted, run mysql command as usual (with the new root password).

>mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1651
Server version: 10.0.22-MariaDB-0+deb8u1-log (Debian)

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

You can also find evidence of the installation by checking the package database for mysql

>dpkg-query -l
Desired=Unknown/Install/Remove/Purge/Hold

ii mysql-common 5.5.46-0+deb8u1 all MySQL database common files, e.g. /etc/mysql/my.cnf
rc mysql-server-5.5 5.5.46-0+deb8u1 amd64 MySQL database server binaries and system database setup

r = Remove – MySQL package is removed
c = Config-files – MySQL configuration files are kept on the server

Here are the entries for

...
ii  mariadb-client-10.0            10.0.22-0+deb8u1            amd64        MariaDB database client binaries
ii  mariadb-client-core-10.0       10.0.22-0+deb8u1            amd64        MariaDB database core client binaries
ii  mariadb-common                 10.0.22-0+deb8u1            all          MariaDB common metapackage
ii  mariadb-server                 10.0.22-0+deb8u1            all          MariaDB database server (metapackage depending on the latest version)
ii  mariadb-server-10.0            10.0.22-0+deb8u1            amd64        MariaDB database server binaries
ii  mariadb-server-core-10.0       10.0.22-0+deb8u1            amd64        MariaDB database core server files
...

i = Install
i = installed

Final Note

When looking around on replacing MySQL by MariaDB, most procedure I found on the web involves uninstalling MySQL first before installing MariaDB. That may be the only way to migrate before and potentially “cleaner”. The (literally) drop in replacement certainly worked for me in this particular instance. Anyway, as mentioned earlier, you should always test the migration process in test/staging before rolling out to production.

Resources

MySQL face-off: MySQL or MariaDB? – Sean Hull (Aug 2013 infoword.com).

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.