Maxscale Sql scaling with mariadb Cluster on Centos in Docker

So scaling sql server has now bean easy with mariadb maxscale. Here i uses it to connect to my mariadb cluster and setup two new servers. One is a loadbalanser and onw is a read/write splitter

1.First prep your mariadb servers with som users for you maxscale

CREATE user 'maxscale'@'%' identified by 'maxscaleW222';
GRANT SELECT ON mysql.user TO 'maxscale'@'%';
GRANT SELECT ON mysql.db TO 'maxscale'@'%';
GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';

 

2. Install maxscale on your centos host

 

echo -e "[mariadb] \nname = MariaDB \nbaseurl = http://yum.mariadb.org/10.1/centos7-amd64 \ngpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB \ngpgcheck=1 \n "  >  /etc/yum.repos.d/MariaDB.repo

yum install MariaDB-server MariaDB-devel -y

rpm -i http://downloads.mariadb.com/enterprise/64mr-1jgt/generate/10.1/mariadb-enterprise-repository.rpm

yum install maxscale -y

 

3. Setup you maxscale to work

 

[maxscale]
threads=4
 
[Galera Monitor]
type=monitor
module=galeramon
servers=sql1,sql2,sql3
user=maxscale
passwd=maxscaleW222
monitor_interval=10000
disable_master_failback=1
 
[qla]
type=filter
module=qlafilter
options=/tmp/QueryLog
 
[fetch]
type=filter
module=regexfilter
match=fetch
replace=select
 
[RW]
type=service
router=readwritesplit
servers=sql1,sql2,sql3
user=maxscale
passwd=maxscaleW222
max_slave_connections=100%
router_options=slave_selection_criteria=LEAST_CURRENT_OPERATIONS
 
[RR]
type=service
router=readconnroute
router_options=synced
servers=sql1,sql2,sql3
user=maxscale
passwd=maxscaleW222
 
[Debug Interface]
type=service
router=debugcli

[CLI]
type=service
router=cli
 
[RWlistener]
type=listener
service=RW
protocol=MySQLClient
port=3307
 
[RRlistener]
type=listener
service=RR
protocol=MySQLClient
port=3308
 
[Debug Listener]
type=listener
service=Debug Interface
protocol=telnetd
address=127.0.0.1
port=4442
 
[CLI Listener]
type=listener
service=CLI
protocol=maxscaled
address=127.0.0.1
port=6603
 
 
[sql1]
type=server
address=mariadb-cluster-master
port=3306
protocol=MySQLBackend
 
[sql2]
type=server
address=mariadb-cluster-slave_1
port=3306
protocol=MySQLBackend
 
[sql3]
type=server
address=mariadb-cluster-slave_2
port=3306
protocol=MySQLBackend

So the servername here are matching the names from my docker-compose and my post about mariadb-cluster setup.

So you need to replace maria-cluster-slave_1 and so on with you mariadb nodes.

 

4. Dockerfile and Docker-compose

Dockerfile

from fareoffice/base

MAINTAINER Fareoffice

LABEL name="Mattias Hemmingsson MaxScale Server"
LABEL vendor="Lifeandshell"

RUN echo -e "[mariadb] \nname = MariaDB \nbaseurl = http://yum.mariadb.org/10.1/centos7-amd64 \ngpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB \ngpgcheck=1 \n " > /etc/yum.repos.d/MariaDB.repo

RUN yum install MariaDB-server MariaDB-devel -y
RUN yum -y install make gcc gcc-c++ ncurses-devel bison glibc-devel openssl-devel libaio libaio-devel telnet

#Install maxscale
RUN rpm -i http://downloads.mariadb.com/enterprise/64mr-1jgt/generate/10.1/mariadb-enterprise-repository.rpm
ADD config/maxscale.cnf /etc/maxscale.cnf
RUN yum install maxscale -y

CMD maxscale -d

docker-compose.yml

 

maxscale:
 build: maxscale/
 links:
 - mariadb-cluster-master
 - mariadb-cluster-slave
mariadb-cluster-master:
 build: mariadb-cluster-master/
mariadb-cluster-slave:
 build: mariadb-cluster-slave/
 links:
 - mariadb-cluster-master

To verify that you maxscale is wokring run the command

 

maxadmin (mariadb is the defult password)

Maxscale>run show servers