MaiDeveloper

#MySQL#HAProxy#Tutorial

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
Server: 107.150.51.163
HAProxy docker: 172.26.0.9
MySQL docker: 172.26.0.3

Configure HAProxy

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.

Configure MySQL

[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.



Mike Mai
Mike Mai   Brooklyn, New York
I am full-stack web developer, passionate about building world class web applications. Knowledge in designing, coding, testing, and debugging. I love to solve problems.