MariaDB vs MySQL – Key Differences – Comparing MySQL 8.0 with MariaDB 10.5

Introduction

The goal of this article is to evaluate and highlight the main similarities and differences between the MySQL Server Database and the MariaDB Server Database. We’ll look into performance aspects, security, main features, and list all aspects which need to be considered before choosing the right database for your requirements.

Who is using MySQL and MariaDB?

Both MySQL and MariaDB publish a respectful list of customers who are using their database as their core data infrastructure.

For MySQL, we can see names such as Facebook, Github, YouTube, Twitter, PayPal, Nokia, Spotify, Netflix and more.

For MariaDB, we can see names such as Redhat, DBS, Suse, Ubuntu, 1&1, Ingenico, Gaming Innovation Group, BlaBla Cars and more.

Comparing features - MySQL vs MariaDB

Many new and exciting features like Windows Functions, Roles or Common Table Expressions (CTE) are probably worth mentioning, but won’t be mentioned in this article. We’re all about comparing the two database engines, so, therefore, we’ll only discuss features which are available only in one of them, to allow you, our readers, to determine the engine that works better for you.

Let’s look into several features which are available only in one of the databases, exclusively:

1. 1. JSON datatype - Starting version 5.7, MySQL supports a native JSON data type defined by RFC 7159 that enables efficient access to data in JSON (JavaScript Object Notation) documents.
      MariaDB decided not to implement this enhancement as they claim it’s not part of the SQL standard. Instead, to support replication from MySQL, they only defined an alias for JSON, which is actually a LONGTEXT column. MariaDB claims there is no significant performance difference between the two, but no benchmarks were done recently to support that claim. MariaDB also explains why they don't support JSON yet.
      It’s worth noting that both MySQL and MariaDB offer different JSON related functions which allow easier access, parsing and retrieval of JSON data.
   2. Default authentication - In MySQL 8.0, caching_sha2_password is the default authentication plugin rather than mysql_native_password. This enhancement should improve security by using the SHA-256 algorithm. MariaDB supports it only using authentication plug-ins.
   3. Oracle compatibility - MariaDB Server 10.3 or higher has Oracle compatibility (e.g., PL/SQL, sequences and data types), system-versioned tables for temporal queries (e.g., AS OF), user-defined aggregate functions, point-in-time rollback a la Oracle Flashback, distributed partitions via Spider, check constraints and set operators (INTERSECT/EXCEPT) and more.
      MySQL has some basic compatibility,  here a detailed comparison between Oracle and MySQL
   4. Encryption - MySQL encrypts redo/undo logs (when configured to do so), while it doesn't encrypt temporary tablespace or binary logs. MariaDB, on the other hand, supports binary log encryption and temporary table encryption.
   5. Key Management - MariaDB offers an AWS key management plugin out of the box. MySQL also provides several plugins for key management, but they’re only available in the Enterprise edition.
   6. Sys schema - MySQL 8.0 includes the sys schema, a set of objects that helps database administrators and software engineers interpret data collected by the Performance Schema. Sys schema objects can be used for optimization and diagnosis use cases. MariaDB doesn’t have this enhancement included.
   7. Validate_password - The validate_password plugin’s goal is to test passwords and improve security. MySQL has this plugin enabled by default, while MariaDB doesn’t.
   8. Super read-only  - MySQL enhances the read_only capabilities by providing the super read-only mode. If the read_only system variable is enabled, the server permits client updates only from users who have the SUPER privilege. If the super_read_only system variable is also enabled, the server prohibits client updates even from users who have SUPER. See the description of the read_only.
   9. Invisible Columns - This feature, which is available on MariaDB, while not on MySQL, allows creating columns which aren’t listed in the results of a SELECT * statement, nor do they need to be assigned a value in an INSERT statement when their name isn’t mentioned in the statement.
   10. Threadpool - MariaDB supports connection thread pools, which are most effective in situations where queries are relatively short and the load is CPU bound (OLTP workloads). On MySQL’s community edition, the number of threads is static, which limits the flexibility in this situations. The enterprise plan of MySQL includes the threadpool capabilities.

Performance & Benchmarking

Over the years, many performance benchmark tests were executed on both MySQL and MariaDB engines. We don't believe there is one answer to the question "which is faster, MySQL or MariaDB?". It very much depends on the use case, the queries, the number of users and connections, and many other factors which should be considered.

These are the most recent benchmark tests we found online, which might provide some indication to which one performs better. Please make sure you read the details of each of the benchmark tests (how the benchmark was done, on which environment, which hardware, what was tested, what wasn't tested, etc.).

• MySQL 8.0 (InnoDB) and MariaDB 10.3.7 (MyRocks) benchmark test
• MariaDB 10.1 and MySQL 5.7 performance on commodity hardware
• MySQL 8.0 and MariaDB 10.3.5 performance and the UTF8 impact

Whichever major change you're planning: migrating from one database type (or engine) to another, from one OS to another, from on-premise servers to the cloud, I believe you should make sure you run your own tests, plan your own database benchmark process and perform the relevant stress testing. These are a few books we found on Amazon that may help you plan your next benchmark process:

• Database Benchmarking and Stress Testing: An Evidence-Based Approach to Decisions on Architecture and Technology - by Bert Scalzo
• High Performance MySQL: Optimization, Backups, and Replication - Chapter 2 - by Baron Schwartz, Peter Zaitsev, Vadim Tkachenko

Replication

Both databases provide the ability to replicate data from one server to another. The main difference we saw here is that most MariaDB versions will allow you to replicate to them, from MySQL databases, which means you can easily migrate MySQL databases to MariaDB. The other way around isn’t that easy, as most MySQL versions won’t allow replication from MariaDB servers.

Also, it’s worth noting that MySQL GTID is different than MariaDB GTID, so once you replicate data from MySQL to MariaDB, the GTID data will be adjusted accordingly.

Few examples to the differences between the replication configurations:

• The default binlog format in MySQL is row based. In MariaDB, the default binlog format is mixed.
• Log_bin_compress - This feature determines whether or not the binary log can be compressed. This enhancement is unique to MariaDB and therefore isn’t supported by MySQL.

Incompatibilities between MySQL and MariaDB

MariaDB’s documentation lists hundreds of incompatibilities between MySQL and MariaDB databases, in different versions. The main conclusion from this documentation is that you can’t rely on an easy migration from one database type to another.

Most database administrators hoped that MariaDB will be kept as a branch of MySQL, so it will be very easy to migrate between the two. For the last few versions, that’s not the case anymore. For a long time now, MariaDB is actually a fork of MySQL, which means you need to put some thought when you migrate from one to another.

Storage engines

MariaDB supports more storage engines than MySQL. Said that, it’s not a matter of which database supports more storage engines, but rather which database supports the right storage engine for your requirements.

• Supported storage engines on MariaDB: XtraDB, InnoDB, MariaDB ColumnStore, Aria, Archive, Blackhole, Cassandra Storage Engine, Connect, CSV, FederatedX, Memory storage engine, Merge, Mroonga, MyISAM, MyRocks, QQGraph, Sequence Storage Engine, SphinxSE, Spider, TokuDB.
• Supported storage engines on MySQL - InnoDB, MyISAM, Memory, CSV, Archive, Blackhole, Merge, Federated, Example.

Deployed on Linux distributions by default

On some Linux distributions, when you install the MySQL database, you might end up actually installing the MariaDB database, as it’s the default in many Linux distributions (though not in all).

MariaDB will be installed by default on latest Red Hat Enterprise/CentOS/Fedora/Debian distributions. On the other hand, MySQL is still the default on other popular distributions such as Ubuntu.

Availability on cloud platforms

MariaDB is available as a service on Amazon Web Services (AWS), Microsoft Azure and Rackspace Cloud.

MySQL is available on all three platforms mentioned above, while also available on Google Cloud’s platform, as a managed service.

Therefore, if you are using GCP and would like your cloud provider to manage the service for you, you might have to consider using MySQL, unless you would like to install and manage MariaDB instances on your own.

Licensing

MariaDB Server is licensed as GPLv2, while MySQL has two licensing options - GPLv2 (for Community edition) and Enterprise.

The main difference between the two licenses for MySQL is the available features and support. While you receive the full-featured package when using MariaDB, that’s not the case with MySQL. The community edition doesn’t include features like the Threadpool, which can have a significant impact on the database and query performance.

Release-rate and updates

Usually, MariaDB has more frequent releases then MySQL. This reality has its pros and cons though. On the upside, features and bug fixes are released more frequently. On the other side, managing those MariaDB servers requires more updates to keep them up to do date at all times.

Technical Support

The MySQL support team, which includes both MySQL developers and support engineers, offer 24/7 support for customers. Oracle offers several support packages, including Extended support, Sustaining support and Premier support, depending on the customer's requirements. MariaDB's support team includes support engineers which are familiar and are experts with both MariaDB and MySQL databases (as many of the features were originally written by MySQL's team). They offer enterprise support for production systems, with 24/7 availability.

Ongoing Development

For MySQL, the exclusive developer is Oracle’s MySQL team. On the other hand, MariaDB’s development process is open for a public vote and mailing lists discussions. In addition, anyone can submit patches to MariaDB, which will be considered to be added to the main repository. Therefore, in a way, MariaDB is developed by the community, while MySQL is developed primarily by Oracle.

SQL Query Optimization

Whether you choose MySQL or MariaDB as your database vendor, you’ll probably end up struggling with some slow queries slowing down your application.

EverSQL offers an easy platform to optimize MySQL and MariaDB queries, which will highly contribute to the overall database performance.

Conclusions

Well, we can't make the decision for you. What we can do, is ask you the right questions to guide you to a decision:

1. Did you test your product's performance with both databases? Which one performed better on average, and why?
2. Are you planning to use a feature which is exclusively available in one of these databases?
3. Are you aiming to use one of the database engines which is supported exclusively in one of these databases?
4. How important is it for you to be able to have an impact on the development process of the database you're using? How important is it for you to have the community vote for the next changes?
5. Are you going to pay for enterprise versions or use the community version? Does the community version have enough features to meet your requirements?
6. Does your OS support the chosen database by default? How easy will it be for you to deploy it?
7. Which cloud provider are you using? Do they offer a managed service which includes the database you've chosen?
8. Are you planning to migrate from one database type to another in the future? If so, did you think about the implications in terms of incompatibilities and replication?

Once you answer these questions, you probably already have a good idea about which database is the right choice for you.