Exciting and New Features in MariaDB 10.5

New Features in MariaDB 10.5MariaDB 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. 

  • 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”.

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

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:

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:

Using “mariadb-dump”:

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:

Using mariadbd-safe:

Using mysqld_safe:

From the above examples, you can see that all the MariaDB server startup is using the “mariadbd”.

More Granular Privileges

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.

  • BINLOG ADMIN – Enables administration of the binary log, including the PURGE BINARY LOGS
  • BINLOG REPLAY – Enables replaying the binary log with the BINLOG statement
  • CONNECTION ADMIN – Enables administering connection resource limit options. This includes ignoring the limits specified by max_connections, max_user_connections, and max_password_errors
  • FEDERATED ADMIN – Execute CREATE SERVER, ALTER SERVER, and DROP SERVER statements. Added in MariaDB 10.5.2.
  • READ_ONLY ADMIN – User can set the read_only system variable and allows the user to perform write operations, even when the read_only option is active. Added in MariaDB 10.5.2.
  • REPLICATION MASTER ADMIN – Permits administration of primary servers, including the SHOW REPLICA HOSTS statement, and setting the gtid_binlog_state, gtid_domain_id, master_verify_checksum, and server_id system variables. Added in MariaDB 10.5.2.
  • REPLICATION SLAVE ADMIN – Permits administering replica servers, including START SLAVE, STOP SLAVE, CHANGE MASTER, SHOW SLAVE STATUS, SHOW RELAYLOG EVENTS statements (new in MariaDB 10.5.2).
  • SET USER – Enables setting the DEFINER when creating triggers, views, stored functions, and stored procedures (new in MariaDB 10.5.2).

And:

  • “REPLICATION CLIENT” is renamed to “BINLOG MONITOR”
  • “SHOW MASTER STATUS” command is now renamed to “SHOW BINLOG STATUS”

Galera With Full GTID Support

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 ).

InnoDB Refactoring

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.

  • INFORMATION_SCHEMA
  • PERFORMANCE_SCHEMA
  • JSON
  • Query Optimizer
  • Binary logs with more metadata

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.

Share this post

Leave a Reply