Differences between MariaDB and MySQL

它们在语法上相似吗?这两种语言有什么不同?为什么我要用一个而另一个?Mariadb10.3正逐步脱离MySQL8.0。一个明显的例子是目前正在为MySQL8.0开发的内部数据字典。这是对元数据在服务器中存储和使用方式的重大更改,Mariadb没有同等的功能。实现这个特性可以标志着MySQL和Mariadb之间的数据文件级兼容性的结束。MySQL8.0和Mariadb10.3之间也存在非技术差异,包括:许可:MySQL在GPL下提供开源代码,并以MySQL企业的形式提供非GPL商业发行的选项。Mariadb只能使用GPL,因为它们的工作是根据该许可证的条款从MySQL源代码派生的。支持服务:Oracle为MySQL提供技术支持、培训、认证和咨询,Mariadb有自己的支持服务。有些人更喜欢与小公司合作,因为传统上,它为他们提供了更多作为客户的杠杆作用。社区贡献:Mariadb吹捧他们比Oracle接受更多的社区贡献。造成这种差异的部分原因是,开发人员喜欢贡献特性、错误修复和其他代码,而不需要大量的文书工作开销(他们抱怨Oracle贡献者协议)。然而,Mariadb有自己的Mariadb出资人协议——这或多或少是出于同样的目的。Colin将研究Mariadb10.3和MySQL8.0之间的一些差异,并帮助回答我们的数据库性能专家关于这两个数据库的一些常见问题。

展开查看详情

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