Overview

Maxscale is proxy that extends the high availability of MariaDB. MariaDB MaxScale is an advanced database proxy, firewall, and query router. MaxScale is a component of MariaDB Platform. MaxScale is compatible with MariaDB Enterprise Server, MariaDB ColumnStore, and MariaDB Community Server. Let's imagine the following configuration:

192.168.10.101 node1

192.168.10.102 node2

192.168.10.103 node3

192.168.10.104 node4

The Cluster will be configured as follows:

Maxscale:

node1

Galera Cluster:

node2

node3

node4

Setting up

To set up we have to install the packages and configure the repositories. Do that on all nodes in the cluster. In our case on all 4:

Repo Config

Install MariaDB Server

After that we can install the Maria DB

Install MariaDB

Install MariaDB Client

Install MariaDB Client

Install MariaDB Client

Setup Galera Cluster

To setup the Galera Cluster, we have to edit the configuration for MariaDB (/etc/my.cnf.d/server.cnf) on: node2, node3 and node4. That configuration is used only for testing, for production, there will be WAY MORE configuration parameters:

Galera Configuration

The description of these parameters you can check below:

wsrep_on – This is a session level flag to indicate of the operations we are performing will be replicated, much like how the sql_log_bin setting controls if statements are written to the binlog when using MariaDB Replication. The default of this is ON, but we set it anyway, to be safe.

wsrep_provider – This points to the location of the Galera library. Although MariaDB is set up to use Galera from scratch, you still have to point to the Galera library. This is installed as part of the MariaDB-Server installation above.

wsrep_cluster_address – This is where we define the nodes in the cluster, in general you don’t need to list all nodes and new nodes can be added later to a running cluster, but in this case we know what the cluster looks like so we set it up here.

binlog_format – Although the binlog, in terms of the actual binlog files, isn’t used by Galera, the binlog facility is, and for Galera to work you have to run with row format in the binlog.

default_storage_engine – Again, this is the default value, but just to be safe let’s set up MariaDB Server to explicitly use the InnoDB Storage Engine, which is the only engine supported by Galera.

innodb_autoinc_lock_mode – This setting defines how the InnoDB Storage Engine generates values for auto_increment columns. Using mode 2 here is very important for Galera to work properly. In short, mode 2 cause much less locking during auto_increment generation and hence doesn’t interfere with other locking. Values other than 2 can cause deadlocking and other issues with Galera.

After that we can finally bootstrat the cluster ONLY on the first node:

Bootstrap the cluter

As you can see we have ONLY one member on the first node. Once that is done start the mysql on the other nodes:

Start the MariaDB on the other Nodes

After that, we can see that the members of the cluster increased:

Cluster members

Prepare users for MaxScale

First we need to set up a user that MariaDB MaxScale use to attach to the cluster to get authentication data. On 192.168.10.182, using the MariaDB command line as the database root user:

Create user & Grant permissions

Setup MaxScale

The configuration of MaxScale is stored in: “/etc/maxscale.cnf”

Maxscale Config

It took my REALLY LONG TIME, to figure out the configurations as they change from release to release. Like every open source software….

After that we can start the maxscale:

Start MaxScale

Test the Environment

In our test:

We will connect to the listener on the maxscale server

Check the server (e.g. server 3)

Shutdown the MariaDB on the server from step 2)

Check the server from our connection (we should be moved)

Session on node1

Now from the session, connected on node3, shutdown the MariaDB:

Session on node 3

Once we shut down the instance, our session should be moved to running server, so let's check it. From the first session, without reconnected, re-query the same data.

Session on node1

After, we can also connect to the GUI, which should listen on: http://192.168.10.101:8989

GUI

The default credentials are: Username: admin Password: mariadb