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$
1. Global Parameters2. Service3. Listener4. MySQL Monitor5. Maxadmin Configuration
Client <-> Listener <-> Service <-> Galera Nodes
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:~#
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:~#
root@ubuntu:~# maxadmin Unable to connect to MaxScale at /tmp/maxadmin.sock: Connection refused root@ubuntu:~#
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:~#
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 ---------------------+--------------------+-----------------+-------+--------
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)