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.
HAProxy docker: 172.26.0.9
MySQL docker: 172.26.0.3
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,220.127.116.11" 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.