Tutorial on using Haproxy to detect the latency of Mysql replication _mysql

In the MySQL world, Haproxy is often used as a software load balancer. Peter Boros explains how to use the Percona xtradb cluster (PXC) to set it up in a previous message. So it only sends the query to the applicable node. The same method can be used for general master-slave settings to read the load and spread across multiple nodes. However, with MySQL replication, another factor starts to work: replication latency. In this case, the mentioned Percona xtradb cluster and our proposed only return "up" or "down" inspection method is not feasible. We will want to rely on its replication latency to adjust one of the weights of the internal haproxy. That's what we're going to do in this article using Haproxy 1.5.

Agent Detection for Haproxy

Haproxy 1.5 Runs we run an agent check, which is a test that can be added to the regular health check. The benefit of agent detection is that the return value can be ' up ' or ' down ', but it can also be a weight value.

What is an agent? It is a simple process that can access a TCP connection on a given port. So, if we're going to run the agent on a MySQL server, this requires:

• If replication is not running, make sure the service is stopped on Haproxy
• If the replication delay is less than 10s, set weight to 100%
• If the delay is greater than or equal to 10s, less than 50s, set weight to 50%
• Set weight to 5% in other cases

We can use a script like this:

$ less agent.php <!--? PHP//Simple socket server//http://php.net/manual/en/function.stream-socket-server.php $p
ORT = $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 <) {return ' Up 100% ';
  else if ($lag-->= && $lag <) {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 emit from port 6789 to connect to the MySQL instance running on port 3306, run this:

$ php agent.php 6789 3306

You also need to specify the MySQL user:

Mysql> GRANT REPLICATION CLIENT on *.* to ' haproxy ' @ ' 127.0.0.1 ' identified by ' haproxy_pwd ';

After the agent starts, you can check that it is working correctly:

# telnet 127.0.0.1 6789
trying 127.0.0.1 ...
Connected to 127.0.0.1.
Escape character is ' ^] '.
Up 100%
Connection closed by foreign host.

Assuming it runs on the local application server, there are two copies running (192.168.10.2 and 192.168.10.3), the application read request is on port 3307, you need to configure a front-end and back end in Haproxy, like this:

Copy Code code as follows:

Frontend 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 check agent-check agent-port 6789 Inter 1000 Rise 1 fall 1 On-marked-down shutdown -sessions
Server slave2 192.168.10.3 weight check agent-check agent-port 6789 Inter 1000 Rise 1 fall 1 On-marked-down shutdown -sessions

Now that all is ready, let's see how we can use Haproxy to dynamically change the heavy-lag server, as follows:

Copy Code code 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

Time delay 1

Copy Code code as follows:

# 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

Time Delay 2

Copy Code code 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: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

Agent checking in Haproxy 1.5 is a good new feature. It is simple in the above setting: for example, if the Haproxy connection agent fails, it will not be flagged. It is recommended to keep regular health checks along with the agent check.

Careful readers (reads) will notice this configuration, and if replicated on all nodes, Haproxy will stop sending to the reader. This may not be the best solution. However, the possible options are to stop the agent and mark the server as up, use a state socket (socket), or add the primary node as the backup server.

Finally, you can edit the agent's code to measure the delay of replication using the pt-heartbeat substitution Seconds_behind_master for the Percona toolset.