MariaDB 10.5 was released in June 2020 and it will be supported until June 2025. This is the current stable version and comes with more exciting new features. In this blog, I am going to explain the new and exciting features involved in MariaDB 10.5.
- Amazon S3 engine
- Column Store
- INET 6 data type
- Binaries name changed to mariadb
- More granular privileges
- Galera with full GTID support
- InnoDB refactoring
Amazon S3 Engine
S3 engine is a nice feature in MariaDB 10.5. Now, you can directly move your table from a local device to Amazon S3 using the ALTER. Still, your data is accessible from MariaDB clients using the standard SQL commands. This is a great solution to those who are looking to archive data for future references at a low cost. I have written a blog about this feature – MariaDB S3 Engine: Implementation and Benchmarking – which has more insights on this.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
#Installation MariaDB [(none)]> install soname 'ha_s3'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> select * from information_schema.engines where engine = 's3'\G *************************** 1. row *************************** ENGINE: S3 SUPPORT: YES COMMENT: Read only table stored in S3. Created by running ALTER TABLE table_name ENGINE=s3 TRANSACTIONS: NO XA: NO SAVEPOINTS: NO 1 row in set (0.000 sec) #Implementation MariaDB [s3_test]> alter table percona_s3 engine=s3; Query OK, 0 rows affected (1.934 sec) Records: 0 Duplicates: 0 Warnings: 0 |
- The S3 engine tables are completely read-only.
- COUNT(*) is pretty fast on s3 engine tables.
ColumnStore
MariaDB ColumnStore 1.5 is available with MariaDB 10.5 community server. It brings a high-performance, open source, distributed, SQL compatible analytics solution. Before MariaDB 10.5, ColumnStore was available as a separate fork of MariaDB. But with MariaDB 10.5, ColumnStore is now completely integrated. All you need to do is install the package for ColumnStore “MariaDB-columnstore-engine.x86_64”.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
[root@mariadb ~]# yum list installed | grep -i columnstore MariaDB-columnstore-engine.x86_64 10.5.5-1.el7.centos @mariadb-main MariaDB [jesus]> select plugin_name,plugin_status,plugin_library,plugin_version from information_schema.plugins where plugin_name like 'columnstore%'; +---------------------+---------------+-------------------+----------------+ | plugin_name | plugin_status | plugin_library | plugin_version | +---------------------+---------------+-------------------+----------------+ | Columnstore | ACTIVE | ha_columnstore.so | 1.5 | | COLUMNSTORE_COLUMNS | ACTIVE | ha_columnstore.so | 1.5 | | COLUMNSTORE_TABLES | ACTIVE | ha_columnstore.so | 1.5 | | COLUMNSTORE_FILES | ACTIVE | ha_columnstore.so | 1.5 | | COLUMNSTORE_EXTENTS | ACTIVE | ha_columnstore.so | 1.5 | +---------------------+---------------+-------------------+----------------+ 5 rows in set (0.002 sec) MariaDB [jesus]> create table hercules(id int, name varchar(16)) engine = ColumnStore; Query OK, 0 rows affected (0.503 sec) MariaDB [jesus]> show create table hercules\G *************************** 1. row *************************** Table: hercules Create Table: CREATE TABLE `hercules` ( `id` int(11) DEFAULT NULL, `name` varchar(16) DEFAULT NULL ) ENGINE=Columnstore DEFAULT CHARSET=latin1 1 row in set (0.000 sec) |
MariaDB ColumnStore 1.5 comes with two .xml utilities, which greatly helps with configuration management.
- mcsGetConfig : Used to display the current configurations
- mcsSetConfig : Used to change the configuration
|
1 2 3 4 5 |
[root@mariadb vagrant]# mcsGetConfig -a | grep CrossEngineSupport.Pass CrossEngineSupport.Password = [root@mariadb vagrant]# mcsSetConfig CrossEngineSupport Password "hercules7sakthi" [root@mariadb vagrant]# mcsGetConfig -a | grep CrossEngineSupport.Pass CrossEngineSupport.Password = hercules7sakthi |
INET6 Data Type
Usually, INET6 refers to the IPv6 family.
- INET6 data type is introduced to store the IPv6 addresses.
- INET6 data type also can be used to store the IPv4 addresses assuming conventional mapping of IPv4 addresses into IPv6 addresses.
- Internally storage engine see the INET6 as BINARY(16) and clients see the INET6 as CHAR(39)
- Values are stored as a 16-byte fixed-length binary string
Example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
MariaDB [jesus]> create table inet6test (id int primary key auto_increment, ipaddresses INET6); Query OK, 0 rows affected (0.005 sec) MariaDB [jesus]> insert into inet6test (ipaddresses) values ('2001:0db8:85b3:0000:0000:8a2e:0370:7334'); Query OK, 1 row affected (0.001 sec) MariaDB [jesus]> insert into inet6test (ipaddresses) values ('::172.28.128.12'); Query OK, 1 row affected (0.002 sec) MariaDB [jesus]> select * from inet6test; +----+------------------------------+ | id | ipaddresses | +----+------------------------------+ | 1 | 2001:db8:85b3::8a2e:370:7334 | | 2 | ::172.28.128.12 | +----+------------------------------+ 2 rows in set (0.000 sec) |
Binaries Name Changed to mariadb
All binaries are now changed to “mariadb” from “mysql”, with symlinks for the corresponding mysql command.
Example:
- “mysql” is now “mariadb”
- “mysqldump” is now “mariadb-dump”
- “mysqld” is now “mariadbd”
- “mysqld_safe” is now “mariadbd-safe”
Using “mariadb” client:
|
1 2 3 4 5 6 |
[root@mariadb ~]# mariadb -e "select @@version, @@version_comment" +----------------+-------------------+ | @@version | @@version_comment | +----------------+-------------------+ | 10.5.5-MariaDB | MariaDB Server | +----------------+-------------------+ |
Using “mariadb-dump”:
|
1 2 3 4 5 6 7 |
[root@mariadb ~]# mariadb-dump mysql > mysql.sql |