Jeremy Wohl

I've written an updated, Python version of Alex Williams' mysqlchk_replication.sh, mentioned in his fine post on HAProxy<->MySQL failover and redundancy. He doesn't actually include said script, but I've implemented the slave status bits along with support for those using mysql_multi.

It does the following:

• Reads your my.cnf to extract the relevant UNIX socket for each local daemon's external port, whether a single mysqld or mysql_multi.
• Verifies that masters connect and execute "show slave status", returning zero rows.
• Further verifies slaves by checking Slave_IO_Running == 'Yes', Slave_SQL_Running == 'Yes' and Seconds_Behind_Master < a_parameterized_value.

The script is a self-contained web server and won't require inetd or tcpserver, etc. Change the pertinent variables at the top and start it running on each of your servers, best under a supervisor.

            PORT = 9200
            CONF = '/etc/my.cnf'
            
            USER = 'root'
            PASS = 'myrootpass'

In your HAProxy config, for each relevant block (listen or backend) include httpchk instructions to point to a specific service port and define maximum allowable slave latency. (It's helpful to define the check port [9200, above] in your defaults, somewhere.)

            option httpchk GET /check?port=3310&max_seconds_behind=5

Find it at https://github.com/jeremywohl/ha-tools.