Panoply Blog - Data Management, Data Infrastructure, and Data Analysis.

MariaDB vs MySQL: In-Depth Comparison 2018

Written by Matan Sarig | May 3, 2018 4:28:00 PM

Healthy competition can bring out the best in organizations. Just look at companies like Coke and Pepsi or General Motors and Ford, all of which were obsessed with outdoing the other while the customer (hopefully!) reaps the rewards.

In this article, I'm going to compare another case where competition has brought about innovation between MySQL and its fork MariaDB.


Who Uses These Databases?

MySQL: MySQL has generated a strong following since it was started in 1995. Some organizations that use MySQL include GitHub, US Navy, NASA, Tesla, Netflix, WeChat, Facebook, Zendesk, Twitter, Zappos, YouTube, Spotify. You can check the full list here: https://www.mysql.com/customers/.

MariaDB: MariaDB is being used by many large corporations, Linux distributions, and more. Some organizations that use MariaDB include Google, Craigslist, Wikipedia, archlinux, RedHat, CentOS, and Fedora.


What About Database Structure?

MySQL: MySQL is an open-source relational database management system (RDBMS). Just like all other relational databases, MySQL uses tables, constraints, triggers, roles, stored procedures and views as the core components that you work with. A table consists of rows, and each row contains a same set of columns. MySQL uses primary keys to uniquely identify each row (a.k.a record) in a table, and foreign keys to assure the referential integrity between two related tables.

MariaDB: Since MariaDB is a fork of MySQL, the database structure and indexes of MariaDB are the same as MySQL. This allows you to switch from MySQL to MariaDB without having to alter your applications since the data and data structures will not need to change.

This means that:

  • data and table definition files are compatible
  • client protocols, structures, and APIs are identical
  • MySQL connectors will work with MariaDB without modification

Even the command line tools are similar to mysqldump and mysqladmin still having the original names, allowing MariaDB to be a drop-in replacement.

To make sure MariaDB maintains drop-in compatibility, the MariaDB developers do a monthly merge of the MariaDB code with the MySQL code. Even with this, there are some differences between MariaDB and MySQL that could cause some minor compatibility issues.

Bill Karwin, author of SQL Antipatterns: Avoiding the Pitfall, believes that MySQL still has a lot of potential and will eventually diverge from MariaDB. He says:


As time goes on, MySQL develops more extensive features or changes to its internal architecture. They have more developers on staff than MariaDB, so they are making changes at a faster pace.

Gradually, MySQL and MariaDB will diverge. A noteworthy example is the internal data dictionary that is currently under development for MySQL 8. This is a major change to the way metadata is stored and used within the server. MariaDB doesn't have an equivalent feature. This may mark the end of datafile-level compatibility between MySQL and MariaDB.


Are Indexes Needed?

Indexes enhance database performance, as they allow the database server to find and retrieve specific rows much faster than without an index. But, indexes add a certain overhead to the database system as a whole, so they should be used sensibly.

Without an index, the database server must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more costly operation.

MySQL and MariaDB: Most MySQL and MariaDB indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions include the indexes on spatial data types that use R-trees. MySQL also supports hash indexes and InnoDB engine uses inverted lists for FULLTEXT indexes.


How Are Their Queries Different?

MySQL queries are the same as MariaDB queries.

Selecting records from the customer table
MySQL:
SELECT * FROM customer;

MariaDB:
SELECT * FROM customer;

Inserting records into the customer table
MySQL:
INSERT INTO customer(cust_id, branch, status) VALUES ('appl01', 'main', 'A');

MariaDB:
INSERT INTO customer(cust_id, branch, status) VALUES ('appl01', 'main', 'A');

Updating records in the customer table
MySQL:
UPDATE customer SET branch="main" WHERE custage > 2;

MariaDB:
UPDATE customer SET branch="main" WHERE custage > 2;


Where (And How) Are These Databases Deployed?

MySQL: MySQL is written in C and C++ and has binaries for the following systems: Microsoft Windows, OS X, Linux, AIX, BSDi, FreeBSD, HP-UX, IRIX, NetBSD, Novell Netware, and many more.

To download MySQL go to the MySQL download page. There are installation instructions for Microsoft Windows, Linux, or OS X.

MariaDB: MariaDB is written in C, C++, Bash, and Perl and has binaries for the following systems: Microsoft Windows, Linux, OS X, FreeBSD, OpenBSD, Solaris, and many more.

Since MariaDB is designed to be a binary drop-in replacement for MySQL, you should be able to uninstall MySQL and then install MariaDB, and (assuming you're using the same version of the data files) be able to connect. Please note, you will need to run mysql_upgrade to complete the upgrade process.

To download MariaDB, go to the MariaDB downloads page. For Ubuntu, Red Hat, Fedora, CentOS, or other Linux distributions, go to the download repository for your operating system. There are also installation instructions for Microsoft Windows, Linux, and OS X.


What Types Of Replication / Clustering Are Available?

Replication is a process that enables you to have multiple copies of the data copied automatically from 'master' to 'slave' databases. There are multiple benefits to this, and few of them being:

  • backup
  • spreading the load to improve performance
  • analytics team can work on one of the slave databases, thus not hurting the performance of the main database in case of long-running and intensive queries.

Clustering, in the context of databases, refers to using shared storage and putting more database front-ends on it. The front end servers share an IP address and cluster network name that clients use to connect, and they decide between themselves who is currently in charge of serving clients requests.

MySQL: Replication in MySQL is one-way asynchronous replication where one server acts as a master and others as slaves. You can replicate all databases, selected databases or even selected tables within a database.

MySQL Cluster is a technology providing shared-nothing (no single point of failure) clustering and auto-sharding (partitioning) for the MySQL database management system.

Internally MySQL Cluster uses synchronous replication through a two-phase commit mechanism to guarantee that data is written to multiple nodes. This is in contrast to what is usually referred to as "MySQL Replication", which is asynchronous.

MariaDB: MariaDB offers master-master and master-slave replication as well. MariaDB uses the Galera Cluster for multi-master. As of MariaDB 10.1, Galera is included with MariaDB. Enabling clustering is as simple as activating the configuration parameters.


Who's Currently Behind The Databases?

In 2010, MySQL was acquired by the Oracle Corporation. At that time, one of the original developers, Michael “Monty” Widenius, felt that the Oracle Corporation had a conflict of interest between MySQL and their Oracle database.

In response to this, he decided to create a fork of the project named MariaDB. Since then, there's been a healthy competition between MySQL and MariaDB that has led to some great innovation. Since Oracle is backing MySQL and has a firm foundation, it continues to be the leader. However, MariaDB offers some compelling reasons for why people may want to switch databases.

MySQL: MySQL was originally started by MySQL AB in 1994 by a Swedish company that was created by David Axmark, Allan Larsson, and Michael “Monty” Widenius. The first version of MySQL was released in 1995. In 2008, Sun Microsystems purchased MySQL AB. In 2010, Sun Microsystems was acquired by Oracle.

MySQL is currently maintained by the Oracle Corporation.

MariaDB: On the day Oracle announced they had purchased MySQL, Michael “Monty” Widenius took several MySQL developers and started MariaDB, a fork of MySQL from that point.

Brian Wheeler from DevOps.com says:


Widenius and many others in the open source community felt Oracle's ownership might be a conflict of interest since Oracle already had a competing closed source commercial database. They believed Oracle would be slow to further develop the MySQL database, given the potentially greater focus on the commercial Oracle database.

MariaDB enterprise is managed by the MariaDB Corporation AB. The community MariaDB server is managed by the MariaDB Foundation.

The MariaDB Foundation uses a community governance model. They chose to separate the open source and commercial sides of the business.

Brian Wheeler from DevOps.com says:


Because of the separation between the open source and commercial sides of the foundation, its governance is seen by the open source community as positive. This is in contrast to Oracle, which has both its commercial Oracle database and the open source MySQL both under the same governance. Often this is perceived as a conflict of interest, especially when it comes to keeping MySQL up to date.

 

Who Provides Support?

MySQL: MySQL offers technical support services as part of Oracle's lifetime support. The support team contains MySQL developers and support engineers who offer 24/7 support as well as bug fixes, patches, and maintenance releases.

Oracle offers MySQL Premier Support, Extended Support, and Sustaining Support depending upon your needs.

MariaDB: MariaDB offers support engineers that are said to be experts in both MariaDB and MySQL. They offer 24/7 support with an enterprise subscription for mission-critical production systems.


Who Supplies Ongoing Development?

MySQL: Ongoing development is done by the Oracle Corporation, and they continue the development. Development decisions are not open to the public. Security releases come out every two months.

MariaDB: Where MySQL is developed by Oracle and decisions are not open to public discussion, MariaDB is developed with a different methodology. The development is open to the public where all development decisions can be debated and reviewed via a public mailing list. People can also submit patches for MariaDB. According to MariaDB, this methodology allows for more transparent and quicker security releases.


Who Maintains The Documentation?

MySQL: For MySQL, documentation is maintained by the Oracle Corporation.

MariaDB: For MariaDB, the main steward is the MariaDB Foundation, but other people can participate in development and documentation.


Is There An Active Community?

MySQL: MySQL is owned and managed by the Oracle Corporation. Oracle offers a Developer Zone on the MySQL website, which can be found at https://forums.mysql.com/. The site contains a variety of forums for running MySQL.  You can view additional information at:

MariaDB: Maria is developed by the open source community, allowing anyone to contribute. You can find additional information on how to connect with online community members, helping with documentation, development, events, and meetup groups at their Getting Involved page.


What About Database Connectors?

MySQL: MySQL offers a variety of database connectors including C, C++, Delphi, Perl, Java, Lua, .NET, Node.js, Python, PHP, Lisp, Go, R, D, and Erlang.

MariaDB: MariaDB offers a variety of database connectors including ADO.NET, C, C++, D, Java, JavaScript, ODBC, Perl, PHP, Python, Ruby, and Visual Studio plug-in.

Whether you choose MySQL, MariaDB, or both, Panoply has connectors for both databases. It provides a no-coding solution without the need for data preparation or transformation. This allows you to consolidate all of your data from MySQL, MariaDB, cloud services, and applications into a single data management platform.


Which Database Is Right For Your Business?

MySQL: MySQL is a proven database that has a strong following around the globe. The Oracle Corporation continues to maintain, enhance, and support the product.

MariaDB: The MariaDB provides a drop-in replacement for organizations running MySQL. It is managed by the MariaDB Foundation, allowing people to contribute to the open source product and documentation.

Both products may continue to diverge in the future. For many organizations, the fundamental question comes down to: Which one do you feel more comfortable with?

Would you prefer a larger corporation like Oracle Corp that manages MySQL, or would you feel more comfortable with an open solution like MariaDB? With either choice, your business will win by connecting your databases with smart data warehouse by Panoply 

Which database is right for your business?