MySQL vs. MariaDB: Reality CheckColin Charles
In this blog, we’ll provide a comparison between MySQL vs. MariaDB (including Percona Server for MySQL).
The goal of this blog post is to evaluate, at a higher level, MySQL, MariaDB and Percona Server for MySQL side-by-side to better inform the decision making process. It is largely an unofficial response to published comments from the MariaDB Corporation.
It is worth noting that Percona Server for MySQL is a drop-in compatible branch of MySQL, where Percona contributes as much as possible upstream. MariaDB Server, on the other hand, is a fork of MySQL 5.5. They cherry-picked MySQL features, and don’t guarantee drop-in compatibility any longer.
|MySQL||Percona Server for MySQL*||MariaDB Server|
|Protocols||MySQL protocol over port 3306, X Protocol over port 33060||MySQL protocol over port 3306, X Protocol over port 33060||MySQL protocol, MariaDB Server extensions|
|Open Source||Open Source||Open Source|
|Community – Development||Open Source, contributions via signing the Oracle Contributor Agreement (OCA)||Open Source||Open Source, contributions via the new BSD license or signing the MariaDB Contributor Agreement (MCA)|
|Community – Collaboration||Mailing list, forums, bugs system||Mailing list, forums, bugs system (Jira, Launchpad)||Mailing list, bugs system (Jira), IRC channel|
|MySQL replication with GTID||MySQL replication with GTID||MariaDB Server replication, with own GTID, compatible only if MariaDB Server is a slave to MySQL, not vice versa|
|MySQL Router (GPLv2)||ProxySQL (GPLv3)||MariaDB MaxScale (Business Source License)|
|Standard||Standard||Standard, with extra engines like SPIDER/CONNECT that offer varying levels of support|
|MySQL Workbench for Microsoft Windows, macOS, and Linux||MySQL Workbench for Microsoft Windows, macOS, and Linux||Webyog’s SQLYog for Microsoft Windows (MySQL Workbench notes an incompatible server)|
|MySQL Enterprise Monitor||Percona Monitoring & Management (PMM) (100% open source)||Webyog’s Monyog|
|MySQL Enterprise Threadpool||Open Source Threadpool with support for priority tickets||Open Source Threadpool|
|MySQL Group Replication||MySQL Group Replication, Percona XtraDB Cluster (based on a further engineered Galera Cluster)||MariaDB Enterprise Cluster (based on Galera Cluster)|
|Tablespace data-at-rest encryption. Amazon KMS, Oracle Vault Enterprise Edition||Tablespace data-at-rest encryption with Keyring Vault plugin||Tablespace and table data-at-rest encryption. Amazon KMS, binlog/redo/tmp file with Aria tablespace encryption|
|ProxySQL data masking||ProxySQL data masking||MariaDB MaxScale data masking|
|MySQL Enterprise Firewall||ProxySQL Firewall||MariaDB MaxScale Firewall|
|MySQL Enterprise Audit Plugin||Percona Audit Plugin (OSS)||MariaDB Audit Plugin (OSS)|
Common Table Expressions
|In-development for MySQL 8.0 (now a release candidate)||In-development for MySQL 8.0 (now a release candidate)||Present in MariaDB Server 10.2|
|In-development for MySQL 8.0 (now a release candidate)||In-development for MySQL 8.0 (now a release candidate)||Present in MariaDB Server 10.2|
|No||No||In development for MariaDB Server 10.3|
|Temporal – system versioned tables||No||No||In development for MariaDB Server 10.3|
|JSON||JSON Data type, 21 functions||JSON Data type, 21 functions||No JSON Data Type, 26 functions|
|C (libmysqlclient), Java, ODBC, .NET, Node.js, Python, C++, mysqlnd for PHP||C (libmysqlclient), Java, ODBC, .NET, Node.js, Python, C++, mysqlnd for PHP||C (libmariadbclient), Java, ODBC|
|Usability – CJK Language support||Gb18030, ngram & MeCab for InnoDB full-text search||Gb18030, ngram & MeCab for InnoDB full-text search||No|
|Monitoring – PERFORMANCE
|Thorough instrumentation in 5.7, sys schema included||Thorough instrumentation in 5.7, sys schema included||Instrumentation from MySQL 5.6, sys schema not included|
|Security – Password authentication||sha256_password (with caching_sha2_password in 8.0)||sha256_password (with caching_sha2_password in 8.0)||ed25519 (incompatible with sha256_password)|
Secure out of the box
|validate_password on by default, to choose a strong password at the start||validate_password on by default, to choose a strong password at the start||No|
|Usability – Syntax differences||EXPLAIN FOR CONNECTION <thread_id>||EXPLAIN FOR CONNECTION <thread_id>||SHOW EXPLAIN FOR <thread_id>|
Super readonly mode
|Security – Password expiry||Yes||Yes||No|
|Security – Password last changed? Password lifetime?||Yes||Yes||No|
|Security – VALIDATE_PASSWORD
|Security – ACCOUNT LOCK/UNLOCK||Yes||Yes||No|
|Usability – Query Rewriting||Yes||Yes||No|
|GIS – GeoJSON &
|Security – mysql_ssl_rsa_setup||Yes||Yes||No (setup SSL connections manually)|
|Backup locks||No (in development for 8.0)||Yes||No|
|Usability – InnoDB memcached interface||Yes||Yes||No|
*Note. Third-party software (such as ProxySQL and ClickHouse) used in conjunction with Percona Server for MySQL is not necessarily covered by Percona Support services.
To get a higher level view of what Percona Server for MySQL offers compared to MySQL, please visit: Percona Server Feature Comparison. Read this for a higher level view of compatibility between MariaDB Server and MySQL written by MariaDB Corporation.
MariaDB Server undoubtedly has an open community, with governance mixed between MariaDB Foundation and MariaDB Corporation. There are open developer meetings on average about twice per year, two mailing lists (one for developers and users), an IRC channel and an open JIRA ticket system that logs bugs and feature requests.
Percona Server for MySQL also has an open community. Developer meetings are not open to general contributors, but there is a mailing list, an IRC channel and two systems – Launchpad and JIRA – for logging bugs and feature requests.
MySQL also has an open community where developer meetings are also not open to general contributors. There are many mailing lists, there are a few IRC channels and there is the MySQL bugs system. The worklogs are where the design for future releases happens, and these are opened up when their features are fully developed and source-code-pushed.
From a source code standpoint, MySQL makes pushes to Github when a release is made; whereas open source development happens for Percona Server for MySQL and MariaDB Server on Github.
Feature development on MySQL continues in leaps and bounds, and Oracle has been an excellent steward of MySQL. Please refer to The Complete List of Features in 5.7, as well as The Unofficial MySQL 8 Optimiser Guide.
Linux distributions have chosen MariaDB Server 5.5, and some have chosen MariaDB Server 10.0/10.1 when there was more backward compatibility to MySQL 5.5/5.6. It is the “default” MySQL in many Linux distributions (such as Red Hat Enterprise Linux, SUSE and Debian). However, Ubuntu still believes that when you ask for MySQL you should get it (and that is what Ubuntu ships).
One of the main reasons Debian switched was due to the way Oracle publishes updates for security issues. They are released as a whole quarterly as Critical Patch Updates, without much detail about individual fixes. This is a policy that is unlikely to change, but has had no adverse effects on distribution.
All projects actively embrace contributions from the open community. MariaDB Server does include contributions like the MyRocks engine developed at Facebook, but so does Percona Server for MySQL. Oracle accepts contributions from a long list of contributors, including Percona. Please see Licensing information for MySQL 5.7 as an example.
A Shared Core Engine
MariaDB Server has differed from MySQL since MySQL 5.5. This is one reason why you don’t get version numbers that follow the MySQL scheme. It is also worth noting that features are cherry-picked at merge time, because the source code has diverged so much since then.
As the table below shows, it took Percona Server for MySQL over four months to get a stable 5.5 release based on MySQL 5.5, while it took MariaDB Server one year and four months to get a stable 5.5 release based on MySQL 5.5. Percona Server for MySQL 5.6 and 5.7 are based on their respective MySQL versions.
|MySQL||Percona Server for MySQL||MariaDB Server|
|3 December 2010||5.5.8 GA|
|28 April 2011||5.5.11-20.2 GA|
|11 April 2012||5.5.23 GA|
|5 February 2013||5.6.10 GA|
|7 October 2013||5.6.13-61.0 GA|
|31 March 2014||10.0.10 GA|
|17 October 2015||10.1.8 GA|
|21 October 2015||5.7.9 GA|
|23 February 2016||5.7.10-3 GA|
|23 May 2017||10.2.6 GA|
MySQL is currently at 8.0.3 Release Candidate, while MariaDB Server is at 10.3.2 Alpha as of this writing.
MariaDB Server is by no means a drop-in replacement for MySQL. The risk of moving to MariaDB Server if you aren’t using newer MySQL features may be minimal, but the risk of moving out of MariaDB Server to MySQL is very prevalent. Linux distributions like Debian already warn you of this.
The differences are beyond just default configuration options. Some features, like time-delayed replication that were present in MySQL since 2013, only make an appearance in MariaDB Server in 2017! (Refer to the MariaDB Server 10.2 Overview for more.) However, it is also worth noting some features such as multi-source replication appeared in MariaDB Server 10.0 first, and only then came to MySQL 5.7.
MySQL and MariaDB Server have a storage engine interface, and this is how you access all engines, including the favored InnoDB/Percona XtraDB. It is worth noting that Percona XtraDB was the default InnoDB replacement in MariaDB Server 5.1, 5.2, 5.3, 5.5, 10.0 and 10.1. But in MariaDB Server 10.2, the InnoDB of choice is upstream MySQL.
Stock MySQL has provided several storage engines beyond just InnoDB (the default) and MyISAM. You can find out more information about 5.7 Supported Engines.
Percona Server for MySQL includes a modified MEMORY storage engine, ships Percona XtraDB as the default InnoDB and also ships TokuDB and MyRocks (currently experimental). MyRocks is based on the RocksDB engine, and both are developed extensively at Facebook.
MariaDB Server includes many storage engines, beyond the default InnoDB. MyISAM is modified with segmented key caches, the default temporary table storage engine is Aria (which is a crash-safe MyISAM), the FederatedX engine is a modified FEDERATED engine, and there are more: CONNECT, Mroonga, OQGRAPH, Sequence, SphinxSE, SPIDER, TokuDB and of course MyRocks.
Storage engines have specific use cases, and have different levels of feature completeness. You should thoroughly evaluate a storage engine before choosing it. We believe that over 90% of installations are fine with just InnoDB or Percona XtraDB. Percona TokuDB is another engine that users who need compression could use. We naturally expect more usage in the MyRocks sphere going forward.
MariaDB ColumnStore is the MariaDB solution to analytics and using a column-based store. It is a separate download and product, and not a traditional storage engine (yet). It is based on the now defunct InfiniDB product.
High Availability is an exciting topic in the MySQL world, considering the server itself has been around for over 22 years. There are so many solutions out there, and some have had evolution as well.
MySQL provides MySQL Cluster (NDBCLUSTER) (there is no equivalent in the MariaDB world). MySQL also provides group replication (similar to Galera Cluster). Combined with the proxy MySQL Router, and the mysqlsh for administration (part of the X Protocol/X Dev API), you can also get MySQL InnoDB Cluster.
We benefit from the above at Percona, but also put lots of engineering work to make Percona XtraDB Cluster.
MariaDB Server only provides Galera Cluster.
While we don’t want to compare the proprietary MySQL Enterprise Firewall, MariaDB’s recommendation is the proprietary, non-open source MariaDB MaxScale (it uses a Business Source License). We highly recommend the alternative, ProxySQL.
When it comes to encryption, MariaDB Server implements Google patches to provide complete data at rest encryption. This supports InnoDB, XtraDB and Aria temporary tables. The log files can also be encrypted (not present in MySQL, which only allows tablespace encryption and not log file encryption).
When it comes to attack prevention, ProxySQL should offer everything you need.
MySQL Enterprise provides auditing, while MariaDB Server provides an audit plugin as well as an extension to the audit interface for user filtering. Percona Server for MySQL has an audit plugin that sticks to the MySQL API, yet provides user filtering and controls the ability to audit (since auditing is expensive). Streaming to syslog is supported by the audit plugins from Percona and MariaDB.
Supporting Ecosystem and Tools
Upgrading from MySQL to MariaDB Server should be a relatively simple process (as stated above). If you want to upgrade away from MariaDB Server to MySQL, you may face hassles. For tools, see the following table:
|Purpose||MySQL||Percona Server for MySQL||MariaDB Server|
|Monitoring||MySQL Enterprise Monitor||Percona Monitoring & Management (PMM) (100% open source)||Webyog Monyog|
|Backup||MySQL Enterprise Backup||Percona XtraBackup||MariaDB Backup (fork of Percona XtraBackup)|
|SQL Management||MySQL Workbench||MySQL Workbench||Webyog SQLyog|
|Load Balancing & Routing||MySQL Router||ProxySQL||MariaDB MaxScale|
|Database Firewall||MySQL Enterprise Firewall||ProxySQL||MariaDB MaxScale|
Enterprise Database Compatibility
MariaDB Server today has window functions and common table expressions (CTEs). These appeared in MariaDB Server 10.2. MySQL 8 is presently in release candidate status and also has similar functionality.
Looking ahead, MariaDB Server 10.3 also includes an Oracle SQL_MODE and a partial PL/SQL parser. This is to aid migration from Oracle to MariaDB Server.
MariaDB Server 10.2 also has “flashback”, developed at Alibaba, to help with log-based rollback using the binary log.
Percona sees healthy competition in the MySQL ecosystem. We support all databases in the ecosystem: MySQL, MariaDB Server and Percona Server for MySQL. Our focus is to provide alternatives to proprietary parts of open source software. Percona has a strong operations focus on compatibility, application scalability, high availability security and observability. We also support many additional tools within the ecosystem, and love integrating and contributing to open source code.
For example, Percona Monitoring and Management (PMM) includes many open source tools like Prometheus, Consul, Grafana, Orchestrator and more. We have made the de facto open source hot backup solution for MySQL, MariaDB Server and Percona Server for MySQL (called Percona XtraBackup). We continue to maintain and extend useful tools for database engineers and administrators in Percona Toolkit. We make Percona XtraDB Cluster safe for deployment out of the box. We have invested in a write-optimized storage engine, TokuDB, and now continue to work with making MyRocks better.
We look forward to supporting your deployments of MySQL or MariaDB Server, whichever option is right for you! If you need assistance on migrations between servers, or further information, don’t hesitate to contact your friendly Percona sales associate.