EmergencyEMERGENCY? Get 24/7 Help Now!

MySQL vs. MariaDB: Reality Check

 | November 2, 2017 |  Posted In: InnoDB, Insight for DBAs, MariaDB, MySQL

PREVIOUS POST
NEXT POST

MySQL vs. MariaDBIn this blog, we’ll provide a comparison between MySQL vs. MariaDB (including Percona Server for MySQL).

Introduction

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
Community –
Source Code
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
Core –
Replication
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
Core –
Routing
MySQL Router (GPLv2) ProxySQL (GPLv3) MariaDB MaxScale (Business Source License)
Core –
Partitioning
Standard Standard Standard, with extra engines like SPIDER/CONNECT that offer varying levels of support
Tool –
Editing
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)
Tool –
Monitoring
MySQL Enterprise Monitor Percona Monitoring & Management (PMM) (100% open source) Webyog’s Monyog
Scalability –
Client Connections
MySQL Enterprise Threadpool Open Source Threadpool with support for priority tickets Open Source Threadpool
Scalability –
Clustering
MySQL Group Replication MySQL Group Replication, Percona XtraDB Cluster (based on a further engineered Galera Cluster) MariaDB Enterprise Cluster (based on Galera Cluster)
Security –
Encryption
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
Security –
Data Masking
ProxySQL data masking ProxySQL data masking MariaDB MaxScale data masking
Security –
Firewall
MySQL Enterprise Firewall ProxySQL Firewall MariaDB MaxScale Firewall
Security –
Auditing
MySQL Enterprise Audit Plugin Percona Audit Plugin (OSS) MariaDB Audit Plugin (OSS)
Analytics No ClickHouse MariaDB ColumnStore
SQL –
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
SQL –
Window Functions
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
Temporal –
Log-based rollback
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
Official
client connectors
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
_SCHEMA
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)
Security –
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>
Optimiser –
Optimiser Tracing
Yes Yes No
Optimiser –
Optimiser Hints
Yes Yes No
DBA –
Super readonly mode
Yes Yes No
Security – Password expiry Yes Yes No
Security – Password last changed? Password lifetime? Yes Yes No
Security – VALIDATE_PASSWORD
_STRENGTH()
Yes Yes No
Security – ACCOUNT LOCK/UNLOCK Yes Yes No
Usability – Query Rewriting Yes Yes No
GIS – GeoJSON &
GeoHash functionality
Yes Yes Incomplete
Security – mysql_ssl_rsa_setup Yes Yes No (setup SSL connections manually)
MySQL Utilities Yes Yes No
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.

Open Community

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.

MySQL vs. MariaDB

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.

Extensibility

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.

Analytics

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.

At Percona, we are quite excited by ClickHouse. We also have plenty of content around it. There is no MySQL story around this.

High Availability

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.

Security

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.

Conclusion

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.

PREVIOUS POST
NEXT POST
Colin Charles

Colin Charles is the Chief Evangelist at Percona. He was previously on the founding team for MariaDB Server in 2009, worked in MySQL since 2005, and been a MySQL user since 2000. Before joining MySQL, he worked actively on the Fedora and OpenOffice.org projects. He's well known within many open source communities, and has spoken on the conference circuit.

16 Comments

  • I would like to note two important differences that make MariaDB lack of compatibility very important for evaluation:
    (1) In matter of instrumentation, MySQL has implemented performance_schema tables for replication, while MariaDB has only a tiny fraction of the instrumentation available in regular tables. MariaDB has less information than MySQL when using multi-source replication and parallel threads.
    (2) MySQL 8.0 implements a completely new data dictionary. Percona Server will get it just fine, while for MariaDB the adoption would be problematic, given how behind it is with MySQL compatibility. The data dictionary guarantees higher performance and better data consistency and it is fairly well integrated with the rest of the server.

  • I will add to this only that I would really, really love to see Percona contribute a supported Gentoo ebuild for Percona XtraDB Cluster.

    There is an ebuild for Percona Server already, but not for XtraDB Cluster, which has forced me into running MariaDB Galera Cluster on my own systems — and it is very clear that MariaDB did not do nearly as good or as thorough a job of integrating Galera as Percona did. For a long time, in testing at work we simply could not get MariaDB Galera Cluster to form a cluster at all, and while it does work now, it can still be difficult to recover it from an all-nodes-down condition, especially if it is allowed to attempt to autostart.

    • Hi Phil,

      I would love to see more customer demand for Gentoo which would allow us to allocate resources to do official build for Gentoo. We are performing extensive testing of our software on all platforms we support so adding another platform is not a trivial investment.

      Thank you for your encouraging words on Percona XtraDB Cluster!

  • For replication-manger, MariaDB, lack super_read_only mode, forcing no writes can happen during critical sections. Useful when using DNS ( Consul, mdns) to point to a master. Is MariaDB ignoring this feature request to force users to use maxscale, ignoring many other open source community around ? I don’t think that is the case, support for haproxy proxy protocol was added by Vlad, so please gave us that feature as well 🙂

  • Mariadb purpose is not to overwhelm mysql or other.
    If you know the history of mariadb you will understand what I mean. I use mariadb that’s ok for me. But if you need more database “power” you can go for mysql or percona.

  • I wonder why MySQL NDB Cluster have not been mentioned in the comparison.
    It is not a small development on MySQL part and has a lot of important functionallity

  • My take on the best thing about MariaDB — so many talented people working on it. But I feel the same about upstream MySQL and Percona. I hope we continue to figure out how to share the improvements we make in each community.

  • Hey, can you tell me where to find the Announce of MariaDB for this:
    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.

    • They claim “drop in replacement” today. I am not sure what that means because that is not as strong as “drop in compatible” but it is easy to confuse one for the other.
      https://mariadb.com/kb/en/library/mariadb-vs-mysql-compatibility/

  • Password expiry does not enhance security, thus implementing such a thing for MySQL merely appeases some outdated corporate ideas rather than deal with any real issue. I would not consider it a feature.
    Please refer to https://pages.nist.gov/800-63-3/sp800-63-3.html (NIST Digital Identities Guidelines) for more information on what actual password security needs to do and not do.

Leave a Reply to Giuseppe Maxia Cancel reply