Deploy MaxScale 2.5 on Debian 10 with ColumnStore Monitor and Read Connection Router

This page is part of MariaDB's Enterprise Documentation.

The parent of this page is: MariaDB Deployment

Topics on this page:

Overview
Prerequisites
MaxScale Components
Term Definitions
Installation
- Install via APT (Debian, Ubuntu)
Configuration
Start the MaxScale Service
Testing
- Check MaxScale Status

Overview

This procedure provides instructions for deploying MariaDB MaxScale 2.5 with MariaDB ColumnStore 1.2.

Note

ColumnStore Monitor (csmon) is a deprecated monitor designed for older ColumnStore 1.2 deployments. To deploy MaxScale with ColumnStore 5 and later, see "Deploy ColumnStore Object Storage Topology" or "Deploy ColumnStore Shared Local Storage Topology" instead.

Prerequisites

These instructions assume that the back-end MariaDB ColumnStore 1.2 cluster has already been deployed.

MaxScale Components

These instructions detail the deployment of MaxScale with the following components:

Component	Description
ColumnStore Monitor (csmon)	It monitors ColumnStore deployments.
Read Connection Router (readconnroute)	It provides simple and lightweight load balancing of connections across a set of servers. It works best for use cases where each client connection needs a consistent connection to a single back-end server, and where queries can be executed on any back-end server. It can be configured to route connections to any running server, any primary server, or any replica server. If connections are routed to non-primary servers, the application should only use read-only queries to maintain consistency of data.

Component

Description

ColumnStore Monitor (csmon)

It monitors ColumnStore deployments.

Read Connection Router (readconnroute)

It provides simple and lightweight load balancing of connections across a set of servers.
It works best for use cases where each client connection needs a consistent connection to a single back-end server, and where queries can be executed on any back-end server.
It can be configured to route connections to any running server, any primary server, or any replica server.
If connections are routed to non-primary servers, the application should only use read-only queries to maintain consistency of data.

Term Definitions

Term	Definition
MaxScale instance	MariaDB MaxScale running by itself on a single host. It interacts with other hosts, such as deployments using MariaDB Replication, Galera Cluster, and ColumnStore. It serves as the database proxy and load balancer.

Installation

MariaDB Corporation provides a APT package repository for Debian 10.

Install via APT (Debian, Ubuntu)

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

Configure the APT package repository.

To configure APT package repositories:

$ sudo apt install wget

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

$ echo "cfcd35671125d657a212d92b93be7b1f4ad2fda58dfa8b5ab4b601bf3afa4eae  mariadb_es_repo_setup" \
    | sha256sum -c -

$ chmod +x mariadb_es_repo_setup

$ sudo ./mariadb_es_repo_setup --token="CUSTOMER_DOWNLOAD_TOKEN" --apply \
   --mariadb-maxscale-version="2.5"

$ sudo apt update

Install MariaDB MaxScale and package dependencies:
```
$ sudo apt install maxscale
```
Configure MaxScale.

Installation only loads MariaDB MaxScale to the system. MariaDB MaxScale requires configuration before MaxScale is ready for use.

Configuration

MariaDB MaxScale's parameters can be configured in two ways:

All parameters can be set in a configuration file (such as the /etc/maxscale.cnf file). When the configuration file is updated, the MaxScale instance must be restarted to apply the changes.
If a parameter supports dynamic changes, it can be set on-the-fly using the MaxCtrl utility.

Configure MaxScale Global Parameters

Determine which global parameters you need to configure.

Useful global parameters for MariaDB MaxScale:

Parameter	Description
local_address	Sets the local IP address or network interface to use when connecting to MariaDB Servers.
admin_enabled	Sets whether the MaxScale Instance supports the REST API. Disable to block access.
admin_host	Sets the network interface used by the REST API.
admin_port	Sets the port used by the REST API.
admin_auth	Sets whether the REST API uses HTTP Basic Access authentication. Users can be created wih maxctrl.

Set your global parameters in maxscale.cnf.

MariaDB MaxScale global parameters, which apply to the all MaxScale modules, are set in the [maxscale] configuration group:

For example:
```
[maxscale]
admin_auth    = true
```

Configure Servers

Determine which server parameters you need to configure.

Mandatory parameters for Server objects:

Parameter	Description
`type`	Set the module type to `server`.
`address`	Set the IP address for the MariaDB Community Server.
`port`	Set the port the MariaDB Community Server listens on.
`protocol`	Set the protocol to `MariaDBBackend` to connect the Server.

Set your server parameters in maxscale.cnf.

For each Server in your deployment, add a new uniquely-named configuration group to the MaxScale Instance. The name of the configuration group is arbitrary, but it cannot contain any white space. Use whatever terms you find most convenient, but it is most common to use names like serverN or the server's host name.

For example:
```
[server1]
type     = server
address  = 192.0.2.102
port     = 3306
protocol = MariaDBBackend
```

Configure ColumnStore Monitor

Determine which parameters for ColumnStore Monitor (csmon) you need to configure.

Mandatory parameters for ColumnStore Monitor (csmon):

Parameter	Description
`type`	Set the type to `monitor`.
`module`	Set to `csmon` for ColumnStore.
`servers`	Set to a comma-separated list of the Server object names.
`user`	Set to the user MariaDB MaxScale uses to connect to the Servers.
`password`	Set to the password MariaDB MaxScale uses to authenticate on the Servers.
`version`	Set to the ColumnStore version.

Set your ColumnStore Monitor parameters in maxscale.cnf.

Add a new uniquely-named configuration group for the monitor to the MaxScale Instance. The name of the configuration group is arbitrary, but it cannot contain any white space. Use whatever terms you find most convenient, but it is most common to use names like col-monitor.

For example:
```
[col-monitor]
type          = monitor
module        = csmon
servers       = server1,server2,server3
user          = maxscale
password      = max_passwd
version       = 1.2
```

Configure Read Connection Router

Determine which parameters for Read Connection Router (readconnroute) you need to configure.

Mandatory parameters for Read Connection Router (readconnroute):

Parameter	Description
`type`	Set the module type as `service`.
`router`	Use to set the type of router you want to use for the service. For the Read Connection Router, set to `readconnroute`.
`servers`	Set as a comma-separated list of the Server object names.
`user`	Set the user you want the MaxScale Instance to use when connecting to Servers.
`password`	Set the password you want the MaxScale Instance to use in authentication when connecting to Servers.
`router_options`	Set to a comma separated list of valid server roles. These roles are used as the valid types of servers the router will form connections to when new sessions are created. Choose from `running`, `master`, and `slave`.

Set your Read Connection Router parameters in maxscale.cnf.

Add a new uniquely-named configuration group for the router to the MaxScale Instance. The name of the configuration group is arbitrary, but it cannot contain any white space. Use whatever terms you find most convenient, but it is most common to use names like connection-router.

For example:
```
[connection-router]
type           = service
router         = readconnroute
servers        = server1,server2,server3
user           = maxscale
password       = max_passwd
router_options = master,slave
```

Configure the Router's Listener

Determine which parameters for the listener you need to configure.

Mandatory parameters for the listener:

Parameter	Description
`type`	Set the module type as a `listener`.
`service`	Use this parameter to connect the listener to a configured routing service.
`protocol`	Set this parameter to `MariaDBClient` to handle incoming client connections.
`port`	Set this parameter to specify the port you want the MaxScale Instance to listen on.

Set your listener parameters in maxscale.cnf.

Add a new uniquely-named configuration group for the listener to the MaxScale Instance. The name of the configuration group is arbitrary, but it cannot contain any white space. Use whatever terms you find most convenient, but it is most common to use names like connection-router-listener.

For example:
```
[connection-router-listener]
type     = listener
service  = connection-router
protocol = MariaDBClient
port     = 3306
```

Configure the User Account

On each back-end MariaDB server, create the user account used by the monitor and the router using the CREATE USER statement. Make sure that the user account can connect from the MaxScale instance's IP address:
```
CREATE USER 'maxscale'@'192.0.2.101'
   IDENTIFIED BY 'max_passwd';
```

On each back-end MariaDB server, grant the required privileges to the user account used by the monitor and the router using the GRANT statement.

For the Read Connection Router (readconnroute), the global SHOW DATABASES privilege and the SELECT privilege on several of the system's privilege tables are required:

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

For ColumnStore Monitor (csmon), no special privileges are required.

Start the MaxScale Service

MariaDB MaxScale installations include configuration to start, stop, restart, enable/disable on boot, and check the status of the MaxScale Instance using the operating system default process management system.

Debian 10 uses systemd. You can manage the MaxScale process using the systemctl command:

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`

Testing

When you have MariaDB MaxScale up and running, you should test it to ensure that it is working and that weren't any issues during startup.

Check MaxScale Status

Check that MaxScale is running properly by using the MaxCtrl utility:

$ sudo maxctrl show maxscale

┌──────────────┬──────────────────────────────────────────────────────────────────────┐
│ Version      │ 2.5.21                                                               │
├──────────────┼──────────────────────────────────────────────────────────────────────┤
│ Commit       │ 61b8bbf7f63c38ca9c408674e66f3627a0b2192e                             │
├──────────────┼──────────────────────────────────────────────────────────────────────┤
│ Started At   │ Fri, 30 Jul 2021 18:05:18 GMT                                        │
├──────────────┼──────────────────────────────────────────────────────────────────────┤
│ Activated At │ Fri, 30 Jul 2021 18:05:18 GMT                                        │
├──────────────┼──────────────────────────────────────────────────────────────────────┤
│ Uptime       │ 109                                                                  │
├──────────────┼──────────────────────────────────────────────────────────────────────┤
│ Parameters   │ {                                                                    │
│              │     "libdir": "/usr/lib/x86_64-linux-gnu/maxscale",                  │
│              │     "datadir": "/var/lib/maxscale",                                  │
│              │     "process_datadir": "/var/lib/maxscale/data3850",                 │
│              │     "cachedir": "/var/cache/maxscale",                               │
│              │     "configdir": "/etc",                                             │
│              │     "config_persistdir": "/var/lib/maxscale/maxscale.cnf.d",         │
│              │     "module_configdir": "/etc/maxscale.modules.d",                   │
│              │     "piddir": "/var/run/maxscale",                                   │
│              │     "logdir": "/var/log/maxscale",                                   │
│              │     "langdir": "/var/lib/maxscale",                                  │
│              │     "execdir": "/usr/bin",                                           │
│              │     "connector_plugindir": "/usr/lib/x86_64-linux-gnu/mysql/plugin", │
│              │     "threads": 1,                                                    │
│              │     "thread_stack_size": 8388608,                                    │
│              │     "writeq_high_water": 0,                                          │
│              │     "writeq_low_water": 0,                                           │
│              │     "auth_connect_timeout": 3,                                       │
│              │     "auth_read_timeout": 1,                                          │
│              │     "auth_write_timeout": 2,                                         │
│              │     "skip_permission_checks": false,                                 │
│              │     "admin_auth": true,                                              │
│              │     "admin_enabled": true,                                           │
│              │     "admin_log_auth_failures": true,                                 │
│              │     "admin_host": "127.0.0.1",                                       │
│              │     "admin_port": 8989,                                              │
│              │     "admin_ssl_key": "",                                             │
│              │     "admin_ssl_cert": "",                                            │
│              │     "admin_ssl_ca_cert": "",                                         │
│              │     "admin_pam_readwrite_service": "",                               │
│              │     "admin_pam_readonly_service": "",                                │
│              │     "passive": false,                                                │
│              │     "query_classifier": "",                                          │
│              │     "query_classifier_cache_size": 155008819,                        │
│              │     "retain_last_statements": 0,                                     │
│              │     "dump_last_statements": "never",                                 │
│              │     "session_trace": 0,                                              │
│              │     "load_persisted_configs": true,                                  │
│              │     "max_auth_errors_until_block": 10                                │
│              │ }                                                                    │
└──────────────┴──────────────────────────────────────────────────────────────────────┘