Configuring MariaDB MaxScale for MySQL Read/Write Splitting

MaxScale, an open‑source database middleware from MariaDB, provides plug‑in architecture for functions such as monitoring, high availability, read/write splitting, and firewall. Its read/write splitting works by routing reads to replicas only when their replication lag stays within a configurable threshold, otherwise directing queries to the primary.

The deployment topology consists of one MaxScale node (node4) and a MySQL cluster with one master (node1) and two semi‑synchronous slaves (node2, node3). The following table shows hostnames, IPs, and roles:

Hostname

IP

Role

node4

10.186.63.88

MaxScale

node1

10.186.61.191

MySQL Master

node2

10.186.61.192

MySQL Slave

node3

10.186.63.64

MySQL Slave

After setting up a one‑master‑two‑slave semi‑synchronous replication, verify the topology with:

## 一主两从
mysql> show slave hosts;
## 半同步复制
mysql> show global status like 'Rpl_semi_sync_master_clients';

Create a MaxScale user and a monitoring user in MySQL and grant the necessary privileges:

mysql> CREATE USER 'maxscale'@'%' IDENTIFIED BY '123';
mysql> GRANT SELECT ON mysql.* TO 'maxscale'@'%';
mysql> GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';

mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY '123';
mysql> GRANT REPLICATION CLIENT ON *.* TO 'monitor'@'%';

Install MaxScale and its dependencies:

yum -y install libcurl libaio openssl gnutls libatomic
wget https://dlm.mariadb.com/1864578/MaxScale/6.1.4/bintar/rhel/7/x86_64/maxscale-6.1.4.rhel.7.tar.gz
groupadd maxscale
useradd -g maxscale maxscale
mkdir /data/maxscale
tar xf maxscale-6.1.4.rhel.7.tar.gz -C /data/maxscale --strip-components=1
chown maxscale.maxscale /data/maxscale/ -R
echo "export PATH=$PATH:/data/maxscale/bin/" >> /etc/profile
source /etc/profile
maxscale --version

Encrypt the passwords for the MaxScale and monitor users using maxkeys and maxpasswd :

# /data/maxscale/bin/maxkeys /data/maxscale/var/lib/maxscale
# /data/maxscale/bin/maxpasswd /data/maxscale/var/lib/maxscale/ 123

Optionally generate a self‑signed certificate for the Web GUI:

# mkdir /data/maxscale/ssl
openssl req -x509 -nodes -days 36500 -newkey rsa:2048 -keyout /data/maxscale/ssl/mariadb.key -out /data/maxscale/ssl/mariadb.crt

Configure MaxScale in /data/maxscale/etc/maxscale.cnf with server definitions, a replication monitor, a read‑write service using the readwritesplit router, and a listener on port 33060:

# vim /data/maxscale/etc/maxscale.cnf
[maxscale]
threads=auto

[dbserv1]
type=server
address=10.186.61.191
port=3306
protocol=MariaDBBackend

[dbserv2]
type=server
address=10.186.61.192
port=3306
protocol=MariaDBBackend

[dbserv3]
type=server
address=10.186.63.64
port=3306
protocol=MariaDBBackend

[Replication-Monitor]
type=monitor
module=mariadbmon
servers=dbserv1, dbserv2, dbserv3
user=monitor
password=F270B97B3D4D37BC619EB02304A02C1456F41B05CF2D7E5F37BA48CC7646C2E4
monitor_interval=2000ms

[Read-Write-Service]
type=service
router=readwritesplit
servers=dbserv1, dbserv2, dbserv3
user=maxscale
password=F270B97B3D4D37BC619EB02304A02C1456F41B05CF2D7E5F37BA48CC7646C2E4
max_slave_connections=2
master_accept_reads=false
max_connections=0
max_slave_replication_lag=1s

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

Create a systemd service unit for MaxScale:

# vim /usr/lib/systemd/system/maxscale.service
[Unit]
Description=MariaDB MaxScale Database Proxy
After=network.target

[Service]
Type=forking
Restart=on-abort
PermissionsStartOnly=true
User=maxscale
Group=maxscale
ExecStart=/data/maxscale/bin/maxscale --user=maxscale --basedir=/data/maxscale/ --config=/data/maxscale/etc/maxscale.cnf
TimeoutStartSec=120
LimitNOFILE=65535
StartLimitBurst=0
WatchdogSec=60s
NotifyAccess=all
RestartForceExitStatus=75
Environment=ASAN_OPTIONS=abort_on_error=1

[Install]
WantedBy=multi-user.target

Reload systemd, start MaxScale, and verify that queries are correctly routed between master and slaves:

# systemctl daemon-reload
# systemctl start maxscale
# /data/mysql/base/5.7.25/bin/mysql -umaxscale -p -h 10.186.63.98 -P 33060
mysql> select @@hostname;  -- returns a slave hostname
# maxctrl list servers  -- shows server states and roles

By following these steps, MaxScale provides transparent read/write splitting, ensuring read consistency while balancing load across the MySQL replica set.