MariaDB Server 10.2 - The Complete Guide

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)