Deploy Xpand Storage Engine Topology 5.3

These instructions detail the deployment of MariaDB Xpand 5.3 with MariaDB Enterprise Server 10.5 on CentOS 7 and RHEL 7.

MariaDB Xpand is deployed by installing and configuring MariaDB Enterprise Server, the Xpand storage engine storage engine, and the Xpand service on each host. These deployments are suited for a transactional or OLTP workload that requires distributed SQL, high availability (HA), fault tolerance, and horizontal scale-out.

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 5.3

  • 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

System

One running operating system. A system typically answers one or more IP addresses.

Service

One set of processes running on a system. A service typically answers one or more TCP port or UNIX socket. A service is typically started and stopped using systemd.

Process

One or more pid running on a system, associated with a service. A service may include one or more process. A process typically serves one or more TCP connection or socket connection.

Node

One service, or multiple services working in concert, on a system.

Deployment

One service, or more than one service connected by a multi-node technology. A deployment may operate on one system or multiple. A deployment may contain one node or multiple.

Plugin

A component of MariaDB Enterprise Server which may be included in the server function optionally, either at runtime or compile-time, and which adheres to an established plugin architecture within Enterprise Server.

Multi-Node

The case where multiple nodes exist in a deployment.

MariaDB Enterprise Server service

The MariaDB Enterprise Server service that runs on each node in a deployment.

MariaDB Xpand service

The MariaDB Xpand service that runs on each node in a deployment.

Scale-Out

In a MariaDB Xpand deployment, the nodes distribute data between themselves in a shared-nothing architecture. One of the major benefits of MariaDB Xpand is that you can scale out your deployment by adding new nodes. When you scale out your deployment, it can handle an increased load for database operations, as well as increased query processing and storage capability.

For information on how to add nodes to the deployment, see Adding Nodes to Xpand.

Deployment Requirements

Before you start the MariaDB Xpand deployment process, there are some important details to review.

Platforms

MariaDB Xpand currently supports the following platforms:

  • CentOS 7

  • Red Hat Enterprise Linux 7

Number of Nodes

A MariaDB Xpand deployment should have 3 or more nodes.

File System Requirements

MariaDB Xpand does not support all file systems. MariaDB Xpand currently requires ext4 to be used for the data directory located at /data/clustrix.

EC2 Requirements

MariaDB Xpand has some extra requirements for deployments in Amazon EC2.

MariaDB Xpand recommends using one of the following machine types:

  • C3

  • I2

  • I3

  • R3

  • X1

  • X1E

MariaDB Xpand will raise a warning if you are using one of the following machine types, which are not a good fit for Xpand:

  • C4

  • D2

  • F1

  • G2

  • G3

  • M3

  • M4

  • P2

  • R4

Note

The xpdnode_install.py installation wizard will ask you if you would like to bypass the warning when you follow the directions in the Xpand Installation section.

MariaDB Xpand will raise an error if you are using one of the following machine types, which are not optimized properly for MariaDB Xpand:

  • T2

Service Requirements

MariaDB Xpand runs two different services on each node:

  • The MariaDB Enterprise Server 10.5 service provides an interface for client and application connections. It can also contain tables that use non-Xpand storage engines.

  • The MariaDB Xpand 5.3 service handles storage for Xpand tables, connections to other nodes and data distribution between them.

Note that Xpand does not support mismatched versions. Using MariaDB Enterprise Server 10.5 with a MariaDB Xpand service other than 5.3 can have unexpected results.

Each service has its own connection socket, port, and data directory:

Enterprise Server

Xpand

Data Directory

/var/lib/mysql

/data/clustrix

Socket

/run/mysqld/mysqld.sock

/data/clustrix/mysql.sock

Default Port

3306

3306 (see note below)

User

mysql

xpand

Default Binary Directory

/usr/bin

/opt/clustrix/bin

Note

Each service attempts to use the same port by default. To prevent that, the xpdnode_install.py installation wizard can configure the MariaDB Xpand service to use a non-default port if you provide the --mysql-port option when you follow the directions in the Xpand Installation section.

User Account Requirements

MariaDB Xpand relies on several OS user accounts, but they are automatically created during the installation process.

The MariaDB Enterprise Server service uses the following OS user accounts:

  • The mysql user runs the server process, and it owns the server's files.

Note

This user can be changed by configuring the user option.

The MariaDB Xpand service uses the following OS user accounts:

  • The xpand user runs the MariaDB Xpand service.

  • The xpandm user is intended for administrative tasks.

Note

The xpdnode_install.py installation wizard can configure MariaDB Xpand to use non-default users when you follow the directions in the Xpand Installation section.

System Configuration

MariaDB Xpand requires some system-level configuration:

  1. Set SELinux to permissive mode.

  2. Stop the firewall service.

SELinux

SELinux must be set to permissive mode, so that it does not block network communication between hosts.

Set SELinux to permissive mode by performing the following procedure on each host:

  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 Considerations

It is recommended to stop the system's firewall service on each host during installation to avoid confusion and potential problems. The specific steps to stop the firewall will depend on the platform.

If you are on a cloud platform, such as AWS, you may need to modify the security group or firewall rules for the cloud instance.

Stop firewalld

The firewalld service must be disabled, so that it does not block network communication between hosts.

Disable firewalld by executing the following commands on each host:

$ sudo systemctl stop firewalld
$ sudo systemctl disable firewalld

Configure the AWS Security Group

If your MariaDB Xpand deployment is in Amazon Web Services, you will need to modify the deployment's security group prior to installation.

MariaDB Xpand requires all internal communications to be open between hosts. Therefore, the security group should allow all protocols and all ports to be open between the hosts in the deployment.

Xpand Installation

MariaDB Corporation provides YUM (RHEL/CentOS) package repositories for MariaDB Enterprise Server and the Xpand storage engine plugin, and a TAR file for installing the MariaDB Xpand service.

Install Xpand on RHEL/CentOS

Perform the following steps on each node to install MariaDB Xpand:

  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.

    Installable version of MariaDB Enterprise Server with the Xpand plugin is 10.5. Pass the version to install using the --mariadb-server-version flag to mariadb_es_repo_setup. The following directions reference 10.5.

    To configure YUM package repositories:

    $ sudo yum install bzip2 xz wget screen ntp ntpdate vim mdadm
    
    $ wget https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup
    
    $ echo "ec08f8ede524f568b3766795ad8ca1a0d0ac4db355a18c3d85681d7f9c0f8c09  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. When using Red Hat Enterprise Linux, edit the repository file:

    $ sudo yum-config-manager --enable rhui-REGION-rhel-server-optional
    
  4. Contact MariaDB Sales for the Xpand service package and upload it to your server.

  5. Untar the Xpand service package:

    $ tar xvf xpand-5.3.16.el7.tar.bz2
    
    $ cd xpand-5.3.16.el7
    
  6. Run the xpdnode_install.py installation wizard:

    $ sudo ./xpdnode_install.py --wizard --mysql-port 5109 --unix-socket /data/clustrix/mysql.sock
    

    By default, the MariaDB Enterprise Server service and the MariaDB Xpand service both attempt to use port 3306 for client connections. To avoid a port conflict, a non-default port must be specified for MariaDB Xpand using the --mysql-port option..

    The port specified to this command will be used later in the Configuring MariaDB for Xpand section to configure the xpand_port system variable.

    This command also opens the wizard, allowing you to perform additional configuration.

    Note

    If your deployment does not meet some of the Deployment Requirements, then the xpdnode_install.py installation wizard may fail.

  7. Perform any additional configuration you would like to use through the wizard. Then, accept the Terms of Use to configure and start the Xpand service:

     0 - Memory to use for MariaDB Xpand, in MiB: 3367 MiB
     1 - CPU cores to use for MariaDB Xpand: All
     2 - Database Storage Path: /data/clustrix
     3 - Storage space to pre-allocate to MariaDB Xpand: 29 GiB
     4 - Database Logs Path: /data/clustrix/log
     5 - MySQL Protocol Unix Socket: /var/lib/mysql/mysql.sock
     6 - Database Listen Address (Front-End IP): 0.0.0.0 (Listen on all available interfaces)
     7 - Database MySQL TCP Port: 5109
     8 - Private (Back-End) IP: 10.0.2.15/255.255.255.0
     9 - Back End Network TCP/UDP Port: 24378
    10 - Health Monitor Network TCP Port: 3581
    11 - OS User: xpand (user will be created)
    12 - Management User: xpandm (user will be created)
    13 - Reduce TCP retry count: Yes
    14 - ClxDBI Install Path: /opt/clustrix/clxdbi
    15 - ClxDBI Listen Address: 0.0.0.0 (Listen on all available interfaces)
    16 - ClxDBI Listen TCP Port: 7888
    17 - ClxDBI Log Path: /data/clustrix/log/clxdbi
    18 - ClxDBI PID File Path: /opt/clustrix/var/run
    19 - ClxGUI Install Path: /opt/clustrix/clxgui
    20 - ClxGUI Data Cache Path: /opt/clustrix/var/cache
    21 - ClxGUI Listen Address: 0.0.0.0 (Listen on all available interfaces)
    22 - ClxGUI Listen TCP Port: 8080
    23 - ClxGUI Log Path: /data/clustrix/log/clxgui
    24 - ClxGUI PID File Path: /opt/clustrix/var/run
    25 - Display MariaDB Xpand Terms of Use
     Q - Quit
     H - Help
    
    Select item to change or enter 'Yes' to accept Terms of Use and continue: Yes
    

    Default memory and storage settings are auto-detected based on what is available to the host.

  8. When the xpdnode_install.py installation wizard is complete, it will output the local IP address used by the MariaDB Xpand service. The IP address of each host will be used later in the Configuring MariaDB for Xpand section to configure the xpand_hosts system variable. If the host has multiple IP addresses, then take note of the specific IP address that is being used.

    The output looks like this:

    *** This Node's IP (Needed later during cluster configuration): 192.0.2.1
    
  9. Install MariaDB Enterprise Server and the MariaDB Xpand storage engine plugin using YUM:

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

Xpand Preparation

Some steps need to be performed to prepare the MariaDB Xpand service for use.

Check Xpand Service

The next steps in the process require the MariaDB Xpand service to be running.

Check that the MariaDB Xpand service is running by executing the clx status command on each node:

$ sudo /opt/clustrix/bin/clx status

If it is not running, then start it by executing the clx dbstart command on each node:

$ sudo /opt/clustrix/bin/clx dbstart

The MariaDB Xpand service can be accessed through the MariaDB Client by specifying --socket=/data/clustrix/mysql.sock.

Set Root Password

MariaDB Xpand does not set a password for the root@127.0.0.1 user account by default. A password should be set for this user account, so that users with access to the host need an additional password to also gain access to MariaDB Xpand.

Set a password for the the root@127.0.0.1 user account by performing the following procedure on each node:

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

    $ mariadb --socket /data/clustrix/mysql.sock -u root
    
  2. Use the SET PASSWORD statement to password protect the account:

    SET PASSWORD FOR root@127.0.0.1 = PASSWORD("xpand_root_passwd");
    

License Configuration

MariaDB Xpand requires that you set the license key for your deployment. This license is a JSON-formatted string, which can be obtained from MariaDB Corporation and need only be set on one node in your deployment.

To set the license, perform the following procedure on one of the nodes:

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

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

    SET GLOBAL license = 'license_json';
    

Once the license key is set on one node, you can add other nodes to the deployment.

Set Path for Xpand Binaries

MariaDB Xpand installs Xpand-related commands to a custom binary path, as specified in the Service Requirements section. If you want to be able to execute these commands as the Xpand administrator account without fully qualifying the path, then you can add the Xpand binary path to the $PATH environment variable.

For example, you can add the Xpand binary path to the $PATH environment variable for the default xpandm account by doing the following on each node:

  1. Edit the .bash_profile file:

    $ sudo vim /home/xpandm/.bash_profile
    
  2. At the very end of the file, add the Xpand binary path to the $PATH variable:

    # Append Xpand Bin to $PATH
    export PATH=$PATH:/opt/clustrix/bin
    

    After adding this text, save and close the file.

  3. Set the owner of the .bash_profile file:

    $ sudo chown xpandm:xpandm /home/xpandm/.bash_profile
    
  4. The next time you log in as the xpandm account, your shell will have the Xpand binary path in its $PATH variable.

  5. Add the Xpand binary path to the $PATH variable for your current session:

    $ export PATH=$PATH:/opt/clustrix/bin
    

Create Xpand User Accounts

Multiple user accounts need to be created on each MariaDB Xpand node.

Create User Account for Enterprise Server

MariaDB Enterprise Server requires a user account with the appropriate privileges to communicate with MariaDB Xpand. The user account should have a password. On each node, the MariaDB Enterprise Server service connects to its local MariaDB Xpand service, so the user account must be able to connect from the node's local IP address.

Create a user account for Enterprise Server on each MariaDB Xpand node:

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

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

    CREATE USER xpand@192.0.2.1 IDENTIFIED BY 'xpand_user_passwd';
    

    Note

    Replace the IP address with the local IP address of the server.

  3. Grant the user account the proper privileges with the GRANT statement:

    GRANT ALL ON *.* TO xpand@192.0.2.1;
    

These credentials will be used later in the Configuring MariaDB for Xpand section to configure the xpand_username and xpand_password system variables.

Create User Account for Management User

As specified in the User Account Requirements section, MariaDB Xpand creates a management user account, which is xpandm by default. The management user requires a user account with the appropriate privileges to communicate with MariaDB Xpand. The user account should have a password. On each node, the management user connects to its local MariaDB Xpand service through the local Unix socket file.

Create the user account for the management user on each MariaDB Xpand node:

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

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

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

    GRANT ALL ON *.* TO xpandm@localhost;
    

Xpand Configuration

MariaDB Xpand requires configuration after it is installed. The configuration file location depends on your operating system.

Enterprise Server 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 RHEL and CentOS, 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 MariaDB for Xpand

MariaDB Enterprise Server 10.5 communicates with MariaDB Xpand using the Xpand storage engine. You need to perform some additional configuration before they will communicate.

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

    Mandatory system variables and options for MariaDB Xpand include:

    System Variable/Option

    Description

    plugin_load_add

    Set this to ha_xpand.so, so that the Xpand storage engine will be loaded.

    xpand_hosts

    Set this to the local IP address, so that the node's local MariaDB Enterprise Server service communicates with the local MariaDB Xpand service.

    xpand_port

    Set this to the port provided to the xpdnode_install.py installation script in the Xpand Installation section.

    xpand_username

    Set this to the user name of the account created in the Create User Account for Enterprise Server section.

    xpand_password

    Set this to the password of the account created in the Create User Account for Enterprise Server section.

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

    We recommend not making custom changes to one of the bundled configuration files. Instead, 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, 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 and CentOS, 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_load_add = ha_xpand.so
    xpand_hosts     = 192.0.2.1
    xpand_username  = xpand
    xpand_password  = xpand_user_passwd
    xpand_port      = 5109
    

    Note that:

Start the Enterprise Server Process

The Enterprise Server process can be started using the systemctl command. In case the process was started during the installation process, use the restart command to ensure that the processes pick up the new configuration.

Start the MariaDB Enterprise Server process and configure it to start automatically by executing the following commands on all MariaDB Xpand nodes:

$ sudo systemctl restart mariadb
$ sudo systemctl enable mariadb

Add Nodes to Xpand

MariaDB Xpand nodes need to be configured to act as a single MariaDB Xpand deployment.

Multiple steps are required to add nodes to the MariaDB Xpand deployment:

  1. Add the nodes with ALTER CLUSTER.

  2. Configure passwordless SSH between nodes.

  3. Check the deployment status.

Add Nodes with ALTER CLUSTER

To configure the nodes to act as a single MariaDB Xpand deployment, you need to connect to the MariaDB Xpand service on the node that was used to configure the license in the License Configuration section. After that, you can add the other nodes to the MariaDB Xpand deployment. The MariaDB Xpand service supports a ALTER CLUSTER statement, which can be used to add nodes using ALTER CLUSTER ADD.

Perform the following procedure on the node that was used to configure the license in the License Configuration section:

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

    $ sudo mariadb --socket /data/clustrix/mysql.sock
    
  2. Execute an ALTER CLUSTER statement and provide the IP addresses of each MariaDB Xpand node that you want to add to the deployment:

    ALTER CLUSTER ADD '192.0.2.11', '192.0.2.12';
    

    The MariaDB Xpand node performs a short group change. The node then connects to each of the new nodes and configures them to receive connections. The rebalancer process then redistributes data and replicas through the expanded deployment.

    When the operation completes, exit the MariaDB Client session.

SSH Configuration

As specified in the User Account Requirements section, MariaDB Xpand requires multiple OS user accounts. The xpand and xpandm user accounts should be configured to allow passwordless SSH between all nodes.

Configure Passwordless SSH Automatically

If your nodes support password authentication for SSH, then a password can be used to configure passwordless SSH.

To configure passwordless SSH automatically:

  1. On each node, confirm that password authentication is enabled for SSH:

    $ sudo grep -i -E "^PasswordAuthentication" /etc/ssh/sshd_config
    PasswordAuthentication yes
    

    If the configuration file sets this to "no", then password authentication is disabled for your node's SSH service. In that case, you will have to configure passwordless SSH manually.

  2. On each node, set the password for the user accounts:

    $ sudo passwd xpand
    $ sudo passwd xpandm
    
  3. Once the users have the same password on each MariaDB Xpand node, run the clx pubkeyinstall commands on any MariaDB Xpand node to configure the public keys:

    $ sudo /opt/clustrix/bin/clx pubkeyinstall -l xpand
    $ sudo /opt/clustrix/bin/clx pubkeyinstall -l xpandm
    

    The command will prompt for the user account passwords.

    The command will then connect to each node in the deployment to configure passwordless SSH.

  4. Test the configuration by using SSH to execute a shell command on each node as the xpandm management user. The shell command can be executed on each node using the clx cmd command:

    $ sudo -u xpandm /opt/clustrix/bin/clx cmd date
    
    --------------------------------------------------------------------------------
    {nid: 1, hostname: xpand-node1, ip: 192.0.2.15}
    Fri Nov 20 22:47:14 UTC 2020
    
    --------------------------------------------------------------------------------
    {nid: 2, hostname: xpand-node2, ip: 192.0.2.30}
    Fri Nov 20 22:47:14 UTC 2020
    
    --------------------------------------------------------------------------------
    {nid: 2, hostname: xpand-node3, ip: 192.0.2.60}
    Fri Nov 20 22:47:14 UTC 2020
    

Configure Passwordless SSH Manually

If your nodes do not support password authentication for SSH, then a password can not be used to configure passwordless SSH, so it will have to be configured manually.

To configure passwordless SSH automatically:

  1. On each node, create an SSH key pair:

    $ ssh-keygen -t rsa
    
  2. On each node, copy the public key from ~/.ssh/id_rsa.pub:

    $ cat ~/.ssh/id_rsa.pub
    ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDAc1NxsO7ps48gI2k6FtwLPx9d2TSgOb822BaGW76KA0QmR60YprQ9ShLgdfZER3lT4Z2JDFuJkaE+jSRf+zZ2OIaybq4kiEiyLctbnl0w8VJn1NWm7AOKUhBaBYd6uWfL7pIJpcEllqvqa1YQ2z1LGpCYjSgA8mO+64qOpcqPjoznwrad/Pd1cW+QKLdy5xhJs10FnQu3b2KFaXFrzjtuIIdaa4oGXWEJ7naKd06+M5UjFmzbtqqyZHuDH8GWhVXu7jdN/H08pm21Y0iIH8ozzn4ciyABrYqQSQOUnOtU6pZ5CKYLvj4c3deOUPoLxxXr3tUitXAYVk81q0FxYQm5 xpandm@xpand-node1
    
  3. On each node, add all of the public keys to ~/.ssh/authorized_keys:

    ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDAc1NxsO7ps48gI2k6FtwLPx9d2TSgOb822BaGW76KA0QmR60YprQ9ShLgdfZER3lT4Z2JDFuJkaE+jSRf+zZ2OIaybq4kiEiyLctbnl0w8VJn1NWm7AOKUhBaBYd6uWfL7pIJpcEllqvqa1YQ2z1LGpCYjSgA8mO+64qOpcqPjoznwrad/Pd1cW+QKLdy5xhJs10FnQu3b2KFaXFrzjtuIIdaa4oGXWEJ7naKd06+M5UjFmzbtqqyZHuDH8GWhVXu7jdN/H08pm21Y0iIH8ozzn4ciyABrYqQSQOUnOtU6pZ5CKYLvj4c3deOUPoLxxXr3tUitXAYVk81q0FxYQm5 xpandm@xpand-node1
    ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDeVTxnqSnXh0GhW3+jrErPuZbPXPsTDk92QEqPzumQ4+gMQBWz86lcdM3Rs9DnDlpJhqH8EhCdQ8WI77I0ZxSuU6JUxBrjaU0/wOFQc3L00wDKyCC5/RclVMO2lIWl21un4ZTnhSqtP9IukCCd2RB6C0uMArTs9ffNNlQ3AgO7sWnlEeiCixutq9wF92G+iRnGsHaAQzGIiUSWh2+xklb0zjAvl4qguKMfACxuGL0Y9HFcllBafVeJ7bVD7LOxVHsmromkAUPpwm9qmAcUQA0uMe9ljU0kSxszqM6PCFQykt9ez0M9iJFtlBqpBp30XiuU31zlYgcLtqUkwyatF3nH xpandm@xpand-node2
    ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDxT2E3aZWp8rqbM0VH3PS9MNahP3H51tHB9j0WtnP7D7f0D7kuiHQ6MR9Xh/xeMySsOzxGP7zjuLIYPR5EwBlAjZ8atgT+e83rZxMl1pAucbiYuMbBI4MKIjevUcJiiSbi1F2QvOpnS6ljvVut8eRAPvc6Vz7Gmhpf/MCAd/0C1ltjdpD1DojNOA+LmucsfcWaCq7+uP9yMlbWN1PZBUfEbUgX7DxZwanvM6O+3aNRIn4Se0gQIvqtkjl/m0EEa6ataYIV1sZig0dh9siC9BV2EiSMXYIFF0qiSqZmjSOJGfmIKsuqlJK8S4UVdD5J6+qTDvq4JVEoPPI9ifXqjg8/ xpandm@xpand-node3
    

    This should include the public key for the current node.

  4. On each node, fix the permissions of ~/.ssh/authorized_keys:

    chmod 0600 ~/.ssh/authorized_keys
    
  5. On each node, test passwordless SSH access to each other node:

    $ ssh 192.0.2.15
    
  6. Test the configuration by using SSH to execute a shell command on each node as the xpandm management user. The shell command can be executed on each node using the clx cmd command:

    $ sudo -u xpandm /opt/clustrix/bin/clx cmd date
    
    --------------------------------------------------------------------------------
    {nid: 1, hostname: xpand-node1, ip: 192.0.2.15}
    Fri Nov 20 22:47:14 UTC 2020
    
    --------------------------------------------------------------------------------
    {nid: 2, hostname: xpand-node2, ip: 192.0.2.30}
    Fri Nov 20 22:47:14 UTC 2020
    
    --------------------------------------------------------------------------------
    {nid: 2, hostname: xpand-node3, ip: 192.0.2.60}
    Fri Nov 20 22:47:14 UTC 2020
    

Check Deployment Status

Confirm that the nodes were added to the deployment by executing the clx status command on one or more of the nodes:

$ sudo -u xpandm /opt/clustrix/bin/clx status
Cluster Name:    example-xpand-nodes
Cluster Version: 5.3.16
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

Administration

MariaDB Xpand has two difference services. Each of those services needs to be administered.

On Red Hat Enterprise Linux and CentOS, the MariaDB Enterprise Server installation package does not start the service. You need to do this manually, using the systemctl command.

Enterprise Server Administration

MariaDB Enterprise Server uses systemctl to start and stop the server's service:

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

Xpand Administration

MariaDB Xpand uses the clx command to start and stop its service:

Operation

Command

Start

sudo -u xpandm /opt/clustrix/bin/clx dbstart

Stop

sudo -u xpandm /opt/clustrix/bin/clx dbstop

Restart

sudo -u xpandm /opt/clustrix/bin/clx dbrestart

Status

sudo -u xpandm /opt/clustrix/bin/clx status

The clx command can operate on all MariaDB Xpand nodes in the deployment.

The clx command can function for OS users that meet the following criteria:

The xpandm user account is usually used.

Testing

Local Connections

As specified in the Service Requirements section, each node in a MariaDB Xpand deployment has a MariaDB Enterprise Server service and a MariaDB Xpand service. Both services are accessible using MariaDB Client.

  • To connect to the MariaDB Enterprise Server service:

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

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

Check Xpand Status

Using the clx status command, check the status of the MariaDB Xpand deployment.

$ sudo -u xpandm /opt/clustrix/bin/clx status
Cluster Name:    example-xpand-nodes
Cluster Version: 5.3.16
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 node 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. On each node, connect to the MariaDB Enterprise Server service using MariaDB Client:

    $ sudo mariadb
    
  2. On each node, use the CREATE DATABASE statement to create the database:

    CREATE DATABASE IF NOT EXISTS test;
    

    Note

    MariaDB Xpand does not synchronize databases automatically. MariaDB Replication can synchronize databases and other objects. Without replication, the database needs to be created separately on each node.

  3. On a single node, use the CREATE TABLE statement to create the table:

    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;
    
  4. On a single node, use the INSERT statement to add data to the table:

    INSERT INTO test.contacts (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 MariaDB Xpand service and distributes the data in slices across the deployment, automatically replicating these slices to ensure fault tolerance.

  5. On each node, compare the output to ensure that they can access the Xpand data:

    SELECT * FROM test.contacts;
    
    +----+------------+-----------+--------------------------+
    | id | first_name | last_name | email                    |
    +----+------------+-----------+--------------------------+
    |  1 | Walker     | Percy     | w.percy@example.com      |
    |  2 | Flannery   | O'Connor  | flan.oconnor@example.edu |
    +----+------------+-----------+--------------------------+