In one of the projects I have been working on, one requirement was the ability to stop traffic from reaching a MySQL host which has been lagging behind its master for longer than a specific amount of time and then bring it back online once the lag has gone away. Of course, this is all automated and no human intervention is required.
In this scenario, we are using HAProxy as the load balancer, and I will walk you through how to configure an agent so we can use HAProxy httpchk to flag the host as up or down, via systemd socket and then automatically set the host as being down/up when applicable, in HAProxy.
I will be setting up a systemd service (I’m running centos7 hosts) and creating a listening socket in the MySQL host we want to monitor so haproxy can have access to replication status.
Scenario:
master: po-mysql1
slaves: po-mysql2, po-mysql3, po-mysql4
secondary slaves: …