MySQL Interview Questions - MySQL Plugin Architecture

This post answers the most common questions of the MySQL Plugin architecture. Answers cover areas such as installation, usage, and monitoring and also why it may be important to you.

What is a MySQL Plugin?

MySQL utilizes a plugin API the allows the creation of server components to extend the server’s functionality. The plugin API is designed to be generic to offer a broad range of functionality for the plugin interface. This allows the plugin API to be used for a range of purposes from storage engines to customized server features.

As from MySQL 5.5.7 upwards, the API is able to support both client plugins in addition to that of server plugins. This means that features can be developed that require both the server-side and the client-side to cooperate such as with authentication of users.

What are the different types of plugins?

The plugin interface is designed to support a range of different types. Currently the plugin types that are known to be supported are storage engines, full text parsing, partitioning support and server extensions. Some of these are already being used in the 5.5 version and more will be developed in the future. A longer list of possible plugin types includes:

Storage engines
Full-text parsers
Daemons
INFORMATION_SCHEMA tables
Semi-synchronous replication
Auditing
Authentication

The current list of plugins is certainly not the full extent of what is able to be offered via the plugin API.

What about the old User-Defined Function (UDF) API?

The plugin API is designed to be a successor to the older UDF interface which can offer easier integration of user functions into the MySQL server and a lot more.

Where can I get the plugins?

There are already plugins being used in the default installation of the MySQL Server. However, there are some plugins that come with the server that are not enabled by default. For these existing plugins, you can check the lib/plugin directory under the installation and then install the plugins for use in the server. All commercial plugins are readily available in the Enterprise editions of MySQL Server.

Note: Some plugins are commercially licensed and may only be available in appropriately licensed versions of the MySQL Server.

How do I install plugins?

The plugins can be loaded using a dynamic method while the server is running, or a static method for when the server is first started. To load a plugin dynamically, invoke the INSTALL PLUGIN command like this:

INSTALL PLUGIN plugin_name SONAME 'shared_library_name'

This will load the plugin and populate the mysql.plugin table to make the plugin permanently enabled and therefore will be loaded on each restart of the service. This table is created as part of the MySQL installation process. To load a plugin statically, simply add a line to the my.cnf file like the following:

[mysqld]
plugin-load=
plugin_name=plugin_library.so

This method will allow the plugin to be installed as the server starts and can be used as a “one-off” method of testing a plugin. That is, unless you keep the plugin-load option in your my.cnf file, or specify it on the command line, the plugin will only load one time. This means that the plugins loaded this way are not recorded in the mysql.plugin table.

Note: The extension that is used in loading the library will vary depending on the operating system being used. The .so extension is for Unix systems, Microsoft Windows .dll is used instead and Mac OS X uses the .dylib extension for shared libraries.

How do I identify installed plugins?

To see what plugins are currently loaded in the MySQL service, run the SHOW PLUGINS command.

mysql> show plugins;
+-----------------------+----------+--------------------+-----------------------+-------------+
| Name                  | Status   | Type               | Library               | License     |
+-----------------------+----------+--------------------+-----------------------+-------------+
| binlog                | ACTIVE   | STORAGE ENGINE     | NULL                  | PROPRIETARY |
| mysql_native_password | ACTIVE   | AUTHENTICATION     | NULL                  | PROPRIETARY |
| mysql_old_password    | ACTIVE   | AUTHENTICATION     | NULL                  | PROPRIETARY |
| MyISAM                | ACTIVE   | STORAGE ENGINE     | NULL                  | PROPRIETARY |
| MRG_MYISAM            | ACTIVE   | STORAGE ENGINE     | NULL                  | PROPRIETARY |
| CSV                   | ACTIVE   | STORAGE ENGINE     | NULL                  | PROPRIETARY |
| MEMORY                | ACTIVE   | STORAGE ENGINE     | NULL                  | PROPRIETARY |
| PERFORMANCE_SCHEMA    | ACTIVE   | STORAGE ENGINE     | NULL                  | PROPRIETARY |
| InnoDB                | ACTIVE   | STORAGE ENGINE     | NULL                  | PROPRIETARY |
.....
| BLACKHOLE             | ACTIVE   | STORAGE ENGINE     | NULL                  | PROPRIETARY |
| FEDERATED             | DISABLED | STORAGE ENGINE     | NULL                  | PROPRIETARY |
| ARCHIVE               | ACTIVE   | STORAGE ENGINE     | NULL                  | PROPRIETARY |
| partition             | ACTIVE   | STORAGE ENGINE     | NULL                  | PROPRIETARY |
| thread_pool           | ACTIVE   | DAEMON             | thread_pool.so        | PROPRIETARY |
| TP_THREAD_STATE       | ACTIVE   | INFORMATION SCHEMA | thread_pool.so        | PROPRIETARY |
| TP_THREAD_GROUP_STATE | ACTIVE   | INFORMATION SCHEMA | thread_pool.so        | PROPRIETARY |
| TP_THREAD_GROUP_STATS | ACTIVE   | INFORMATION SCHEMA | thread_pool.so        | PROPRIETARY |
| authentication_pam    | ACTIVE   | AUTHENTICATION     | authentication_pam.so | PROPRIETARY |
+-----------------------+----------+--------------------+-----------------------+-------------+
25 rows in set (0.00 sec)

How do I remove a plugin?

A plugin known to the server can be uninstalled to disable it at runtime with the UNINSTALL PLUGIN statement. The statement unloads the plugin and removes it from the mysql.plugin table if it is registered there.

Note: Since the plugin is no longer registered in the table, the server will not load the plugin automatically on subsequent restarts. It will need to be installed again if required again in the future.

The UNINSTALL PLUGIN statement will unload plugins regardless of the method they were loaded.

Can I create a plugin?

The API enables anyone to create a functional plugin that suits their needs. Details on how to write a plugin can be seen in the manual at: https://dev.mysql.com/doc/en/writing-plugins.html

Can I create my own commercially licensed plugin?

Licensing requirements may come into play for any type of third-party plugin. Please contact the Sales department at Oracle to discuss your plans further and to get the correct advice.

Where can I find out more information?

There is a lot more explanation of the plugin API and how it all works in the MySQL Manual. Please see the MySQL reference guide for further reading.