Differences between MariaDB Server and MySQL

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

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

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 (shipping MySQL 5.7) • It isn’t MySQL - there are also many cloud providers that have an option for MariaDB • Amazon Web Services (AWS) RDS MariaDB, Microsoft Azure, Rackspace Cloud • 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 • Backup & Restore, ColumnStore Kafka data adapter, MariaDB MaxScale CDC Data Adapter: Business Source License • MySQL has Community (GPLv2) and Enterprise releases

8. Support • What is the support ecosystem and landscape like? • Training? • MySQL Certification (MariaDB Corporation started certification 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 code?

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”