MENU


Database Server Plugin

This section covers the top-level objects that sit above tables: databases (schemas) themselves, remote server definitions used by the FEDERATED storage engine, and the server plugins and components that extend MySQL's capabilities.

CREATE, ALTER, DROP DATABASE

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
  [create_specification] ...

create_specification:
  [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

ALTER {DATABASE | SCHEMA} [db_name]
  alter_specification ...
ALTER {DATABASE | SCHEMA} db_name
  UPGRADE DATA DIRECTORY NAME

alter_specification:
  [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

USE db_name

DATABASE and SCHEMA are synonyms in MySQL. The DEFAULT CHARACTER SET and DEFAULT COLLATE specifications set the database-level defaults that new tables and columns inherit unless overridden; see Data Types for character set and collation details at the column level. USE selects a database as the default (current) database for subsequent statements in a session.


Dropping a database removes every table, view, stored routine, event, and trigger it contains, along with the database itself – the operation cannot be undone, so IF EXISTS is commonly paired with a preceding backup. UPGRADE DATA DIRECTORY NAME is used after upgrading MySQL on certain platforms to re-encode a database directory name that contains non-ASCII characters, so it matches the encoding used by the new server version.

CREATE, ALTER, DROP SERVER

A SERVER definition records the connection details for a remote MySQL server, for use with the FEDERATED storage engine, which lets a local table act as a proxy for a table stored on that remote server.

CREATE SERVER server_name
  FOREIGN DATA WRAPPER wrapper_name
  OPTIONS (option [, option] ...)

option:
  { HOST character-literal
  | DATABASE character-literal
  | USER character-literal
  | PASSWORD character-literal
  | SOCKET character-literal
  | OWNER character-literal
  | PORT numeric-literal }

ALTER SERVER server_name
  OPTIONS (option [, option] ...)

DROP SERVER [IF EXISTS] server_name

Currently mysql is the only supported wrapper_name. Once a server is defined, a table can be created with ENGINE=FEDERATED CONNECTION='server_name' so its rows are read from and written to the corresponding table on the remote server rather than stored locally. See the worked example.


INSTALL/UNINSTALL PLUGIN, COMPONENT

INSTALL PLUGIN plugin_name SONAME 'shared_library_name'
UNINSTALL PLUGIN plugin_name

The shared library must be located in the plugin directory, as indicated by the plugin_dir system variable. Plugins extend the server with storage engines, full-text parsers (see Indexes for the ngram and MeCab full-text parser plugins), authentication methods, and similar functionality. Refer to the official MySQL documentation for the plugins bundled with a given server distribution.


INSTALL COMPONENT component_name [, component_name ...]
  [SET variable = expr [, variable = expr] ...]

variable:
  {GLOBAL | @@GLOBAL.} [component_prefix.]system_var_name
  | {PERSIST | @@PERSIST.} [component_prefix.]system_var_name

UNINSTALL COMPONENT component_name [, component_name ...]

Installs one or more components, which become active immediately. A component provides services available to the server and to other components, and is named with a URN beginning file:// that gives the base name of the library file implementing it, located in the directory named by the plugin_dir system variable. Components are the newer, more modular successor to the plugin architecture and are the mechanism used, for example, to load the server-side components behind SQL roles auditing and the keyring.


Components are distinct from plugins at the implementation level, but both extend the running server without recompiling it, and both require their shared library to already exist in plugin_dir before installation. 

CREATE DATABASE IF NOT EXISTS shopdb
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_0900_ai_ci;

ALTER DATABASE shopdb
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE shopdb;
CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(100));

DROP DATABASE IF EXISTS shopdb;

-- run on the remote server first:
--   CREATE DATABASE test;
--   CREATE TABLE t (s1 INT) ENGINE=InnoDB;

CREATE SERVER s
    FOREIGN DATA WRAPPER mysql
    OPTIONS (
        USER 'Remote',
        HOST '192.168.1.106',
        DATABASE 'test');

CREATE TABLE t (s1 INT)
    ENGINE=FEDERATED
    CONNECTION='s';

-- rows inserted into t are stored on the remote server's test.t table
INSERT INTO t VALUES (1),(2),(3);
SELECT * FROM t;

s1
1
2
3

ALTER SERVER s
    OPTIONS (USER 'ReadOnlyRemote');

DROP SERVER IF EXISTS s;

-- plugins: shared library must already exist in plugin_dir
INSTALL PLUGIN mecab SONAME 'libpluginmecab.so';
UNINSTALL PLUGIN mecab;

-- components: named by a file:// URN, library also in plugin_dir
INSTALL COMPONENT 'file://component1', 'file://component2';

INSTALL COMPONENT 'file://component1', 'file://component2'
    SET GLOBAL component1.var1 = 12 + 3, PERSIST component2.var2 = 'strings';

UNINSTALL COMPONENT 'file://component1', 'file://component2';