Oracle has done a great technical work with MySQL. Specifically a nice job has been done around security. There is one useful feature that exists in Oracle MySQL and that currently does not exist in MariaDB.
Oracle MySQL offers the possibility from within the server to generate asymetric key pairs. It is then possible use them from within the MySQL server to encrypt, decrypt or sign data without exiting the MySQL server. This is a great feature. This is defined as a set of UDF (User Defined Function : CREATE FUNCTION asymmetric_decrypt, asymmetric_encrypt, asymmetric_pub_key … SONAME 'openssl_udf.so';).
Keep on reading!
Category: community
MariaDB AWS Key Management Service (KMS) Encryption Plugin
MariaDB 10.1 introduced Data at Rest Encryption. By default we provide a file_key_management plugin. This is a basic plugin storing keys in a file that can be itself encrypted. This file can come from a usb stick removed once keys have been brought into memory. But this remains a basic solution not suitable for security compliance rules.
Keep on reading!
MariaDB and Native JSON support ?
A question raised by my previous post is : What about MariaDB and native JSON support ? In my previous post I mentioned the possibility to use the MariaDB CONNECT storage Engine to store and access JSON content in normal text field. Of course having a native JSON datatype brings more value. It introduces JSON validation, a more efficient access to attribute through an optimized storage format.
Keep on reading!
MariaDB JSON text indexing
It is not new that we can store a JSON content in a normal table text field. This has always been the case in the past. But two key features were missing : filtering based on JSON content attributes and indexing of the JSON content. With MariaDB 10.1 CONNECT storage Engine we offer support for external content including JSON files. The MariaDB CONNECT storage Engine also comes with a set of JSON related UDFs. This allows us to do the following thing :
Keep on reading!
Using JSON’s Arrays for MariaDB Dynamic Columns
The JSON format includes the concept of array. A JSON object cant contain an attribute of array type. We have seen that we can use the MariaDB CONNECT Storage Engine provided UDFs (user defined functions) to implement dynamic columns.
Keep on reading!
MariaDB CONNECT Storage Engine JSON Autodiscovery
The MariaDB CONNECT storage engine offers access to JSON file and allows you to see a external JSON file as a MariaDB table.
A nice feature of the CONNECT storage Engine is its capability to auto discover a table structure when the table correspond to external data. In our case the CONNECT storage engine will automatically define the columns based on the JSON file hierarchical structure.
This is possible thanks to a feature of MariaDB Storage Engine API. This auto discovery feature allows a create statement to delegate the discovery of the table structure to the chosen storage engine.
Keep on reading!
Select from a mariaDB table into JSON format ?
Can we output content of a mariaDB table into JSON using the CONNECT Storage Engine ?
MariaDB and MySQL are not currently JSON friendly databases. The usage of JSON in MySQL is almost inexistent with the excetion of the explain JSON output and of MariaDB dynamic column dump.
There also exist a few udf (in MySQL lab area) to help manipulate JSON strings(searching, modification …).
Keep on reading!
Since now on github is the place for MariaDB new dev !
Today while browsing through my emails I was very happy to read this email from Sergei Golubchik :
Hi! I'm happy to announce that MariaDB-10.1 tree has been completely migrated to github. Since now on we'll use github for the new development in MariaDB. It's https://github.com/MariaDB/server, go on, fork it, hack around, submit pull requests. Have fun! Older trees (10.0, 5.5, 5.3, 5.2, 5.1) are not on github - we do hope to migrate them too eventually, but at the moment they are still on launchpad. If you're a maria-captain on launchpad - for you to get write access to the mariadb repository on github you need to tell me your github account name. Regards, Sergei P.S.: Don't forget that 10.1 is still *pre-alpha*. You've been warned. _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
So if you want to embark the MariaDB adventure
it is here : https://github.com/MariaDB/server
Go on,
Fork it,
Hack around,
Submit pull requests.
Have fun!
MaxScale, ProxySQL and MySQL Proxy
At FOSDEM 2014 ProxySQL and MaxScale were both presented. Both are proxy that can help build sophisticated MariaDB/MySQL architectures.
But currently what is the most used proxy with MySQL? It is HAproxy. HAproxy is a level 4 proxy that has no knowledge of the MySQL protocol. Being low level makes it very fast but it cannot accomplish any advanced proxy task. In many case this is enough for pure load balancing. But it cannot handle filtering, routing, query rewriting. This requires to understand the MySQL protocol and to look at the query passing through the proxy.
Keep on reading!
MariaDB CONNECT Storage Engine and parallelism
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 😉