How to setup MaxScale with MariaDB Galera Cluster

This post is just following up my previous blog post which describes how to setup 3-nodes MariaDB Galera Cluster with MySQL-Sandbox on single server.

 

Today, I’ll try to explain how we can setup MariaDB MaxScale over the Galera Cluster. Before I move ahead, I would like to explain about MaxScale little bit.

MariaDB MaxScale is a database proxy that enables horizontal database scaling while maintaining a fast response to client applications. You can implement MaxScale on either MySQL Replication or Galera cluster. With MySQL Replication, you can either use Read/Write Splitting or Connection routing and same with Galera Cluster. You can get more information here about this product.

https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-14/setting-up-maxscale/

https://mariadb.com/products/mariadb-maxscale

So here, I’m going to setup MaxScale with Read/Write Splitting on MariaDB Galera Cluster. I’m using Ubuntu and 3-node Galera Cluster setup which is running on single server.

MariaDB [(none)]> show global status like 'wsrep_cluster_size%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
1 row in set (0.00 sec)

Download and Install MaxScale  :  https://mariadb.com/downloads/maxscale

nilnandan@ubuntu:~/MariaDB$ wget https://downloads.mariadb.com/MaxScale/2.0.5/ubuntu/dists/xenial/main/binary-amd64/maxscale-2.0.5-1.ubuntu.xenial.x86_64.deb
... 
2017-03-27 11:01:29 (1.86 MB/s) - ‘maxscale-2.0.5-1.ubuntu.xenial.x86_64.deb’ saved [3739198/3739198]
nilnandan@ubuntu:~/MariaDB$ sudo dpkg -i maxscale-2.0.5-1.ubuntu.xenial.x86_64.deb 
Selecting previously unselected package maxscale.
(Reading database ... 216604 files and directories currently installed.)
Preparing to unpack maxscale-2.0.5-1.ubuntu.xenial.x86_64.deb ...
Unpacking maxscale (2.0.5) ...
Setting up maxscale (2.0.5) ...
Processing triggers for man-db (2.7.5-1) ...
Processing triggers for libc-bin (2.23-0ubuntu7) ...
nilnandan@ubuntu:~/MariaDB$
Configure Maxscale :  
To make MaxScale work, first we have to configure maxscale.cnf file. There are 5 sections in this,
1. Global Parameters
2. Service
3. Listener
4. MySQL Monitor
5. Maxadmin Configuration
Here, You can set all MySQL related global parameters with 1st section.
“A service represents the database service that MaxScale offers to the clients”
“A listener defines a port and protocol pair that is used to listen for connections to a service.”
“MySQL Monitor modules are used by MaxScale to internally monitor the state of the backend databases in order to set the server flags for each of those servers”
“Maxadmin is client utility which connects with MaxScale, run commands and check status of cluster”
You can get more information here about this sections. But it should be like this,
Client  <-> Listener <-> Service <-> Galera Nodes
For the service and MySQL Monitor, we have to create separate MySQL users with permissions. But here, I’m using one MySQL user maxscale with all permissions as this is testing server. If you want to check what permissions are needed then you can visit this page.   Setting up MaxScale
Here is my configuration file:
root@ubuntu:~# cat /etc/maxscale.cnf
# Global parameters
[maxscale]
threads=4
# Service definitions
[Read-Write Service]
type=service
router=readwritesplit
servers=dbnode1, dbnode2, dbnode3
user=maxscale
passwd=msandbox
# Listener definitions for the services
[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006
socket=/tmp/galeramaster.sock
# Server definitions
[dbnode1]
type=server
address=127.0.0.1
port=19222
socket=/tmp/mysql_sandbox19222.sock
protocol=MySQLBackend
priority=1
[dbnode2]
type=server
address=127.0.0.1
port=19223
socket=/tmp/mysql_sandbox19223.sock
protocol=MySQLBackend
priority=2
[dbnode3]
type=server
address=127.0.0.1
port=19224
socket=/tmp/mysql_sandbox19224.sock
protocol=MySQLBackend
priority=3
# Monitor for the servers
[Galera Monitor]
type=monitor
module=galeramon
servers=dbnode1, dbnode2, dbnode3
user=maxscale
passwd=msandbox
monitor_interval=10000
use_priority=true
# This service enables the use of the MaxAdmin interface
# MaxScale administration guide:
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Reference/MaxAdmin.md
[MaxAdmin Service]
type=service
router=cli
[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
port=6603
socket=default
root@ubuntu:~#
For more details about this settings, you can visit this page : MaxScale Read/Write Splitting With Galera Cluster
Start MaxScale from root with command : service maxscale start
root@ubuntu:~# ps -ef | grep maxscale
maxscale 10532 1 0 14:57 ? 00:00:00 /usr/bin/maxscale --user=maxscale
root 10547 10405 0 14:57 pts/21 00:00:00 grep --color=auto maxscale
root@ubuntu:~#
If any error occurs, you can check here:  /var/log/maxscale 
When you’ll start maxscale with service command, by default it will use –user=maxscale. This is linux user which will not have any /home dir.Due to that, maxadmin command will not work and give error like
root@ubuntu:~# maxadmin
Unable to connect to MaxScale at /tmp/maxadmin.sock: Connection refused
root@ubuntu:~#
To make it simple, I would suggest to change maxscale.service file and update user to root rather than maxscale
root@ubuntu:~# cat /lib/systemd/system/maxscale.service
...
[Service]
...
ExecStartPre=/usr/bin/install -d /var/run/maxscale -o maxscale -g maxscale
#ExecStart=/usr/bin/maxscale --user=maxscale
ExecStart=/usr/bin/maxscale --user=root
...
root@ubuntu:~#
after that you’ll be able to login and run commands with maxadmin and check the health/status of cluster nodes.
root@ubuntu:~# maxadmin 
MaxScale> 
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status 
-------------------+-----------------+-------+-------------+--------------------
dbnode1 | 127.0.0.1 | 19222 | 0 | Master, Synced, Running
dbnode2 | 127.0.0.1 | 19223 | 0 | Slave, Synced, Running
dbnode3 | 127.0.0.1 | 19224 | 0 | Slave, Synced, Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale>
MaxScale> list services
Services.
--------------------------+----------------------+--------+---------------
Service Name | Router Module | #Users | Total Sessions
--------------------------+----------------------+--------+---------------
Read-Write Service | readwritesplit | 2 | 2
MaxAdmin Service | cli | 3 | 4
--------------------------+----------------------+--------+---------------
MaxScale> list listeners
Listeners.
---------------------+--------------------+-----------------+-------+--------
Service Name | Protocol Module | Address | Port | State
---------------------+--------------------+-----------------+-------+--------
Read-Write Service | MySQLClient | * | 4006 | Running
Read-Write Service | MySQLClient | /tmp/galeramaster.sock | 0 | Running
MaxAdmin Service | maxscaled | * | 6603 | Running
MaxAdmin Service | maxscaled | default | 0 | Running
---------------------+--------------------+-----------------+-------+--------
In “list servers” output, you can see that MaxAdmin defines Master or Slave servers in status column. This thing you can manage by priority. You can see in configuration file that I’ve set “use_priority=true” in [Galera Monitor] sections and set “priority=1,2 or 3” in [dbnode] sections. Priority=1 will be master here.
How to connect to Galera through MaxScale:
root@ubuntu:# mysql -umaxscale -p --socket=/tmp/galeramaster.sock
Enter password: 
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 12642
Server version: 10.0.0 2.0.5-maxscale MariaDB Server
..
MySQL [(none)]>
MySQL [(none)]> show global status like 'wsrep_cluster_size%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
1 row in set (0.01 sec)
So this is how you can setup MaxScale over Galera Cluster. There many more things which needs to be explained related to MaxScale like how to monitor the status, how we can put any node in maintenance mode etc but as this become already very long post, I’ll try to explain these things in my next blog post.

Leave a Reply

Your email address will not be published. Required fields are marked *