Deploy MariaDB Enterprise Server 10.5 with Xpand Plugin on Ubuntu 20.04 LTS

These instructions detail the deployment of MariaDB Enterprise Server 10.5 on Ubuntu 20.04 LTS with the Xpand plugin.

MariaDB Enterprise Server deployments with the Xpand plugin are part of MariaDB Xpand's separate deployment type. These deployments are suited for a transactional or OLTP workload that requires distributed SQL, high availability (HA), fault tolerance, and horizontal scale-out.

Note

MariaDB Xpand is a beta release.

Xpand Deployment Types

MariaDB Enterprise Server instances with the Xpand Plugin are a component of the separate MariaDB Xpand deployment type. In a separate MariaDB Xpand deployment, Xpand Instance back-ends run on separate hosts from the MariaDB Enterprise Server front-ends.

However, the recommended deployment type for most users is a combined MariaDB Xpand deployment. In a combined MariaDB Xpand deployment, Xpand Nodes function both as Xpand Instance back-ends and as MariaDB Enterprise Server front-ends.

See the following resources for how to deploy an Xpand Node in a combined MariaDB Xpand deployment instead:

Dependencies

These instructions assume that one or more Xpand Instances will also be deployed.

See the following resources for how to deploy an Xpand Instance:

MariaDB Platform Components

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

Component

Description

MariaDB Enterprise Server 10.5

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

MariaDB Enterprise Server Components

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

Component

Description

Xpand

  • It is a storage engine that performs distributed SQL.

  • It uses a shared-nothing architecture.

  • It provides high availability.

  • It is fault tolerant.

  • It provides horizontal read and write scale-out.

  • It is transactional.

Term Definitions

Term

Definition

MariaDB Enterprise Server (front-end)

  • MariaDB Enterprise Server with Xpand plugin.

  • Receives queries from clients, applications, or MariaDB MaxScale.

  • Uses Xpand Instances as back-end storage for Xpand tables.

Xpand Instance (back-end)

  • MariaDB Xpand back-end instance.

  • Stores and serves data for Xpand tables that were created on MariaDB Enterprise Server front-ends.

  • Connects to other Xpand instances to distribute data slices and maintain replicas.

Xpand Node

  • This is the recommended server type for most users.

  • In a combined deployment type, it acts both as a MariaDB Enterprise Server front-end and as a MariaDB Xpand Instance back-end.

Installation

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

Install via APT (Debian/Ubuntu)

  1. Retrieve your Customer Download Token at https://customers.mariadb.com/downloads/token/ and substitute for customer_download_token in the following directions.

  2. Configure the APT package repository.

    To configure APT package repositories:

    $ sudo apt install wget
    
    $ wget https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup
    
    $ echo "eeebe9e08dffb8a4e820cc0f673afe437621060129169ea3db0790eb649dbe9b  mariadb_es_repo_setup" \
        | sha256sum -c -
    
    $ chmod +x mariadb_es_repo_setup
    
    $ sudo ./mariadb_es_repo_setup --token="customer_download_token" --apply \
       --mariadb-server-version="10.5"
    
    $ sudo apt update
    
  3. Install MariaDB Enterprise Server and package dependencies:

    $ sudo apt install mariadb-server mariadb-backup mariadb-plugin-xpand
    
  4. Configure MariaDB.

    Installation only loads MariaDB Enterprise Server to the system. MariaDB Enterprise 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 and Loading Xpand

In order to use the Xpand plugin in MariaDB Enterprise Server 10.5, you need to perform some additional configuration.

MariaDB Enterprise Server ships configured to require all loaded plugins to have stable maturity. The MariaDB Xpand plugin is currently beta and is not loaded by default. To use it, you need to configure the Server to accept beta plugins and configure it to load Xpand on start.

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

    System Variable/Option

    Description

    plugin_maturity

    Sets the minimum acceptable maturity of plugins to load into the Server. Must be set to beta to load the Xpand plugin.

    plugin_load_add

    Adds a plugin library to the list to load during Server start.

    xpand_hosts

    List of IP addresses for Xpand nodes.

    xpand_port

    Port to use when connecting to Xpand nodes.

    xpand_username

    Name of the user to use when connecting to Xpand nodes.

    xpand_password

    Password for the user connecting to the Xpand nodes.

  2. Choose a configuration file in which to configure your system variables and options.

    It 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].

    [mariadb]
    plugin_maturity = beta
    plugin_load_add = ha_xpand.so
    xpand_hosts     = 192.0.2.1,192.0.2.1,192.0.2.3
    xpand_username  = xpand_user
    xpand_password  = xpand_user_passwd
    

    Note that:

    • The plugin_maturity system variable must be set to beta to load Xpand. This means that other plugins with beta maturity can be installed as well.

    • The xpand_username and xpand_password system variables must correspond to the user account created on the Xpand node.

    • The xpand_password system variable is visible to all users.

Starting the Server

MariaDB Enterprise 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 20.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

Testing

When MariaDB Enterprise Server is up and running on your system, you should test that it is working and there weren't any issues during startup.

Check Plugin Status

In order to use the Xpand storage engine, you need need the plugin installed and active on MariaDB Enterprise Server.

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

    $ sudo mariadb
    
  2. Check that Xpand is available using the SHOW PLUGINS SONAME statement:

    SHOW PLUGINS SONAME LIKE '%xpand%';
    
    +-------+--------+----------------+-------------+---------+
    | Name  | Status | Type           | Library     | License |
    +-------+--------+----------------+-------------+---------+
    | XPAND | ACTIVE | STORAGE ENGINE | ha_xpand.so | GPL     |
    +-------+--------+----------------+-------------+---------+
    

If the SHOW PLUGINS SONAME statement shows a status of ACTIVE, the Xpand storage engine is available.

Using the Xpand Storage Engine

Using the Xpand storage engine requires that you specify the ENGINE table option for the table.

  1. Connect to MariaDB Enterprise Server:

    $ sudo mariadb
    
  2. Use CREATE DATABASE and CREATE TABLE statements to create a test schema for Xpand:

    CREATE DATABASE IF NOT EXISTS test;
    
    CREATE TABLE test.contacts (
       id INT PRIMARY KEY AUTO_INCREMENT,
       first_name VARCHAR(50),
       last_name VARCHAR(50),
       email VARCHAR(100)
    ) ENGINE = Xpand;
    
  3. Use an INSERT statement to add data to the table:

    INSERT INTO test.t1 (first_name, last_name, email) VALUES
       ("Walker", "Percy",  "w.percy@example.com"),
       ("Flannery", "O'Connor", "flan.oconnor@example.edu");
    

The Xpand storage engine connects to the Xpand Instances and distributes the data in slices across the deployment, automatically replicating these slices to ensure fault tolerance.