Home

Setting up maxscale

This entry is more or less a kind of checklist for myself, because it’s difficult to find a decent all-encompassing guide for this.

Install MariaDB servers and set up replication

The Master DB

Lets begin with aligning the master. In /etc/mysql/ -> depending on the distro, you might have mariadb.conf.d or other similar folders.

Let’s add or adjust a few important variables:

[mysqld]
server-id=1
gtid_strict_mode=1
log_bin = /var/lib/mysql/bin-mariadb.log
expire_logs_days=8
sync_binlog = 1
binlog_format = row
log_slave_updates = 1

A few important details on these values:

Restart mariadb 

sudo systemctl restart mariadb

Verify that the settings take effect (e.g. isn’t overwritten by something else in the .cnf file hierarchy)

e.g.

MariaDB [(none)]> SHOW VARIABLES LIKE "%gtid_strict%";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| gtid_strict_mode | ON    |
+------------------+-------+
1 row in set (0.001 sec)

A few things to checK before proceeding:

Finally, login to the Database as root, and set this variable, because maxscale will not do failovers without it:

CHANGE MASTER TO MASTER_USE_GTID = SLAVE_POS;

If you forget this step, messages about it in the logs of /var/log/maxscale/maxscale.log should show up:

2025-04-18 20:41:51.874   error  : [mariadbmon] (check_cluster_operations_support): The backend cluster does not support failover/switc
hover due to the following reason(s):\nReplica connection from server2 to [192.168.2.24]:3309 is not using gtid-replication.\n

Create a database user that handles the actual replication process:

CREATE USER 'repl'@'%' IDENTIFIED BY '<PASSWORD>';

GRANT REPLICATION SLAVE ON *.* to 'repl'@'%';

Finally, dump the DB so that it can be used as a starting point on the replica:

mysqldump --all-databases --master-data=2 --single-transaction -u root > dump.sql

The Replica DB

That brings us to the end of setting up the master DB. Now, make the same edits to my.cnf as was done for the master DB and restart the DB. Then, load the dumped data into the DB.

mysql -u root -p < /tmp/dump.sql

NOTE: If you’re already running maxscale at THIS stage for some reason, you’ll have divergent GTIDs and will need to fix it manually:

The steps to fix it: Go to master DB, and check the binlog position, make sure that NO writes are coming in to Master DB during this time:

MariaDB [(none)]> SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| bin-mariadb.000001 |   407858 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

Keep this info at hand, and return to the replica DB, and execute:

STOP SLAVE;
RESET MASTER;
CHANGE MASTER TO MASTER_LOG_FILE='bin-mariadb.000001', MASTER_LOG_POS=407858;
CHANGE MASTER TO MASTER_USE_GTID=slave_pos;
START SLAVE;

Note that the usage of RESET MASTER is generally not recommended, but this is OK when setting up a new server. Do not do this on any important server with real data in it.

If you’ve already got important data you DONT want to risk losing, then just undo the transaction(s) that caused the GTID divergence, and set MASTER_LOG_FILE and MASTER_LOG_POS WITHOUT doing RESET MASTER.

With that caveat, if you’ve no such problems, just proceed with actually setting up the replication:

CHANGE MASTER TO
MASTER_HOST='<MASTER IP>',
MASTER_PORT=3309,
MASTER_USER='repl',
MASTER_PASSWORD='<PASSWORD>',
MASTER_USE_GTID=slave_pos;

START SLAVE;

Again, remembering to set the MASTER_USE_GTID, which is needed for maxscale failovers.

Optionally, the replica server can be set to read only, so that writes will fail with an error instead of causing GTID divergence:

SET GLOBAL super_read_only=ON:

This way, not even root can make changes.

Maxscale

Before jumping into configuring maxscale, we’ll need to create a maxscale user on the Master DB (the user’s GRANTS should replicate down to the replica DBs)

CREATE USER 'maxscaleuser'@'%' IDENTIFIED BY '<PASSWORD>';

GRANT SELECT ON mysql.user TO 'maxscaleuser'@'%';

GRANT SELECT ON mysql.db TO 'maxscaleuser'@'%';

GRANT SELECT ON mysql.tables_priv TO 'maxscaleuser'@'%';

GRANT SELECT ON mysql.roles_mapping TO 'maxscaleuser'@'%';

GRANT SHOW DATABASES ON *.* TO 'maxscaleuser'@'%';

GRANT REPLICATION CLIENT on *.* to 'maxscaleuser'@'%';

GRANT SELECT ON mysql.* TO 'maxscaleuser'@'%';

GRANT SELECT ON mysql.columns_priv TO 'maxscaleuser'@'%';

GRANT SELECT ON mysql.procs_priv TO 'maxscaleuser'@'%';

GRANT SELECT ON mysql.proxies_priv TO 'maxscaleuser'@'%';

GRANT SLAVE MONITOR ON *.* to 'maxscaleuser'@'%';

GRANT SUPER ON *.* to 'maxscaleuser'@'%';

GRANT READ_ONLY ADMIN ON *.* TO 'maxscaleuser'@'%';

GRANT RELOAD ON *.* TO 'maxscaleuser'@'%';

Maxscale uses a .cnf format that works in a similar way to the one of mariadb. The file should be located at /etc/maxscale.cnf.

Some global params

Start by defining a few key global settings:

[maxscale]
threads=1
ms_timestamp = 1
syslog = 1

There are many other potentially useful variables, governing further logging settings, changing default directories and so on.

Server definitions

This is the section for defining what MariaDB servers should maxscale be aware of and potentially route traffic to. The server-id values specified in each DB server’s my.cnf must match the internal server identity that maxscale uses here. 

[server1]
type=server
address=192.168.2.24
port=3309
protocol=MariaDBBackend

[server2]
type=server
address=192.168.2.99
port=3306
protocol=MariaDBBackend

The only noteworthy param here is protocol, which specifies the communication protocol used between maxscale and the backend DBs.

Monitor for the servers

This keeps maxscale aware of server statuses, whether a server is down, whether GTID’s have diverged, etc. The monitor is essential for features like automated failover and rejoin.

[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2
user=maxscaleuser
password=
replication_user=repl
replication_password=
auto_failover=true
auto_rejoin=true
enforce_simple_topology=true
enforce_read_only_slaves=1
monitor_interval=2000ms

Service definitions

Here we can define what type of services maxscale should provide. In this example with two servers, we configure:

In more complex environments, it might make sense to have multiple different read services, to separate out different types of queries to different groups of replicas. For example, one might want to restrict heavy dwh analytical queries away to dedicated servers, to reduce resource usage on replicas used by applications.

[Read-Only-Service]
type=service
router=readconnroute
servers=server2
user=maxscaleuser
password=
router_options=slave,running

[Read-Write-Service]
type=service
router=readwritesplit
servers=server1
user=maxscaleuser
password=

Listener definitions

The listeners define what port goes with which of the previously defined service. So rather than connecting to databases directly, users and applications will connect to these maxscale ports, and maxscale takes things from there.

[Read-Only-Listener]
type=listener
service=Read-Only-Service
protocol=MariaDBClient
port=4008

[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=4006

Start maxscale

systemctl start maxscale

Test it all out!

If everything works as expected, maxscale should report a simple master-replica set up.

Use the following command to see that status of your servers via maxscale:

maxctrl list servers

┌─────────┬──────────────┬──────┬─────────────┬─────────────────┬─────────┬─────────────────┐
│ Server  │ Address      │ Port │ Connections │ State           │ GTID    │ Monitor         │
├─────────┼──────────────┼──────┼─────────────┼─────────────────┼─────────┼─────────────────┤
│ server1 │ 192.168.2.24 │ 3309 │ 0           │ Master, Running │ 0-1-800 │ MariaDB-Monitor │
├─────────┼──────────────┼──────┼─────────────┼─────────────────┼─────────┼─────────────────┤
│ server2 │ 192.168.2.99 │ 3306 │ 0           │ Slave, Running  │ 0-1-800 │ MariaDB-Monitor │
└─────────┴──────────────┴──────┴─────────────┴─────────────────┴─────────┴─────────────────┘

To test the failover, stop the mariadb service on server1 and wait for the magic to happen:

systemctl stop mariadb

Now run the maxscale command again to observe

maxctrl list servers

It should show something like

┌─────────┬──────────────┬──────┬─────────────┬─────────────────┬─────────┬─────────────────┐
│ Server  │ Address      │ Port │ Connections │ State           │ GTID    │ Monitor         │
├─────────┼──────────────┼──────┼─────────────┼─────────────────┼─────────┼─────────────────┤
│ server1 │ 192.168.2.24 │ 3309 │ 0           │ Down            │ 0-1-800 │ MariaDB-Monitor │
├─────────┼──────────────┼──────┼─────────────┼─────────────────┼─────────┼─────────────────┤
│ server2 │ 192.168.2.99 │ 3306 │ 0           │ Master, Running │ 0-1-800 │ MariaDB-Monitor │
└─────────┴──────────────┴──────┴─────────────┴─────────────────┴─────────┴─────────────────┘

Restart the downed server:

systemctl start mariadb

and then check again with

maxctrl list servers

and it should show the old master rejoining as a replica

┌─────────┬──────────────┬──────┬─────────────┬─────────────────┬─────────┬─────────────────┐
│ Server  │ Address      │ Port │ Connections │ State           │ GTID    │ Monitor         │
├─────────┼──────────────┼──────┼─────────────┼─────────────────┼─────────┼─────────────────┤
│ server1 │ 192.168.2.24 │ 3309 │ 0           │ Slave, Running  │ 0-1-800 │ MariaDB-Monitor │
├─────────┼──────────────┼──────┼─────────────┼─────────────────┼─────────┼─────────────────┤
│ server2 │ 192.168.2.99 │ 3306 │ 0           │ Master, Running │ 0-1-800 │ MariaDB-Monitor │
└─────────┴──────────────┴──────┴─────────────┴─────────────────┴─────────┴─────────────────┘

We can make the switch back by downing server2:

┌─────────┬──────────────┬──────┬─────────────┬─────────────────┬─────────┬─────────────────┐
│ Server  │ Address      │ Port │ Connections │ State           │ GTID    │ Monitor         │
├─────────┼──────────────┼──────┼─────────────┼─────────────────┼─────────┼─────────────────┤
│ server1 │ 192.168.2.24 │ 3309 │ 0           │ Master, Running │ 0-1-800 │ MariaDB-Monitor │
├─────────┼──────────────┼──────┼─────────────┼─────────────────┼─────────┼─────────────────┤
│ server2 │ 192.168.2.99 │ 3306 │ 0           │ Down            │ 0-1-800 │ MariaDB-Monitor │
└─────────┴──────────────┴──────┴─────────────┴─────────────────┴─────────┴─────────────────┘

and then starting it up again, bringing us back to our original state:

┌─────────┬──────────────┬──────┬─────────────┬─────────────────┬─────────┬─────────────────┐
│ Server  │ Address      │ Port │ Connections │ State           │ GTID    │ Monitor         │
├─────────┼──────────────┼──────┼─────────────┼─────────────────┼─────────┼─────────────────┤
│ server1 │ 192.168.2.24 │ 3309 │ 0           │ Master, Running │ 0-1-800 │ MariaDB-Monitor │
├─────────┼──────────────┼──────┼─────────────┼─────────────────┼─────────┼─────────────────┤
│ server2 │ 192.168.2.99 │ 3306 │ 0           │ Slave, Running  │ 0-1-800 │ MariaDB-Monitor │
└─────────┴──────────────┴──────┴─────────────┴─────────────────┴─────────┴─────────────────┘

And that concludes this writeup.