Deploy MaxScale 2.3 on Debian 10 with Galera Monitor and Read Connection Router

These instructions detail the deployment of MariaDB MaxScale 2.3 on Debian 10 in a MaxScale Instance with Galera Monitor and Read Connection Router.

These instructions detail how to deploy MariaDB MaxScale as a load balancing and high availability solution for Galera Cluster.

Prerequisites

These instructions assume that the back-end MariaDB Server instances have already been deployed.

See the following resources for how to deploy Galera Cluster nodes:

MariaDB Enterprise Components

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

Component

Description

MariaDB MaxScale 2.3

  • MariaDB MaxScale is an advanced database proxy, firewall, and query router.

MaxScale Components

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

Component

Description

Galera Monitor (galeramon)

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

Installation

MariaDB Corporation provides a APT package repository for Debian 10.

Install on Debian/Ubuntu (APT)

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

  2. Configure the APT package repository.

    To configure APT package repositories:

    $ sudo apt install wget
    
    $ wget https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup
    
    $ echo "b741361ea3a0a9fcaa30888a63ff3a8a4021882f126cf4ef26cf616493a29315  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.3"
    
    $ sudo apt update
    
  3. Install MariaDB MaxScale and package dependencies:

    $ sudo apt install maxscale
    
  4. Configure MaxScale.

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

    See Configuration.

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's Global Parameters

  1. 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 Enterprise 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.

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

  1. 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 Enterprise Server.

    port

    Set the port the MariaDB Enterprise Server listens on.

    protocol

    Set the protocol to MariaDBBackend to connect the Server.

  2. 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.2
    port     = 3306
    protocol = MariaDBBackend
    

Configure Galera Monitor

  1. Determine which parameters for Galera Monitor (galeramon) you need to configure.

    Mandatory parameters for Galera Monitor (galeramon):

    Parameter

    Description

    type

    Set the type to monitor.

    module

    Set to galeramon for MariaDB Enterprise Cluster.

    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.

    Useful parameters for Galera Monitor (galeramon):

    Parameter

    Description

    disable_master_failback

    If a node marked as master inside MaxScale happens to fail and the master status is assigned to another node MaxScale will normally return the master status to the original node after it comes back up. With this option enabled, if the master status is assigned to a new node it will not be reassigned to the original node for as long as the new master node is running.

    available_when_donor

    This option allows Galera nodes to be used normally when they are donors in an SST operation when the SST method is non-blocking.

  2. Set your Galera 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 cluster-monitor.

    For example:

    [cluster-monitor]
    type           = monitor
    module         = galeramon
    servers        = server1,server2,server3
    user           = maxscale
    password       = max_passwd
    

Configure Read Connection Router

  1. 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, synced, master, and slave.

  2. 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 = synced
    

Configure the Router's Listener

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

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

  1. 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.1'
       IDENTIFIED BY 'max_passwd';
    
  2. 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.1';
    GRANT SELECT ON mysql.columns_priv
         TO 'maxscale'@'192.0.2.1';
    GRANT SELECT ON mysql.db
         TO 'maxscale'@'192.0.2.1';
    GRANT SELECT ON mysql.proxies_priv
         TO 'maxscale'@'192.0.2.1';
    GRANT SELECT ON mysql.roles_mapping
         TO 'maxscale'@'192.0.2.1';
    GRANT SELECT ON mysql.tables_priv
         TO 'maxscale'@'192.0.2.1';
    GRANT SELECT ON mysql.user
         TO 'maxscale'@'192.0.2.1';
    

    For Galera Monitor (galeramon), the required permissions depend on the version of MariaDB Enterprise Server that is being used.

    In ES10.5.8-5 and later, the following privileges are required:

    GRANT BINLOG MONITOR,
       REPLICA MONITOR
       ON *.*
       TO 'maxscale'@'192.0.2.1';
    

    In ES10.5.6-4 and before, the following privileges are required:

    GRANT BINLOG MONITOR,
       REPLICATION SLAVE ADMIN
       ON *.*
       TO 'maxscale'@'192.0.2.1';
    

    In ES10.4 and before, the following privileges are required:

    GRANT REPLICATION CLIENT
       ON *.*
       TO 'maxscale'@'192.0.2.1';
    

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

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

    $ sudo maxctrl show maxscale
    
    ┌──────────────┬──────────────────────────────────────────────────────────────────────┐
    │ Version      │ 2.3.20                                                               │
    ├──────────────┼──────────────────────────────────────────────────────────────────────┤
    │ 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                                │
    │              │ }                                                                    │
    └──────────────┴──────────────────────────────────────────────────────────────────────┘