Open Source Software Technical Articles

Want the Best of the Wazi Blogs Delivered Directly to your Inbox?

Subscribe to Wazi by Email

Your email:

Connect with Us!

Current Articles | RSS Feed RSS Feed

Replacing MySQL with MariaDB

  
  
  

For years, MySQL has been the king of open source database servers. It powers a large part of the web and numerous applications worldwide. However, concerns about the future of MySQL since its acquisition by Oracle, combined with an increasing demand for performance and scalability, have driven people to consider alternative options, such as PostgreSQL and MongoDB. Switching to either of those alternatives, however, is not a simple proposition. MariaDB, by contrast, offers enhanced performance in a DBMS that can be a drop-in replacement for MySQL.

MySQL's creator, Michael "Monty" Widenius, started working on MariaDB in 2009 as a fork of MySQL, and supports it via his company, Monty Program Ab. In late 2012, the nonprofit MariaDB Foundation was founded to support the development of the database and manage the MariaDB trademark. All code for the project is open source, distributed under a GPLv2, LGPL, or BSD license, and the Foundation is commited to keep the development open to the community. Besides transparency, the project also aims to improve the quality of the database with timely bug fixes and stability improvements. The next versions of Fedora and openSUSE will include both MySQL and MariaDB, but will offer MariaDB as the default.

Compatible, yet improved

The biggest strength of MariaDB is, of course, its compatibility with MySQL. It is a drop-in replacement for the same version of MySQL; that is, if an application works with MySQL 5.5, it should work with MariaDB 5.5 without any modification. Even the names of the command tools are the same – you interact with MariaDB databases via the mysql command, and perform backups with mysqldump. Developers and system administrators should feel right at home, with minimal adjustments to their familiar workflow. I've tested some open source applications by replacing MySQL with MariaDB – including WordPress, Drupal, phpMyAdmin, Adminer, and software I've written – without problems. The project offers a list of open source applications that officially support MariaDB and a detailed compatibility list.

The core of any database system is its data storage engine, and MariaDB offers several powerful engines to choose from, including the standard MyISAM, Blackhole, CSV, Memory, and Archive engines. A new storage engine, Aria (previously known as Maria), was developed with the goal of becoming the default non-transactional and transactional engine for MariaDB, and might be included in future versions of MySQL too. The current version of Aria offers a crash-free alternative to MyISAM, and support for transactions and additional features is planned for the next version. The Percona XtraDB engine is included as a replacement for InnoDB, MySQL's default engine since 5.5. XtraDB is a faster version of InnoDB, designed for more efficient scalability on modern multicore systems. It is also backward-compatible with InnoDB. The current development version, MariaDB-10.0, also includes the Apache Cassandra NoSQL storage engine, and more NoSQL storage options are promised for future versions. You can find all the storage engines included in MariaDB in the list of feature differences between the two databases.

MySQL is not a slow database, and MariaDB aims to improve performance further. The project's blog offers some benchmarks comparing MariaDB 5.5 and 10.0 with MySQL 5.5 and 5.6. MariaDB outperforms MySQL in most of the tests. A post by Asher Feldman of Wikimedia largely verifies the results of that benchmark.

One feature of MariaDB that can greatly improve performance is the option of running server threads in a thread pool. This feature is available in the commercial, closed source versions of MySQL as a plugin, but the MariaDB implementation is more efficient and built-in. Thread pools offer better system resource management and can make a big difference in most work loads, especially on busy servers. To enable a thread pool, just add thread_handling = pool-of-threads to the MariaDB configuration file, my.cnf, on Unix-like systems. It is enabled by default in the Windows version.

MariaDB also includes user statistics, a diagnostic feature that is also available as a patch for MySQL 5.5. Combined with an automated, scripted process, userstat can be a powerful tool for monitoring server activity. To enable statistics to be gathering, add userstat = 1 in my.cnf. Userstat adds several new information schema tables and new FLUSH and SHOW commands. Enter SELECT * FROM INFORMATION_SCHEMA.USER_STATISTICS\G or SHOW USER_STATISTICS\G at the mysql prompt to view information about users' activity on your server.

Installation

Future versions of most Linux distributions and BSDs will ship MariaDB packages in their official repositories, but at the moment, distro availability is limited. However, the project offers prebuilt packages for popular distributions and a nice repository configuration tool to help you select the right repository for your distribution. Considering the slow pace of updates in server-oriented distributions, including RHEL and CentOS, I don't expect to see MariaDB RPMs in official repos soon. Luckily, MariaDB offers long-term support for updates on RHEL, CentOS, and Ubuntu LTS. If you just want to test how MariaDB behaves on your current setup without disrupting your installation, you can install it alongside MySQL.

It's simple to install MariaDB on a CentOS 6 (64-bit) server. If MySQL is already installed on the server and you want to replace it with MariaDB, you should back up /etc/my.cnf and /var/lib/mysql beforehand, then uninstall MySQL with yum erase mysql-server mysql. Using the repo config tool, create a repo entry for yum and saved it as /etc/yum.repos.d/MariaDB.repo:

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

After that, just run yum install MariaDB-server MariaDB-client. You can also try yum install mysql to install MariaDB or just yum update if MySQL is already installed, but currently doing so installs the Galera version of MariaDB, which I'll discuss in a moment, so it is better to be explicit about what you want. If you prefer to create your own RPM packages from source, you can follow the project's detailed instructions.

To migrate your previous MySQL installation, overwrite the fresh /etc/my.cnf with your backup. Optionally, you can also break your my.cnf file into multiple configuration files in /etc/my.cnf.d. After starting the server process you should run mysql_upgrade, as you would with any MySQL updates between major versions. You cannot undo this last step, so make sure you have backups of your databases before proceeding. Running mysql -e 'SHOW GLOBAL VARIABLES LIKE "version"' will show you the version of your newly installed MariaDB server.

MariaDB for the enterprise

Large database installations often utilize multiple servers with replication, for extended availability and increased performance. The standard replication mechanisms of MySQL and PostgreSQL are, mainly, asynchronous. Writes to the database occur on a master server initially, and then propagate to one or more slave servers, which are used only for reading data. Another approach is the multi-master topology, which can offer seamless failover, at least in theory. However, due to the asynchronous nature of current replication methods, it is easy for servers to get out of sync and cause more problems.

The MariaDB Galera cluster aims to fix this problem by offering true synchronous replication between multiple master servers. Any changes to the database are committed to all nodes simultaneously, via certification-based replication. Synchronous replication offers great benefits in enterprise installations. Applications with many client write transactions can scale to previously unimagined levels by distributing the transactions across the slave nodes of the cluster. Even traditional master-slave designs can benefit from synchronous replication with zero slave lag.

The Galera cluster is still under development, although at the time of this writing it is considered release-candidate quality. To test it, you need at least three server nodes to form the database cluster. Instead of the MariaDB-server package you need to install MariaDB-Galera-server, along with the galera package, which offers the replication engine (wsrep). The current implementation supports only InnoDB and XtraDB.

Besides speed and scalability, enterprise database installations require premium support services. An increasing number of service providers offer support for MariaDB. Corporations with large investment in MariaDB can also support the Foundation and help move the development of the database forward.

MariaDB has already made its mark in the database ecosystem as a worthy replacement for MySQL, and you can expect its adoption to keep increasing in the future. After all, having a tuple of high-quality open source databases to choose from is not a bad thing.




This work is licensed under a Creative Commons Attribution 3.0 Unported License
Creative Commons License.

Comments

Great artcicle! I've made a switch from MySQL to MariaDB on some of my dev servers and see nice difference in performance. I've also checked popular CMSes like Wordpress and Joomla. But have some problem with good GUI for managing MariaDB databases. Navicat is too expensive for me and MySQL Workbench is not fully compatible. Could you maybe suggest a good for managing MariaDB databases? 
 
Regards
Posted @ Wednesday, May 14, 2014 9:26 AM by Dawid
PhpMyAdmin works just as well with MariaDB as with MySQL.
Posted @ Wednesday, June 18, 2014 7:28 PM by robert syputa
Post Comment
Name
 *
Email
 *
Website (optional)
Comment
 *

Allowed tags: <a> link, <b> bold, <i> italics