This blog post is continuation of series of blog post on Maxscale, Part-1 which provides detailed introductory to Maxscale, Part-2 deals about the operation and administration of Maxscale. In this we will discusses, How Maxscale can be efficiently used for read scaling with mysql replication (Master – Slave).

Replication is a great feature which is having a great journey along with mysql history. With the introduction of row based replication in 5.1 replication capabilities has stabilised and now with the latest 5.7 we can have enhance multi-threaded replication , semi-synchronous replication.

Speaking of great performance improvement in replication, Even today most of the replication slaves are just used for backup and DR purposes this makes the resource and the money spent on the hardware to be wasted or kept idle most of the time.

To make valuable usage of the underlying slave resource, we can have Maxscale as a load balancer to route the read queries by monitoring the underlying state of the slave.

The installation part is already covered in the previous blog post.

We will now discuss about the configuration.

The monitor module used to monitor replication is “mysqlmon” below is the snippet of the monitor configuration.

The monitor module monitors the node and status of replication between the master and slave, Monitor provides the status information to Maxscale core to make decision for query routing.

Now will proceed to configure the router module, The router which is used is “readwritesplit” for efficient splitting and balancing of writes and reads between master and slave server, below is the snippet of the router configuration

The important feature of Maxscale with regards to replication is, we can set the acceptable replication lag for reads, here i have defined the value as ’30’ when the slave lag goes beyond ’30’, the queries will be routed back to the master or the other slave with lag less than 30 secs.

Next we would be defining a listener for router (readwritesplit), listener is nothing but a network end-point (port), through which the application interacts with Queries to and from DB via maxscale.

below is the listener configuration.

Protocol defines the way in which the DB server is being interacted. here we use “MySQLClient”, which is pretty much equivalent to the mysql-client program.

Along with the above configuration the servers information has to be provided in the config as defined in the part-1 of this serial blog.

Restart maxscale after completion of configuration as below.

#service maxscale restart

Now you can connect your application to Maxscale IP ( Load Balancer IP ) on port 3306, as per this configuration for effect usage of the underlying slave, below is the snippet taken from live production server.

As you can see above load is being equally shared between the master and slave, maxscale also supports many administrative and operation tasks like taking node out of replication, without interrupting the application.

Maxscale can also integrate fail-over script like MHA, mysqlfailover,etc for promoting a slave in-case of disaster, which is beyond the scope of this document.

Note : In the above Maxscale is a SPOF ( Single Point Of Failure ). But a High availability solution can be implemented via Keepalived.