I have two MySQL servers and replication is configured bidirectional. Connections to Database is handled by HAProxy. At any time one mysql server will be the primary(accepts all connections) and the other one will be standby. AS of now, backend pool in haproxy.cfg has below
define frontend and backend servers
server mysqldb_01 X.X.X.1:3306 check
server mysqldb_02 X.X.X.X.2:3306 check backup
But this check is not aware of the slave lag. I wanted to make haproxy slave lag aware so that i am not in a situation wherein haproxy routes db connections to my standby node, which still has got some transactions in relay log to apply.
stumbled upon this blog which explains how we can make use of agent check to make haproxy replication lag aware. https://blog.pythian.com/haproxy-mysql-lag-awareness-via-systemd/
server po-slave1 X.X.X.1:53306 check port 9876 inter 12000 rise 3 fall 3 on-marked-down shutdown-sessions
server po-slave1 X.X.X.2:53306 check port 9876 inter 12000 rise 3 fall 3 on-marked-down shutdown-sessions
Now in this approach, haproxy checks the health of mysql and replication lag through agent running in my mysql hosts.
My worry is what if the agent which is running as service in the DB host crashes or down for some reason. My haproxy now considers that the MySQL service in that host is not available.
Now coming to my question:
Is there a way to do both the health check and agent check for my backend mysql servers and only if both conditions satisfy, let the haproxy mark the server up or down?