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 Parameters2. Service3. Listener4. MySQL Monitor5. 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.