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.
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 |
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 herculesG *************************** 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.
|
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 |
Usually, INET6 refers to the IPv6 family.
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) |
All binaries are now changed to “mariadb” from “mysql”, with symlinks for the corresponding mysql command.
Example:
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 [root@mariadb ~]# less mysql.sql | head -n5 -- MariaDB dump 10.17 Distrib 10.5.5-MariaDB, for Linux (x86_64) -- -- Host: localhost Database: mysql -- ------------------------------------------------------ -- Server version 10.5.5-MariaDB |
MariaDB server startup via systemd service will be started using the mariadbd binary. This is applicable for mariadbd-safe wrapper script as well. Even when called via the mysqld_safe symlink, it will start the actual server process as mariadbd, not mysqld.
Example:
Using startup service:
|
1 2 3 4 5 |
[root@mariadb ~]# service mysql start Redirecting to /bin/systemctl start mysql.service [root@mariadb ~]# ps -ef | grep -i mysql mysql 9002 1 1 01:23 ? 00:00:00 /usr/sbin/mariadbd root 9021 8938 0 01:23 pts/0 00:00:00 grep --color=auto -i mysql |
Using mariadbd-safe:
|
1 2 3 4 5 6 7 |
[root@mariadb ~]# mariadbd-safe --user=mysql & [root@mariadb ~]# 200806 01:30:43 mysqld_safe Logging to '/var/lib/mysql/mariadb.err'. 200806 01:30:43 mysqld_safe Starting mariadbd daemon with databases from /var/lib/mysql [root@mariadb ~]# [root@mariadb ~]# ps -ef | grep -i mysql root 9088 8938 0 01:30 pts/0 00:00:00 /bin/sh /bin/mariadbd-safe --user=mysql mysql 9162 9088 1 01:30 pts/0 00:00:00 //sbin/mariadbd --basedir=/ --datadir=/var/lib/mysql --plugin-dir=//lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/mariadb.err --pi |
Using mysqld_safe:
|
1 2 3 4 5 6 |
[root@mariadb ~]# mysqld_safe --user=mysql & [root@mariadb ~]# 200806 01:31:40 mysqld_safe Logging to '/var/lib/mysql/mariadb.err'. 200806 01:31:40 mysqld_safe Starting mariadbd daemon with databases from /var/lib/mysql [root@mariadb ~]# ps -ef | grep -i mysql root 9179 8938 0 01:31 pts/0 00:00:00 /bin/sh /bin/mysqld_safe --user=mysql mysql 9255 9179 0 01:31 pts/0 00:00:00 //sbin/mariadbd --basedir=/ --datadir=/var/lib/mysql --plugin-dir=//lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/mariadb.err --pid-file=mariadb.pid |
From the above examples, you can see that all the MariaDB server startup is using the “mariadbd”.
Privileges are more granular now. SUPER privilege is split now with more small privileges, similar to MySQL 8 dynamic privileges. Security-wise this is a very good implementation to avoid unwanted privileges allocation to users.
And:
|
1 2 3 4 5 6 7 |
MariaDB [jesus]> show binlog status; +-------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------+----------+--------------+------------------+ | herc.000003 | 525 | | | +-------------+----------+--------------+------------------+ 1 row in set (0.000 sec) |
Galera is now completely supported with GTID from MariaDB 10.5. It will greatly help the cluster + Async replication environment. With this feature, all nodes in a cluster will have the same GTID for replicated events originating from the cluster.
MariaDB 10.5 also has the new SESSION variable “wsrep_gtid_seq_no”. With this variable, we can manually update the WSREP GTID sequence number in the cluster ( like gtid_seq_no for non WSREP transactions ).
|
1 2 3 4 5 6 7 |
MariaDB [jesus]> show variables like 'wsrep_gtid_seq_no'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | wsrep_gtid_seq_no | 0 | +-------------------+-------+ 1 row in set (0.001 sec) |
There are some notable changes in InnoDB engine, which makes MariaDB more divergent from MySQL.
Apart from this, MariaDB 10.5 has more improvements on the following topics as well.
I am looking forward to experimenting with the new MariaDB 10.5 features and how they are going to help in the production environments. I am also planning to write blogs on some of these topics, so stay tuned!
Your mission-critical applications depend on your MariaDB database environment. What happens if your database goes down? Contact Percona MariaDB Database Support! Percona is the premier support provider for open source databases, including MariaDB, the most well-known fork of MySQL.
