December 2, 2021

MariaDB vs MySQL: Know the Key Differences

Databases

By

Finding the right database can be hard — open source or otherwise. Often the decision can come down to a single feature or functionality that aligns well with the needs of the application or system, or even a license type that aligns better with the business goals. For those looking at open source relational database management systems, comparing MySQL vs MariaDB will be a valuable exercise.

In this blog, we compare MariaDB and MySQL, including differences in syntax, performance, support, release cadence, and discuss when to use MariaDB vs when to use MySQL.

A Short History of MySQL and MariaDB

When software is created, there is often a long term vision for how that software will exist in the world. Some tools are created by businesses and the sole intent is to sell that product and grow the business. Others take a more altruistic philosophy and create a community around the idea that the software is for the greater good and should be distributed freely and openly for all. Neither idea is good or bad in nature, simply two different working models we see in the software ecosystem.

When Oracle was in the process of acquiring MySQL, certain developers had the foresight to fork the project into MariaDB to maintain it’s open source availability to the community. What we now have are two near identical products with different licensing options to meet just about anyone's needs.

MariaDB vs. MySQL: Key Differences and Similarities

It’s true that MariaDB is by design a drop-in replacement to MySQL. Both were written in C and C++, but MariaDB has in some ways taken the lead by being better performing, it’s capabilities in handling large amounts of data, faster privilege checks, features like binary encryption and temporary table space and more.

One thing that MySQL does that MariaDB does not is offer support for JSON datatypes. MariaDB opted not to implement this because they believed it to not be part of the SQL standard. MariaDB by default also offers support for AWS key management plugins, MySQL only supports those plugins in the enterprise edition. MySQL by default enables the validate_password plugin, increasing security, while MariaDB does not. 

Versioning and Release Cadence

Currently MariaDB has releases beginning with 10 while the community edition of MySQL is still on 8. In recent times, because of how much MariaDB is pulling away from MySQL, it appears that MariaDB is releasing more frequently, as their latest releases came November 8th, 2021 while 8.0.27 of MySQL was released back in October. While it is still rather easy to migrate from MySQL to MariaDB, the 10.X versions of Maria are not considered “drop in.”

Community Support Lifecycle

Just like all open source software, there are expiration dates tied to every release, and no release lasts forever. MariaDB and MySQL support several versions that vary from the latest and greatest to several versions back. OpenLogic’s rule is to support the latest major branch and 3 versions back. It may be possible to get EOL support with MySQL enterprise, but that is not confirmed. When a version reaches EOL for OpenLogic, we go with the communities decision and end support for that version, while fulfilling support on any open contracts. We do offer extended support on EOL versions for additional costs.

Syntax

While there are a lot of commands that transfer over from MySQL to MariaDB, there is still quite a few differences in syntax as MariaDB has grown over the years. There’s a variable called sql_mode that consists of comma-separated list of flags, each flag being a different aspect of SQL syntax. old_mode is very similar to sql_mode but instead it provides compatibility with older MariaDB versions. It’s flags shouldn’t affect compatibility with SQL Server.

Executable Comments

MariaDB also supports executable comments. These are used to write generic queries that are executed by MariaDB and only certain versions. When initially forked from MySQL, executable commands were also available for MySQL.

Delimiters

When it comes to delimiters, SQL server uses batch terminator and query terminator. Batch terminator is the go command and tells Microsoft clients to send the text we typed to SQL server. Query terminator is a semicolon and tells SQL server where a query ends. Rarely is it necessary to use ; in SQL server. But with MariaDB you only use ;.

Names

With MariaDB most names have a max length of 64 characters. You may run into an issue as SQL server allows for 128 characters, so be aware of this limitation when migrating from SQL server to MariaDB.

Case Sensitivity

The default setting in MariaDB is that if the OS is case sensitive such as Linux, MariaDB will be as well. If using a case-insensitive OS like Windows, MariaDB will default to that setting. SQL server is case-insensitive by default on all operating systems. When migrating from SQL server to MariaDB on Linux, you can set lower_case_table_names system variable to 1, allowing table names, database names and aliases case-insensitive.

Licensing

MariaDB is currently licensed under the GNU General Public License version 2. The community edition of MySQL is licensed under the same license. Enterprise support is available on MySQL community edition. As a side note, OpenLogic supports both MySQL and MariaDB at a production level.

Performance

Talking about performance is one thing, but when it comes down to the actual numbers, there’s nothing like a good old fashioned bench marking test to lay the results down on paper. Leave it to MariaDB to provide their own benchmarking results.

While a little dated, it still demonstrates that in certain circumstances, MariaDB can certainly outperform MySQL.

To quote the findings:

“In general, MariaDB significantly outperforms MySQL at lower scale points and continues to maintain a performance differential at higher scale. This is indicated in the charts by lower latency for MariaDB than MySQL and higher throughput from MariaDB than MySQL.” 

Security

Both databases are considered to be secure products to use, as both are used in production by many giant logos out there. Really, the security of your database comes down to your own level of configuration and how you secure your operating system as well.

Just like all open source software, there are known vulnerabilities for both, so it’s important that you use the latest and greatest versions of the software as well as schedule upgrade cycles on some kind of schedule. You don’t want to fall too far behind in upgrades and find yourself stuck in the past. Be sure to research CVE’s and learn as much as you can about the product before implementing it into your environment.

Final Thoughts

With its origin as a fork of MySQL, it's clear that there are still many remaining similarities between MariaDB and MySQL. However, as discussed above, their divergence has made them vastly different databases today.

Each has their own advantages and use cases, of course, so choosing one over the other ultimately comes down to aligning your business needs with the capabilities and strengths of the database. At the end of the day, both databases can be an excellent choice for enterprise use.

Get Support for Your Open Source Data Stack

From MariaDB and MySQL, to Kafka and Spark, OpenLogic can provide the technical support and services you need to succeed in the enterprise. Talk to an open source expert today to see how we can support your goals.

Talk to an Expert

Additional Resources

image-zend-blog-author-bio-andrew-pomponio

Andrew Pomponio

Associate Enterprise Architect, OpenLogic by Perforce

Andrew's areas of specialization include networking, Linux, network security including OpenSSL, and operational troubleshooting. He has been working in the industry for over seven years and is acquiring new skills every day.