HAProxy community

HAProxy - health check and agent check for backend MySQL servers

Hi All

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

frontend mysql-in
bind *:3306
default_backend mysqlservers

backend mysqlservers
mode tcp
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/

listen mysql-secondary-slaves
bind :53307
option httpchk
option tcplog
mode tcp
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?

Thanks