There are different reasons for adding a load balancer between your application and your database. If you have high traffic (and you want to balance the traffic between different database nodes) or you want to use the load balancer as a single endpoint (so in case of failover, this load balancer will cope with this issue sending the traffic to the available/healthy node.) It could also be that you want to use different ports to write and read data from your database.
In this blog, we will show you how to install and configure it manually, and how ClusterControl can help you in this task. For this example, we will use a MariaDB replication cluster with 1 master and 1 slave node, and CentOS8 as the operating system.
How to Install MaxScale
We will assume you have your MariaDB database up and running, and also a machine (virtual or physical) to install MaxScale. We recommend you use a different host, so in case of master failure, MaxScale can failover to the slave node, otherwise, MaxScale can’t take any action if the server where it is running goes down.
$ curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash [info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo [info] Adding trusted package signing keys... [info] Successfully added trusted package signing keys
Now, install the MaxScale package:
$ yum install maxscale
Now you have your MaxScale node installed, before starting, you need to configure it.
How to Configure MaxScale
As MaxScale perform tasks like authentication, monitoring, and more, you need to create a database user with some specific privileges:
MariaDB [(none)]> CREATE USER 'maxscaleuser'@'%' IDENTIFIED BY 'maxscalepassword'; MariaDB [(none)]> GRANT SELECT ON mysql.user TO 'maxscaleuser'@'%'; MariaDB [(none)]> GRANT SELECT ON mysql.db TO 'maxscaleuser'@'%'; MariaDB [(none)]> GRANT SELECT ON mysql.tables_priv TO 'maxscaleuser'@'%'; MariaDB [(none)]> GRANT SELECT ON mysql.roles_mapping TO 'maxscaleuser'@'%'; MariaDB [(none)]> GRANT SHOW DATABASES ON *.* TO 'maxscaleuser'@'%'; MariaDB [(none)]> GRANT REPLICATION CLIENT on *.* to 'maxscaleuser'@'%';
Keep in mind that MariaDB versions 10.2.2 to 10.2.10 also require:
MariaDB [(none)]> GRANT SELECT ON mysql.* TO 'maxscaleuser'@'%';
Now you have the database user ready, let’s see the configuration files. When you install MaxScale, the file maxscale.cnf will be created under /etc/. There are several variables and different ways to configure it, so let’s see an example:
$ cat /etc/maxscale.cnf # Global parameters [maxscale] threads = auto log_augmentation = 1 ms_timestamp = 1 syslog = 1 # Server definitions [server1] type=server address=192.168.100.126 port=3306 protocol=MariaDBBackend [server2] type=server address=192.168.100.127 port=3306 protocol=MariaDBBackend # Monitor for the servers [MariaDB-Monitor] type=monitor module=mariadbmon servers=server1,server2 user=maxscaleuser password=maxscalepassword monitor_interval=2000 # Service definitions [Read-Only-Service] type=service router=readconnroute servers=server2 user=maxscaleuser password=maxscalepassword router_options=slave [Read-Write-Service] type=service router=readwritesplit servers=server1 user=maxscaleuser password=maxscalepassword # Listener definitions for the services [Read-Only-Listener] type=listener service=Read-Only-Service protocol=MariaDBClient port=4008 [Read-Write-Listener] type=listener service=Read-Write-Service protocol=MariaDBClient port=4006
In this configuration, we have 2 database nodes, 192.168.100.126 (Master) and 192.168.100.127 (Slave), as you can see in the Servers Definition section.
We have also 2 different services, one for read-only, where there is the slave node, and another one for read-write where there is the master node.
Finally, we have 2 listeners, one for each service. The read-only listener, listening in the port 4008, and the read-write one, listening in the port 4006.
This is a basic configuration file. If you need something more specific you can follow the official MariaDB documentation.
Now you are ready to start it, so just run:
$ systemctl start maxscale.service
And check it:
$ maxctrl list services
$ maxctrl list servers
Now let’s test the connection. For this, you can try to access your database using the MaxScale IP address and the port that you want to test. In our case, the traffic on the port 4006 should be sent to server1, and the traffic on the port 4008 to server2.
$ mysql -h 192.168.100.128 -umaxscaleuser -pmaxscalepassword -P4006 -e 'SELECT @@hostname;' +------------+ | @@hostname | +------------+ | server1 | +------------+ $ mysql -h 192.168.100.128 -umaxscaleuser -pmaxscalepassword -P4008 -e 'SELECT @@hostname;' +------------+ | @@hostname | +------------+ | server2 | +------------+
How to Deploy MaxScale with ClusterControl
Go to ClusterControl -> Select the MariaDB cluster -> Cluster Actions -> Add Load Balancer -> MaxScale.
Here you can deploy a new MaxScale node or you can also import an existing one. If you are deploying it, you need to add the IP Address or Hostname, the admin and user MaxScale credentials, amount of threads, and ports (write and read-only). You can also specify which database node you want to add to the MaxScale configuration.
You can monitor the task in the ClusterControl Activity section. When it finishes, you will have a new MaxScale node in your MariaDB cluster.
And running the MaxScale commands from the ClusterControl UI without the need of accessing the server via SSH.
It looks easier than deploying it manually, right?
The installation is easy, but the configuration and usage could be difficult if it is something new for you. In that case, you can use ClusterControl to deploy, configure, and manage it in an easier way.