Make HAProxy 1.5 aware of the delay of MySQL Replication

Make HAProxy 1.5 aware of the delay of MySQL Replication

In the MySQL world, HAProxy is usually used as a software Load balancer. Peter BOLOs explained in his past emails how to use the percona xtradb cluster (pxc) to set it. Therefore, it only sends the nodes that can be queried to the application. The same method can be used for general master-slave settings to read loads and distribute them to multiple slave nodes. However, another factor that may take effect when using MySQL replication is replication latency. In this case, the mentioned Percona xtraDB cluster and the check method we proposed to only return "up" or "down" won't work. We will want to adjust an internal Haproxy weight based on its replication latency. This is what we need to do. In this article, we will use HAProxy 1.5.

HAProxy proxy Detection

HAProxy 1.5 runs a proxy detection, which can be added to a regular health check item. The advantage of proxy detection is that the return value can be 'up' or 'down', but it can also be a weight value.

What is proxy? It is a simple process that can access TCP connections on a given port. Therefore, if we want to run the proxy on a MySQL server, we need:

• If you do not run replication, make sure the service is stopped on HAProxy.
• If the replication latency is less than 10 s, set weight to 100%.
• If the delay is greater than or equal to 10 s and less than 50 s, set weight to 50%
• In other cases, set weight to 5%.

We can use this script:

$ Less agent. php
<! --? Php
// Simple socket server
// See http://php.net/manual/en/function.stream-socket-server.php
$ Port = $ argv [1];
$ Mysql_port = $ argv [2];
$ Mysql = "/usr/bin/mysql ";
$ User = 'haproxy ';
$ Password = 'haproxy _ pwd ';
$ Query = "show slave status ";
Function set_weight ($ lag ){
# Write your own rules here
If ($ lag = 'null '){
Return "down ";
}
Else if ($ lag <10 ){
Return "up 100% ";
}
Else if ($ lag --> = 10 & $ lag <60 ){
Return "up 50% ";
}
Else
Return "up 5% ";
}
Set_time_limit (0 );
$ Socket = stream_socket_server ("tcp: // 127.0.0.1: $ port", $ errno, $ errstr );
If (! $ Socket ){
Echo "$ errstr ($ errno)
N ";
} Else {
While ($ conn = stream_socket_accept ($ socket, 9999999999999 )){
$ Cmd = "$ mysql-h127.0.0.1-u $ user-p $ password-P $ mysql_port-Ee" $ query "| grep Seconds_Behind_Master | cut-d ': '-f2 | tr-d ''";
Exec ("$ cmd", $ lag );
$ Weight = set_weight ($ lag [0]);
Unset ($ lag );
Fputs ($ conn, $ weight );
Fclose ($ conn );
}
Fclose ($ socket );
}
?>

If you want the script to send a connection from Port 6789 to the MySQL instance running on port 3306, run the following command:

$ Php agent. php 6789 3306

You also need to specify the MySQL user:

Mysql> grant replication client on *. * TO 'haproxy' @ '2017. 0.0.1 'identified by 'haproxy _ pwd ';

After the proxy is started, you can check whether it is running properly:

# Telnet 127.0.0.1 6789
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
Group 100%
Connection closed by foreign host.

Assume that the application is running on the local application server and two copies are running (192.168.10.2 and 192.168.10.3). The application's read requests are on port 3307. You need to configure a front-end and backend in HAProxy, like this:
 
Front D read_only-front
Bind *: 3307
Mode tcp
Option tcplog
Log global
Default_backend read_only-back
Backend read_only-back
Mode tcp
Balance leastconn
Server slave1 192.168.10.2 weight 100 check agent-port 6789 inter 1000 rise 1 fall 1 on-marked-down shutdown-sessions
Server slave2 192.168.10.3 weight 100 check agent-port 6789 inter 1000 rise 1 fall 1 on-marked-down shutdown-sessions

Now all the preparations are ready. Now let's see how to use HAProxy to dynamically change the heavy-lag server. The Code is as follows:
 
# Slave1
$ Mysql-Ee "show slave status" | grep Seconds_Behind_Master
Seconds_Behind_Master: 0
# Slave2
$ Mysql-Ee "show slave status" | grep Seconds_Behind_Master
Seconds_Behind_Master: 0
# HAProxy
$ Echo "show stat" | socat stdio/run/haproxy/admin. sock | cut-d ','-f1, 2,18, 19
# Pxname, svname, status, weight
Read_only-front, FRONTEND, OPEN,
Read_only-back, slave1, UP, 100
Read_only-back, slave2, UP, 100
Read_only-back, BACKEND, UP, 200

Latency 1

# Slave1
$ Mysql-Ee "show slave status" | grep Seconds_Behind_Master
Seconds_Behind_Master: 25
# Slave2
$ Mysql-Ee "show slave status" | grep Seconds_Behind_Master
Seconds_Behind_Master: 0
# Echo "show stat" | socat stdio/run/haproxy/admin. sock | cut-d ','-f1, 2,18, 19
# Pxname, svname, status, weight
Read_only-front, FRONTEND, OPEN,
Read_only-back, slave1, UP, 50
Read_only-back, slave2, UP, 100
Read_only-back, BACKEND, UP, 150

Latency 2

# Slave1
$ Mysql-Ee "show slave status" | grep Seconds_Behind_Master
Seconds_Behind_Master: 0
# Slave2
$ Mysql-Ee "show slave status" | grep Seconds_Behind_Master
Seconds_Behind_Master: NULL
# Echo "show stat" | socat stdio/run/haproxy/admin. sock | cut-d ','-f1, 2,18, 19
# Pxname, svname, status, weight
Read_only-front, FRONTEND, OPEN,
Read_only-back, slave1, UP, 100
Read_only-back, slave2, DOWN (agent), 100
Read_only-back, BACKEND, UP, 100

Conclusion

In HAProxy 1.5, proxy check is a good new feature. The preceding settings are simple: for example, if HAProxy fails to connect to the proxy, it will not be marked. We recommend that you check the health status together with the agent.

Careful readers will notice this configuration. If it is replicated on all nodes, HAProxy will stop sending it to the reader. This may not be the best solution. However, the possible options are: Stop the proxy and mark the server as UP, use the status socket (socket) or add the master node as the backup server.

Finally, replace Seconds_Behind_Master with pt-heartbeat in the Percona toolset. You can edit the proxy code to measure replication latency.

Haproxy + Keepalived build Weblogic high-availability server Load balancer Cluster

Keepalived + HAProxy configure high-availability Load Balancing

Haproxy + Keepalived + Apache configuration notes in CentOS 6.3

Haproxy + KeepAlived WEB Cluster on CentOS 6

Haproxy + Keepalived build high-availability Load Balancing

For details about HAproxy, click here
HAproxy: click here

This article permanently updates the link address: