MariaDB 10.5 and Galera 4 State of MariaDB in Debian and What is new? 27th August 2020 23:00 UTC Follow @ottokekalainen for more news!

MariaDB A variant of MySQL by the original authors of MySQL Forked in 2008 when Oracle acquired MySQL MariaDB 5.5 full drop-in replacement for MySQL 5.5 MariaDB 10.5 backwards compatible with all previous versions of MariaDB and MySQL Divergence increases for each new MySQL version

Galera Cluster multi-master cluster based on synchronous replication for InnoDB tables By Codership Oy Available for MariaDB, MySQL and also used in Percona XtraDB Cluster Included by default since MariaDB Server 10.1 MariaDB/MySQL clients can read and write to any node, all nodes are equal

State of MariaDB and Galera in Debian

MariaDB and MySQL timeline MariaDB 5.1 MariaDB 5.5 shipped in Debian 8 MariaDB 10.0 shipped in Debian 9 MariaDB 10.1 MariaDB 10.2 shipped in Debian 10 MariaDB 10.3 MariaDB 10.4 ships in Debian 11 MariaDB 10.5 MySQL 5.1 shipped in Debian 6 MySQL 5.5 shipped in Debian 7 and 8 MySQL 5.6 MySQL 5.7 MySQL 8.0 GA 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020

State of MariaDB and MySQL in Debian ● MariaDB 10.3 in Buster (latest Debian stable release) ● MariaDB 10.5 currently in NEW queue, will be in Bullseye ● Galera 3 in Buster; Galera 4 already in Bullseye and also in Buster backports and Stretch backports ● No MySQL in stable Debian releases since Jessie ○ Exists in Debian unstable but will not migrate to testing and stable ○ Debian unstable had MySQL 5.7 for some years, MySQL 8.0 this month ● mysql-defaults define the settings ○ Debian defaults to MariaDB as decided by the Debian release team ○ Ubuntu defaults to Oracle MySQL as decided by Canonical

State of MariaDB and MySQL in Debian ● Maintainer dashboard ○ https://udd.debian.org/dmd/?pkg-mysql-maint%40lists.alioth.debian.org ● Packages overview ○ https://qa.debian.org/developer.php?email=pkg-mysql-maint%40lists.alioth.debian.org ● Selected tracker pages: ○ https://tracker.debian.org/pkg/mariadb-10.3 ○ https://tracker.debian.org/pkg/mariadb-10.5 ○ https://tracker.debian.org/pkg/galera-4

Packages overview for Debian MySQL Maintainers

Old MariaDB versions in maintenance https://packages.debian.org/search?lang=en&suite=all&searc hon=names&keywords=mariadb-server https://packages.ubuntu.com/search?lang=en&suite=all&sear chon=names&keywords=mariadb-server

Plenty of work in both developing new and maintaining old Contributors welcome!

Commit summary 2018–2020 git summary debian/1%10.1.34-1..HEAD commits : 386 authors : 349 Otto Kekäläinen 90.4% 7 Andreas Beckmann 1.8% 6 Faustin Lammler 1.6% 5 Samuel Thibault 1.3% 4 Vicențiu Ciorbaru 1.0% 3 Christian Göttsche 0.8% 2 James Clarke 0.5% 2 Julien Muchembled 0.5% 1 Adrian Bunk 0.3% 1 Bastian Germann 0.3% 1 Christian Ehrhardt 0.3% 1 Gregor Riepl 0.3% 1 Helge Deller 0.3% 1 Helmut Grohne 0.3% 1 Olaf 0.3% 1 Scott Kitterman 0.3% git summary mysql-5.7/debian/5.7.23-1..HEAD commits : 175 authors : 104 Lars Tangvald 59.4% 56 Robie Basak 32.0% 6 Marc Deslauriers 3.4% 2 Andreas Hasenack 1.1% 1 Dimitri John Ledkov 0.6% 1 Karl Stenerud 0.6% 1 Matthias Klose 0.6% 1 Rafael David Tinoco 0.6% 1 Sergio Durigan Junior 0.6% 1 Steve Langasek 0.6% 1 William Grant 0.6%

Please join! You don’t have to be a coder/developer to help, there are plenty of other valued skills as well! ● Participate in bugs.debian.org discussions! ● Send merge requests on Salsa: https://wiki.debian.org/Teams/MySQL/patches ● Contribute upstream: https://mariadb.org/get-involved/ ● Join pkg-mysql-maint mailing list https://alioth-lists.debian.net/cgi-bin/mailman/listinfo /pkg-mysql-maint

Many aspects of package maintenance are working very well (e.g. Salsa-CI) while other aspects are in the need of some help..

48% Of messages on mailing list in July were spam… https://alioth-lists.debian.net/pipermail/pkg-mysql-maint/

Number of bugs stable and decreasing for MariaDB MariaDB 10.4 entered Debian experimental

Many bugs become stale because they cannot be reproduced, are vague, badly reported.. You can help out with these by simply commenting on the bugs! No coding skills required.

MariaDB vs MySQL compatibility?

Compatibility with MySQL? Oracle does not care about MySQL–MariaDB compatibility, may break in any new MySQL release at any time MariaDB strives for backwards compatibility and cross-compatibility with all previous versions of MariaDB and MySQL MySQL 5.7 → MariaDB 10.5 upgrades in-place MySQL 8.0 → MariaDB 10.5 needs SQL dump + import

How it works in Debian Running apt install mariadb-server always installs MariaDB On new systems apt install mysql-server installs MariaDB If running MySQL 5.5, 5.6, 5.7 the upgrade to MariaDB is fully automatic with apt update / apt dist-upgrade If system has MySQL 8.0, apt install mariadb-server will install MariaDB, but the database will be empty: the local admin needs to do a SQL dump + import (same applies if going from MySQL 8.0 back to 5.7 or older)

What about app compatibility? All the usual apps such as WordPress, Nextcloud etc work fine with any MariaDB/MySQL version In rare cases if an app uses special SQL syntax or features only available in a specific version of MariaDB or MySQL, then downgrading/crossgrading will be limited Assume that over time MySQL and MariaDB will diverge more and more

My recommendation: migrate to MariaDB now when it is still easy https://linuxnatives.net/2015/10-reasons-to-migrate-to-mariadb-if-still-using-mysql

MariaDB 10.5 What is new?

Diversion in practice: new parallel mariadb-* commands mariadb-access mariadb-conv mariadb-ldb mariadb-slap mariadb-admin mariadb-convert-table-format mariadb-optimize mariadb-test mariadb-analyze mariadb-dump mariadb-plugin mariadb-test-embedded mariadb-backup mariadb-dumpslow mariadb-repair mariadb-tzinfo-to-sql mariadb-binlog mariadb-find-rows mariadb-report mariadb-upgrade mariadb-check mariadb-fix-extensions mariadb-secure-installation mariadb-waitpid mariadb-client-test mariadb-hotcopy mariadb-service-convert mariadb-client-test-embedded mariadb-import mariadb-setpermission mariadb-config mariadb-install-db mariadb-show mariadb mariadbd Old commands still work in parallel so you have time to update your scripts!

Service and process names: mariadb and mariadbd $ systemctl start mariadb $ service mariadb restart $ ps faxu /bin/sh /usr/bin/mysqld_safe \_ /usr/sbin/mariadbd --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --skip-log-error --pid-file=/run/mysqld/mysqld.pid --socket=/run/mysqld/mysqld.sock \_ logger -t mysqld -p daemon error Systems that upgrade from MySQL will still have the mysql service name available (as symlink to mariadb) so scripts/automation continue to work.

More naming changes In MariaDB 10.5 the term REPLICA is a synonym for SLAVE in SQL statements

MariaDB 10.5 performance improvements Binary row logging is faster Connect speed is faster (up to 25%) New PCRE regex library version New crc32 function New performance_schema_* variables Many small performance improvements across the code base ANALYZE improved for easier visibility into query optimizer InnoDB has been refactored and many configuration variables have been deprecated in favor of being automatically correct as InnoDB optimizes itself Doublewrite buffer used less InnoDB redo log format optimized to be faster and to need fewer log operations https://mariadb.com/openworks/sessions-on-demand/faster-b etter-stronger-the-new-innodb/

MariaDB 10.5 security improvements Option require_secure_transport to force encryption in transit There are 8 new subsets to the SUPER privileges for more fine-grained access control There have been a huge amount of security features added since 10.0 that most sysadmins have not heard about yet. Get studying! :) The passwordless root user used in Debian for security purposes since 2015 was fully adopted upstream in the MariaDB 10.4 release Hence the 10.5 packaging in Debian.org will have much less delta to upstream mariadb.org Debian packaging

MariaDB 10.5 enhanced SQL New DDL: CREATE DATABASE ... COMMENT= ‘comment’ ALTER DATABASE ... COMMENT= ‘comment’ ALTER TABLE ... RENAME INDEX ALTER TABLE ... RENAME COLUMN ALTER TABLE IF EXISTS ... RENAME TABLE IF EXISTS ... Also new variable sql_if_exists Support for INSERT ... RETURNING and REPLACE ... RETURNING statements Temporal tables support WITHOUT_OVERLAP and STARTS RELEASE_ALL_LOCKS() SHOW MASTER STATUS renamed to SHOW BINLOG STATUS (old still works)

MariaDB 10.5 new JSON functions JSON_ARRAYAGG() Returns a JSON array containing an element for each value in a given set of JSON or SQL values. It acts on a column or an expression that evaluates to a single value. JSON_OBJECTAGG() Returns a JSON object containing key-value pairs. It takes two expressions that evaluate to a single value, or two column names, as arguments, the first used as a key, and the second as a value.

MariaDB 10.5 new storage engines mariadb-plugin-s3 The S3 storage engine allows one to archive MariaDB tables in Amazon S3 (or any third-party public or private cloud that implements S3 API), but still have them accessible in MariaDB in read-only mode. New binary package soon in Debian unstable! mariadb-plugin-columnstore MariaDB ColumnStore is a columnar storage engine that utilizes a massively parallel distributed data architecture. It's a columnar storage system built by porting InfiniDB 4.6.7 to MariaDB, and released under the GPL license. Not in Debian.org yet as packaging needs more quality assurance work.

MariaDB 10.5 removed storage engines mariadb-plugin-tokudb The storage engine based on fractal indexes hasn’t been maintained much at upstream since Percona acquired Tokutek Thus no new releases for a long time and removed in MariaDB 10.5 mariadb-plugin-cassandra Removed in upstream MariaDB 10.5 Was never in Debian.org repos

Galera 4 What is new?

MariaDB 10.5 and Galera Full GTID support in MariaDB for Galera (commit) New variable wsrep_gtid_seq_no to manually update the WSREP GTID sequence number in the cluster Upgrading a Galera cluster running MariaDB 10.3 (in Debian stable) to MariaDB 10.5 will involve upgrading to new WSREP API version 26. Rolling upgrades of live cluster possible one host at a time. Streaming replication enables running transactions on unlimited size Group commit support SST with lightweight locks to help making backups New metadata tables: wsrep_cluster wsrep_cluster_members wsrep_streaming_log

MariaDB 10.5 and Galera 4 What is old?

A huge collection of software Storage engines: InnoDB Aria RocksDB Mroonga Spider OQGraph via Connect many more! Integrated search with Sphinx mariadb-backup audit plugin, password rules See also Debian packages: libdbd-mariadb-perl mariadb-connector-java mariadb-connector-odbc mariadb-mysql-kbs

Too much to cover in a single presentation :)

MariaDB Server Fest Online Conference 14-20 Sep 2020 https://mariadb.org/fest2020/ Want to learn more? ● Run apt install mariadb-server ● Read more at mariadb.org and mariadb.com ● Attend the upcoming MariaDB event