MySQL插件（一）介绍

简介

在第一节中提到，MySQL 是支持动态可插拔的储存引擎体系架构。

而储存引擎其实也是一种插件类型，所以在继续储存引擎的后续介绍之前，有必要先来了解一下MySQL的插件。

可以从INFORMATION_SCHEMA.PLUGINS 或者 SHOW PLUGINS 可以获取到当前 MySQL 内部所有插件的详情。

mysql> mysql> select * from information_schema.plugins;
+----------------------------+----------------+---------------+--------------------+---------------------+----------------+------------------------+--------------------------------------------+---------------------------------------------------------------------------+----------------+-------------+
| PLUGIN_NAME                | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE        | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY | PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR                              | PLUGIN_DESCRIPTION                                                        | PLUGIN_LICENSE | LOAD_OPTION |
+----------------------------+----------------+---------------+--------------------+---------------------+----------------+------------------------+--------------------------------------------+---------------------------------------------------------------------------+----------------+-------------+
| binlog                     | 1.0            | ACTIVE        | STORAGE ENGINE     | 50725.0             | NULL           | NULL                   | MySQL AB                                   | This is a pseudo storage engine to represent the binlog in a transaction  | GPL            | FORCE       |
| mysql_native_password      | 1.1            | ACTIVE        | AUTHENTICATION     | 1.1                 | NULL           | NULL                   | R.J.Silk, Sergei Golubchik                 | Native MySQL authentication                                               | GPL            | FORCE       |
| sha256_password            | 1.1            | ACTIVE        | AUTHENTICATION     | 1.1                 | NULL           | NULL                   | Oracle                                     | SHA256 password authentication                                            | GPL            | FORCE       |
| CSV                        | 1.0            | ACTIVE        | STORAGE ENGINE     | 50725.0             | NULL           | NULL                   | Brian Aker, MySQL AB                       | CSV storage engine                                                        | GPL            | FORCE       |
| MEMORY                     | 1.0            | ACTIVE        | STORAGE ENGINE     | 50725.0             | NULL           | NULL                   | MySQL AB                                   | Hash based, stored in memory, useful for temporary tables                 | GPL            | FORCE       |
| InnoDB                     | 5.7            | ACTIVE        | STORAGE ENGINE     | 50725.0             | NULL           | NULL                   | Oracle Corporation                         | Supports transactions, row-level locking, and foreign keys                | GPL            | FORCE       |
| INNODB_TRX                 | 5.7            | ACTIVE        | INFORMATION SCHEMA | 50725.0             | NULL           | NULL                   | Oracle Corporation                         | InnoDB transactions                                                       | GPL            | FORCE       |
| INNODB_LOCKS               | 5.7            | ACTIVE        | INFORMATION SCHEMA | 50725.0             | NULL           | NULL                   | Oracle Corporation                         | InnoDB conflicting locks                                                  | GPL            | FORCE       |
| INNODB_LOCK_WAITS          | 5.7            | ACTIVE        | INFORMATION SCHEMA | 50725.0             | NULL           | NULL                   | Oracle Corporation                         | InnoDB which lock is blocking which                                       | GPL            | FORCE       |
  ......
| INNODB_SYS_VIRTUAL         | 5.7            | ACTIVE        | INFORMATION SCHEMA | 50725.0             | NULL           | NULL                   | Oracle Corporation                         | InnoDB SYS_VIRTUAL                                                        | GPL            | FORCE       |
| MyISAM                     | 1.0            | ACTIVE        | STORAGE ENGINE     | 50725.0             | NULL           | NULL                   | MySQL AB                                   | MyISAM storage engine                                                     | GPL            | FORCE       |
| MRG_MYISAM                 | 1.0            | ACTIVE        | STORAGE ENGINE     | 50725.0             | NULL           | NULL                   | MySQL AB                                   | Collection of identical MyISAM tables                                     | GPL            | FORCE       |
| PERFORMANCE_SCHEMA         | 0.1            | ACTIVE        | STORAGE ENGINE     | 50725.0             | NULL           | NULL                   | Marc Alff, Oracle                          | Performance Schema                                                        | GPL            | FORCE       |
| ARCHIVE                    | 3.0            | ACTIVE        | STORAGE ENGINE     | 50725.0             | NULL           | NULL                   | Brian Aker, MySQL AB                       | Archive storage engine                                                    | GPL            | ON          |
| BLACKHOLE                  | 1.0            | ACTIVE        | STORAGE ENGINE     | 50725.0             | NULL           | NULL                   | MySQL AB                                   | /dev/null storage engine (anything you write to it disappears)            | GPL            | ON          |
| FEDERATED                  | 1.0            | DISABLED      | STORAGE ENGINE     | 50725.0             | NULL           | NULL                   | Patrick Galbraith and Brian Aker, MySQL AB | Federated MySQL storage engine                                            | GPL            | OFF         |
| partition                  | 1.0            | ACTIVE        | STORAGE ENGINE     | 50725.0             | NULL           | NULL                   | Mikael Ronstrom, MySQL AB                  | Partition Storage Engine Helper                                           | GPL            | ON          |
| ngram                      | 0.1            | ACTIVE        | FTPARSER           | 1.1                 | NULL           | NULL                   | Oracle Corp                                | Ngram Full-Text Parser                                                    | GPL            | ON          |
+----------------------------+----------------+---------------+--------------------+---------------------+----------------+------------------------+--------------------------------------------+---------------------------------------------------------------------------+----------------+-------------+

插件类型

在 MySQL 5.7.25 中，主要有以下几种类型：

• 储存引擎（storage engines）
• 全文索引解析插件（Full-text parsers）
• 守护插件（Daemons）
• INFORMATION_SCHEMA 插件（INFORMATION_SCHEMA tables）
• 日志同步插件（Semisynchronous replication）
• 认证插件（Authentication）
• 密码验证插件（Password validation and strength checking）
• 密钥环插件（Keyring）

源码中 include/mysql/plugin.h 有各种插件类型的宏定义：

/*
  The allowable types of plugins
*/
#define MYSQL_UDF_PLUGIN             0  /* User-defined function        */
#define MYSQL_STORAGE_ENGINE_PLUGIN  1  /* Storage Engine               */
#define MYSQL_FTPARSER_PLUGIN        2  /* Full-text parser plugin      */
#define MYSQL_DAEMON_PLUGIN          3  /* The daemon/raw plugin type */
#define MYSQL_INFORMATION_SCHEMA_PLUGIN  4  /* The I_S plugin type */
#define MYSQL_AUDIT_PLUGIN           5  /* The Audit plugin type        */
#define MYSQL_REPLICATION_PLUGIN     6	/* The replication plugin type */
#define MYSQL_AUTHENTICATION_PLUGIN  7  /* The authentication plugin type */
#define MYSQL_VALIDATE_PASSWORD_PLUGIN  8   /* validate password plugin type */
#define MYSQL_GROUP_REPLICATION_PLUGIN  9  /* The Group Replication plugin */
#define MYSQL_KEYRING_PLUGIN         10  /* The Keyring plugin type   */
#define MYSQL_MAX_PLUGIN_TYPE_NUM    11  /* The number of plugin types   */

如果需要实现自定义的储存引擎，需要自定义一个类型为#define MYSQL_STORAGE_ENGINE_PLUGIN 1的插件类型。

加载和卸载插件

在源码编译时，MySQL 插件可以直接以 buildin 的方式作为静态插件内嵌到 mysqld 中，从而在 mysqld 服务起来之后，自动加载插件。

此外，在编译时，插件也可以编译为动态插件，从而在在运行时可以加载和卸载插件，做到可插拔。

动态插件可以在服务器启动时通过指定 option 加载，也可以在运行时加载和卸载插件。

• 启动时加载
  指定plugin_load选项并指定特定的插件名称和对应动态插件库。

  support-files/mysql.server restart --plugin_load=example=ha_example.so
  

  也可以写入到 my.cnf 配置项中

  [mysqld]
  plugin-load=example=ha_example.so
  

  Tips:
  一般源码编译的会有ha_example.so动态查看库，这是一个储存引擎实例插件，位于plugin_dir路径中， plugin_dir一般位于安装目录下的lib/plugin中，也可以通过查询得到：

  mysql> select @@plugin_dir;
  +-------------------------------------+
  | @@plugin_dir                        |
   +-------------------------------------+
   | /opt/mysql-5.7.25/mysql/lib/plugin/ |
   +-------------------------------------+
   1 row in set (0.01 sec)
  

  可以去路径下查看都有哪些动态插件

  root@ivan:/opt/mysql-5.7.25/mysql/lib/plugin# ls
  adt_null.so            keyring_file.so               libtest_session_in_thd.so     libtest_sql_processlist.so                  locking_service.so                       rewrite_example.so
  auth.so                keyring_udf.so                libtest_sql_2_sessions.so     libtest_sql_replication.so                  mypluglib.so                             rewriter.so
  auth_socket.so         libdaemon_example.so          libtest_sql_all_col_types.so  libtest_sql_shutdown.so                     mysql_no_login.so                        semisync_master.so
  auth_test_plugin.so    libtest_framework.so          libtest_sql_cmds_1.so         libtest_sql_sqlmode.so                      mysqlx.so                                semisync_slave.so
  connection_control.so  libtest_services.so           libtest_sql_commit.so         libtest_sql_stored_procedures_functions.so  qa_auth_client.so                        test_security_context.so
  daemon_example.ini     libtest_services_threaded.so  libtest_sql_complex.so        libtest_sql_views_triggers.so               qa_auth_interface.so                     test_udf_services.so
  group_replication.so   libtest_session_detach.so     libtest_sql_errors.so         libtest_x_sessions_deinit.so                qa_auth_server.so                        validate_password.so
  ha_example.so          libtest_session_info.so       libtest_sql_lock.so           libtest_x_sessions_init.so                  replication_observers_example_plugin.so  version_token.so
  root@ivan:/opt/mysql-5.7.25/mysql/lib/plugin# 
  

• 运行时动态加载

   mysql> install plugin example soname 'ha_example.so';
   Query OK, 0 rows affected (0.01 sec)
   mysql> show plugins;
   +----------------------------+----------+--------------------+---------------+---------+
    | Name                       | Status   | Type               | Library       | License |
    +----------------------------+----------+--------------------+---------------+---------+
    | MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL          | GPL     |
    | InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL          | GPL     |
    ......
    | partition                  | ACTIVE   | STORAGE ENGINE     | NULL          | GPL     |
    | ngram                      | ACTIVE   | FTPARSER           | NULL          | GPL     |
    | EXAMPLE                    | ACTIVE   | STORAGE ENGINE     | ha_example.so | GPL     |
    +----------------------------+----------+--------------------+---------------+---------+
  

• 卸载插件

   mysql> uninstall plugin example;
   Query OK, 0 rows affected (0.01 sec)
  

对 MySQL 自定义引擎工作感兴趣的
v 信：yutellite