MariaDB Server 10.2 - The Complete Guide

新的Mariadb Server 10.2版本已发布。它有一些有趣的新特性,但除了特性概述之外,我们还需要了解如何使用它们。这次谈话将讨论Mariadb10.2提供的所有新功能。
在本次网络研讨会中,我们将学习窗口函数、公共表表达式、更细粒度的创建用户语句等,包括让mysqlbinlog与mysql进行对等。
mysql中还没有独特的mariadb特性,比如静态加密、集成galera集群、线程池、innodb碎片整理、角色、扩展regexp等。
它将描述所有新特性,包括MySQL兼容和Mariadb独有的特性,并显示使用示例和实际用例。我们还将回顾Mariadb Server 10.3的功能路线图。

展开查看详情

1.MariaDB Server 10.2: The somewhat Complete Guide Colin Charles, Chief Evangelist, Percona Inc colin.charles@percona.com / byte@bytebot.net http://bytebot.net/blog/ | @bytebot on Twitter Originally at Percona Live Santa Clara, California, USA on 26 April 2017, now as a webminar on 27 June 2017

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

3. whoami • Chief Evangelist (in the CTO office), 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 lives include Fedora Project (FESCO), OpenOffice.org • MySQL Community Contributor of the Year Award winner 2014 3

4.What is MariaDB Server? 4

5. What is MariaDB Server, really? • GPLv2 fork of MySQL 5.5 with lots of added community development • Different features • Generally application compatible with aims for feature completeness with MySQL • A somewhat “drop-in replacement” (from an upgrade standpoint)

6. MariaDB thru the years • MariaDB 5.1 • MariaDB 10.3 alpha • MariaDB 5.2 • MariaDB Galera Cluster 5.5 • MariaDB 5.3 • MariaDB Galera Cluster • MariaDB 5.5 10.0 • MariaDB 10.0 • C Connector • MariaDB 10.1 • Java Connector • MariaDB 10.2 • ODBC Connector

7. MariaDB governance • MariaDB Corporation • Releases other products like MariaDB MaxScale, MariaDB ColumnStore • MariaDB Foundation • Ensuring continuity and open collaboration in the MariaDB ecosystem

8. MariaDB Server 5.1 • Aria as the temporary table storage engine • Thread pool v1 • Table Elimination - https://mariadb.com/kb/en/mariadb/ what-is-table-elimination/ • Microsecond precision in processlist • select id, time, time_ms, command, state from information_schema.processlist, (select sleep(2)) t;

9. MariaDB Server 5.2 • Virtual columns (ala generated columns in MySQL 5.7) • Pluggable authentication • User statistics

10. MariaDB Server 5.3 • Optimiser, optimser, optimiser - https:// mariadb.com/kb/en/mariadb/optimizer-feature- comparison-matrix/ • Many replication improvements that are only present in newer MySQL • HandlerSocket • Dynamic columns

11. MariaDB Server 5.5 • LIMIT ROWS EXAMINED • non-blocking client API • Threadpool v2 • MySQL 5.5 base

12. MariaDB Server 10.0 • replication: parallel replication, GTID, multi- source replication • engines: cassandra, connect, spider, tokudb, mroonga • show explain + output in slow query log • roles, pcre regex

13. Why MariaDB? • MySQL ecosystem development is at its most vibrant now than it has ever been • Community can get features inside a shipping server with ease • Storage engine vendors get shipping & wide distribution including testing • Roadmaps are public on Jira

14.Where is MariaDB found? • http://mariadb.org/ • Your Linux/BSD distribution - it is the default in RHEL 7, SUSE Enterprise 12, Debian 9, openSUSE, CentOS, Fedora, OpenBSD, etc. • Pivotal.io CloudFoundry, RackSpace Cloud, Azure, Amazon AWS RDS, etc.

15. The 10.1 “community release” • 30 Jun 2014 - MariaDB 10.1.0 • 9 September 2015 - MariaDB 10.1.7 RC • 17 Oct 2014 - MariaDB 10.1.1 • 17 October 2015 - MariaDB • 7 Dec 2014 - MariaDB 10.1.2 10.1.8 GA • 2 March 2015 - MariaDB • 23 Nov 2015, 24 Dec 2015, 29 10.1.3 Jan 2016, 25 Feb 2016, 25 March 2016, 10 May 2016, 18 • 13 April 2015 - MariaDB 10.1.4 July 2016, 30 Aug 2016, 30 Sep 2016, 7 Nov 2016, 15 Dec • 4 June 2015 - MariaDB 10.1.5 2016, 18 Jan 2017, 14 Mar 2017 • 27 July 2015 - MariaDB 10.1.6

16. The 10.2 release so far • 18 April 2016 - 10.2.0 alpha • 4 July 2016 - 10.2.1 alpha • 27 September 2016 - 10.2.2 beta • 24 December 2016 - 10.2.3 beta • 17 February 2017 - 10.2.4 release candidate • 5 April 2017 - 10.2.5 release candidate • 23 May 2017 - 10.2.6 stable GA

17. 10.2 Themes • Analytical queries, optimiser • Client & protocol • Removing historical limitations, refactoring • Replication • Engines: InnoDB, MyRocks

18. Analytical queries • views and subqueries in the FROM clause • Common Table Expressions + recursive CTEs • Window functions • Pushdown conditions into non-mergeable views/ derived tables

19. The analytical queries slide deck to visit • Common Table Expressions and Window Functions simple, maintainable, fast queries by Vicentiu-Marian Ciorbaru • 26 April - 2:00 PM - 2:50 PM @ Ballroom E • https://www.percona.com/live/17/sessions/ common-table-expressions-and-window- functions-simple-maintainable-fast-queries

20. Client & protocol • MariaDB Connector/C for MySQL and MariaDB Server. It is libmysqlclient API compatible. LGPL. OpenSSL/GnuTLS/schannel (no more yaSSL/ wolfSSL) • EXECUTE IMMEDIATE statement

21. Removing historical limitations, refactoring • CHECK CONSTRAINT enforced (previously accepted but ignored) • https://mariadb.com/kb/en/mariadb/constraint/ • DEFAULT clause can be an arbitrary expression • https://mariadb.com/kb/en/mariadb/create-table/#default • BLOB & TEXT columns can now have a DEFAULT value • Generated columns - virtual columns can finally be indexed (not just persistent), up to 64K per expression. Constant expressions, refer to virtual columns, and it works with non-deterministic functions • Supported decimals in DECIMAL is now 38 (was 30)

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

23.The Flashback slide deck to review • Slides: https://www.percona.com/live/17/ sessions/flashback-rolling-back-mysqlmariadb- instance-database-or-table-previous-snapshot

24. JSON & GeoJSON • SQL Standard 2014, with MySQL compatible extensions and functions • No JSON type - not in the standard (it says to use regular VARCHAR or TEXT); apparently there are benchmarks, and VARCHAR is as fast as JSON type (waiting to be published)

25.InnoDB from MySQL 5.7 • This is the first release of MariaDB Server that does not have Percona XtraDB as the default InnoDB! • InnoDB is taken from MySQL 5.7 • New variable for setting a directory for storing temporary non-tablespace InnoDB files, innodb_tmpdir • Persistent AUTO_INCREMENT for InnoDB

26. MyRocks • This is integrated and a big deal! • Alpha-maturity plugin at the moment

27. The MyRocks slide deck to review • MariaRocks: MyRocks in MariaDB by Sergei Petrunia • 27 April - 11:00 AM - 11:50 AM @ Ballroom E • https://www.percona.com/live/17/sessions/ mariarocks-myrocks-mariadb

28. Manageability • CREATE USER MAX_*_PER_HOUR & MAX_USER_CONNECTIONS • Limiting resource usage, and also TLS/SSL

29.Google Summer of Code • SQL Roles • Kerberos authentication • PCRE regular expressions • (per-query variable settings)