Deploy a Spider Node with MariaDB Community Server 10.3 on Debian 9
This procedure provides instructions detailing the deployment of MariaDB Community Server 10.3 as a Spider Node. If you are using MariaDB Enterprise Server 10.3, see "Deploy Enterprise Spider Sharded Topology" or "Deploy Enterprise Spider Federated Topology".
Spider provides support for advanced operations on a remote database.
MariaDB Community Server Components
These instructions detail the deployment of the following MariaDB Community Server components:
A Data Node is a MariaDB Enterprise Server node that contains one or more Data Tables.
A Data Table stores data for a Spider Table. When a Spider Table is queried, the Enterprise Spider storage engine uses the MariaDB foreign data wrapper to read from and write to the Data Table on a Data Node. The Data Table must be created on the Data Node with the same structure as the Spider Table. The Data Table must use a non-Spider storage engine, such as InnoDB or ColumnStore.
ODBC Data Source
An ODBC Data Source relies on an ODBC Driver and an ODBC Driver Manager to query an external data source.
An ODBC Driver is a library that integrates with a ODBC Driver Manager to query an external data source.
ODBC Driver Manager
An ODBC Driver Manager allows applications to use ODBC Drivers.
A Spider Node is a MariaDB Enterprise Server node that contains one or more Spider Tables.
A Spider Table is a virtual table that does not store data. When a Spider Table is queried, the Enterprise Spider storage engine uses foreign data wrappers to read from and write to Data Tables on Data Nodes or ODBC Data Sources.
MariaDB Corporation provides a APT package repository for Debian 9.
Install on Debian/Ubuntu (APT)
Configure the APT package repository.
To configure APT package repositories:
$ sudo apt install wget $ wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup $ echo "fd3f41eefff54ce144c932100f9e0f9b1d181e0edd86a6f6b8f2a0212100c32c mariadb_repo_setup" \ | sha256sum -c - $ chmod +x mariadb_repo_setup $ sudo ./mariadb_repo_setup \ --mariadb-server-version="mariadb-10.3" $ sudo apt update
Install MariaDB Community Server and package dependencies:
$ sudo apt install mariadb-server mariadb-backup mariadb-plugin-spider
Installation only loads MariaDB Community Server to the system. MariaDB Community Server requires configuration before the database server is ready for use.
MariaDB Community Server can be configured in the following ways:
If a system variable supports dynamic changes, then it can be set on-the-fly using the SET statement.
MariaDB's packages include several bundled configuration files. It is also possible to create custom configuration files.
On Debian and Ubuntu, MariaDB's packages bundle the following configuration files:
And on Debian and Ubuntu, custom configuration files from the following directories are read by default:
Configuring the Server
Useful system variables and options for MariaDB Community Server include:
Sets the local TCP/IP address on which MariaDB Community Server listens for incoming connections. When testing on a local system, bind the address to the local host at
127.0.0.1to prevent network access.
Sets the maximum number of simultaneous connections MariaDB Community Server allows.
Sets how MariaDB Community Server handles threads for client connections.
Sets the file name for the error log.
Choose a configuration file in which to configure your system variables and options.
It is not recommended to make custom changes to one of the bundled configuration files. Instead, it is recommended to create a custom configuration file in one of the included directories. Configuration files in included directories are read in alphabetical order. If you want your custom configuration file to override the bundled configuration files, then it is a good idea to prefix the custom configuration file's name with a string that will be sorted last, such as
On Debian and Ubuntu, a good custom configuration file would be:
Set your system variables and options in the configuration file:
These need to be set in a group that will be read by mysqld, such as
[mariadb] bind_address = 192.0.2.50 max_connection = 1000 log_error = mariadbd.err
Starting the Server
MariaDB Community Server includes configuration to start, stop, restart, enable/disable on boot, and check the status of the Server using the operating system default process management system.
Debian 9 uses systemd. You can manage the Server process using the
Enable during startup
Disable during startup
Load the Spider Plugin
In order to use MariaDB Community Server as a Spider Node, you need to configure the Server to use the plugin. This can be done using the
Install the Spider storage engine by executing the
install_spider.sqlscript using MariaDB Client:
To install the Spider storage engine on Linux:
$ mysql --user db_user --password \ < /usr/share/mysql/install_spider.sql
The script runs a series of SQL statements, configuring the Server to operate as a Spider Node.
After the script completes, the Spider storage engine is available.
Checking Spider Availability
Connect to the Server using the MariaDB Client using the
$ sudo mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 38 Server version: 10.3.31-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
Execute SHOW PLUGINS to check Spider availability:
+------------------+--------+--------------------+--------------+---------+ | Name | Status | Type | Library | License | +------------------+--------+--------------------+--------------+---------+ ... | SPIDER | ACTIVE | STORAGE ENGINE | ha_spider.so | GPL | | SPIDER_ALLOC_MEM | ACTIVE | INFORMATION SCHEMA | ha_spider.so | GPL | +------------------+--------+--------------------+--------------+---------+
This shows the plugins in the
ha_spider.solibrary are active on the Server.
Continue through the Spider deployment process.