Deploy MariaDB Xpand Node on CentOS 7

These instructions detail the deployment of MariaDB Xpand Node on CentOS Linux 7.

Xpand Node deployments are part of MariaDB Xpand's combined 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.

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 Xpand

  • It provides a storage back-end for Xpand tables on MariaDB Enterprise Server 10.5.

MariaDB Enterprise Server Components

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

Component

Description

InnoDB

  • It is a general purpose storage engine.

  • It is ACID-compliant.

  • It is performant.

Xpand

  • It provides distributed SQL.

  • It automatically distributes data and replicas to other Xpand Nodes.

  • It is highly available (HA) and fault tolerant.

  • It supports horizontal scale-out.

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.

System Configuration

Xpand Nodes require some system-level configurations to prevent SELinux and firewalld from blocking network communication.

SELinux

To set SELinux to permissive mode:

  1. Edit the SELinux configuration file:

    $ sudo vim /etc/selinux/config
    
  2. Change the SELINUX variable to permissive mode:

    SELINUX=permissive
    
  3. Then, set the current SELinux process to permissive mode:

    $ sudo setenforce permissive
    

Once this is done, you can check that SELinux is in permissive mode using the sestatus command:

$ sudo sestatus
SELinux status:                 enabled
SELinuxfs mount:                /sys/fs/selinux
SELinux root directory:         /etc/selinux
Loaded policy name:             targeted
Current mode:                   permissive
Mode from config file:          permissive
Policy MLS status:              enabled
Policy deny_unknown status:     allowed
Max kernel policy version:      31

Firewall Configuration:

Disable firewalld to prevent it from blocking Xpand Instance communication:

To disable firewalld:

  1. Stop firewalld and disable it at startup:

    $ sudo systemctl stop firewalld
    $ sudo systemctl disable firewalld
    

Installation

MariaDB Corporation provides YUM (RHEL/CentOS) package repositories for MariaDB Enterprise Server and a TAR file for installing the MariaDB Xpand back-end storage on CentOS Linux 7.

Note

MariaDB Enterprise Server and the back-end Xpand Instance both attempt to use port 3306 for client connections. To avoid a port conflict, specify a non-default port for the Xpand Instance.

Install on RHEL/CentOS

To install the Xpand Node:

  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 YUM package repository.

    To configure YUM package repositories:

    $ sudo yum install bzip2 xz wget screen ntp ntpdate vim htop mdadm
    
    $ 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"
    
  3. Contact MariaDB Sales for the Xpand back-end package and upload it to your server.

  4. Untar the Xpand back-end package:

    $ tar xvf xpand-5.3.11_rc.el7.tar.bz2
    
    $ cd xpand-5.3.11_rc.el7
    
  5. Run the installation script, specifying an alternate port:

    $ ./xpdnode_install.py --mysql-port 5001
    

    Note, the xpdnode_install.py script expects to find the Xpand data and log directories on separate drives. If you don't want to do this, use the --force option.

  6. Install MariaDB Enterprise Server and the MariaDB Xpand storage engine plugin:

    $ sudo yum install MariaDB-server MariaDB-backup MariaDB-xpand-engine
    
  7. Configure MariaDB Enterprise Server and MariaDB Xpand.

Configuration

Xpand Nodes require additional configuration for both MariaDB Enterprise Server and MariaDB Xpand, to ensure that the Server can access the Xpand Instances and that the Xpand Instances can connect to each other.

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.

The back-end Xpand Instance can be configured through MariaDB Client.

License Configuration

MariaDB Xpand requires that you set the license key for your deployment. This license is a JSON object, which can be obtained from MariaDB Corporation and need only be set on the node used to start the deployment.

To set the license:

  1. Use the MariaDB Client to connect to the Xpand process:

    $ mariadb --socket /data/clustrix/mysql.sock
    
  2. Use a SET statement to configure the license variable:

    SET GLOBAL license = 'license_json';
    

The license is now set for your deployment of MariaDB Xpand. You can now add additional nodes to the deployment.

Setting Path for Xpand Binaries

MariaDB Xpand provides a bin/ directory with Xpand related commands. For convenience, add the path to this directory to the $PATH environmental variable:

  1. Edit the .bash_profile file:

    $ vim ~/.bash_profile
    
  2. Add the bin/ directory to the $PATH variable:

    export PATH=$PATH:/opt/clustrix/bin
    
  3. Save the file, then source the .bash_profile file to update the variable:

    $ source ~/.bash_profile
    

NTP Configuration

MariaDB Xpand requires NTP to synchronize system clocks between the various Xpand Nodes.

To enable NTP:

  1. Start the NTP daemon and enable it at startup:

    $ sudo systemctl start ntpd
    $ sudo systemctl enable ntpd
    

Granting Privileges

MariaDB Enterprise Servers require privileges on Xpand Instances. For each Xpand Node you add, issue a GRANT statement for a user at that IP address or domain.

To grant privileges for a new Xpand Node:

  1. Connect to the Xpand Instance:

    $ sudo mariadb --socket /data/clustrix/mysql.sock
    
  2. Create the user account using the CREATE USER statement:

    CREATE USER xpand_user@192.0.2.1
       IDENTIFIED BY 'xpand_user_passwd';
    
  3. Grant the user account the proper privileges with the GRANT statement:

    GRANT ALL ON *.* TO xpand_user@192.0.2.1;
    
  4. In the MariaDB Enterprise Server configuration, set the xpand_username and xpand_password system variables to the values for the corresponding user account:

    [mariadb]
    xpand_username = xpand_user
    xpand_password = xpand_user_passwd
    

    hen the Server operates on an Xpand table, it now uses these credentials in connecting to the Xpand nodes.

Configuration Files

MariaDB's packages include several bundled configuration files. It is also possible to create custom configuration files.

On RHEL, CentOS, and SLES, MariaDB's packages bundle the following configuration files:

  • /etc/my.cnf

  • /etc/my.cnf.d/client.cnf

  • /etc/my.cnf.d/mariadb-enterprise.cnf

  • /etc/my.cnf.d/mysql-clients.cnf

  • /etc/my.cnf.d/server.cnf

  • /etc/my.cnf.d/xpand.cnf

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

  • /etc/my.cnf.d/

Configuring the Server and Loading Xpand

MariaDB Enterprise Server 10.5 connects to the Xpand Instances using the Xpand storage engine plugin. In order to use this plugin, 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. 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 RHEL, CentOS, and SLES, a good custom configuration file would be: /etc/my.cnf.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 in the previous section.

    • The xpand_password system variable is visible to all users.

Scaling out Xpand Nodes

Xpand Nodes distribute data between themselves in a shared-nothing architecture. By adding Xpand Nodes to your deployment, you can handle an increased load for database operations, as well as increased query processing and storage capability for your deployment.

Xpand Instances distribute data between themselves through a rebalancer process running in the background.

  • You can add nodes from any existing Xpand Instance in the deployment.

  • The first Xpand Instance is the deployment is the one in which you set the license key.

Xpand Nodes are added to the deployment using an ALTER CLUSTER statement on the Xpand Instance (not MariaDB Enterprise Server).

To add Xpand Nodes to the deployment:

  1. Use the MariaDB Client to the Xpand Instance:

    $ sudo mariadb --socket /data/clustrix/mysql.sock
    
  2. Issue an ALTER CLUSTER statement to add the nodes to the deployment:

    ALTER CLUSTER ADD '192.0.2.11', '192.0.2.12';
    

MariaDB Xpand connects to the other Xpand Nodes and configures them to receive connections. The rebalancer process redistributes data and replicas to include the new node in the deployment.

SSH Configuration

MariaDB Xpand installation configures two users to handle Xpand processes on the host:

User

Description

xpand

User for the database daemon, which is used in upgrades.

xpandm

User for management and monitoring functions.

When adding Xpand Nodes to your deployment, configure these users for password-less SSH connections.

To configure SSH:

  1. On each new Xpand node, set the password for each user:

    $ sudo passwd xpand
    $ sudo passwd xpandm
    
  2. Then, on any existing Xpand node, run clx to configure the public keys:

    $ sudo clx pubkeyinstall
    

clx requests the password for the users, then uses the list of Xpand nodes in the deployment configuration, connecting to each to apply the relevant SSH configurations.

Starting the Node

Xpand Node controls MariaDB Enterprise Server processes using systemd and the Xpand Instance using the clx command.

Starting the Server

CentOS Linux 7 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

Starting the Xpand Instance

MariaDB Xpand automatically starts the Xpand Instance during installation. To control it otherwise, use the clx command.

Operation

Command

Start

sudo clx dbstart

Stop

sudo clx dbstop

Restart

sudo clx dbrestart

Status

sudo clx status

Testing

Local Connections

Xpand Nodes have a front-end MariaDB Enterprise Server and a back-end Xpand Instance, both of which are accessible using the MariaDB Client.

When connecting from the local system, the client defaults to the Server regardless of --port and --host options. To connect to the Xpand Instance, you need to specify the --socket option.

  • To connect to MariaDB Enterprise Server:

    $ sudo mariadb
    
  • To connect to the MariaDB Xpand Instance:

    $ sudo mariadb --socket /data/clustrix/mysql.sock
    

Check Xpand Status

Using the clx command, check the status of the Xpand Instances in your deployment.

$ clx status
Cluster Name:    example-xpand-nodes
Cluster Version: 5.3.11_rc
Cluster Status:  OK
Cluster Size:    3 nodes - 6 CPUs per Node
Current Node:    xpand-node1 - nid 1

nid |   Hostname  | Status | IP Address | TPS |       Used      |  Total
----+-------------+--------+------------+-----+-----------------+--------
  1 | xpand-node1 |    OK  | 192.0.2.15 |   0 |   48.3M (0.02%) |  231.9G
  2 | xpand-node2 |    OK  | 192.0.2.30 |   0 |   46.3M (0.02%) |  231.9G
  3 | xpand-node3 |    OK  | 192.0.2.60 |   0 |   46.3M (0.02%) |  231.9G
----+-------------+--------+------------+-----+-----------------+--------
                                            0 |  140.9M (0.02%) |  695.6G

The clx command provides information on each Xpand Instance in the deployment, including their IP address, status, and data usage.

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 MariaDB Enterprise Server:

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