2 of 7

Galera Cluster

Overview

Software Version

Diagram

Features

Enterprise Server 10.4
Enterprise Server 10.5
Enterprise Server 10.6
Enterprise Server 11.4

Multi-Primary Cluster Powered by Galera for Transactional/OLTP Workloads

InnoDB Storage Engine
Highly available
Virtually synchronous, certification-based replication
Automated provisioning of new nodes (IST/SST)
Scales reads via MaxScale
Enterprise Server 10.3+, MariaDB Enterprise Cluster (powered by Galera), MaxScale 2.5+

This procedure describes the deployment of the Galera Cluster topology with MariaDB Enterprise Server 11.4 and MariaDB MaxScale 25.01.

MariaDB Enterprise Cluster is powered by Galera.

MariaDB Enterprise Cluster provides read scalability and fault tolerance through virtually synchronous multi-primary certification-based write-set replication (wsrep).

This procedure has 6 steps, which are executed in sequence.

MariaDB products can be deployed in many different topologies to suit specific use cases. Enterprise Cluster can be deployed on its own, or integrated with MariaDB Replication to integrate with other clusters or topologies.

This procedure represents basic product capability with 3 Enterprise Cluster nodes and 1 MaxScale node.

This page provides an overview of the topology, requirements, and deployment procedures.

Please read and understand this procedure before executing.

Procedure Steps

Step

Description

Step 1

Install MariaDB Enterprise Server

Step 2

Start and Configure MariaDB Enterprise Server

Step 3

Test MariaDB Enterprise Server

Step 4

Install MariaDB MaxScale

Step 5

Start and Configure MariaDB MaxScale

Step 6

Test MariaDB MaxScale

Support

Customers can obtain support by submitting a support case.

Components

The following components are deployed during this procedure:

Component

Function

MariaDB Enterprise Server

Modern SQL RDBMS with high availability, pluggable storage engines, hot online backups, and audit logging.

MariaDB MaxScale 25.01

Database proxy that extends the availability, scalability, and security of MariaDB Enterprise Servers

MariaDB Enterprise Server Components

Component

Description

Enterprise Cluster

MariaDB Enterprise Server leverages the Galera Enterprise 4 wsrep provider plugin

Provides virtually synchronous multi-primary replication for MariaDB Enterprise Server
All nodes can handle both reads and writes
Replicates write-sets to all other nodes in the cluster
Supports data-at-rest encryption of the write-set cache

InnoDB

General purpose storage engine
ACID-compliant
Performance
Required for Enterprise Cluster

MariaDB MaxScale Components

Component

Description

Galera Monitor

Tracks changes in the state of MariaDB Enterprise Servers operating as Enterprise Cluster nodes.

Listener

Listens for client connections to MaxScale, then passes them to the router service associated with the listener

Read Connection Router

Routes connections from the listener to any available Enterprise Cluster node

Read/Write Split Router

Routes read operations from the listener to any available Enterprise Cluster node, and routes write operations from the listener to a specific server that MaxScale uses as the primary server

Server Module

Connection configuration in MaxScale to an Enterprise Cluster node

Topology

MariaDB Enterprise Cluster topology provides read scalability through certification-based write-set replication (wsrep) that is multi-primary and virtually synchronous.

The Enterprise Cluster topology consists of:

1 or more MaxScale nodes
3 or more MariaDB Enterprise Servers (ES) configured as Enterprise Cluster nodes

The MaxScale nodes:

Monitor the health and availability of each Enterprise Cluster node using the Galera Monitor (galeramon)
Accept client and application connections
Route queries to the Enterprise Cluster nodes using the Read Connection (readconnroute) or the Read/Write Split (readwritesplit) routers.

The Enterprise Cluster nodes:

Receive queries from MaxScale
Store data locally using the InnoDB storage engine
Perform certification-based virtually synchronous replication to other Enterprise Cluster nodes
Provide State Snapshot Transfers (SST) to bring MariaDB Enterprise Server nodes into sync with the cluster

Requirements

These requirements are for the Galera Cluster topology when deployed with MariaDB Enterprise Server and MariaDB MaxScale 25.01.

Node Count
Operating System

Node Count

MaxScale nodes, 1 or more are required.
Enterprise Cluster nodes, 3 or more are required.

To avoid problems in establishing a quorum in the event of a network partition or outage, MariaDB recommends deploying an odd number of Enterprise Cluster nodes. When using multiple network switches, deploy across an odd number of switches, each with an odd number of nodes. When using multiple data centers, deploy across an odd number of data centers, each with an odd number of switches.

Operating System

In alignment to the enterprise lifecycle, the Galera Cluster topology with MariaDB Enterprise Server and MariaDB MaxScale 25.01 is provided for:

AlmaLinux 8 (x86_64, ARM64)
AlmaLinux 9 (x86_64, ARM64)
Debian 11 (x86_64, ARM64)
Debian 12 (x86_64, ARM64)
Red Hat Enterprise Linux 8 (x86_64, ARM64)
Red Hat Enterprise Linux 9 (x86_64, PPC64LE, ARM64)
Red Hat UBI 8 (x86_64, ARM64)
Rocky Linux 8 (x86_64, ARM64)
Rocky Linux 9 (x86_64, ARM64)
SUSE Linux Enterprise Server 15 (x86_64, ARM64)
Ubuntu 20.04 LTS (x86_64, ARM64)
Ubuntu 22.04 LTS (x86_64, ARM64)
Ubuntu 24.04 LTS (x86_64, ARM64)

Quick Reference

MariaDB Enterprise Server Configuration Management

Method

Description

Configuration File

Configuration files (such as /etc/my.cnf) can be used to set system-variables and options. The server must be restarted to apply changes made to configuration files.

Command-line

The server can be started with command-line options that set system-variables and options.

SQL

Users can set system-variables that support dynamic changes on-the-fly using the SET statement.

MariaDB Enterprise Server packages are configured to read configuration files from different paths, depending on the operating system. Making custom changes to Enterprise Server default configuration files is not recommended because custom changes may be overwritten by other default configuration files that are loaded later.

To ensure that your custom changes will be read last, create a custom configuration file with the z- prefix in one of the include directories.

Distribution

Example Configuration File Path

CentOS
Red Hat Enterprise Linux (RHEL)
SUSE Linux Enterprise Server (SLES)

/etc/my.cnf.d/z-custom-mariadb.cnf

Debian
Ubuntu

/etc/mysql/mariadb.conf.d/z-custom-mariadb.cnf

MariaDB Enterprise Server Service Management

The systemctl command is used to start and stop the MariaDB Enterprise Server service. The galera_new_cluster and galera_recovery scripts are used for Enterprise Cluster-specific operations.

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

Bootstrap a cluster node

sudo galera_new_cluster

Recover a cluster node's position

sudo galera_recovery

For additional information, see "Starting and Stopping MariaDB".

MariaDB Enterprise Server Logs

MariaDB Enterprise Server produces log data that can be helpful in problem diagnosis.

Log filenames and locations may be overridden in the server configuration. The default location of logs is the data directory. The data directory is specified by the datadir system variable.

Log

System Variable/Option

Default Filename

MariaDB Error Log

log_error

<hostname>.err

MariaDB Enterprise Audit Log

server_audit_file_path

server_audit.log

Slow Query Log

slow_query_log_file

<hostname>-slow.log

<hostname>.log

<hostname>-bin

MaxScale Configuration Management

MaxScale can be configured using several methods. These methods make use of MaxScale's REST API.

Method

Benefits

MaxCtrl

Command-line utility to perform administrative tasks through the REST API. See MaxCtrl Commands.

MaxGUI

MaxGUI is a graphical utility that can perform administrative tasks through the REST API.

REST API

The REST API can be used directly. For example, the curl utility could be used to make REST API calls from the command-line. Many programming languages also have libraries to interact with REST APIs.

The procedure on these pages configures MaxScale using MaxCtrl.

MaxScale Service Management

The systemctl command is used to start and stop the MaxScale service.>

Operation

Command

Start

sudo systemctl start maxscale

Stop

sudo systemctl stop maxscale

Restart

sudo systemctl restart maxscale

Enable during startup

sudo systemctl enable maxscale

Disable during startup

sudo systemctl disable maxscale

Status

sudo systemctl status maxscale

For additional information, see "Starting and Stopping MariaDB".

Next Step

Navigation in the procedure "Deploy Galera Cluster Topology":

Next: Step 1: Install MariaDB Enterprise Server

Step 1: Install MariaDB Enterprise Server

Overview

This page details step 1 of the 6-step procedure "Deploy Galera Cluster Topology".

This step installs MariaDB Enterprise Server.

MariaDB Enterprise Server installations support MariaDB Enterprise Cluster, powered by Galera. MariaDB Enterprise Cluster uses the Galera Enterprise 4 wsrep provider plugin.

MariaDB Enterprise Cluster requires an odd number of 3 or more nodes. Nodes must meet requirements.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Retrieve Download Token

MariaDB Corporation provides package repositories for CentOS / RHEL (YUM) and Debian / Ubuntu (APT). A download token is required to access the MariaDB Enterprise Repository.

Customer Download Tokens are customer-specific and are available through the MariaDB Customer Portal.

To retrieve the token for your account:

Navigate to
Log in.
Copy the Customer Download Token.

Substitute your token for CUSTOMER_DOWNLOAD_TOKEN when configuring the package repositories.

Set Up Repository

On each Enterprise ColumnStore node, install the prerequisites for downloading the software from the Web. Install on CentOS / RHEL (YUM):

$ sudo yum install curl

Install on Debian / Ubuntu (APT):

$ sudo apt install curl apt-transport-https

On each Enterprise ColumnStore node, configure package repositories and specify Enterprise Server:

$ curl -LsSO https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup

$ echo "4d483b4df193831a0101d3dfa7fb3e17411dda7fc06c31be4f9e089c325403c0  mariadb_es_repo_setup" \
       | sha256sum -c -

$ chmod +x mariadb_es_repo_setup

$ sudo ./mariadb_es_repo_setup --token="CUSTOMER_DOWNLOAD_TOKEN" --apply \
      --skip-maxscale \
      --skip-tools \
      --mariadb-server-version="11.4"

Install Enterprise Server

On each Enterprise Cluster node, install MariaDB Enterprise Server and MariaDB Enterprise Backup.

Install via CentOS / RHEL (YUM):

$ sudo yum install MariaDB-server MariaDB-backup

Install via Debian / Ubuntu (APT):

$ sudo apt update

$ sudo apt install mariadb-server mariadb-backup

Install via SLES (ZYpp):

$ sudo zypper install MariaDB-server MariaDB-backup

Next Step

Navigation in the procedure "Deploy Galera Cluster Topology":

This page was step 1 of 6.

Next: Step 2: Start and Configure MariaDB Enterprise Server

Step 2: Start and Configure MariaDB Enterprise Server

Overview

This page details step 2 of the 6-step procedure "Deploy Galera Cluster Topology".

This step configures MariaDB Enterprise Servers to operate as Enterprise Cluster nodes and starts MariaDB Enterprise Cluster.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Stop the Enterprise Server Service

The installation process might have started the Enterprise Server service. The service should be stopped prior to making configuration changes.

On each Enterprise Cluster node, stop the MariaDB Enterprise Server service:

$ sudo systemctl stop mariadb

Acquire TLS Certificate

By default, MariaDB Enterprise Cluster requires data-in-transit encryption to secure Galera replication traffic.

MariaDB Enterprise Cluster encrypts the data using the Transport Layer Security (TLS) protocol, which is a newer version of the Secure Socket Layer (SSL) protocol.

In MariaDB Enterprise Cluster 10.5 and earlier, TLS was supported, but not required. For backward compatibility, MariaDB Enterprise Cluster supports the Provider WSREP TLS Mode, which is equivalent to Enterprise Cluster's TLS implementation in ES 10.5 and earlier. For additional information, see "WSREP TLS Modes".

TLS configuration requires 3 files.

Example Filename

Description

ca-cert.pem

Certificate Authority (CA) file.

server-cert.pem

X.509 certificate file.

server-key.pem

X.509 key file.

Self-signed certificates are supported. However, in environments where security is critical, it is recommended to use certificates signed by a trusted Certificate Authority (CA).

For additional information, see "Data-in-Transit Encryption".

Configure Enterprise Cluster

MariaDB Enterprise Server installations support MariaDB Enterprise Cluster, powered by Galera. MariaDB Enterprise Cluster uses the Galera Enterprise 4 wsrep provider plugin. The path to the wsrep provider plugin must be configured using the wsrep_provider system variable.

Required System Variables and Options

Enterprise Cluster nodes require that you set the following system variables and options:

System Variable/Option

Description

bind_address

The network socket Enterprise Cluster listens on for incoming TCP/IP client connections. On Debian or Ubuntu, this system variable must be set to override the 127.0.0.1 default configuration.

binlog_format

Enterprise Cluster requires use of the ROW Binary Log format.

innodb_autoinc_lock_mode

Enterprise Cluster requires an auto-increment lock mode of 2.

ssl_ca

Certificate Authority (CA) file in PEM format.

ssl_cert

X.509 certificate file in PEM format.

ssl_key

X.509 key file in PEM format.

wsrep_cluster_address

Sets the Group Communications back-end (usually gcomm:), followed by a comma-separated list of IP addresses or domain names for each Cluster Node. It is best practice to include all Enterprise Cluster nodes in this list.

wsrep_cluster_name

Sets the logical name for the cluster. Must be the same on all Cluster Nodes.

wsrep_on

Enables Enterprise Cluster.

wsrep_provider

Path to the Galera Enterprise 4 wsrep provider plugin. In MariaDB Enterprise Cluster 11.4, the path is /usr/lib/galera/libgalera_enterprise_smm.so on Debian and Ubuntu and /usr/lib64/galera/libgalera_enterprise_smm.so on CentOS, RHEL, and SLES.

wsrep_provider_options

Accepts options for the Galera Enterprise 4 wsrep provider plugin. Multiple options can be specified, separated by a semi-colon (;). For example, can be used to configure Galera cache size or to enable TLS. For a complete list of available options, see wsrep_provider_options

Example Configuration

Edit a configuration file and set these system variables and options:

[mariadb]
bind_address             = 0.0.0.0
binlog_format            = ROW
innodb_autoinc_lock_mode = 2
wsrep_cluster_address    = gcomm://192.0.2.101,192.0.2.102,192.0.2.103
wsrep_cluster_name       = example-cluster
wsrep_on                 = ON

# wsrep provider path for Debian and Ubuntu:
wsrep_provider = /usr/lib/galera/libgalera_enterprise_smm.so

# wsrep provider path for CentOS, RHEL, and SLES:
# wsrep_provider = /usr/lib64/galera/libgalera_enterprise_smm.so

wsrep_provider_options = "gcache.size=2G;gcs.fc_limit=128"

# TLS Configuration
ssl_ca   = /path/to/ca-cert.pem
ssl_cert = /path/to/sever-cert.pem
ssl_key  = /path/to/server-key.pem

For additional information, see "MariaDB Enterprise Server Configuration Management".

Configure MariaDB Replication

MariaDB Enterprise Cluster can be deployed alongside MariaDB Replication. Deploying MariaDB Enterprise Cluster with MariaDB Replication enables integrating Enterprise Cluster with other products and clusters, for example as separate clusters in different data centers, or as a small dedicated write cluster with two larger dedicated read clusters.

For additional information, see "Replication Configuration".

Bootstrap the Primary Component

When an Enterprise Cluster node starts, it checks the addresses in the wsrep_cluster_address system variable to establish connections with other nodes. The node does not become active until it finds a node that belongs to the Primary Component.

To start the cluster when all nodes are down, you must bootstrap the Primary Component on one node. This allows the other nodes to connect to a working cluster.

On one Enterprise Cluster node, when all nodes are down, bootstrap the Primary Component.

Bootstrap the Primary Component:

$ sudo galera_new_cluster

For additional information, see "Bootstrap a Galera Cluster".

Connect with MariaDB Client:

$ sudo mariadb

The sudo command is used here to connect to the Enterprise Server node using the root@localhost user account, which authenticates using the unix_socket authentication plugin. Other user accounts can be used by specifying the --user and --password command-line options.

Use the SHOW STATUS statement to check the wsrep_cluster_size status variable:

SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size';

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size |     1 |
+--------------------+-------+

The Enterprise Cluster node launches as the Primary Component of a single-node cluster.

Add Nodes to the Cluster

To add nodes to a cluster that has a Primary Component running, complete the following procedure for each Enterprise Cluster node to be added. Nodes should be added one at a time.

On the Enterprise Cluster node being added, start MariaDB Enterprise Server:

$ sudo systemctl start mariadb

For additional information, see "Start and Stop Services".

On the Enterprise Cluster node being added, connect with MariaDB Client:

$ sudo mariadb

On the bootstrapped Enterprise Cluster node, use the SHOW STATUS statement to check the wsrep_cluster_size status variable:

SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size';

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size |     2 |
+--------------------+-------+

On the Enterprise Cluster node being added, use the SHOW STATUS statement to check the wsrep_local_state_comment status variable. If wsrep_local_state_comment is SYNCED, the node has been successfully added, and the Add Node procedure can be repeated to add more nodes.

SHOW GLOBAL STATUS LIKE 'wsrep_local_state_comment';

+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| wsrep_local_state_comment | Synced |
+---------------------------+--------+

When each new Enterprise Cluster node joins the cluster, it requests the current cluster position. If the new node is missing transactions, it initiates either a State Snapshot Transfer (SST) or an Incremental State Transfer (IST) from a donor node to synchronize its data with the Primary Component. Depending on the value of wsrep_sst_method , the donor node may or may not be blocked during an SST.

When the new Enterprise Cluster node finishes its state transfer, the node updates the wsrep_local_state_comment status variable to SYNCED. MaxScale registers the change and begins routing connections or queries to the new node.

Next Step

Navigation in the procedure "Deploy Galera Cluster Topology":

This page was step 2 of 6.

Next: Step 3: Test MariaDB Enterprise Server

Step 3: Test MariaDB Enterprise Server

Overview

This page details step 3 of the 6-step procedure "Deploy Galera Cluster Topology".

This step tests MariaDB Enterprise Server.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Test Enterprise Server Service

Use Systemd to test whether the MariaDB Enterprise Server service is running.

This action is performed on each Enterprise Cluster node.

Check if the MariaDB Enterprise Server service is running by executing the following:

$ systemctl status mariadb

If the service is not running on any node, start the service by executing the following on that node:

$ sudo systemctl start mariadb

Test Local Client Connections

Use MariaDB Client to test the local connection to the Enterprise Server node.

This action is performed on each Enterprise Cluster node:

$ sudo mariadb

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 11.4.5-3-MariaDB-Enterprise MariaDB Enterprise 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)]>

Test Cluster Status

MariaDB Enterprise Cluster is operational when the cluster has a Primary Component. Query the wsrep_cluster_status status variable with SHOW GLOBAL STATUS to confirm that each node belongs to the Primary Component.

This action is performed on each Enterprise Cluster node.

Check the cluster status by executing the following:

SHOW GLOBAL STATUS LIKE 'wsrep_cluster_status';

+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| wsrep_cluster_status | Primary |
+----------------------+---------+

If the Value column does not contain Primary on any node, then the node is not part of the Primary Component. Investigate network connectivity between the node and the nodes in the Primary Component.

Test Cluster Size

MariaDB Enterprise Cluster maintains a count of the cluster size. Query the wsrep_cluster_size status variable with SHOW GLOBAL STATUS to confirm the number of nodes currently in the cluster.

This action is performed on each Enterprise Cluster node.

Check the cluster size by executing the following:

SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size';

+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| wsrep_cluster_size |       3 |
+--------------------+---------+

If the Value column does not contain the expected number of nodes, then some nodes might not be in the cluster. Check the value of the wsrep_cluster_address on each node to confirm that all nodes are in the same cluster.

Test DDL

Use MariaDB Client to test DDL.

On a single Enterprise Cluster node, use the MariaDB Client to connect to the node:

$ sudo mariadb

Create a test database and InnoDB 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)
);

On each other Enterprise Cluster node, use the MariaDB Client to connect to the node:

$ sudo mariadb

Confirm that the database and table exist:

SHOW CREATE TABLE test.contacts\G;

If the database or table do not exist on any node, then check that:

The nodes are in the Primary Component of the same cluster.

The wsrep_osu_method system variable is not set to RSU.

Test DML

Use MariaDB Client to test DML.

On a single Enterprise Cluster node, use the MariaDB Client to connect to the node:

$ sudo mariadb

Insert sample data into the table created in the DDL test:

INSERT INTO test.contacts (first_name, last_name, email)
   VALUES
   ("Kai", "Devi", "kai.devi@example.com"),
   ("Lee", "Wang", "lee.wang@example.com");

On each other Enterprise Cluster node, use the MariaDB Client to connect to the node:

$ sudo mariadb

Execute a SELECT query to retrieve the data:

SELECT * FROM test.contacts;

+----+------------+-----------+----------------------+
| id | first_name | last_name | email                |
+----+------------+-----------+----------------------+
| 1  | Kai        | Devi      | kai.devi@example.com |
| 2  | Lee        | Wang      | lee.wang@example.com |
+----+------------+-----------+----------------------+

If the data is not returned on any node, then check that:

The nodes are in the Primary Component of the same cluster.
The table uses the InnoDB storage engine.
The wsrep_on system variable is set to ON.

Next Step

Navigation in the procedure "Deploy Galera Cluster Topology":

This page was step 3 of 6.

Next: Step 4: Install MariaDB MaxScale

Step 4: Install MariaDB MaxScale

Overview

This page details step 4 of the 6-step procedure "Deploy Galera Cluster Topology".

This step installs MariaDB MaxScale.

MariaDB Enterprise Cluster requires 1 or more MaxScale nodes.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Retrieve Customer Download Token

MariaDB Corporation provides package repositories for YUM (RHEL / CentOS), APT (Debian / Ubuntu), and ZYpp (SLES). A download token is required to access the MariaDB Enterprise Repository.

Customer Download Tokens are customer-specific and are available through the MariaDB Customer Portal.

To retrieve the token for your account:

Navigate to
Log in.
Copy the Customer Download Token.

Substitute your token for CUSTOMER_DOWNLOAD_TOKEN when configuring the package repositories.

Set Up Repository

On the MaxScale node, install the prerequisites for downloading the software from the Web.

Install on CentOS / RHEL (YUM):

$ sudo yum install curl

Install on Debian / Ubuntu (APT):

$ sudo apt install curl apt-transport-https

Install on SLES (ZYpp):

$ sudo zypper install curl

On the MaxScale node, configure package repositories and specify MariaDB MaxScale 25.01:

$ curl -LsSO https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup

$ echo "4d483b4df193831a0101d3dfa7fb3e17411dda7fc06c31be4f9e089c325403c0  mariadb_es_repo_setup" \
       | sha256sum -c -

$ chmod +x mariadb_es_repo_setup

$ sudo ./mariadb_es_repo_setup --token="CUSTOMER_DOWNLOAD_TOKEN" --apply \
      --skip-server \
      --skip-tools \
      --mariadb-maxscale-version="25.01"

Install MaxScale

On the MaxScale node, install MariaDB MaxScale.

Install on CentOS / RHEL (YUM):

$ sudo yum install maxscale

Install on Debian / Ubuntu (APT):

$ sudo apt install maxscale

Install on SLES (ZYpp):

$ sudo zypper install maxscale

Next Step

Navigation in the procedure "Deploy Galera Cluster Topology":

This page was step 4 of 6.

Next: Step 5: Start and Configure MariaDB MaxScale

Step 5: Start and Configure MariaDB MaxScale

Overview

This page details step 5 of the 6-step procedure "Deploy Galera Cluster Topology".

This step configures MariaDB MaxScale to route connections to MariaDB Enterprise Cluster.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Replace the Default Configuration File

MariaDB MaxScale installations include a configuration file with some example objects. This configuration file should be replaced.

On the MaxScale node, replace the default /etc/maxscale.cnf with the following configuration:

[maxscale]
threads          = auto
admin_host       = 0.0.0.0
admin_secure_gui = false

For additional information, see "Global Parameters".

Restart MaxScale

On the MaxScale node, restart the MaxScale service to ensure that MaxScale picks up the new configuration:

$ sudo systemctl restart maxscale

For additional information, see "Start and Stop Services".

Create MaxScale User Account

MariaDB MaxScale connects to Enterprise Cluster through the client port. MaxScale requires its own user account to monitor and orchestrate Enterprise Cluster nodes.

On any Enterprise Cluster node, use the CREATE USER statement to create a new user for MaxScale:

CREATE USER mxs@192.0.2.104 IDENTIFIED BY "passwd";

Use the GRANT statement to grant required privileges to the MaxScale user:

GRANT SHOW DATABASES ON *.* TO mxs@192.0.2.104;
GRANT SELECT ON mysql.columns_priv TO mxs@192.0.2.104;
GRANT SELECT ON mysql.db TO mxs@192.0.2.104;
GRANT SELECT ON mysql.procs_priv TO mxs@192.0.2.104;
GRANT SELECT ON mysql.proxies_priv TO mxs@192.0.2.104;
GRANT SELECT ON mysql.roles_mapping TO mxs@192.0.2.104;
GRANT SELECT ON mysql.tables_priv TO mxs@192.0.2.104;
GRANT SELECT ON mysql.user TO mxs@192.0.2.104;

Enterprise Cluster replicates the new user and privileges to the other Enterprise Cluster nodes.

Configure Server Objects

MariaDB MaxScale uses server objects to define the connections it makes to MariaDB Enterprise Servers.

On the MaxScale node, use maxctrl create server to create a server object for each MariaDB Enterprise Server:

$ maxctrl create server node1 192.0.2.101

$ maxctrl create server node2 192.0.2.102

$ maxctrl create server node3 192.0.2.103

Configure the Galera Monitor

MaxScale uses monitors to retrieve additional information from the servers. This information is used by other services in filtering and routing connections based on the current state of the node. For Enterprise Cluster, use the Galera Monitor (galeramon).

On the MaxScale node, use maxctrl create monitor to create a Galera Monitor for the cluster:

$ maxctrl create monitor cluster_monitor galeramon \
     user=mxs \
     password='MAXSCALE_USER_PASSWORD' \
     --servers node1 node2 node3

In this example:

cluster_monitor is an arbitrary name that is used to identify the new monitor.
galeramon is the name of the module that implements the Galera Monitor.
user=MAXSCALE_USER sets the user parameter to the database user account that MaxScale uses to monitor the ES nodes.
password='MAXSCALE_USER_PASSWORD' sets the password parameter to the password used by the database user account that MaxScale uses to monitor the ES nodes.
--servers sets the servers parameter to the set of nodes that MaxScale should monitor. All non-option arguments after --servers are interpreted as server names.
Other Module Parameters supported by galeramon in MaxScale 25.01 can also be specified.

Choose a MaxScale Router

Routers control how MaxScale balances the load between Enterprise Cluster nodes. Each router uses a different approach to routing queries. Consider the specific use case of your application and database load and select the router that best suits your needs.

Router

Configuration Procedure

Description

Read Connection (readconnroute)

Configure Read Connection Router

• Connection-based load balancing • Routes connections to Enterprise ColumnStore nodes designated as replica servers for a read-only pool • Routes connections to an Enterprise ColumnStore node designated as the primary server for a read-write pool.

Read/Write Split (readwritesplit)

Configure Read/Write Split

• Query-based load balancing • Routes write queries to an Enterprise ColumnStore node designated as the primary server • Routes read queries to Enterprise ColumnStore node designated as replica servers • Automatically reconnects after node failures • Automatically replays transactions after node failures • Optionally enforces causal reads

Configure Read Connection Router

Use MaxScale Read Connection Router (readconnroute) to route connections to replica servers for a read-only pool.

On the MaxScale node, use maxctrl create service to create a router:

$ maxctrl create service connection_router_service readconnroute \
     user=mxs \
     password='MAXSCALE_USER_PASSWORD' \
     router_options=slave \
     --servers node1 node2 node3

In this example:

connection_router_service is an arbitrary name that is used to identify the new service.
readconnroute is the name of the module that implements the Read Connection Router.
user=MAXSCALE_USER sets the user parameter to the database user account that MaxScale uses to connect to the ES nodes.
password=MAXSCALE_USER_PASSWORD sets the password parameter to the password used by the database user account that MaxScale uses to connect to the ES nodes.
router_options=slave sets the router_options parameter to slave, so that MaxScale only routes connections to the replica nodes.
--servers sets the servers parameter to the set of nodes to which MaxScale should route connections. All non-option arguments after --serversare interpreted as server names.
Other Module Parameters supported by readconnroute in MaxScale 25.01 can also be specified.

Configure Listener for the Read Connection Router

These instructions reference TCP port 3308. You can use a different TCP port. The TCP port used must not be bound by any other listener.

On the MaxScale node, use the maxctrl create listener command to configure MaxScale to use a listener for the Read Connection Router (readconnroute):

$ maxctrl create listener connection_router_service connection_router_listener 3308 \
     protocol=MariaDBClient

In this example:

connection_router_service is the name of the readconnroute service that was previously created.
connection_router_listener is an arbitrary name that is used to identify the new listener.
3308 is the TCP port.
protocol=MariaDBClient sets the protocol parameter.
Other Module Parameters supported by listeners in MaxScale 25.01 can also be specified.

Configure Read/Write Split Router for Queries

MaxScale Read/Write Split Router (readwritesplit) performs query-based load balancing. The router routes write queries to the primary and read queries to the replicas.

On the MaxScale node, use the maxctrl create service command to configure MaxScale to use the Read/Write Split Router (readwritesplit):

$ maxctrl create service query_router_service readwritesplit  \
     user=mxs \
     password='MAXSCALE_USER_PASSWORD' \
     --servers node1 node2 node3

In this example:

query_router_service is an arbitrary name that is used to identify the new service.
readwritesplit is the name of the module that implements the Read/Write Split Router.
user=MAXSCALE_USER sets the user parameter to the database user account that MaxScale uses to connect to the ES nodes.
password=MAXSCALE_USER_PASSWORD sets the password parameter to the password used by the database user account that MaxScale uses to connect to the ES nodes.
--servers sets the servers parameter to the set of nodes to which MaxScale should route queries. All non-option arguments after --servers are interpreted as server names.
Other Module Parameters supported by readwritesplit in MaxScale 25.01 can also be specified.

Configure a Listener for the Read/Write Split Router

These instructions reference TCP port 3307. You can use a different TCP port. The TCP port used must not be bound by any other listener.

On the MaxScale node, use the maxctrl create listener command to configure MaxScale to use a listener for the Read/Write Split Router (readwritesplit):

$ maxctrl create listener query_router_service query_router_listener 3307 \
     protocol=MariaDBClient

In this example:

query_router_service is the name of the readwritesplit service that was previously created.
query_router_listener is an arbitrary name that is used to identify the new listener.
3307 is the TCP port.
protocol=MariaDBClient sets the protocol parameter.
Other Module Parameters supported by listeners in MaxScale 25.01 can also be specified.

Start Services

To start the services and monitors, on the MaxScale node use maxctrl start services:

$ maxctrl start services

Next Step

Navigation in the procedure "Deploy Galera Cluster Topology":

This page was step 5 of 6.

Next: Step 6: Test MariaDB MaxScale

Step 6: Test MariaDB MaxScale

Overview

This page details step 6 of the 6-step procedure "Deploy Galera Cluster Topology".

This step tests MariaDB MaxScale.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Check Global Configuration

Use maxctrl show maxscale command to view the global MaxScale configuration.

This action is performed on the MaxScale node:

$ maxctrl show maxscale

┌──────────────┬───────────────────────────────────────────────────────┐
│ Version      │ 25.01.2                                               │
├──────────────┼───────────────────────────────────────────────────────┤
│ Commit       │ 3761fa7a52046bc58faad8b5a139116f9e33364c              │
├──────────────┼───────────────────────────────────────────────────────┤
│ Started At   │ Thu, 05 Aug 2021 20:21:20 GMT                         │
├──────────────┼───────────────────────────────────────────────────────┤
│ Activated At │ Thu, 05 Aug 2021 20:21:20 GMT                         │
├──────────────┼───────────────────────────────────────────────────────┤
│ Uptime       │ 868                                                   │
├──────────────┼───────────────────────────────────────────────────────┤
│ Config Sync  │ null                                                  │
├──────────────┼───────────────────────────────────────────────────────┤
│ Parameters   │ {                                                     │
│              │     "admin_auth": true,                               │
│              │     "admin_enabled": true,                            │
│              │     "admin_gui": true,                                │
│              │     "admin_host": "0.0.0.0",                          │
│              │     "admin_log_auth_failures": true,                  │
│              │     "admin_pam_readonly_service": null,               │
│              │     "admin_pam_readwrite_service": null,              │
│              │     "admin_port": 8989,                               │
│              │     "admin_secure_gui": false,                        │
│              │     "admin_ssl_ca_cert": null,                        │
│              │     "admin_ssl_cert": null,                           │
│              │     "admin_ssl_key": null,                            │
│              │     "admin_ssl_version": "MAX",                       │
│              │     "auth_connect_timeout": "10000ms",                │
│              │     "auth_read_timeout": "10000ms",                   │
│              │     "auth_write_timeout": "10000ms",                  │
│              │     "cachedir": "/var/cache/maxscale",                │
│              │     "config_sync_cluster": null,                      │
│              │     "config_sync_interval": "5000ms",                 │
│              │     "config_sync_password": "*****",                  │
│              │     "config_sync_timeout": "10000ms",                 │
│              │     "config_sync_user": null,                         │
│              │     "connector_plugindir": "/usr/lib64/mysql/plugin", │
│              │     "datadir": "/var/lib/maxscale",                   │
│              │     "debug": null,                                    │
│              │     "dump_last_statements": "never",                  │
│              │     "execdir": "/usr/bin",                            │
│              │     "language": "/var/lib/maxscale",                  │
│              │     "libdir": "/usr/lib64/maxscale",                  │
│              │     "load_persisted_configs": true,                   │
│              │     "local_address": null,                            │
│              │     "log_debug": false,                               │
│              │     "log_info": false,                                │
│              │     "log_notice": true,                               │
│              │     "log_throttling": {                               │
│              │         "count": 10,                                  │
│              │         "suppress": 10000,                            │
│              │         "window": 1000                                │
│              │     },                                                │
│              │     "log_warn_super_user": false,                     │
│              │     "log_warning": true,                              │
│              │     "logdir": "/var/log/maxscale",                    │
│              │     "max_auth_errors_until_block": 10,                │
│              │     "maxlog": true,                                   │
│              │     "module_configdir": "/etc/maxscale.modules.d",    │
│              │     "ms_timestamp": false,                            │
│              │     "passive": false,                                 │
│              │     "persistdir": "/var/lib/maxscale/maxscale.cnf.d", │
│              │     "piddir": "/var/run/maxscale",                    │
│              │     "query_classifier": "qc_sqlite",                  │
│              │     "query_classifier_args": null,                    │
│              │     "query_classifier_cache_size": 289073971,         │
│              │     "query_retries": 1,                               │
│              │     "query_retry_timeout": "5000ms",                  │
│              │     "rebalance_period": "0ms",                        │
│              │     "rebalance_threshold": 20,                        │
│              │     "rebalance_window": 10,                           │
│              │     "retain_last_statements": 0,                      │
│              │     "session_trace": 0,                               │
│              │     "skip_permission_checks": false,                  │
│              │     "sql_mode": "default",                            │
│              │     "syslog": true,                                   │
│              │     "threads": 1,                                     │
│              │     "users_refresh_interval": "0ms",                  │
│              │     "users_refresh_time": "30000ms",                  │
│              │     "writeq_high_water": 16777216,                    │
│              │     "writeq_low_water": 8192                          │
│              │ }                                                     │
└──────────────┴───────────────────────────────────────────────────────┘

Output should align to the global MaxScale configuration in the new configuration file you created.

Check Server Configuration

Use the maxctrl list servers and maxctrl show server commands to view the configured server objects.

This action is performed on the MaxScale node:

Obtain the full list of servers objects:

$ maxctrl list servers

┌────────┬─────────────┬──────┬─────────────┬─────────────────────────┬──────┐
│ Server │ Address     │ Port │ Connections │ State                   │ GTID │
├────────┼─────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ node1  │ 192.0.2.101 │ 3306 │ 0           │ Slave, Synced, Running  │      │
├────────┼─────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ node2  │ 192.0.2.102 │ 3306 │ 0           │ Slave, Synced, Running  │      │
├────────┼─────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ node3  │ 192.0.2.103 │ 3306 │ 0           │ Master, Synced, Running │      │
└────────┴─────────────┴──────┴─────────────┴─────────────────────────┴──────┘

For each server object, view the configuration:

$ maxctrl show server node3

┌─────────────────────┬───────────────────────────────────────────┐
│ Server              │ node3                                     │
├─────────────────────┼───────────────────────────────────────────┤
│ Address             │ 192.0.2.103                               │
├─────────────────────┼───────────────────────────────────────────┤
│ Port                │ 3306                                      │
├─────────────────────┼───────────────────────────────────────────┤
│ State               │ Master, Synced, Running                   │
├─────────────────────┼───────────────────────────────────────────┤
│ Version             │ 11.4.5-3-MariaDB-enterprise-log           │
├─────────────────────┼───────────────────────────────────────────┤
│ Last Event          │ master_up                                 │
├─────────────────────┼───────────────────────────────────────────┤
│ Triggered At        │ Thu, 05 Aug 2021 20:22:26 GMT             │
├─────────────────────┼───────────────────────────────────────────┤
│ Services            │ connection_router_service                 │
│                     │ query_router_service                      │
├─────────────────────┼───────────────────────────────────────────┤
│ Monitors            │ cluster_monitor                           │
├─────────────────────┼───────────────────────────────────────────┤
│ Master ID           │ -1                                        │
├─────────────────────┼───────────────────────────────────────────┤
│ Node ID             │ 1                                         │
├─────────────────────┼───────────────────────────────────────────┤
│ Slave Server IDs    │                                           │
├─────────────────────┼───────────────────────────────────────────┤
│ Current Connections │ 1                                         │
├─────────────────────┼───────────────────────────────────────────┤
│ Total Connections   │ 1                                         │
├─────────────────────┼───────────────────────────────────────────┤
│ Max Connections     │ 1                                         │
├─────────────────────┼───────────────────────────────────────────┤
│ Statistics          │ {                                         │
│                     │     "active_operations": 0,               │
│                     │     "adaptive_avg_select_time": "0ns",    │
│                     │     "connection_pool_empty": 0,           │
│                     │     "connections": 1,                     │
│                     │     "max_connections": 1,                 │
│                     │     "max_pool_size": 0,                   │
│                     │     "persistent_connections": 0,          │
│                     │     "reused_connections": 0,              │
│                     │     "routed_packets": 0,                  │
│                     │     "total_connections": 1                │
│                     │ }                                         │
├─────────────────────┼───────────────────────────────────────────┤
│ Parameters          │ {                                         │
│                     │     "address": "192.0.2.103",               │
│                     │     "disk_space_threshold": null,         │
│                     │     "extra_port": 0,                      │
│                     │     "monitorpw": null,                    │
│                     │     "monitoruser": null,                  │
│                     │     "persistmaxtime": "0ms",              │
│                     │     "persistpoolmax": 0,                  │
│                     │     "port": 3306,                         │
│                     │     "priority": 0,                        │
│                     │     "proxy_protocol": false,              │
│                     │     "rank": "primary",                    │
│                     │     "socket": null,                       │
│                     │     "ssl": false,                         │
│                     │     "ssl_ca_cert": null,                  │
│                     │     "ssl_cert": null,                     │
│                     │     "ssl_cert_verify_depth": 9,           │
│                     │     "ssl_cipher": null,                   │
│                     │     "ssl_key": null,                      │
│                     │     "ssl_verify_peer_certificate": false, │
│                     │     "ssl_verify_peer_host": false,        │
│                     │     "ssl_version": "MAX"                  │
│                     │ }                                         │
└─────────────────────┴───────────────────────────────────────────┘

Output should align to the Server Object configuration you performed.

Check Monitor Configuration

Use the maxctrl list monitors and maxctrl show monitor commands to view the configured monitors.

This action is performed on the MaxScale node:

Obtain the full list of monitors:

$ maxctrl list monitors

┌─────────────────┬─────────┬─────────────────────┐
│ Monitor         │ State   │ Servers             │
├─────────────────┼─────────┼─────────────────────┤
│ cluster_monitor │ Running │ node1, node2, node3 │
└─────────────────┴─────────┴─────────────────────┘

For each monitor, view the monitor configuration:

$ maxctrl show monitor cluster_monitor

┌─────────────────────┬──────────────────────────────────────────────────────┐
│ Monitor             │ cluster_monitor                                      │
├─────────────────────┼──────────────────────────────────────────────────────┤
│ Module              │ galeramon                                            │
├─────────────────────┼──────────────────────────────────────────────────────┤
│ State               │ Running                                              │
├─────────────────────┼──────────────────────────────────────────────────────┤
│ Servers             │ node1                                                │
│                     │ node2                                                │
│                     │ node3                                                │
├─────────────────────┼──────────────────────────────────────────────────────┤
│ Parameters          │ {                                                    │
│                     │     ..                                               │
│                     │ }                                                    │
├─────────────────────┼──────────────────────────────────────────────────────┤
│ Monitor Diagnostics │ {                                                    │
│                     │     ..                                               │
│                     │ }                                                    │
└─────────────────────┴──────────────────────────────────────────────────────┘

Output should align to the Galera Monitor (galeramon) configuration you performed.

Check Service Configuration

Use the maxctrl list services and maxctrl show service commands to view the configured routing services.

This action is performed on the MaxScale node:

Obtain the full list of routing services:

$ maxctrl list services

┌───────────────────────────┬────────────────┬─────────────┬───────────────────┬─────────────────────┐
│ Service                   │ Router         │ Connections │ Total Connections │ Servers             │
├───────────────────────────┼────────────────┼─────────────┼───────────────────┼─────────────────────┤
│ connection_router_service │ readconnroute  │ 0           │ 0                 │ node1, node2, node3 │
├───────────────────────────┼────────────────┼─────────────┼───────────────────┼─────────────────────┤
│ query_router_service      │ readwritesplit │ 1           │ 1                 │ node1, node2, node3 │
└───────────────────────────┴────────────────┴─────────────┴───────────────────┴─────────────────────┘

For each service, view the service configuration:

$ maxctrl show service query_router_service

┌─────────────────────┬─────────────────────────────────────────────────────────────┐
│ Service             │ query_router_service                                        │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Router              │ readwritesplit                                              │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ State               │ Started                                                     │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Started At          │ Thu Aug  5 20:23:38 2021                                    │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Current Connections │ 1                                                           │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Total Connections   │ 1                                                           │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Max Connections     │ 1                                                           │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Cluster             │                                                             │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Servers             │ node1                                                       │
│                     │ node2                                                       │
│                     │ node3                                                       │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Services            │                                                             │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Filters             │                                                             │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Parameters          │ {                                                           │
│                     │     "auth_all_servers": false,                              │
│                     │     "causal_reads": "false",                                │
│                     │     "causal_reads_timeout": "10000ms",                      │
│                     │     "connection_keepalive": "300000ms",                     │
│                     │     "connection_timeout": "0ms",                            │
│                     │     "delayed_retry": false,                                 │
│                     │     "delayed_retry_timeout": "10000ms",                     │
│                     │     "disable_sescmd_history": false,                        │
│                     │     "enable_root_user": false,                              │
│                     │     "idle_session_pool_time": "-1000ms",                    │
│                     │     "lazy_connect": false,                                  │
│                     │     "localhost_match_wildcard_host": true,                  │
│                     │     "log_auth_warnings": true,                              │
│                     │     "master_accept_reads": false,                           │
│                     │     "master_failure_mode": "fail_instantly",                │
│                     │     "master_reconnection": false,                           │
│                     │     "max_connections": 0,                                   │
│                     │     "max_sescmd_history": 50,                               │
│                     │     "max_slave_connections": 255,                           │
│                     │     "max_slave_replication_lag": "0ms",                     │
│                     │     "net_write_timeout": "0ms",                             │
│                     │     "optimistic_trx": false,                                │
│                     │     "password": "*****",                                    │
│                     │     "prune_sescmd_history": true,                           │
│                     │     "rank": "primary",                                      │
│                     │     "retain_last_statements": -1,                           │
│                     │     "retry_failed_reads": true,                             │
│                     │     "reuse_prepared_statements": false,                     │
│                     │     "router": "readwritesplit",                             │
│                     │     "session_trace": false,                                 │
│                     │     "session_track_trx_state": false,                       │
│                     │     "slave_connections": 255,                               │
│                     │     "slave_selection_criteria": "LEAST_CURRENT_OPERATIONS", │
│                     │     "strict_multi_stmt": false,                             │
│                     │     "strict_sp_calls": false,                               │
│                     │     "strip_db_esc": true,                                   │
│                     │     "transaction_replay": false,                            │
│                     │     "transaction_replay_attempts": 5,                       │
│                     │     "transaction_replay_max_size": 1073741824,              │
│                     │     "transaction_replay_retry_on_deadlock": false,          │
│                     │     "type": "service",                                      │
│                     │     "use_sql_variables_in": "all",                          │
│                     │     "user": "mxs",                                          │
│                     │     "version_string": null                                  │
│                     │ }                                                           │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Router Diagnostics  │ {                                                           │
│                     │     "avg_sescmd_history_length": 0,                         │
│                     │     "max_sescmd_history_length": 0,                         │
│                     │     "queries": 1,                                           │
│                     │     "replayed_transactions": 0,                             │
│                     │     "ro_transactions": 0,                                   │
│                     │     "route_all": 0,                                         │
│                     │     "route_master": 0,                                      │
│                     │     "route_slave": 1,                                       │
│                     │     "rw_transactions": 0,                                   │
│                     │     "server_query_statistics": [                            │
│                     │         {                                                   │
│                     │             "avg_selects_per_session": 0,                   │
│                     │             "avg_sess_duration": "0ns",                     │
│                     │             "id": "node2",                                  │
│                     │             "read": 1,                                      │
│                     │             "total": 1,                                     │
│                     │             "write": 0                                      │
│                     │         }                                                   │
│                     │     ]                                                       │
│                     │ }                                                           │
└─────────────────────┴─────────────────────────────────────────────────────────────┘

Output should align to the Read Connection Router (readconnroute) or Read/Write Split Router (readwritesplit) configuration you performed.

Test Application User

Applications should use a dedicated user account. The user account must be created on the primary server.

When users connect to MaxScale, MaxScale authenticates the user connection before routing it to an Enterprise Server node. Enterprise Server authenticates the connection as originating from the IP address of the MaxScale node.

The application users must have one user account with the host IP address of the application server and a second user account with the host IP address of the MaxScale node.

The requirement of a duplicate user account can be avoided by enabling the proxy_protocol parameter for MaxScale and the proxy_protocol_networks for Enterprise Server.

Create a User to Connect from MaxScale

This action is performed on any Enterprise Cluster node:

Connect to the node:

$ sudo mariadb

Create the database user account for your MaxScale node:

CREATE USER 'app_user'@'192.0.2.104' IDENTIFIED BY 'app_user_passwd';

Replace 192.0.2.104 with the relevant IP address specification for your MaxScale node.

Passwords should meet your organization's password policies.

Grant the privileges required by your application to the database user account for your MaxScale node:

GRANT ALL ON test.* TO 'app_user'@'192.0.2.104';

The privileges shown are designed to allow the tests in the subsequent sections to work. The user account for your production application may require different privileges.

Create a User to Connect from the Application Server

This action is performed on any Enterprise Cluster node:

Create the database user account for your application server:

CREATE USER 'app_user'@'192.0.2.11' IDENTIFIED BY 'app_user_passwd';

Replace 192.0.2.11 with the relevant IP address specification for your application server.

Passwords should meet your organization's password policies.

Grant the privileges required by your application to the database user account for your application server:

GRANT ALL ON test.* TO 'app_user'@'192.0.2.11';

The privileges shown are designed to allow the tests in the subsequent sections to work. The user account for your production application may require different privileges.

Test Connection with Application User

To test the connection, use the MariaDB Client from your application server to connect to an Enterprise Cluster node through MaxScale.

This action is performed on the application server:

$ mariadb --host 192.0.2.104 --port 3307
      --user app_user --password

Test Connection with Read Connection Router

If you configured the Read Connection Router, confirm that MaxScale routes connections to the replica servers.

On the MaxScale node, use the maxctrl list listeners command to view the available listeners and ports:

$ maxctrl list listeners

┌────────────────────────────┬──────┬──────┬─────────┬───────────────────────────┐
│ Name                       │ Port │ Host │ State   │ Service                   │
├────────────────────────────┼──────┼──────┼─────────┼───────────────────────────┤
│ connection_router_listener │ 3308 │ ::   │ Running │ connection_router_service │
├────────────────────────────┼──────┼──────┼─────────┼───────────────────────────┤
│ query_router_listener      │ 3307 │ ::   │ Running │ query_router_service      │
└────────────────────────────┴──────┴──────┴─────────┴───────────────────────────┘

Open multiple terminals connected to your application server, in each use MariaDB Client to connect to the listener port for the Read Connection Router (in the example 3308):

$ mariadb --host 192.0.2.104 --port 3308 \
      --user app_user --password

Use the application user credentials you created for the --user and --password options.

In each terminal, query the hostname system variable to identify to which you're connected:

SELECT @@global.hostname;

+-------------------+
| @@global.hostname |
+-------------------+
|             node2 |
+-------------------+

Different terminals should return different values since MaxScale routes the connections to different nodes.

Since the router was configured the slave router option, the Read Connection Router only routes connections to replica servers.

Test Write Queries with the Read/Write Split Router

If you configured the Read/Write Split Router, confirm that readwritesplit correctly routes write queries.

This action is performed with multiple client connections to the MaxScale node.

On the MaxScale node, use the maxctrl list servers command to identify the Enterprise Cluster node currently operating as the primary server:

$ maxctrl list servers

┌────────┬─────────────┬──────┬─────────────┬─────────────────────────┬──────┐
│ Server │ Address     │ Port │ Connections │ State                   │ GTID │
├────────┼─────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ node1  │ 192.0.2.101 │ 3306 │ 0           │ Slave, Synced, Running  │      │
├────────┼─────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ node2  │ 192.0.2.102 │ 3306 │ 0           │ Slave, Synced, Running  │      │
├────────┼─────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ node3  │ 192.0.2.103 │ 3306 │ 0           │ Master, Synced, Running │      │
└────────┴─────────────┴──────┴─────────────┴─────────────────────────┴──────┘

The server listed as Master is currently operating as the primary server.

On the MaxScale node, use the maxctrl list listeners command to identify the correct listener port:

$ maxctrl list listeners galerarouter

┌────────────────────────────┬──────┬───────┬─────────┬───────────────────────────┐
│ Name                       │ Port │ Host  │ State   │ Service                   │
├────────────────────────────┼──────┼───────┼─────────┼───────────────────────────┤
│ connection_router_listener │ 3308 │       │ Running │ connection_router_service │
│ query_router_listener      │ 3307 │       │ Running │ query_router_service      │
└────────────────────────────┴──────┴───────┴─────────┴───────────────────────────┘

In the example, the listener port for the Read/Write Split router is 3307.

Use the MariaDB Client to establish multiple connections to the listener configured for the Read/Write Split routing service, query_router_listener, on the MaxScale node:

$ mariadb --host=192.0.2.104 --port=3307 \
      --user=app_user --password=app_user_passwd

The database user account for your application server should be specified by the --user option.

Using any client connection, create a test table:

CREATE TABLE test.load_balancing_test (
   id INT PRIMARY KEY AUTO_INCREMENT,
   hostname VARCHAR(256)
);

Using each client connection, insert the values of the hostname system variable into the table using the INSERT statement to identify the node that executes the statement:

INSERT INTO test.load_balancing_test (hostname)
VALUES (@@global.hostname);

Using any client connection, query the table using the SELECT statement:

SELECT * FROM test.load_balancing_test;

+----+----------+
| id | hostname |
+----+----------+
|  1 | node3    |
|  4 | node3    |
|  7 | node3    |
+----+----------+

The output shows the hostname from the Enterprise Cluster node operating as the primary server. (Enterprise Cluster offsets auto-increment values by node to avoid write conflicts.)

Confirm that MaxScale is routing write queries to the Enterprise Cluster node operating as the primary server by checking that the test table only contains the hostname of the correct Enterprise Cluster node.

Test Read Queries with the Read/Write Split Router

If you configured the Read/Write Split Router, confirm that readwritesplit properly routes read queries to multiple replica servers.

This action is performed with multiple clients connected to the MaxScale node.

On the MaxScale node, use maxctrl list servers to identify the Enterprise Cluster nodes that are currently operating as replica servers:

$ maxctrl list servers

┌────────┬─────────────┬──────┬─────────────┬─────────────────────────┬──────┐
│ Server │ Address     │ Port │ Connections │ State                   │ GTID │
├────────┼─────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ node1  │ 192.0.2.101 │ 3306 │ 0           │ Slave, Synced, Running  │      │
├────────┼─────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ node2  │ 192.0.2.102 │ 3306 │ 0           │ Slave, Synced, Running  │      │
├────────┼─────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ node3  │ 192.0.2.103 │ 3306 │ 0           │ Master, Synced, Running │      │
└────────┴─────────────┴──────┴─────────────┴─────────────────────────┴──────┘

The servers listed as Slave are currently operating as replica servers.

On the MaxScale node, use the maxctrl list listeners command to identify the correct listener port:

$ maxctrl list listeners

┌────────────────────────────┬──────┬───────┬─────────┬───────────────────────────┐
│ Name                       │ Port │ Host  │ State   │ Service                   │
├────────────────────────────┼──────┼───────┼─────────┼───────────────────────────┤
│ connection_router_listener │ 3308 │       │ Running │ connection_router_service │
│ query_router_listener      │ 3307 │       │ Running │ query_router_service      │
└────────────────────────────┴──────┴───────┴─────────┴───────────────────────────┘

In the example, the listener port for the Read/Write Split router is 3307.

Use the MariaDB Client to establish multiple connections to query_router_listener which is the listener configured for the Read/Write Split routing service on the MaxScale node:

$ mariadb --host=192.0.2.104 --port=3307 \
   --user=app_user --password=app_user_passwd

The database user account for your application server should be specified by the --user option.

Using each client connection, query the hostname system variable to identify the node that executes the statement:

SELECT @@global.hostname;

+-------------------+
| @@global.hostname |
+-------------------+
|             node2 |
+-------------------+

The output shows the hostname value from one of the Enterprise Cluster nodes operating as replica servers.

Confirm that MaxScale is routing read queries to multiple Enterprise Cluster nodes operating as replica servers by checking that different client connections return different hostname values.

For more information on different routing criteria, slave_selection_criteria

Next Step

Navigation in the procedure "Deploy Galera Cluster Topology":

This page was step 6 of 6.

This procedure is complete.