Servers haProxy
Mihalis Tsoukalos teaches you how to install and set up HAProxy to load balance a MySQL Replica Set, all before lunchtime.
Mihalis Tsoukalos tires of endless maths and tinkers with his web proxy instead.
HAProxy, which stands for High Availability Proxy, is a Load Balancing reverse proxy for TCP and HTTP applications. This tutorial will use HAProxy in combination with MySQL to illustrate its load balancing capabilities. However, HAProxy can also be used with other TCP servers such as Apache and Nginx.
Please do make backup copies of every file you make changes to, so you’ll be able to get back to your initial configuration more easily. The main reason for this is that software that deals with web sites and database servers can make them inaccessible to clients when configured incorrectly, and this will provide a quick fix.
Getting and installing
Installing HAProxy on Debian or Ubuntu Linux machines is as simple as executing the following command with root privileges: # apt-get install haproxy
Then, you can find out the version of HAProxy you are using as follows: # haproxy -v HA-Proxy version 1.5.8 2014/10/31
Copyright 2000-2014 Willy Tarreau <w@1wt.eu>
Please bear in mind that stable Debian distributions tend to install older versions of packages because they are more secure and stable despite the fact that they have fewer features. At the time of writing this the latest stable HAProxy versions are 1.7.2, 1.6.11 and 1.5.19. If your main concern is stability use either version 1.6.x or version 1.5.x as version 1.7.x of HAProxy is pretty new.
Configuring
The main HAProxy configuration directory is /etc/haproxy, which contains the following: # ls -l /etc/haproxy total 8 drwxr-xr-x 2 root root 4096 Jan 21 19:16 errors -rw-r--r-- 1 root root 1129 Jul 14 2015 haproxy.cfg # ls /etc/haproxy/errors 400.http 403.http 408.http 500.http 502.http 503.http 504.http
The main configuration file of HAProxy is /etc/haproxy/ haproxy.cfg. The error directory contains various error messages related to given HTTP status codes. The screenshot above shows the contents of /etc/haproxy/ haproxy.cfg and /etc/haproxy/errors/400.http.
Get on with it…
With the basic install done, let’s take a look at basic function before continuing with illustrating how to use HAProxy to load balance multiple MySQL instances. You can start HAProxy by: # service haproxy start
The following output proves that HAProxy is up and running successfully:
# ps ax | grep -i haproxy 17747 ? Ss 0:00 /usr/sbin/haproxy-systemd-wrapper -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid 17749 ? S 0:00 /usr/sbin/haproxy -f /etc/haproxy/ haproxy.cfg -p /run/haproxy.pid -Ds 17751 ? Ss 0:00 /usr/sbin/haproxy -f /etc/haproxy/ haproxy.cfg -p /run/haproxy.pid -Ds
The good thing is that the previous output shows various useful things about your running HAProxy instance, including the full path of the configuration file used and the location where you can find its process ID (/ run/haproxy.pid).
Similarly, you can stop HAProxy from running: # service haproxy stop
It is now time to load balance two MySQL instances. For the purposes of this tutorial, the two MySQL instances will be on the same network using two test machines because you cannot try such things on production servers! The IP address of the first machine is 192.168.1.200 (MyA) whereas the IP address of the second machine will be 192.168.1.4 (MyB). The IP address of the HAProxy machine is 10.0.2.15. As you can see it belongs to a different network but this should not be a problem as long as the two networks can communicate with each other successfully. To get a better understanding of what is going on, have in mind that HAProxy runs on a Virtual Machine on MyB.
Please note that you might need to enable network access to both MySQL servers, which will allow them to listen for TCP/IP connections – this feature is disabled by default for security reasons. The following diff output shows the change you need to make to the MySQL configuration file in order to enable remote TCP/IP connections: $ diff my.cnf my.cnf.orig 31d30 < bind-address = 192.168.1.200
Both MySQL processes listen to the default port number of MySQL which is 3306. Next, you will need to grant the required permissions that enable remote access to the selected users, which in this case is just root: mysql> GRANT ALL ON *.* TO ‘root'@'192.168.1.200’ IDENTIFIED BY ‘mypass’; Query OK, 0 rows affected, 1 warning (0.00 sec) You should also execute the next command on MyB: mysql> GRANT ALL ON *.* TO ‘root'@'192.168.1.4’ IDENTIFIED BY ‘mypass’; Query OK, 0 rows affected, 1 warning (0.00 sec)
Do not forget to restart both MySQL servers after making these changes. You can make sure that both MySQL instances are up and running and can be accessed from the network by trying to connect to each one of them from a third machine, as follows: $ ifconfig | grep “inet addr” | head -1
inet addr:10.0.2.15 Bcast:10.0.2.255 Mask:255.255.255.0 $ mysql -u root -h 192.168.1.4 -p $ mysql -u root -h 192.168.1.200 -p
You will also need to set up MySQL Master-Master Replication. Talking about that is beyond the scope of this tutorial, but just make sure that the replication is working properly before continuing; the easiest way to do so is by creating a new table to a new database on one of the two
MySQL instances and see whether this will get replicated to the other database.
Lastly, you will have to create two more MySQL users on each one of the two MySQL databases in order to allow HAProxy to monitor them – the good thing is that if the replication works as expected, you will only have to execute the following commands once: $ mysql -u root -p mysql> INSERT INTO mysql.user (Host,User) values ('10.0.2.15’,‘haproxy_check'); FLUSH PRIVILEGES; mysql> GRANT ALL PRIVILEGES ON *.* TO ‘haproxy_ root'@'10.0.2.15’ IDENTIFIED BY ‘password’ WITH GRANT OPTION; FLUSH PRIVILEGES;
Please note that you will need to use the IP address of the Linux machine that runs the HAProxy server. Executing the same commands using the IP addresses of the two MySQL servers might make your life easier, especially if you are using any virtual machines, so go ahead and run them as well.
Then, you should make some changes to the configuration file of HAProxy. Please have in mind that the original haproxy.
cfg file is saved as haproxy.cfg.orig. The following output shows the changes to the original haproxy.cfg file using the
diff command line utility: $ diff haproxy.cfg haproxy.cfg.orig 36,44d35 < < listen mysql-setup < bind 127.0.0.1:3306 < mode tcp < option mysql-check user haproxy_check < balance roundrobin < server mysql-1 192.168.1.4:3306 check < server mysql-2 192.168.1.200:3306 check
Although using IP addresses instead of machine names might make you life a little more difficult, it saves HAProxy from having to resolve the machine names.
Please make sure that the machine that runs HAProxy does not run MySQL in any way because the next commands will try to connect to the local copy of MySQL instead: $ mysql -h 127.0.0.1 -u haproxy_root -p -e “show variables like ‘server_id'” Enter password: +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id |2 | +---------------+-------+ $ mysql -h 127.0.0.1 -u haproxy_root -p -e “show variables like ‘server_id'” Enter password: +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id |1 | +---------------+-------+
As each MySQL member of a replica set has a different server_id, the previous output tells us that you can connect to both MySQL servers of the replica set while querying localhost, which is the machine that runs HAProxy. So, from now on when you need a MySQL server you can only give the IP address and the port number of the HAProxy server instead of giving the IP address of one of the two MySQL servers, and HAProxy takes care of the rest. This is a transparent way of using services without the world knowing what is going on behind the scenes.
Log files
On an Ubuntu system, the log messages of HAProxy can be found at /var/log/haproxy.log. The kind of log entries you are going to find inside /var/log/haproxy.log will be similar to the following: Jan 31 23:19:08 LTTng haproxy[936]: Server mysql-setup/ mysql-1 is DOWN, reason: Layer4 connection problem, info: “General socket error (Network is unreachable)”, check duration: 0ms. Jan 31 23:21:57 LTTng haproxy[936]: 127.0.0.1:52778 [31/ Jan/2017:23:21:57.856] stats stats/<STATS> 0/0/0/0/0 200 1346 - - LR-- 1/1/0/0/0 0/0 “GET /hastats;csv HTTP/1.1”
The screenshot on the previous page shows more entries from /var/log/haproxy.log to get a better understanding of the kind of information found in /var/log/haproxy.log. The general idea is that you should keep an eye on the related log files when you are learning a new piece of software because log files give you a better understanding of what is happening behind the scenes.
HAProxy offers a large number of metrics that allow you to monitor the way it works as well as its performance. Those metrics can be divided in three main categories: frontend, backend and health metrics. Frontend metrics collect information about clients whereas backend metrics collect data about the availability and the status of the backend machines. The last kind of metrics informs you about the status of the HAProxy setup.
Frontend metrics include information such as HTTP requests per second (req_rate), number of request errors (ereq) and number of bytes sent (bout). Backend metrics include ways to measure the average response time (rtime) and the number of requests that are not in a queue (qcur).
The most secure way to get the HAProxy metrics is using UNIX sockets. On an Ubuntu Linux system, the default
HAProxy configuration has support for the desired Unix socket, so you will not need to do anything else. To make sure that the Unix socket (/ run/haproxy/admin.sock) has been created and is usable, you can do the following: $ sudo nc -U /run/haproxy/admin.sock prompt > show info ...
The image on the previous page also shows the kind of output you can get from the Unix socket using the netcat command line utility. Keep in mind that if you are going to do any serious work with HAProxy, you will need to learn how to interpret its metrics.
Monitoring page
HAProxy offers a monitoring page where you can learn more about the HAProxy operation in a graphical way. This should be the first place to visit when you are having problems with HAProxy. However, this page is not enabled by default. In order to enable it, you should add the next block to the HAProxy configuration file: listen stats bind *:8080 stats enable stats hide-version stats realm Haproxy\ Statistics stats uri /hastens stats auth user:password The last line allows you to define valid username and password combinations whereas the uri definition defines the URI of the statistics page. The bind value defines the port number the statistics page will listen to. For changes to take effect, you will need to restart HAProxy.
The image on the previous page (top) shows the monitoring page of HAProxy, which usually listens to port number 6427 on the localhost address, which means that this page is not accessible from the internet by default. However, the presented configuration uses port number 8080. As you can see from the web page, you can also get the output in CSV format, which can be very handy because it allows you to easily store the values of the metrics on a database server. If you are using HAProxy, you will most likely need to enable the monitoring page.
Please note that HAproxy provides its own web server for displaying the monitoring page.
Algorithms
HAProxy can use a plethora of algorithms to decide which server is going to be selected when load balancing multiple servers. The algorithms that can be used include Round Robin, an algorithm that selects the server with the lowest number of connections and another one based on the IP address of the client. The last method makes sure that each IP address always connects to the same server. Additionally, you can assign a weight to each server that defines how often each server will be selected compared to the other servers.
You should not deal with HAProxy algorithms unless you have performance problems. A handy new feature of the latest HAProxy version is that if the configuration file given using the -f switch is a directory, all files found in the directory will be loaded in alphabetical order. Additionally, it has support for OpenSSL 1.1.0, performs better than version 1.6.x and includes many bug fixes.
You can find more information about HAProxy at http://www.haproxy.org whereas you can find the full documentation of all the HAProxy versions at: http://cbonte.github.io/haproxy-dconv.