"Schema" How to use HAProxy to Set up MySQL Load balancing

How to use HAProxy to Set up MySQL Load balancing Dec 2, MySQL, Scaling, Server optimization Ubuntu, Debian

Prelude

HAProxy is an open source software which can load balance HTTP and TCP servers. In the previous article on HAPROXY we configured load balancing for HTTP and in this one we'll do the same for MySQL. All your MySQL servers has to is configured to perform master-master replication as load balancing involves both reading and writing to all the backends.

The following three droplets would be a used in this article:

Droplet 1-load Balancer
Hostname:haproxy
Os:ubuntu Private ip:10.0.0.100

Droplet 2-node 1
Hostname:mysql-1
Os:debian 7 Private ip:10.0.0.1

Droplet 2-node 2
Hostname:mysql-2
Os:debian 7 Private ip:10.0.0.2

Before proceeding, make sure all MySQL servers is up, running and is properly replicating database writes.

Prepare MySQL Servers

We need to prepare the MySQL servers by creating and the additional users for HAProxy. The first user is used by HAProxy to check the status of a server.

[email protected]# mysql -u root -p -e "INSERT INTO mysql.user (Host,User) values (‘10.0.0.100‘,‘haproxy_check‘); FLUSH PRIVILEGES;"

A MySQL user is needed with root privileges if accessing the MySQL cluster from HAProxy. The default root user on all the servers is allowed to login only locally. While this can was fixed by granting additional privileges to the root user, it's better to has a separate user with root privileges.

[email protected]# mysql -u root -p -e "GRANT ALL PRIVILEGES ON *.* TO ‘haproxy_root‘@‘10.0.0.100‘ IDENTIFIED BY ‘password‘ WITH GRANT OPTION; FLUSH PRIVILEGES"

Replace haproxy_root and password with your own secure values. It is enough to execute these queries on one MySQL master as changes would replicate to others.

Install MySQL Client

MySQL client has to be installed on the HAProxy droplet to test connectivity.

[email protected]# apt-get install mysql-client

Now try executing a query on one of the Masters as the haproxy_root user.

[email protected]# mysql -h 10.0.0.1 -u haproxy_root -p -e "SHOW DATABASES"

This should display a list of MySQL databases.

Installing HAProxy

On the HAProxy server, install the package.

[email protected]# apt-get install haproxy

Enable HAProxy to is started by the Init script.

[email protected]# sed -i "s/ENABLED=0/ENABLED=1/" /etc/default/haproxy

The To check if the-is-done properly execute, the init script of HAProxy without any parameters.

[email protected]:~# service haproxyUsage: /etc/init.d/haproxy {start|stop|reload|restart|status}

Configuring HAProxy

Rename the original configuration file

mv /etc/haproxy/haproxy.cfg{,.original}

Create and edit a new one

nano /etc/haproxy/haproxy.cfg

The first block is the global and Defaults configuration block.

global    log 127.0.0.1 local0 notice    user haproxy    group haproxydefaults    log global    retries 2    timeout connect 3000    timeout server 5000    timeout client 5000

More information on each of the these options is covered in this article. Since we ' ve told HAProxy to send logs messages to 127.0.0.1 we had to configure Rsyslog to listen on it. This have too been covered in the same article under Configure Logging for HAProxy.

Moving to the main configuration part.

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

Unlike HTTP load balancing HAProxy doesn ' t has a specific "mode" for MySQL so we use TCP. We ' ve set HAProxy to listen-only in the loopback address (assuming that application are on the same server) however if your Application resides on a different droplet make it listen on 0.0.0.0 or the private IP address.

We need one more configuration block to see the statistics of the load balancing. This is completely optional and can be omitted if you don't want stats.

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 would make HAProxy listen on port 8080 for HTTP requests and the statistics would be protected with HTTP Basic Authentication. So 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.

[email protected]# mysql -h 127.0.0.1 -u haproxy_root -p -e "SHOW DATABASES"

The "-H" option has a to is present with the loopback IP address. Omitting it or using localhost would make the MySQL client connect to the mysql.sock file which would fail .

Testing Load balancing and Failover

To check if load balancing are working query the server_id variable twice or more.

[email protected]# mysql -h 127.0.0.1 -u haproxy_root -p -e "show variables like ‘server_id‘"+---------------+-------+| Variable_name | Value |+---------------+-------+| server_id     | 1     |+---------------+-------+[email protected]# mysql -h 127.0.0.1 -u haproxy_root -p -e "show variables like ‘server_id‘"+---------------+-------+| Variable_name | Value |+---------------+-------+| server_id     | 2     |+---------------+-------+

This demonstrates Roundrobin load Balancing with equal weights, we'll change the weight for mysql-2 and see the results.

nano /etc/haproxy/haproxy.cfg

server mysql-2 10.0.0.2:3306 check weight 2

Reload to apply the change.

service haproxy reload

Query for the server_id multiple times.

  [email protected]:~# for I in ' SEQ 1 6 ' domysql-h 127.0.0.1-u Haproxy_root-ppassword- E "show variables like ' server_id '" done+---------------+-------+| variable_name | Value |+---------------+-------+| server_id | 1 |+---------------+-------+ +---------------+-------+| variable_name | Value |+---------------+-------+| server_id | 2 |+---------------+-------+ +---------------+-------+| variable_name | Value |+---------------+-------+| server_id | 2 |+---------------+-------+ +---------------+-------+| variable_name | Value |+---------------+-------+| server_id | 1 |+---------------+-------+ +---------------+-------+| variable_name | Value |+---------------+-------+| server_id | 2 |+---------------+-------+ +---------------+-------+| variable_name | Value |+---------------+-------+| server_id | 2 |+---------------+-------+  

Now load balancing works on the ratio of 1:2 with One-thirds of the requests going to MYSQL-1 and two-thirds goin G to Mysql-2.

Fail a MySQL server either by stopping the service

[email protected]# service mysql stop

or bringing the interface down.

[email protected]# ifconfig eth1 down

Try the "Show variables" query now for See the result. The following log entries would indicate when and how HAProxy detected the failure.

tail /var/log/haproxy/haproxy.log

Nov 15 00:08:51 localhost haproxy[1671]: Server mysql-cluster/mysql-1 is DOWN, reason: Layer4 timeout, check duration: 2002ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.

Reducing Failover Interval

When a MySQL server goes is HAProxy takes some time to detect this failure and remove it from the cluster. In this section we'll see how to control this time. First we ll see how to measure this value. One-to-block the MySQL port using iptables for a certain amount of time, then remove the rule and check the log.

[email protected]:~# ifconfig eth1 down &&date &&sleep 20 &&ifconfig eth1 up &&dateFri Nov 15 00:37:09 IST 2013Fri Nov 15 00:37:29 IST 2013

The port 3306 is blocked for the seconds, we'll look at the log file now.

[email protected]:~# tail /var/log/haproxy.logNov 15 16:49:38 localhost haproxy[1275]: Server mysql-cluster/mysql-1 is DOWN, reason: Layer4 connection problem, info: "Connection refused", check duration: 0ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.Nov 15 16:49:56 localhost haproxy[1275]: Server mysql-cluster/mysql-1 is UP, reason: Layer7 check passed, code: 0, info: "5.5.31-0+wheezy1-log", check duration: 1ms. 2 active and 0 backup servers online. 0 sessions requeued, 0 total in queue.

It took 6 seconds to detect a failure (difference between 16:49:38 and 16:49:32) and 4 seconds_to detect that the server C An is reached (difference between 16:49:56 and 16:49:52). This was determined by the server parameters rise, fall and Inter.

The rise parameter sets the number of checks a server must pass to be declared operational. Default is 2.

The Fall parameter sets the number of checks a server must pass to be declared dead. Default is 3.

The inter parameter sets the interval between these checks. Default is milliseconds.

Putting this info together a server must fail 3 continuous checks which is performed at an interval of 2 sec Onds to be considered dead. So in our example above the following would ' ve happened.

16:49:32 - Port 3306 on mysql-1 was blocked16:49:34 - Check - Failed - Failure No. 116:49:36 - Check - Failed - Failure No. 216:49:38 - Check - Failed - Failure No. 3 (server removed and event logged)

The firewall rule was removed.

16:49:52 - Firewall rule removed port 3306 accessible16:49:54 - Check - Passed - Success No. 116:49:56 - Check - Passed - Success No. 2 (server added to cluster and event logged)

The following settings would reduce the test interval to 1 second and also reduce the number of fall tests.

nano /etc/haproxy/haproxy.cfg

server mysql-1 10.0.0.1:3306 check fall 2 inter 1000server mysql-2 10.0.0.2:3306 check fall 2 inter 1000

Sometimes want to flood the private network with too many "test" packets especialy if you have a large amount of MySQL servers. In such cases the Fastinter and Downinter parameters would come handy.

The fastinter parameter sets the interval between checks while a server was transitioning up or down.

The downinter parameter sets the test interval when a server was down.

That explanation might is confusing so we'll see it with an example.

nano /etc/haproxy/haproxy.cfg

server mysql-1 10.0.0.1:3306 check fastinter 1000server mysql-2 10.0.0.2:3306 check fastinter 1000

Since We haven ' t specified the "Inter" parameter it defaults to 2000ms. With this configuration we'll restart HAProxy and do the test again.

[email protected]:~# iptables -A INPUT -p tcp --dport 3306 -j REJECT &&date &&sleep 20 &&iptables -D INPUT -p tcp --dport 3306 -j REJECT &&dateFri Nov 15 17:18:48 IST 2013Fri Nov 15 17:19:08 IST 2013

Check the HAProxy log file.

[email protected]:~# tail /var/log/haproxy.logNov 15 17:18:52 localhost haproxy[1353]: Server mysql-cluster/mysql-1 is DOWN, reason: Layer4 connection problem, info: "Connection refused", check duration: 0ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.Nov 15 17:19:11 localhost haproxy[1353]: Server mysql-cluster/mysql-1 is UP, reason: Layer7 check passed, code: 0, info: "5.5.31-0+wheezy1-log", check duration: 1ms. 2 active and 0 backup servers online. 0 sessions requeued, 0 total in queue.

Now it took only 4 seconds (compared to 6 earlier) to detect a failure and 3 seconds (compared to 4) to detect that the SE RVer is up. Behind The scenes this was what happened.

17:18:48 - Port 3306 blocked17:18:50 - Check - Failed - Failure No. 117:18:51 - Check - Failed - Failure No. 217:18:52 - Check - Failed - Failure No. 3 (server removed and event logged)

And when the port is unblocked.

17:19:08 - Firewall rule removed17:19:10 - Check - Passed - Success No. 117:19:11 - Check - Passed - Success No. 2 (server added to cluster and event logged)

First notice the interval between the Port block event (17:18:48) and the first check (17:18:50), it is 2 seconds (the ' in ter "interval). Then notice the interval between Test 1 <-> Test 2 and Test 2 <-> Test 3, it was only 1 second (the "Fastinter" Interval). The same intervals can be noticed when the server is moved from down to up. So "Fastinter" controls the interval between these checks.

Why is Downinter? When a server had been declared HAProxy continues checking it every 2 seconds (or the interval mentioned in Inter). If you feel your ' re using up unnecessary network resources setting the Downinter to say the "Make HAProxy che" CK A down server is only once in 5 seconds.

Important

The tests we do previously rejected the packets which means when HAProxy initiated a connection by sending a SYN packet T o mysql-1 it received a RST packet (instead of SYN + ACK). This is why the log entry mentioned "Connection refused". The fall, Inter and Fastinter values come into the scene.

Instead if HAProxy didn ' t receive anything after sending SYN the connection times out. In the the addition to the above mentioned parameters the "timeout" duration comes into the scene. This situation can happen if

• Iptables is set to DROP
• The private interface is down
• There is a problem with the private networking infrastructure

Further Reading

Official documentation http://cbonte.github.io/haproxy-dconv/configuration-1.4.html

Submitted By:jesin A Reference: https://www.digitalocean.com/community/tutorials/ How-to-use-haproxy-to-set-up-mysql-load-balancing--3

"Schema" How to use HAProxy to Set up MySQL Load balancing