HAProxy setup for mysql load balancing

Available Servers
—————–
HAProxy: 10.0.0.100
mysql1 : 10.0.0.1
mysql2 : 10.0.0.2

Before setup, make sure that mysql servers are working fine and database replication is proper.

HAProxy Installation
——————–
Create 2 mysql users in any of the mysql server for HAProxy, one for checking status and another one with root privileges.

$ mysql -u root -p -e “INSERT INTO mysql.user (Host,User) values (‘10.0.0.100′,’haproxy_check’); FLUSH PRIVILEGES;”
$ mysql -u root -p -e “GRANT ALL PRIVILEGES ON *.* TO ‘haproxy_root’@’10.0.0.100’ IDENTIFIED BY ‘password’ WITH GRANT OPTION; FLUSH PRIVILEGES”

Install mysql client in haproxy server.

$ apt-get install mysql-client

Try accessing databases from haproxy server.

$ mysql -h 10.0.0.1 -u haproxy_root -p -e “SHOW DATABASES”

Install haproxy package.

$ apt-get install haproxy

Enable haproxy startup in init script.

$ sed -i “s/ENABLED=0/ENABLED=1/” /etc/default/haproxy

HAProxy Configuration
———————

Add the below entry to configuration file.

$ vi /etc/haproxy/haproxy.cfg

global
log 127.0.0.1 local0 notice
user haproxy
group haproxy

defaults
log global
retries 2
timeout connect 3000
timeout server 5000
timeout client 5000

listen mysql-cluster
bind 127.0.0.1:3306
mode tcp
option mysql-check user haproxy_check
balance roundrobin
server mysql-1 10.0.0.1:3306 check
server mysql-2 10.0.0.2:3306 check

If you need to see statistics of load balancing, add the below entry too to the configuration.

listen 0.0.0.0:8080
mode http
stats enable
stats uri /
stats realm Strictly\ Private
stats auth A_Username:YourPassword
stats auth Another_User:passwd

Replace the usernames and passwords in “stats auth”. This will make HAProxy listen on port 8080 for HTTP requests and the statistics will be protected with HTTP Basic Authentication. So you can access stats at http://<Public IP of Load Balancer>:8080/

Once you’re done configuring start the HAProxy service.

$ service haproxy start

Use the mysql client to query HAProxy.

$ mysql -h 127.0.0.1 -u haproxy_root -p -e “SHOW DATABASES”

Ref : https://www.digitalocean.com/community/tutorials/how-to-use-haproxy-to-set-up-mysql-load-balancing–3

Leave a Reply

Your email address will not be published. Required fields are marked *