mariadb prez by joffrey michaie

SkySQL AB © 2013 Confidential

Principal Consultant

Joffrey MICHAÏE

Linux User Group of MauritiusSkySQL Ab / MariaDB


Agenda

•SkySQL General Presentation

•High-Availability Solutions for MySQL and MariaDB

•MariaDB history and 5.x presentation

•MariaDB 10 new features

•Questions / Answers


Me, myself and I - Joffrey MICHAIE

• Unix Sysadmin from 2002 to 2009

• Joined MySQL Ab (Sun Microsystems) in 2009 as Junior consultant

• Joined SkySQL Ab in 2010 as Senior Consultant

• Principal Consultant since 2013


Principal Duties

• Onsite and Remote Consulting• Performance tuning• Architecture and design• Migration• High availability• MySQL Cluster™ Consulting


Live my life ? 2009-2014

SkySQL Ab 2012 Confidential

The MySQL AB Story

• MySQL

– #1 Open Source DBMS Worldwide

– 50K downloads per day since 2003

– +13M active installations

• MySQL AB

– Founded by Monty Widenius & David Axmark

– My (pronounced Muu) is Monty's daugther

3


MySQL Founders turned SkySQL InvestorsThe shirts however are still as bad ...


MySQL AB

• Founded 1995

• 2007 : $100M. Preparation for IPO - Nasdaq

• Jan 2008 : Bought by Sun for $1B

• May 2009 : Sun bought by Oracle for $6B

• European Commission Enquiry

– DBMS Competition

• Oracle made some promises

• Green light just in time for Xmas 2009

5


The Oracle promises to the EU

6

• Continued Availability of Storage Engine APIs

• Non-assertion against any 3rd party or vendor for the

implementation of storage engines under GPL

• Same licence commitment for commercial customers

• All MySQL® improvement licenses under the GPL

• Non-mandatory support

• Increased R&D

• MySQL® Customer Advisory Board

• MySQL® Storage Engine Advisory Board

• MySQL® Reference Manual

• Maintain customer’s choice to buy yearly support


SkySQL in 60 Seconds

8

The employees are ex-MySQL (actually >90%)

The founders are ex-MySQL executives The investors include the original founders of MySQL

Under Oracle's stewardship, a lot of MySQL customers are facing reduced service levels, price rises

& vendor lock-in

We offer 24/7 worldwide MySQL & MariaDB support, consulting & training for MySQL as well as

products designed to better deploy MySQL in the Cloud

– 85% of our revenues come from 24/7, 30-min-SLA support contracts

Operational since October 2010, we have over 350 customers incl. Amadeus, Deutsche Börse,

Deutsche Telekom, Juniper Networks, Pitney Bowes, Time Warner, Virgin Mobile & Vodafone


SkySQL Group (currently ~70 people worldwide)

10

CEO - Patrik Sallner - Cloud & SaaS specialist (previously Nokia & F-Secure)

CTO - Ivan Zoratti - Ex. Dir. Field Services EMEA @ MySQL AB

EVP Product Man. - Kaj Arnö – Ex VP Engineering & VP Community MySQL AB

VP Support - Dean Ellis – Ex Director WW Support MySQL AB

VP Sales - Michael Carney – Ex. SEUR Sales Manager @ MySQL AB

VP Sales EMEA - Magnus Stenberg – Ex. NEUR Sales Manager @ MySQL AB

Engineering - Monty Program ~20 MySQL developers (including 8 of the 10 key core developers) lead by

Monty Widenius (MySQL founder)


SkySQL is the alternative to Oracle

11


SkySQL – Partner Eco-system

L3 Support Contracts

NRE Deals

End

Customer

L3 Support

Corrections

Hot Fixes

Monitoring

Consulting

Training

Corrections

POs

Contracts

Support Tickets

PR

SkySQL 24/7 WW SupportSkySQL 24/7 WW Support

L1, L2, L3 Support

Corrections

Hot Fixes

L1, L2, L3 Support

Contracts and NRE Deals

MHAMHA

Included in Advanced subscriptionIncluded in Advanced subscription

SkySQL&AB&©&2013&Confiden4al&

Introduc4on&to&HA&

“High&availability&is&a&system&design&protocol&and&associated&implementa6on&that&ensures&a&certain&degree&of&opera6onal&con6nuity&during&a&given&

measurement&period”&


Up4me,&Down4me,&9s&

& &90%& & & & & &1&nine&& & & &36.5&days&/&year &&

& &99%& & & & & &2&nines& & & &3.65&days&/&year&

& &99.9%&& & & & &3&nines& & & &8.76&hours&/&year&

& &99.99%& & & & &4&nines& & & &52&minutes&/&year&

& &99.999%& & & & &5&nines& & & &5&minutes&/&year&

& &99.9999%&& & & &6&nines& & & &31&seconds&/&year&

Availability&=&up4me&/&(up4me&+&down4me)&

Availability&=&MTBF&/&(MTBF&+&MTTR)&


Terminology&

• Synchronous&vs.&Asynchronous&• SharedHDisk&vs.&SharedHNothing&vs.&SharedHMemory&

• Single&Point&Of&Failure&H&SPOF&• Failover&vs.&Switchover&• Split&Brain&• Node&Fencing,&STONITH,&Quorum&


Designing&for&HA&

• Which&level&of&availability&do&I&need?&

• How&many&nines?&

• Do&I&require&no&loss&of&data?&• Could&I&loose&some&transac4ons?&

• Will&my&users&no4ce&or&care?&

• Do&I&need&automa4c&failover&or&is&manual&switchover&ok?&

• How&do&I&test&this?&• Can&I&provide&a&reasonable&service&when&X&is&down?&• Replace&X&with&each&component&of&the&service&


MySQL&Replica4on&H&Asynchronous&

• Asynchronous:&3&Phases&1.  Commit&and&write&to&binlog&on&Master&

2.  Ship&changes&to&relay&log&on&slave&3.  Apply&changes&on&slave&&

• Master&H>&slave&rela4onship&

• MonoHthreaded&on&slaves&un4l&MySQL&5.5&

• MySQL&5.6&allows&mul4Hthreaded&

• No&conflict&resolu4on&• MasterHmaster&replica4on&or&circular&

replica4on&need&applica4on&logic&

&

Master&

Slave(s)&


MySQL&Replica4on&–&SemiHSynchronous&

• Added&as&a&plugin&in&MySQL®&5.5&

• Ensures&that&changes&have&been&shipped&to&at&least&one&slave&(or&4meouts)&&

• A&COMMIT&on&the&master&waits&for&ONE&

Slave&to&acknowledge&the&transac4on&

• Important:&The&Master&does&not&wait&for&the&

Slave&to&execute&the&transac4on,&only&to&write&

it&to&the&relay&log&

• So&the&Slave&SQL&Thread&may&s4ll&lag&behind&

the&Master&and&queries&to&the&Slave&may&s4ll&

return&old&data&

• Poten4ally&adds&latency&to&queries&

Master&

Slave(s)&


MySQL®&5.6&–&New&Replica4on&Features&

• Global&Transac4on&ID&(GTID)&• Makes&it&easy&to&automate&failover&and&slave&promo4on&

• Replica4on&failover&and&admin&u4li4es&

• Mul4Hthreaded&slaves&

• Replica4on&event&checksums&

• TimeHdelayed&replica4on&


MariaDB&10.0&–&New&Replica4on&Features&

• Mul4Hsource&replica4on&

• Global&Transac4on&ID&(GTID)&• Different&implementa4on&from&MySQL&5.6&

• GTID&per&domain&instead&of&server&


MHA&

Master&High&Availability&Manager&

• Automates&master&failover&and&slave&

promo4on&

• Monitors&the&master&or&can&integrate&with&

Pacemaker/Heartbeat&

• Failover&is&an&online&opera4on&• Also&allows&manual&switchover&

• Short&down4me:&open&a&few&seconds&

• MySQLHReplica4on&consistency&

• No&performance&penalty&

• Drop&in&solu4on&on&exis4ng&deployment&

&


MHA&

Failover&Process&

1.  Aiempt&to&contact&MySQL&master&server&

by&SSH&

2.  If&master&server&is&alive&access&the&binary&

log&and&recover&events&

3.  Find&the&slave&with&the&most&advanced&

relay&log&

4.  Sync&all&slaves&to&the&latest&available&binlog&event&

5.  STONITH&master&if&necessary&

6.  Promote&slave&to&master&


Galera&/&MariaDB&Galera&Cluster&

• Provides&“virtually”&synchronous&replica4on&

• Works&with&InnoDB&

• No&slave&lag&• Transac4ons&are&validated&by&slaves&upon&commit&

• Cer4fica4on&and&quorum&

• Transac4ons&may&be&rolled&back&

at&this&stage&

• Mul4Hmaster&or&masterHslave&possible&

Galera Replication

wsrep

MySQL

wsrep

MySQL

wsrep

MySQL

Clients


Galera&–&Transac4on&Process&

• Transac4on&is&processed&locally&up&to&commit&4me&

Galera Replication

wsrep

MySQL

wsrep

MySQL

wsrep

MySQL

trx

Client




• Transac4on&is&replicated&to&whole&cluster&

Galera Replication

wsrep

MySQL

wsrep

MySQL

wsrep

MySQL

Client

trx

wsws





• Client&gets&OK&status&

Galera Replication

wsrep

MySQL

wsrep

MySQL

wsrep

MySQL

trx

Client





• Client&gets&OK&status&• Transac4on&is&applied&in&slaves&

Galera Replication

wsrep

MySQL

wsrep

MySQL

wsrep

MySQL

Client

trx trx


SharedHdisk&Solu4on&

• Ac4ve&–&Passive&replica4on&• Failover&requires&MySQL&crash&recovery&(and&

open&file&system&crash&recovery)&

• Combined&with&Pacemaker/Heartbeat&for&

automa4c&failover&

• Virtual&IP&most&open&used&to&fail&over&

• In&theory&the&SAN&is&a&SPOF&


DRBD®&&

• Synchronous&replica4on&(three&modes)&

• Ac4ve&–&Passive&replica4on&• Failover&requires&MySQL&crash&recovery&(and&

open&file&system&crash&recovery)&

• Combined&with&Pacemaker/Heartbeat&for&

automa4c&failover&

• Virtual&IP&most&open&used&to&fail&over&

• STONITH&or&other&fencing&mechanism&

needed&to&avoid&splitHbrain&scenarios&

• Available&on&Linux&


MySQL&Cluster&H&Features&

• Synchronous&replica4on&between&nodes&

• Through&TwoHPhase&Commit&Protocol&

• ACID&transac4ons&• Row&level&locking&• Shared&nothing&architecture&• No&single&point&of&failure&

• Automa4c&failover&

• InHmemory&storage&

• Some&data&can&be&stored&on&disk&

• Checkpoin4ng&to&disk&for&durability&• Two&types&of&indexes&• Ordered&THtrees&• Unique&hash&indexes&

• Online&opera4ons&• Add&node&groups&• Sopware&upgrade&• Some&table&altera4ons&


MySQL&Cluster&H&Architecture&


Where&does&MySQL&Cluster&fit?&

• High&demands&on&availability&(5&nines)&

• You&need&write&scalability&• You&have&3&or&more&”machines”&available&

• Where&the&queries&and&data&model&are&simple&

• When&the&data&fits&in&memory&

• When&you&have&skilled&people&


Geographical&Replica4on&

• For&geographical&(mul4Hsite)&redundacy&

synchronous&solu4ons&are&open&not&

desirable&

• Standard&Replica4on&can&be&combined&

with&any&synchronous&solu4on&locally&

• MySQL&Cluster&

• DRBD&/&shared&disk&• Galera&


Thank&you!&

MySQL is a registered trademark of Oracle and/or its affiliates. MariaDB is a registered trademark of Monty Program Ab. SkySQL and the SkySQL logo are trademarks of SkySQL Inc. or SkySQL Ab. SkySQL is not affiliated with MySQL. All other company and product names may be trademarks or service marks of their respective owners.

SkySQL&Ab&

www.skysql.com&

www.facebook.com/skysql&

www.linkedin.com/company/skysql&

Joffrey&Michaie&

[email protected]&

What is MariaDB?

• A branch of MySQL – MariaDB is a backward compatible, drop-in replacement

for the MySQL Database Server

• Open Source – The source code for MariaDB is publicly available from

Launchpad – All code in MariaDB is open source

• No closed source modules

– Open bugs database

10.09.2012

SkySQL Ab 2011 Confidential 2

A Brief History

• First version of what would become MySQL created by Monty Widenius 1983

• MySQL AB founded in 1995 • MySQL AB acquired by Sun Microsystems

early 2008 for $1bn • Monty Program founded in late 2008 • Oracle acquire Sun April 2009 • MariaDB 5.1 released in February 2010

10.09.2012


Who’s Behind MariaDB

• Monty Program – ~30 core MySQL developers – Located around the world

• Community

10.09.2012


Community and MariaDB

• The goal of MariaDB is to provide a community developed, stable and free database

• MariaDB takes community contributions • Many MariaDB Captains outside of Monty

Program (64% MP, 36% outside) • Open development model

10.09.2012


MariaDB 5.1 – Feb 2010

• Storage Engines – PBXT* – XtraDB – FederatedX – Aria

• Bugfixes • Removal of Mutexes

10.09.2012


MariaDB 5.1 = MySQL 5.1 + the following:

Table Elimination

10.09.2012


create view actors as select ACNAM_Name, ACDOB_birthdate, ACRAT_rating from ac_anchor left join ac_name on ac_anchor.AC_ID=ac_name.AC_ID left join ac_birthdate on ac_anchor.AC_ID=ac_birthdate.AC_ID left join ac_rating on (ac_anchor.AC_ID=ac_rating.AC_ID and ac_rating.ACRAT_fromdate = (select max(sub.ACRAT_fromdate) from ac_rating sub where sub.AC_ID = ac_rating.AC_ID))

select ACRAT_rating from actors where ACNAM_name='Gary Oldman'

Actor

Name Birth date

Rating

1 n

Table Elimination

10.09.2012


create view actors as select ACNAM_Name, ACDOB_birthdate, ACRAT_rating from ac_anchor left join ac_name on ac_anchor.AC_ID=ac_name.AC_ID left join ac_birthdate on ac_anchor.AC_ID=ac_birthdate.AC_ID left join ac_rating on (ac_anchor.AC_ID=ac_rating.AC_ID and ac_rating.ACRAT_fromdate = (select max(sub.ACRAT_fromdate) from ac_rating sub where sub.AC_ID = ac_rating.AC_ID))

select ACRAT_rating from actors where ACNAM_name='Gary Oldman'

Actor

Name Birth date

Rating

1 n

x

MariaDB 5.2 – Nov 2010

• Pluggable authentication – Authentication handled by plugins – PAM plugin included

• User statistics – CLIENT_STATISTICS – USER_STATISTICS – INDEX_STATISTICS – TABLE_STATISTICS

10.09.2012


MariaDB 5.2 = MariaDB 5.1 + the following:

MariaDB 5.2

• Virtual Columns – PERSISTENT or VIRTUAL

• Sphinx Storage Engine – Allows access to Sphinx through MySQL

• Segmented MyISAM key cache – Key cache divided into different segments – Allows for better key cache concurrency – Between 1 and 64 segments

10.09.2012


Segmented Key Cache

10.09.2012


MariaDB 5.3 – Jan 2012

• Handler socket – Direct access to InnoDB/XtraDB storage layer – No SQL statements

• Simple CRUD operations on tables

– Can be match faster for large batch operations

• Dynamic columns – Allows you to create columns with dynamic

content – Basically a blob with handling functions

10.09.2012



Dynamic Columns

10.09.2012


MariaDB [test]> create table t1 (id int auto_increment primary key, -> name varchar(40), -> type enum ("shirt", "phone", "computer"), -> price decimal(10,2), -> dynstr mediumblob); Query OK, 0 rows affected (0.11 sec) MariaDB [test]> insert into t1 (name, type, price, dynstr) values -> ("Funny shirt", "shirt", 10.0, COLUMN_CREATE(1, "blue", 10, "XL")), -> ("nokia", "phone", 649, COLUMN_CREATE(1, "black", 2, "touchscreen")), -> ("htc Desire hd", "phone", 579, COLUMN_CREATE(1, "black", 3, "Android")), -> ("BM/Lenovo Thinkpad X60s", "computer", 419, COLUMN_CREATE(1, "black", 3, "Linux")); Query OK, 4 rows affected (0.04 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [test]> select id, name, type, price, length(dynstr) as len, column_list(dynstr) as list from t1; +----+-------------------------+----------+--------+------+------+ | id | name | type | price | len | list | +----+-------------------------+----------+--------+------+------+ | 1 | Funny shirt | shirt | 10.00 | 17 | 1,10 | | 2 | nokia | phone | 649.00 | 27 | 1,2 | | 3 | htc Desire hd | phone | 579.00 | 23 | 1,3 | | 4 | BM/Lenovo Thinkpad X60s | computer | 419.00 | 21 | 1,3 | +----+-------------------------+----------+--------+------+------+ 4 rows in set (0.03 sec)

MariaDB 5.3

• Replication enhancements – Original statement logged with RBR events – Checksum for binlog events – RBR fixed for tables with no PK – Consistent snapshot between storage engines

• User feedback plugin • Extended OpenGIS SFS

10.09.2012



Progress Report

• Progress report for ALTER TABLE

10.09.2012


MariaDB [employees]> alter table salaries engine = maria; Stage: 1 of 2 'copy to tmp table' 17.55% of stage done

MariaDB [employees]> select id, user, db, command, state, -> time_ms, progress from information_schema.processlist; +---------+-------------------+-----------+----------+ | command | state | time_ms | progress | +---------+-------------------+-----------+----------+ | Query | copy to tmp table | 23407.131 | 17.551 | +---------+-------------------+-----------+----------+ 1 row in set (0.47 sec)

Optimizer Enhancements

1. Sub-query optimizations 2. Join additions 3. Optimizations for derived tables and views 4. Disk access optimization 5. Optimizer control

10.09.2012



1. Sub-query optimizations – Semi-join subquery optimization – Materialization for non-correlated IN-queries – Sub-query cache

10.09.2012


Subqueries are finally usable in practice!

It is no longer necessary to rewrite subqueries manually into joins or into separate queries


2. Join additions – Block Nested-Loop-Joins for outer joins – Block Hash-Joins – Batch-Key-Access

10.09.2012


Before MariaDB 5.3 only Nested-Loop-Joins available


3. Optimizations for derived tables and views – Mergeable derived tables processed like VIEWs – Optimizer can create indexes over materialized

derived tables

4. Disk access optimization – Index Condition Pushdown – Multi-Range-Read optimization (MRR)

5. Optimizer control – @@optimizer_switch for all new options

10.09.2012


Group Commit

10.09.2012


XtraDB / InnoDB

Group Commit

10.09.2012


COMMIT

Binary log

XtraDB / InnoDB

3 fsyncs / transaction

2

Group Commit

10.09.2012


MariaDB 5.5 – April 2012

• New optimized thread pool implementation – Pool size dynamic – Different implementation on Linux and Windows

• Fine grained tuning possible on Linux

• @@skip_replication • LIMIT ROWS EXAMINED

10.09.2012


MariaDB 5.5 = MySQL 5.5 + MariaDB 5.3 features + the following:

MariaDB [employees]> SELECT * from t1, t2 -> LIMIT 10 ROWS EXAMINED 1000;

Next MariaDB

• Global transaction Id (from MySQL 5.6) • Multi-source replication • New improved InnoDB (from MySQL 5.6) • Cassandra storage engine

10.09.2012


All highly speculative

Vote for features on http://www.skysql.com/content/ new-server-functionality-have-your-say

A branch or a fork?

• MySQL 5.1 CE = MySQL EE <-> MariaDB 5.1 • MariaDB 5.2 and 5.3 <-> MySQL 5.1 CE & EE • MySQL 5.5 CE != MySQL 5.5 EE

– Commercial extensions – MariaDB 5.5 implements same features

• Merged nonetheless

• 5.6 ? 10.09.2012


Getting MariaDB

• http://mariadb.org is the main place • Available via OpenSUSE build services • Also available via

– Gentoo – FreeBSD – Homebrew – Slackware – ArchLinux

• yum and apt repos available from MP 10.09.2012


Support & Services

• Monty Program does NRE • Enterprise level support available from SkySQL

– Monty Program providing L3

• Training, consulting etc available from SkySQL • Others providing MariaDB services:

http://mariadb.org/service-providers/

10.09.2012


More Information

• Downloads: http://mariadb.org/ • Mailing lists on launchpad • #maria on freenode • Knowledgbase: http://kb.askmonty.org/ • Support:

http://www.skysql.com/products/skysql-enterprise

• Training & consulting: http://www.skysql.com/services/mysql/overview

• MariaDB book: MariaDB Crash Course by Ben Forta 10.09.2012


Who’s using it?

Other MariaDB projects

• MariaDB Galera Cluster

• Connectors/Drivers– MariaDB C Client Library (LGPL)

– MariaDB JDBC driver (LGPL)

• MariaDB Audit Plugin

10.0 new features

• Replication enhancements

• Storage engines

• Optimizer enhancements

• Administration improvements

• More features, optimizations, syntax

extensions, …

• Merges from 5.6

Replication features

• Global Transaction ID

• Crash-safe slave

• Multi-Source Replication

• Parallel Slave

Global Transaction ID

• Monitor replication progress in complex topologies

• Simple fail-over and slave promotion

A

B C

D E F

Global Transaction ID – Commands

Setting up a new slave:

Switch a slave to use GTID:

Change master:

Crash-safe slave

• Global Transaction ID enabled

• GTID position stored in InnoDB table

Conclusio

n: In M

ariaDB 10.0

use GTID

for y

our replic

ation

setups

Multi-Source Replication

• Replicating from many masters to one

slave:

• Compatible with GTID

• Contributed by Lixun Peng, Taobao

Multi-source - Commands

Setting up multi-source replication:

All commands take a slave name:

Or use a default connection:

Parallel Slave

• Automatically detects independent

transactions

• Parallel even within the same table

• Adapts to master load

• Preserves commit order

New storage engines

• CassandraSE

• Connect

• Sequence

• Spider

• TokuDB

Cassandra Storage Engine

• Access Cassandra from MariaDB with SQL

• Flexible schema, using “dynamic columns”

• Joins with non-Cassandra tables

• Transparent data mapping

• Many MariaDB nodes — one Cassandra ring

Connect Storage Engine

• CSV and Federated on steroids

• CSV, BIN, FMT, INI, XML, DBF

• column store

• ODBC, MySQL

• TBL (similar to MERGE)

• OEM (external library)

• Flexible table structure specification

• Developed by Olivier Bertrand

Sequence Storage Engine

• Completely virtual read-only tables

• Magically created on demand, magically

disappearing, no .frm files:

• Supports indexes, transactions, XA

Sequence: Example

• Prime numbers below 50:

Spider Storage Engine

• Federated-like storage engine

• Sharding, partitioning

• Transactions, XA

• High-Availability Cluster

• MySQL, HandlerSocket, Oracle

• Developed by Kentoku Shiba

Spider Storage Engine

TokuDB Storage Engine

• When you have billions of rows and terabytes

of data

• 10x to 20x faster INSERTs

• Compression — space saving up to 90%

• Online ALTER TABLE

• Both HDD and Flash

• Developed by Tokutek

Unique Use Case Example

• CONNECT Storage Engine (XML, ODBC,

etc.)

• CONNECT to Oracle (via ODBC), join results

from Cassandra and store the result in

InnoDB– Yes, collaboration between Oracle, Cassandra

and MariaDB is possible today

• Remember to turn on engine condition

pushdown

Optimizer

• Engine independent statistics

• Histogram-based statistics

• Subquery optimizations

Optimizer: Statistics

• Engine independent– stored in mysql database

– stable, uniform, controlled

– for indexes and columns

– used instead of or together with engine

statistics

• Histograms

Subqueries

• EXISTS-to-IN

Can be rewritten into

• Semi-join EXISTS

Administration

• SHOW EXPLAIN

• SHOW PLUGIN SONAME

• SHUTDOWN

• KILL QUERY ID

• Per-connection memory accounting

• Roles

SHOW EXPLAIN

• Shows EXPLAIN for a query running in

another thread

SHOW PLUGIN SONAME

• Information about not installed plugins

• LIKE 'soname', WHERE …

• INFORMATION_SCHEMA.ALL_PLUGINS

SHUTDOWN

• SQL statement to shut the server down

• Same as mysqladmin shutdown

• Requires SHUTDOWN privilege

KILL QUERY ID

• Abort a running query

• Uses query id, not thread id

• Avoid nasty race conditions

Per-connection memory usage

• Monitor memory usage per connection:

• Contributed by Lixun Peng, Taobao

Roles

• SQL standard implementation of roles– CREATE ROLE, DROP ROLE

– GRANT role TO user, GRANT role TO role

– SET ROLE

– DEFINER=role

– CURRENT_ROLE

– INFORMATION_SCHEMA tables

• GSoC–2013, Vicentiu Ciorbaru

Misc. enhancements

• DELETE … RETURNING

• ALTER … IF [NOT] EXISTS

• Named dynamic columns

• Table discovery, assisted table

discovery

• Multiple GET_LOCK– Contributed by Konstantin Osipov

• New regular expression library– GSoC–2013, Sudheera Palihakkara

Merged from 5.6

• InnoDB

• Performance_schema

• Online ALTER TABLE

• ORDER BY … LIMIT optimization

• Privileges on temporary tables

• Character set extensions

• GET DIAGNOSTICS

• TO_BASE64(), FROM_BASE64()

MARIADB AUDIT PLUGIN

MariaDB Audit Plugin

• Track user access to

server and data

• Track all type of access

to data in tables

• Download the MariaDB

Audit Plugin Library

from http://

www.skysql.com/downlo

ads/mariadb-audit-plu

gin-beta

Suddenly it’s straight forward to comply with laws and industry standards

Logged information:• Timestamp• Server-Host• User Client-Host• Connection-ID• Query-ID• Operation• Database Table• Error-Code• ...and more…

Configuration

Logging

• Connect events

– Logs connects, disconnects, and failed connects + error code

• Query events

– Executed queries will be logged with user information

– Queries are logged exactly as executed and might reveal sensitive

data in the logs

– Also queries that cannot be executed will be logged, e.g. user

doesn’t have the rights or a syntax fault

• Table events

– Only available in MariaDB 5.5.31 or newer (not available in

MySQL)

– Views, Stored Procedures, Functions and Triggers can access

table data without showing it in the executed query

Logging

Contact us

• MariaDB.org and MariaDB.com

• Knowledge base: http://mariadb.com/kb

• Mailing lists:

• [email protected]

• [email protected]

• IRC: #maria channel on FreeNode

• Jira: http://mariadb.org/jira

• Rasmus: [email protected]

Thank you!

mariadb prez by joffrey michaie

Technology

confidential mysql ab

mysql ab story mysql

confidential mysql founders

mysql developers

mysql ab evp product

active installations

confidential skysql

skysql investors