Deploy a Spider Node with MariaDB Community Server 10.5 on Ubuntu 18.04 LTS

These instructions detail the deployment of MariaDB Community Server 10.5 on Ubuntu 18.04 LTS in a Spider Node configuration.

These instructions detail how to deploy a Spider Node, which can use the Spider storage engine to perform advanced operations on a remote database.

MariaDB Enterprise Components

These instructions detail the deployment of the following MariaDB Enterprise components:

Component

Description

MariaDB Community Server 10.5

  • It is a 100% Open Source modern SQL database.

MariaDB Community Server Components

These instructions detail the deployment of the following MariaDB Community Server components:

Component

Description

InnoDB

Spider

  • It supports sharding a table's data across multiples backend MariaDB servers

  • It supports federated access to a table stored on a separate MariaDB Server

  • It supports federated access to a table stored on a separate non-MariaDB Server using ODBC

  • It is transactional.

Term Definitions

Term

Definition

Data Node

A Data Node is a MariaDB Enterprise Server node that contains one or more Data Tables.

Data Table

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.

ODBC Driver

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.

Spider Node

A Spider Node is a MariaDB Enterprise Server node that contains one or more Spider Tables.

Spider Table

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.

Installation

MariaDB Corporation provides a APT package repository for Ubuntu 18.04 LTS.

Install on Debian/Ubuntu (APT)

  1. Configure the APT package repository.

    To configure APT package repositories:

    $ sudo apt install wget
    
    $ wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
    
    $ echo "fc84b8954141ed3c59ac7a1adfc8051c93171bae7ba34d7f9aeecd3b148f1527 mariadb_repo_setup" \
        | sha256sum -c -
    
    $ chmod +x mariadb_repo_setup
    
    $ sudo ./mariadb_repo_setup \
       --mariadb-server-version="mariadb-10.5"
    
    $ sudo apt update
    
  2. Install MariaDB Community Server and package dependencies:

    $ sudo apt install mariadb-server mariadb-backup mariadb-plugin-spider
    
  3. Configure MariaDB.

    Installation only loads MariaDB Community Server to the system. MariaDB Community Server requires configuration before the database server is ready for use.

Configuration

MariaDB Enterprise Server can be configured in the following ways:

  • System variables and options can be set in a configuration file (such as /etc/my.cnf). MariaDB Enterprise Server must be restarted to apply changes made to the configuration file.

  • System variables and options can be set on the command-line.

  • If a system variable supports dynamic changes, then it can be set on-the-fly using the SET statement.

Configuration Files

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:

  • /etc/mysql/my.cnf

  • /etc/mysql/mariadb.cnf

  • /etc/mysql/mariadb.conf.d/50-client.cnf

  • /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf

  • /etc/mysql/mariadb.conf.d/50-mysqld_safe.cnf

  • /etc/mysql/mariadb.conf.d/50-server.cnf

  • /etc/mysql/mariadb.conf.d/60-galera.cnf

  • /etc/mysql/mariadb.conf.d/mariadb-enterprise.cnf

  • /etc/mysql/mariadb.conf.d/xpand.cnf

And on Debian and Ubuntu, custom configuration files from the following directories are read by default:

  • /etc/mysql/conf.d/

  • /etc/mysql/mariadb.conf.d/

Configuring the Server

  1. Determine which system variables and options you need to configure.

    Useful system variables and options for MariaDB Enterprise Server include:

    System Variable/Option

    Description

    bind_address

    Sets the local TCP/IP address on which MariaDB Enterprise Server listens for incoming connections. When testing on a local system, bind the address to the local host at 127.0.0.1 to prevent network access.

    max_connections

    Sets the maximum number of simultaneous connections MariaDB Enterprise Server allows.

    thread_handling

    Sets how MariaDB Enterprise Server handles threads for client connections.

    log_error

    Sets the file name for the error log.

  2. 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 z-.

    • On Debian and Ubuntu, a good custom configuration file would be: /etc/mysql/mariadb.conf.d/z-custom-my.cnf

  3. Set your system variables and options in the configuration file:

    These need to be set in a group that will be read by mariadbd, such as [mariadb] or [server].

    For example:

    [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.

Ubuntu 18.04 LTS uses systemd. You can manage the Server process using the systemctl command:

Operation

Command

Start

sudo systemctl start mariadb

Stop

sudo systemctl stop mariadb

Restart

sudo systemctl restart mariadb

Enable during startup

sudo systemctl enable mariadb

Disable during startup

sudo systemctl disable mariadb

Status

sudo systemctl status mariadb

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 an INSTALL SONAME statement.

  1. Install the Spider storage engine plugin using the INSTALL SONAME statement:

    INSTALL SONAME 'ha_spider';
    

    This statement adds and loads the ha_spider.so shared library to the Server. The Spider storage engine is now available and it will automatically load when the Server restarts.

Testing

Checking Spider Availability

  1. Connect to the Server using the MariaDB Client using the root@localhost user account:

    $ sudo mariadb
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 38
    Server version: 10.5.12-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)]>
    
  2. Execute SHOW PLUGINS to check Spider availability:

    SHOW PLUGINS;
    
    +------------------+--------+--------------------+--------------+---------+
    | 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.so library are active on the Server.

Next steps: