Differences between MariaDB Server and MySQL

1 .Differences between MariaDB and MySQL Colin Charles, Chief Evangelist, Percona Inc. colin.charles@percona.com / byte@bytebot.net http://bytebot.net/blog/ | @bytebot on Twitter Percona Webminar 12 September 2017 

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

3 .Agenda 

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

5 . Why this matters • MariaDB Server is the “default” MySQL in pretty much every Linux distribution except Ubuntu • It isn’t MySQL - there are also many cloud providers that have an option for MariaDB • There are incompatibilities 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 

6 . 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.” 

7 . Licensing • MariaDB Server: GPLv2 only • MariaDB MaxScale: Business Source License • MariaDB ColumnStore: GPLv2 only • MySQL has Community and Enterprise releases 

8 . Support • What is the support ecosystem and landscape like? • Training? • MySQL Certification (recently MariaDB has started this at m17) 

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

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

11 . 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 

12 . What’s documented? • https://mariadb.com/kb/en/the-mariadb-library/mariadb-vs-mysql- compatibility/ • Tracker bug: https://jira.mariadb.org/browse/MDEV-10392 • https://mariadb.com/kb/en/library/system-variable-differences-between- mariadb-and-mysql/ 

13 .Replication 

14 . GTID variances • https://mariadb.com/kb/en/library/gtid/#the-domain-id 

15 . Replication • Default binlog format is now MIXED • Default replicate_annotate_row_events is ON • Binlog event compression • Time delayed replication arrives • 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 

16 . 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 

17 . 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/ 

18 .

19 .10.0 

20 .10.1/10.2 

21 . 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 

22 . X Protocol • MariaDB Server has no support for the MySQL X Protocol • This means you cannot use mysqlsh to access MariaDB Server either 

23 . 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) • One does not encrypt logs, the other does • 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 

24 . CJK language support • Add “ngram” support to MariaDB Server: https://jira.mariadb.org/browse/ MDEV-10267 • Add “MeCab” support to MariaDB Server: https://jira.mariadb.org/browse/ MDEV-10268 • gb18030 support: https://jira.mariadb.org/browse/MDEV-7495 

25 . PERFORMANCE_SCHEMA • No sys schema - https://jira.mariadb.org/browse/MDEV-9077 • No new PERFORMANCE_SCHEMA instrumentation from 5.7 - https:// jira.mariadb.org/browse/MDEV-6114 • e.g. 52 rows in set (0.00 sec) vs. 87 rows in set (0.00 sec) 

26 . Security • MySQL: sha256_password • MariaDB: ed25519 password plugin • Needless to say the above are incompatible with each other • validate_password is on by default in MySQL 5.7 (not in MariaDB) 

27 . Other bits • MariaDB: SHOW EXPLAIN FOR <thread_id> • MySQL: EXPLAIN FOR CONNECTION <thread_id> • https://jira.mariadb.org/browse/MDEV-10000 • MySQL has SUPER READONLY, missing in MariaDB - https://jira.mariadb.org/browse/ MDEV-9458 • Optimiser trace: https://jira.mariadb.org/browse/MDEV-6111 • Replication crash-safety for non-GTID slaves - https://jira.mariadb.org/browse/MDEV-8946 • Minimal/NOBLOB Binlog Row Image replication fails when tables from master have different PK in slave - https://jira.mariadb.org/browse/MDEV-8398 

28 . Installation… • MySQL: https://dev.mysql.com/downloads/repo/yum/ - grab a package — e.g. mysql57-community-release-el7-9.noarch.rpm • MariaDB Server: https://downloads.mariadb.org/mariadb/repositories/ - copy/paste, edit a file, then install • Percona Server: https://www.percona.com/doc/percona-server/5.7/ installation/yum_repo.html - yum install Percona-Server-server-57 

29 . Starting up • MySQL / Percona Server • service mysqld start • grep 'temporary password' /var/log/mysqld.log • ALTER USER 'root'@'localhost' IDENTIFIED BY ‘rootmeOK!’; • MariaDB Server? mysql -uroot “just works”