Book Image

MariaDb Essentials

Book Image

MariaDb Essentials

Overview of this book

This book will take you through all the nitty-gritty parts of MariaDB, right from the creation of your database all the way to using MariaDB’s advanced features. At the very beginning, we show you the basics, that is, how to install MariaDB. Then, we walk you through the databases and tables of MariaDB, and introduce SQL in MariaDB. You will learn about all the features that have been added in MariaDB but are absent in MySQL. Moving on, you’ll learn to import and export data, views, virtual columns, and dynamic columns in MariaDB. Then, you’ll get to grips with full-text searches and queries in MariaDb. You’ll also be familiarized with the CONNECT storage engine. At the end of the book, you’ll be introduced to the community of MariaDB.
Table of Contents (10 chapters)
9
Index

Managing plugins

MariaDB has several functionalities, but more can be added by installing plugins. This allows the users to deactivate some unneeded functionality by uninstalling a plugin, or activating functionalities that are not needed by the majority of users. More importantly, some plugins implement the same class of functionalities in different ways. This is the case with storage engines, a special plugin type that will be discussed in Chapter 2, Databases and Tables. Some plugins are developed by the MariaDB team, others by individuals or companies that are members of the community. Several plugins, developed by the MariaDB team or by third parties, are included in the official MariaDB distributions. Others are available at their respective developer's websites.

Plugins are contained in files with the .so extension on Linux and with the .ddl extension on Windows. Each file is a library that can contain one or more plugins. These files need to be located in the plugins directory. To discover the path of such a directory in our MariaDB installation, we can query the @@plugin_dir server variable:

MariaDB [(none)]> SELECT @@plugin_dir;
+------------------------------+
| @@plugin_dir                 |
+------------------------------+
| /usr/local/mysql/lib/plugin/ |
+------------------------------+
1 row in set (0.00 sec)

MariaDB provides some SQL statements to manage plugins at runtime. The following list shows these statements, before discussing them in detail:

  • SHOW PLUGINS displays a list of available plugins
  • INSTALL SONAME installs all the plugins from a file
  • UNINSTALL SONAME uninstalls all the plugins contained in a library
  • INSTALL PLUGIN installs an individual plugin
  • UNINSTALL PLUGIN uninstalls an individual plugin

The syntax of SHOW PLUGINS is very simple. Consider the following example:

MariaDB [(none)]> SHOW PLUGINS;
+-----------------------------+----------+--------------------+---------------------+---------+
| Name                        | Status   | Type               | Library             | License |
+-----------------------------+----------+--------------------+---------------------+---------+
| binlog                      | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |
| mysql_native_password       | ACTIVE   | AUTHENTICATION     | NULL                | GPL     |
| mysql_old_password          | ACTIVE   | AUTHENTICATION     | NULL                | GPL     |
...
+-----------------------------+----------+--------------------+---------------------+---------+
54 rows in set (0.00 sec)

The list has been truncated because it was very long. However, from the example, we can see that five columns are returned by this statement:

  • Name: Plugin name.
  • Status: ACTIVE means that the plugin is installed, INACTIVE means that the plugin is not installed, DISABLED means that the plugin has been disabled with a server option and cannot be installed, and DELETED means that the library file has been removed.
  • Type: This value indicates the plugin type. For example, the value AUTHENTICATION means that the plugin handles the user's login, and INFORMATION SCHEMA means that the plugin provides metainformation to the user.
  • Library: This indicates the library file name. If this value is NULL, the plugin is built-in and cannot be uninstalled.
  • License: Indicates the plugin's license, which determines the user's rights. This is just the license name: the complete text should be provided as a file distributed along with the plugin.

If a library contains more than one plugin, we will want to install them all to enable the whole set of related functionalities. For this reason, we will usually prefer the INSTALL SONAME statement. The name of the file must be passed to this statement. The file extension is optional, which allows us to install a library on any system using the identical command. For example, to install the SEQUENCE storage engine, we use the following command:

INSTALL SONAME 'ha_sequence';

Similarly, we can uninstall the whole set of plugins with UNINSTALL SONAME, like in the following example:

UNINSTALL SONAME 'ha_sequence';

In very rare cases, we may want to install or uninstall a single plugin. In such cases, we will use the INSTALL PLUGIN or UNINSTALL PLUGIN statement, specifying the name of the plugin that we want to install or uninstall, and the file name. For example:

INSTALL PLUGIN sequence SONAME 'ha_sequence';
UNINSTALL PLUGIN sequence;

Some plugins create a set of server variables that can be used to configure them at runtime. Such variables do not exist until the plugin is installed or after it is uninstalled. By convention, usually all these variables have the same prefix, which is the plugin name. This makes it easier to discover them with the SHOW VARIABLES statement. The following example shows how this mechanism works:

MariaDB [(none)]> SHOW VARIABLES LIKE 'spider%';
Empty set (0.00 sec)

MariaDB [(none)]> INSTALL SONAME 'ha_spider';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE 'spider%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| spider_auto_increment_mode            | -1    |
| spider_bgs_first_read                 | -1    |
...
+---------------------------------------+-------+
99 rows in set (0.00 sec)