How To Use HAProxy to Set Up MySQL Load Balancing?
Doing MySQL load balancing on HAProxy has several advantages; such as ip address restriction. Configuring MySQL to use HAProxy proxy protocol is a ticky part. It needs to know HAProxy ip address.
Scenario
listen mysql
bind *:3307
mode tcp
timeout connect 10s
timeout client 480m
timeout server 480m
acl network_allowed src 127.0.0.1
tcp-request connection reject if !network_allowed
server mysql1 172.26.0.3:3306 send-proxy check
timeout client 480m and timeout server 480m will keep the connection open for 8 hours which is default for MySQL. Otherwise , it will close immediately. This should also set in MySQL configuration file (wait_timeout , interactive_timeout ).
acl network_allowed src 127.0.0.1 this will only allow specified ip address(es).tcp-request connection reject if !network_allowed deny the connection if the ip address does not allow.
send-proxy this tells HAProxy that this is a proxy connection.
[mysqld]
proxy_protocol_networks="172.26.0.9,107.150.51.163"
bind-address="172.26.0.3"
wait_timeout=28800
interactive_timeout=28800
proxy_protocol_networks set all the ip address on proxy server.bind-address must bind address to an ip address if we going to do a proxy connection.
wait_timeout and interactive_timeout in seconds. 28800 seconds equals to (480 minutes / 8 hours). Similiar settings on HAProxy.
