Deploy MaxScale 2.5 on SLES 15 with MariaDB Monitor and Read/Write Split Router

Install via ZYpp (SLES)

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

   To configure ZYpp package repositories:

   $ sudo zypper install wget
   
   $ wget https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup
   
   $ echo "bf67a231c477fba0060996a83b197c29617b6193e1167f6f062216ae13c716c7  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"
   

3. Install MariaDB MaxScale and package dependencies:

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

Configuring 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's global parameters, which apply to the whole instance, are set in the [maxscale] configuration group:

   For example:

   [maxscale]
   admin_auth    = true
   

Configuring 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
   

Configuring MariaDB Monitor

1. Determine which parameters for MariaDB Monitor (mariadbmon) you need to configure.

   Mandatory parameters for MariaDB Monitor (mariadbmon):

   Parameter	Description
   type	Set the type to monitor.
   module	Set to mariadbmon for MariaDB Replication.
   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 MariaDB Monitor (mariadbmon):

   Parameter	Description
   auto_failover	Enables Automatic Failover for MariaDB Replication deployments, allowing the monitor to respond to a master server failure by reconfiguring a replica server to operate as the new master server.
   auto_rejoin	Enables Automatic Rejoin for MariaDB Replication deployments, allowing the monitor to reconfigure replica servers that were down during Automatic Failover to use the new master server.

2. Set your MariaDB 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 repl-monitor.

   For example:

   [repl-monitor]
   type          = monitor
   module        = mariadbmon
   servers       = server1,server2,server3
   user          = maxscale
   password      = max_passwd
   auto_failover = ON
   auto_rejoin   = ON
   

Configuring Read/Write Split Router

1. Determine which parameters for Read/Write Split Router (readwritesplit) you need to configure.

   Mandatory parameters for Read/Write Split Router (readwritesplit):

   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/Write Split router, set to readwritesplit.
   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.

2. Set your Read/Write Split 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 split-router.

   For example:

   [split-router]
   type     = service
   router   = readwritesplit
   servers  = server1,server2,server3
   user     = maxscale
   password = max_passwd
   

Configuring 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 split-router-listener.

   For example:

   [split-router-listener]
   type     = listener
   service  = split-router
   protocol = MariaDBClient
   port     = 3306
   

Configuring 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/Write Split Router (readwritesplit) , 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 MariaDB Monitor (mariadbmon), 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 ADMIN,
      READ_ONLY ADMIN,
      RELOAD,
      REPLICA MONITOR,
      REPLICATION MASTER ADMIN,
      REPLICATION REPLICA ADMIN,
      REPLICATION REPLICA,
      SHOW DATABASES
      ON *.*
      TO 'maxscale'@'192.0.2.1';
   

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

   GRANT BINLOG ADMIN,
      BINLOG MONITOR,
      READ_ONLY ADMIN,
      RELOAD,
      REPLICATION MASTER ADMIN,
      REPLICATION REPLICA ADMIN,
      REPLICATION REPLICA,
      SHOW DATABASES
      ON *.*
      TO 'maxscale'@'192.0.2.1';
   

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

   GRANT SUPER,
      REPLICATION CLIENT,
      RELOAD,
      PROCESS,
      SHOW DATABASES,
      EVENT
      ON *.*
      TO 'maxscale'@'192.0.2.1';
   

Checking MaxScale Status

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

   $ sudo maxctrl show maxscale
   
   ┌──────────────┬──────────────────────────────────────────────────────────────────────┐
   │ Version      │ 2.5.9                                                              │
   ├──────────────┼──────────────────────────────────────────────────────────────────────┤
   │ Commit       │ 61b8bbf7f63c38ca9c408674e66f3627a0b2192e                             │
   ├──────────────┼──────────────────────────────────────────────────────────────────────┤
   │ Started At   │ Fri, 03 Jan 2020 18:05:18 GMT                                        │
   ├──────────────┼──────────────────────────────────────────────────────────────────────┤
   │ Activated At │ Fri, 03 Jan 2020 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                                │
   │              │ }                                                                    │
   └──────────────┴──────────────────────────────────────────────────────────────────────┘
   

Testing Load Balancing

In a MariaDB Replication deployment, each MariaDB Enterprise Server has the server_id system variable set to a unique value. You can see what server you are connected to by querying this variable:

SELECT @@server_id AS "Server ID";

+-----------+
| Server ID |
+-----------+
|         2 |
+-----------+

We can also use this system variable to test the Read/Write Split Router's load balancing:

1. Connect to the MaxScale instance using MariaDB Client:

   $ mariadb --host=maxscale \
      --port=3306 \
      --user=myuser \
      --password
   

2. Create a test table:

   CREATE TABLE test.readwrite_test (
      id INT PRIMARY KEY AUTO_INCREMENT,
      write_id INT
   );
   

3. Insert the value of the the server_id system variable to the table using the INSERT statement:

   INSERT INTO test.readwrite_test (write_id) VALUES (@@server_id);
   INSERT INTO test.readwrite_test (write_id) VALUES (@@server_id);
   INSERT INTO test.readwrite_test (write_id) VALUES (@@server_id);
   

4. Read the newly-inserted rows while querying the value of the the server_id system variable again using the SELECT statement:

   SELECT readwrite.id AS "Primary Key",
      current.id AS "Current Server ID",
      readwrite.write_id AS "Stored Server ID"
   FROM (
      SELECT @@server_id AS id
   ) AS current
   INNER JOIN (
      SELECT id, write_id
      FROM test.readwrite_test
   ) AS readwrite;
   

   The results will show that the primary server's server_id value was written to the table during the INSERT statement, but the replica server's value was read during the SELECT statement:

   +-------------+-------------------+------------------+
   | Primary Key | Current Server ID | Stored Server ID |
   +-------------+-------------------+------------------+
   |           1 |                 2 |                1 |
   |           2 |                 2 |                1 |
   |           3 |                 2 |                1 |
   +-------------+-------------------+------------------+