Differences between MariaDB and MySQL

1 .Differences between MariaDB and MySQL — MariaDB 10.3 vs. MySQL 8.0 Colin Charles, Chief Evangelist, Percona Inc. colin.charles@percona.com / byte@bytebot.net http://bytebot.net/blog/ | @bytebot on Twitter Percona Webinar 18 July 2018 

2 .whoami • Chief Evangelist, Percona Inc • Founding team of MariaDB Server (2009-2016) [Monty Program Ab, merged with SkySQL Ab, now MariaDB Corporation] • Formerly MySQL AB (exit: Sun Microsystems) • Past lives include The Fedora Project (FESCO), OpenOffice.org • MySQL Community Contributor of the Year Award winner 2014 

3 .License • Creative Commons BY-NC-SA 4.0 • https://creativecommons.org/licenses/by-nc-sa/4.0/legalcode 
 

4 .A good base blog post resource • High level, answer to a whitepaper • https://www.percona.com/blog/2017/11/02/mysql-vs-mariadb- reality-check/ • Watch the blog for updates after this webinar (since we do want to compare MySQL 8 to MariaDB Server 10.3) • and also, watch it after we release Percona Server for MySQL 8! • Versions compared in this webinar: • MySQL 8.0.11 (released: 19 April 2018) • MariaDB Server 10.3.8 (released: 2 July 2018) 

5 .Define: compatibility (OED) • A state in which two things are able to exist or occur together without problems or conflict. 

6 .Why this matters • MariaDB Server is the “default” MySQL in many Linux distributions • not Ubuntu! (shipping MySQL 5.7) • MariaDB isn’t MySQL - there are many cloud providers that have an option for MariaDB • Amazon Web Services (AWS) RDS MariaDB, Microsoft Azure, Rackspace Cloud • There are incompatibilities (e.g. with connectors) • https://github.com/brianmario/mysql2/issues/878 • MariaDB Connector/C for MySQL and MariaDB Server. It is libmysqlclient API compatible. LGPL. OpenSSL/GnuTLS/schannel (no more yaSSL/wolfSSL) • header change in MariaDB 10.2.6 and mysqlclient python binding - https:// lists.launchpad.net/maria-developers/msg10726.html 

7 .Commitments • Verbal commitments: “MySQL 5.6, should be comparable to MariaDB Server 10.1. And for 10.2 it should be compatible with MySQL 5.7” — Michael “Monty” Widenius, CTO of MariaDB Corporation and MariaDB Foundation, 7 October 2016, MariaDB Developer’s Meeting, Amsterdam • http://mariadb.org/about/ • “It is an enhanced, drop-in replacement for MySQL.” 

8 .Licensing • MariaDB Server: GPLv2 only • MariaDB MaxScale: Business Source License • MariaDB ColumnStore: GPLv2 only • Backup & Restore, ColumnStore Kafka data adapter, MariaDB MaxScale CDC Data Adapter: Business Source License • MySQL has Community (GPLv2) and Enterprise releases 

9 .Support • What is the support ecosystem and landscape like? • Training? • MySQL Certification (MariaDB Corporation started certification at M| 17) 

10 .Community Contributions • Oracle Contributor Agreement (OCA) • MariaDB Contributor Agreement (MCA) • BSD New • Who maintains the code? What is the state of community contributed code? 

11 .Governance • MariaDB Corporation • MariaDB Foundation • Is there vendor lock-in in open source? • How many users are there, really? 

12 .Releases MariaDB MySQL 5.1: 1 Feb 2010 5.1: 14 Nov 2008 5.2: 10 Nov 2010 5.3: 29 Feb 2012 5.5: 11 Apr 2012 5.5: 3 Dec 2010 5.6: 5 Feb 2013 10.0: 31 Mar 2014 10.1: 17 Oct 2015 5.7: 21 Oct 2015 10.2: 23 May 2017 8.0: 19 Apr 2018 10.3: 25 May 2018 

13 .

14 .What’s documented? • https://mariadb.com/kb/en/the-mariadb-library/mariadb-vs-mysql- compatibility/ • (closed!?)Tracker bug: https://jira.mariadb.org/browse/MDEV-10392 • https://mariadb.com/kb/en/library/system-variable-differences- between-mariadb-and-mysql/ • https://mariadb.com/kb/en/library/incompatibilities-and-feature- differences-between-mariadb-102-and-mysql-57/ 

15 .Replication 

16 .GTID variances between MariaDB & MySQL • https://mariadb.com/kb/en/library/gtid/#the-domain-id 

17 .Replication • Default binlog format is now MIXED (ROW in MySQL) • Default replicate_annotate_row_events is ON • Binlog event compression - log_bin_compress • Time delayed replication (present in MySQL 5.6; arrived in MariaDB 10.2) • read_binlog_speed_limit - restricting the speed at which the slave reads the binlog from the master • DML only Flashback - rollback instances/databases/tables to an older snapshot (via Alibaba!) • Continuous streaming binary log backup added to mysqlbinlog in 10.2 

18 .5.1/5.2 • mysqld reads [mariadb] part of my.cnf for MariaDB Server only options • Binary-only storage engines won’t work without recompilation due do different THD structure (e.g. commercial engines like ScaleDB) • Extended slow query log statistics (microslow patch from Percona) • More memory utilised: Aria used to handle internal temporary tables, needs configuration • MariaDB only: table elimination 

19 .5.3 • Error numbers for MariaDB are at 1900+; MySQL has to deal: https:// bugs.mysql.com/bug.php?id=72062 • Microseconds arrived; but got fixed in MariaDB 10.1 to follow the MySQL 5.6 format • SHOW PROCESSLIST with progress reporting • New features: dynamic columns, virtual columns (5.7), HandlerSocket plugin, Cassandra storage engine (now deprecated) • Huge changes in optimiser + replication • https://mariadb.com/kb/en/library/optimizer-feature-comparison-matrix/ 

20 .

21 .10.0 

22 .10.1 

23 .10.2 

24 .10.2 

25 .10.2 

26 .

27 .JSON • 5.7 has a binary data type, MariaDB chooses not to implement it this way, choosing to not “violate the SQL standard” - https:// jira.mariadb.org/browse/MDEV-9144 • claims it is as fast, but there are no benchmarks - https:// jira.mariadb.org/browse/MDEV-13777 

28 .X Protocol • MariaDB Server has no support for the MySQL X Protocol • This means you cannot use mysqlsh to access MariaDB Server • This is significant as a limitation 

29 .Encryption • MySQL 5.7 and MariaDB Server 10.1+ implement encryption differently (one is fully tablespace encryption, the other is based on the Google patch for tablespace encryption in addition to having table encryption via Eperi) • MySQL 8 encrypts redo/undo logs via configuration options; temporary tablespace or binary log encryption does not exist (MariaDB supports binary log encryption, and temporary table encryption) • MySQL requires innodb_file_per_table • MySQL implementation works fully with Percona XtraDB Cluster • MariaDB Galera Cluster gcache is unencrypted - https://jira.mariadb.org/browse/MDEV-9639 • mysqlbinlog cannot read encrypted binary logs - https://jira.mariadb.org/browse/MDEV-8813 • workaround is use —read-from-remote-server as the server has access to the keys via the encryption plugin API. This adds load to the server! • Backups: you have to use MariaDB Backup for encrypted backups (Percona XtraBackup fork; upstream does not work) • Key management: Percona has Vault, MariaDB has an AWS Key Management Plugin, MySQL has several but they are part of the Enterprise release