|
|
The slides and replay of yesterday’s webinar on the MariaDB CONNECT storage engine have just been posted. First I want to thank the numerous attendees.
You have shown great interest on the parallel execution of query on distributed MySQL Servers. I agree this is cool.
The ODBC capabilities seems also to generate interest. This make it simple to access an ODBC datasource (SQLServer, Oracle …) from plain MySQL syntax.
Here to view the replay
Here to get the slides
Let us know about your test. You just need to download MariaDB 10.0.5 which includes the CONNECT storage engine. Give us your feedback : positive, negative, improvement requests, bugs, doc pb, …
Here to download the latest MariaDB 10.0.5 Beta release
Here to get the CONNECT Storage Engine Documentation
The CONNECT Storage engine implement the concept of a table made of multiple tables. These underlying tables can be distributed remotely. For example the underlying remote tables can be of ODBC or MySQL table type. this allows to execute distributed queries. What is nice is that we can execute this distributed query with parallelism.
How does it work ?
To explain it let us suppose we have 4 nodes : Node0 and Node1 Node2 Node3
Node0 has a MariaDB 10.0.4 installed with the CONNECT storage engine activated.
MariaDB [dbt3]> install plugin connect soname 'ha_connect.so'; On the 3 other nodes we have MariaDB or plain MySQL installed.
the ‘lineitem’ table of the dbt3 benchmark has been created and loaded with one chunk of the ‘lineitem’ table on each node. This is a sharded table. For example to generate the second chunk of 3 chunks ‘lineitem’ table:
./dbgen -T L -fF -q -b dists.dss -s 1 -C 3 -S 2 Now on node0 we can create a CONNECT table. This table definition embed a SRCDEF parameter that force the computation of an aggregate on the remote server :
create Table lineitem1 ENGINE=CONNECT TABLE_TYPE=MYSQL
SRCDEF='select l_suppkey, sum(l_quantity) qt from dbt3.lineitem3 group by l_suppkey'
connection='mysql://proxy:manager1@node1:3306/dbt3/lineitem3'; if we do a select on this table on node0 we get the result data that has been aggregated on node1. We access only one shard of the data.
MariaDB [dbt3]> select * from lineitem1;
+-----------+------+
| l_suppkey | qt |
+-----------+------+
| 1 | 2302 |
| 2 | 1960 |
...
| 9999 | 1908 |
| 1000 | 1756 |
+-----------+------+
10000 rows in set (26.82 sec) Let us now do the same on for node2 and node3 :
create Table lineitem2 ENGINE=CONNECT TABLE_TYPE=MYSQL
SRCDEF='select l_suppkey, sum(l_quantity) qt from dbt3.lineitem3 group by l_suppkey'
connection='mysql://proxy:manager1@node2:3306/dbt3/lineitem3';
create Table lineitem3 ENGINE=CONNECT TABLE_TYPE=MYSQL
SRCDEF='select l_suppkey, sum(l_quantity) qt from dbt3.lineitem3 group by l_suppkey'
connection='mysql://proxy:manager1@node3:3306/dbt3/lineitem3'; Now we can use the ability of the the CONNECT storage engine to build a table that allows to see the 3 shards as a single table :
create Table alllineitem (`l_suppkey` INT(4) NOT NULL,`qt` DOUBLE ) ENGINE=CONNECT
TABLE_TYPE=TBL
table_list='lineitem1,lineitem2,lineitem3'; We can now query this table. All the aggregation computation will be done on the remote nodes and not on node0:
MariaDB [dbt3]> select * from alllineitem;
+-----------+------+
| l_suppkey | qt |
+-----------+------+
| 1 | 2302 |
| 2 | 1960 |
...
| 9998 | 2395 |
| 9999 | 1908 |
| 1000 | 1756 |
+-----------+------+
30000 rows in set (1 min 19.23 sec) This basically takes 3 times longer than querying a single shard. This is because the request is executed sequentially on node 1 2 and 3.
We can request the CONNECT storage engine to do the job in parallel by adding the extra option : option_list=’thread=1′;
create Table alllineitem2 (`l_suppkey` INT(4) NOT NULL,`qt` DOUBLE ) ENGINE=CONNECT
TABLE_TYPE=TBL
table_list='lineitem1,lineitem2,lineitem3' option_list='thread=1'; If we rerun the query we get
MariaDB [dbt3]> select * from alllineitem2;
+-----------+------+
| l_suppkey | qt |
+-----------+------+
| 1 | 2302 |
| 2 | 1960 |
...
| 9999 | 1908 |
| 1000 | 1756 |
+-----------+------+
30000 rows in set (26.69 sec) We go back to the same execution time as with a single shard ! Is not that cool. I am sure you have a lot of objections, ideas about this approach. I do. But it is nice anyways.
If you want to know more do not forget to attend to the MariaDB CONNECT Storage Engine webinar :
November 7, 2013 – 5 pm CET / 4 PM UTC / 8 AM PST Register
and if you want to give it a try you are welcome and the doc to help is here:
MariaDB Connect Storage Engine documentation
Your help is welcome : bug reports, documentation fixes, usage feedback, suggestions.
Be indulgent it is still Alpha software 
This week I will have the opportunity to deliver a webinar on the MariaDB CONNECT Storage Engine.
The MariaDB CONNECT Storage Engine allows to access various file formats (CSV, XML, Excel, etc). It give access to any ODBC data sources (Oracle, DB2, SQLServer, SQLite etc). It also allows to access remote MySQL tables. A CONNECT table itself can be a set of remote MySQL tables. This opens the door to interesting distributed architectures that can help to address big data.
This webinar is a technical overview of the MariaDB CONNECT Storage Engine and it will show you typical use cases to help you get benefits from your existing data sources.
We will see how to use the MariaDB CONNECT Storage Engine.
Register for this webinar to learn what benefits you can get from the MariaDB CONNECT Storage Engine :
November 7, 2013 – 5 pm CET / 4 PM UTC / 8 AM PST Register
MySQL Fabric is a very promising sharding framework. If I take Ulf Wendel definition of MySQL Fabric :
MySQL Fabric is an administration tool to build large “farms” of MySQL servers. In its most basic form, a farm is a collection of MySQL Replication clusters. In its most advanced form, a farm is a collection of MySQL Replication clusters with sharding on top.
So MySQL Fabric takes care of two very orthogonal features :
- High availability of servers
- Sharding of data
Let us forget about sharding and look at the High availability infrastructure.
Servers are included in groups, called “High Availability Groups” when we talk about HA.
Each Server has an associated Status (or Role): primary secondary, spare
Each Server has also a mode : Offline, Read-only, and Read-Write.
The implementation has been made to allow various HA implementation patterns.
The most common HA pattern is the Master/Slave HA group ( in that case we should call it a “replica set” which is the terminology used in MongoDB or Facebook MySQL Pool Scanner (MPS).
Mats Kindahl in his blog post on MySQL Fabric High Availability Groups mentioned that other HA solutions are possible for an availability group :
- Shared Storage with SAN or NAS
- Replicated storage like DRBD
- MySQL Cluster shared nothing cluster
In the case of a HA group based on MySQL Cluster the group is self-managing regarding HA and MySQL Fabric does not handles the failover. With the “Shared Storage” and “Replicated storage” availability groups the secondary servers will be offline.
So one of my ideas that I hope is feasible would be to use MariaDB Galera Cluster as another HA solution with MySQL Fabric. The main advantage of this solution relates to the characteristics of MariaDB Galera Cluster. MariaDB Galera Cluster is an Active-active multi-master topology with synchronous replication. MariaDB Galera Cluster being innoDB based does not carry all the usage limitations associated with MySQL Cluster (main one being limited join capabilities).
Regarding to MySQL fabric the behavior of an availability group based on MariaDB Galera Cluster is identical to MySQL Cluster. It is a self-managing availability group.
MariaDB Galera Cluster
MariaDB Galera Cluster
Getting Started with MariaDB Galera Cluster
MySQL Fabric
MySQL Fabric: A new kid in the MySQL sharding world 2013-10-09 Serge Frezefond
MySQL Fabric: High Availability Groups 2013-10-21 Mats Kindahl
A Brief Introduction to MySQL Fabric 2013-09-21 Mats Kindahl
MySQL Fabric – Sharding – Introduction 2013-09-21 VN (Narayanan Venkateswaran)
MySQL Fabric – Sharding – Simple Example 2013-09-22 VN (Narayanan Venkateswaran)
MySQL Fabric – Sharding – Shard Maintenance 2013-09-27 VN
MySQL Fabric – Sharding – Migrating From an Unsharded to a Sharded Setup 2013-09-22 VN
Installing MySQL Fabric on Windows 2013-10-03 Todd Farmer
MySQL 5.7 Fabric: any good? 2013-09-23 Ulf Wendel
Writing a Fault-tolerant Database Application using MySQL Fabric 2013-09-21 Alfranio Junior
Sharding PHP with MySQL Fabric 2013-10-09 Johannes Schlüter
MySQL Fabric support in Connector/Python 2013-09-22 Geert Vanderkelen
MySQL Connector/J with Fabric Support 2013-09-21 Jess Balint
Like TokuDB, InfiniDB is now a fully open source server product. In the past infiniDB was “almost open source”. The open source version was an old release with no access to the advance functions like MPP multi-server execution. This is no more the case. With InfiniDB 4 the open source version is the latest release giving access to all the advanced functionalities.
This is a really great move for the MariaDB / MySQL ecosystem. InfiniDB and TokuDB were two unique pieces of technology in the MySQL ecosystem. Having them both open source will trigger a broader adoption that will benefit to their enterprise releases.
Having in the MySQL ecosystem a column oriented database specifically designed for big data analytics is filling a real customer need. Column stores overcome the query limitations that exist in traditional RDBMS. InfiniDB is extremely good at using multicore server and massively parallel processing with multi-servers. InfiniDB can scale up on multi-cores server and scale out on a distributed architecture.
So thanks a lot to the InfiniDB team for their move. This will greatly benefit to them and to the dynamism of the MySQL / MariaDB ecosystem.
InfiniDB community web site
InfiniDB 4 sources
InfiniDB Enterprise web site
MySQL Connect 2013 has been a great edition. There was of course a lot of nice announcements of improvements in the the core MySQL server technology. One of the major announcement that received a lot of buzz was MySQL Fabric. MySQL Fabric is an infrastructure component aimed at simplifying construction of a highly available, sharded, MySQL server based architecture.
Horizontal scale out for MySQL is a hard problem. The MySQL sharding requirement has up till now only be addressed in a non general solution way. Using MySQL at Scale remains a big challenge.
All the big actors of the web have been faced with this scale out issue. They all have developed their own tools/framework to address this need. The new cloud providers have also been faced with this requirement when trying to offer database as a service solutions around MySQL with transparent elasticity.
We can mention the following Sharding solutions davelopped by the key players of the web :
- Google/Youtube has built Vitess (in Go) and open sourced it
- Twitter has built Gizzard (in Scala) and open sourced it
- Tumblr has built Jetpants (in Ruby) and open sourced it
- Facebook has also it own sharding framework which is a range based model. The HA is addressed with MHA. they have presented their architecture at various events
Theses frameworks cover the needs:
- Master promotions,
- Cloning slaves,
- Supports a range-based sharding scheme for MySQL
- Rebalancing shards,
- Split a range-based shard into N new shards
High Availability is also addressed by most of these frameworks. MySQL Fabric also handles HA. All the improvements made around replication have helped handle correctly the HA part of the architecture in a more resilient way.
So now with the introduction of MySQL Fabric there is new sharding framework available. So what ? Interesting ?
Yes it is very interesting.
First, MySQL fabric has been written in python which in my opinion is a good point as devops love python.
Second, MySQL fabric has been released under the GPL license which is also a very good point.
Last MySQL Fabric is aimed as a general purpose sharding framework.
MySQL fabric is quite simple :
- A Fabric Server holding and serving the sharding metadata.
- A set of commands to do various action (split shard ,…)
- Specific connectors : Java, Python to request shard location to the Fabric server through XMLRPC call.
Two questions for me:
- What is the status of the PHP and C drivers regarding MySQL Fabric ?
- Is it possible to fully abstract the client code from the fact that the data is sharded. Some connector config could specify the fabric server. The client code would then not be changed at all.
======
A Brief Introduction to MySQL Fabric 2013-09-21 Mats Kindahl
MySQL Fabric – Sharding – Introduction 2013-09-21 VN (Narayanan Venkateswaran)
MySQL Fabric – Sharding – Simple Example 2013-09-22 VN (Narayanan Venkateswaran)
MySQL Fabric – Sharding – Shard Maintenance 2013-09-27 VN (Narayanan Venkateswaran)
MySQL Fabric – Sharding – Migrating From an Unsharded to a Sharded Setup 2013-09-22 VN (Narayanan Venkateswaran)
Installing MySQL Fabric on Windows 2013-10-03 Todd Farmer
MySQL 5.7 Fabric: any good? 2013-09-23 Ulf Wendel
—
Writing a Fault-tolerant Database Application using MySQL Fabric 2013-09-21 Alfranio Junior
MySQL Fabric support in Connector/Python 2013-09-22 Geert Vanderkelen
MySQL Connector/J with Fabric Support 2013-09-21 Jess Balint
When full auditing is activated with the the MariaDB Audit Plugin a large volume of audit data is generated and it can put an extra burden on the server. The MariaDB audit plugin offers the two following setup variables that allow to restrict what data will be logged.
MariaDB [test]> show variables like '%audit%users';
+-----------------------------+-----------------------+
| Variable_name | Value |
+-----------------------------+-----------------------+
| server_audit_excl_users | |
| server_audit_incl_users | |
+-----------------------------+-----------------------+
14 rows in set (0.00 sec) These variables allow to restrict the data that is going to be pushed to the audit log.
You can say: I only want to log data from these users
MariaDB [test]> set global server_audit_syslog_incl_users='proxy'; Only activities coming from the user ‘proxy’ will be logged.
Or you can say : I want to log data except for these users.
MariaDB [test]> set global server_audit_excl_users='user2'; No activities coming from the user ‘user2′ will be logged.
What happens if a user is both included and excluded ? If a user is both included and excluded database activities for that user will be logged. Include has priority over exclude. It is important to notice that the audit plugin logs data only based on the username. This username is different from the MariaDB and MySQL user definition. For them a user is combination of the username and hostname (‘user’@'host’).
This capability to filter audit data is crucial to avoid too much burden on the audited server and to avoid generating too much volume of audit data.
Syslog is widely used for logging. It allows distributed logging. Having MySQL/MariaDB audit data logged to a remote Syslog server is a strong guaranty regarding security of the audit data. PCI compliance requires separation of duties. The separation of duties between DBA profiles and a security officer is a way to guaranty that Audit data is tamper-proof from the DBA.
To set up the MariaDB Audit Plugin to log to remotely syslog is quite simple. First you install the MariaDB Audit Pluggin : You download the MariaDB audit plugin, you copy it to lib/plugin in your MySQL/MariaDB install directory and you activate it :
MariaDB [(none)]> INSTALL PLUGIN server_audit SONAME 'server_audit.so';
MariaDB [test]> SET GLOBAL server_audit_output_type=SYSLOG;
MariaDB [test]> SET GLOBAL server_audit_events='CONNECT,QUERY ';
MariaDB [test]> SET GLOBAL server_audit_logging=on; To have the audit logging data sent to a remote server you first need to configure the remote syslog server to accept request from the network(here on port 514) by editing /etc/rsyslog.conf
# Provides UDP syslog reception
$ModLoad imudp
$UDPServerRun 514
# Provides TCP syslog reception
$ModLoad imtcp
$InputTCPServerRun 514 Restart the syslog daemon :
To check that your syslog system accept data from a remote source we verify that it is listening on the configured 514 port :
[root@centos2 etc]# netstat -anp|grep 514
tcp 0 0 0.0.0.0:514 0.0.0.0:* LISTEN 11467/rsyslogd
tcp 0 0 :::514 :::* LISTEN 11467/rsyslogd
udp 0 0 0.0.0.0:514 0.0.0.0:* 11467/rsyslogd
udp 0 0 :::514 :::* 11467/rsyslogd On the source server where your MariaDB / MySQL server produce audit entries you should configure syslog to push log entries to the remote system here 192.168.56.11. You edit /etc/rsyslog.conf that way:
*.info;mail.none;authpriv.none;cron.none @192.168.56.11 And on the target system you now get the audit records tagged withe the originating system:
Sep 21 00:52:37 centos1 kernel: imklog 5.8.10, log source = /proc/kmsg started.
Sep 21 00:52:37 centos1 rsyslogd: [origin software="rsyslogd" swVersion="5.8.10" x-pid="1647" x-info="http://www.rsyslog.com"] start
Sep 21 00:52:59 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,19,QUERY,test,'show tables',0
Sep 21 00:53:14 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,20,QUERY,test,'show tables',0 So this is quite simple to setup. Of course you can have multiple MariaDB/MySQL servers sending audit data to a single syslog server.
Syslog is widely used for logging. It allows separation of the storage of generated messages from the system that generated them. A lot of solution exists to aggregate, monitor, query, analyze syslog data. Syslog separation of data from the source is a key element for security auditing as it make it more difficult to repudiate or wipe out logging data. Syslog data can be consolidated in a central repository with make it easier to correlate various events. Consolidated logs can go through reporting, analytics or artificial intelligence algorithms to detect patterns and alert customers of problems.
To set up the MariaDB Audit Plugin to logging to syslog is quite simple :
You download the MariaDB audit plugin (a single share library file) from :
http://www.skysql.com/downloads/mariadb-audit-plugin-beta
You copy the server_audit.so file to the lib/plugin directory in your MySQL/MariaDB install directory and activate it through :
MariaDB [(none)]> INSTALL PLUGIN server_audit SONAME 'server_audit.so'; By default login is done to file and you need to switch it to syslog :
MariaDB [test]> SET GLOBAL server_audit_output_type=SYSLOG;
MariaDB [test]> SET GLOBAL server_audit_events='CONNECT,QUERY';
MariaDB [test]> SET GLOBAL server_audit_logging=on; You can then see all the variable allowing to configure it :
MariaDB [test]> show variables like '%audit%';
+-------------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------------+-----------------------+
| server_audit_events | CONNECT,QUERY |
| server_audit_excl_users | | |
| server_audit_file_path | server_audit.log |
| server_audit_file_rotate_now | OFF |
| server_audit_file_rotate_size | 1000000 |
| server_audit_file_rotations | 9 |
| server_audit_incl_users | |
| server_audit_logging | ON |
| server_audit_mode | 0 |
| server_audit_output_type | syslog |
| server_audit_syslog_facility | LOG_USER |
| server_audit_syslog_ident | mysql-server_auditing |
| server_audit_syslog_info | |
| server_audit_syslog_priority | LOG_INFO |
+-------------------------------------+-----------------------+
14 rows in set (0.00 sec) you get a few status values:
MariaDB [test]> show status like '%audit%';
+------------------------------ +--------------+
| Variable_name | Value |
+----------------------------- -+--------------+
| server_audit_active | ON |
| server_audit_current_log | [SYSLOG] |
| server_audit_last_error | |
| server_audit_writes_failed | 0 |
+-------------------------------+--------------+
4 rows in set (0.00 sec) Check that rsyslog is running :
[root@centos1 log]# service rsyslog restart
Shutting down system logger: [ OK ]
Starting system logger: [ OK ] Now all connect and query MariaDB actions are written to the audit log in syslog.
[root@centos1 log]# tail -f /var/log/messages
Sep 21 00:07:07 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,10,QUERY,,'set global server_audit_logging=on',0
Sep 21 00:07:11 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,11,QUERY,,'show status like \'%audit%\'',0
Sep 21 00:07:21 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,12,QUERY,,'show variables like \'%audit%\'',0
Sep 21 00:10:06 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,13,QUERY,,'set global server_audit_events=\'CONNECT,QUERY\'',0
Sep 21 00:13:09 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,14,QUERY,,'SELECT DATABASE()',0
Sep 21 00:13:09 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,16,QUERY,test,'show databases',0
Sep 21 00:13:09 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,17,QUERY,test,'show tables',0
Sep 21 00:13:14 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,18,QUERY,test,'show tables',0 The fact that the log entries goes to /var/log/messages comes from the /etc/rsyslog.conf config file line
*.info;mail.none;authpriv.none;cron.none /var/log/messages To log to a different file you just add a line to the /etc/rsyslog.conf config file
if $programname == 'mysql-server_auditing' then /var/log/mariadbaudit1 Using MariaDB Audit Plugin with the syslog system is a good choice for security and sys admins.
By going to the download section of SkySQL website some users have noticed “MariaDB Audit Plugin”. This auditing feature for MySQL has been requested by more and more customers. Legal constraints make it mandatory for more and more companies to keep logging information about database access and activity.
It is very important for the MySQL community to have an open source audit plugin available. MariaDB team has always stick to the principle of keeping MySQL 100% open source and has developed the MariaDB Audit Plugin according to these principles. The MariaDB Audit Plugin has been developed using the standard MySQL Audit Plugin API. Being based on standard API makes it run both with MariaDB and with Oracle MySQL Server. The MariaDB Audit Plugin also has some unique features only available for MariaDB.
To develop this plugin we have sticked to the principle of listening to our big customer to define the specifications. This is the first version and some more improvements will come. We will be pleased to hear your feedbacks. All bug reports and critics are welcomed. The MariaDB Audit Plugin already covers the main requests that have been expressed. But we have more to come and your improvement requests are welcomed.
The purpose of the MariaDB Audit Plugin is to log the server’s activity. Who connected to the server, what queries ran and what tables were touched is stored to a rotating log file. The MariaDB Audit Plugin also supports output to the widely used syslogd system. It offers the option to include/exclude users from auditing.
Oracle has released an audit plugin available through its MySQL Enterprise Server version. This make it mandatory to have an active subscription. The version you run is then a fully closed source server under a commercial license. In that case you cannot rely on third party support like SkySQL or Percona.
The MariaDB Audit Plugin is delivered as a very light shared library that can be very easily downloaded and installed on any MariaDB/MySQL server.
Where to download the MariaDB Audit Plugin ?
http://www.skysql.com/downloads/mariadb-audit-plugin-beta
Source Code of the MariaDB Audit Plugin :
https://code.launchpad.net/~maria-captains/maria/5.5-noga-hf
Pointers :
MariaDB Audit Plugin Getting started guide
MariaDB Audit Plugin documentation
My blog post on other auditing techniques
Oracle MySQL Audit Plugin
Oracle MySQL Audit Plugin doc
Macfee Audit Plugin
Macfee Audit Plugin doc
|
add yourself to followers
|
Recent Comments