Of Stack

Tutorial on using HAProxy to detect delays in MySQL replication

  • 2020-10-23 20:19:21
  • OfStack

In the MySQL world, HAProxy is often used as a software load balancer. Peter Boroche explained in a past email how to set it up using the percona xtradb cluster (pxc). So it only sends queries to nodes that can be applied. The same approach can be used with the general master-slave setup to read the load and spread it across multiple slave nodes. With MySQL replication, however, another factor comes into play: replication latency. In this case, the Percona xtraDB cluster mentioned and our proposed checking method of returning only "up" or "down" will not work. We will want to rely on its replication delay to adjust one of the weights of the internal Haproxy. That's what we're going to do with HAProxy 1.5 for this article.

Proxy detection for HAProxy


HAProxy 1.5 Run We run a proxy test, which is a test that can be added to a regular health test. 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 an agent? It is a simple procedure that can access an TCP connection on a given port. So, if we want to run the agent on an MySQL server, this requires:

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


We can use a script like this: 


$ 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 issue a connection from port 6789 to an instance of MySQL running on port 3306, run like this: 


$ php agent.php 6789 3306

You also need to specify MySQL users: 


mysql> GRANT REPLICATION CLIENT ON *.* TO 'haproxy'@'127.0.0.1' IDENTIFIED BY 'haproxy_pwd';

After the agent is started, you can check 1 whether it is working properly: 


# 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.

Suppose it is running on the local application server, there are two replicates running (192.168.10.2 and 192.168.10.3), the application's read request is on port 3307, you need to configure 1 front end and 1 back end in HAProxy, like this: 



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 100 check agent-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-check agent-port 6789 inter 1000  rise 1  fall 1 on-marked-down shutdown-sessions

Now that everything is set up, let's look at how HAProxy can be used to dynamically change the relag server, 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 



# 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 

Delay 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

Agent checking is a nice addition to HAProxy 1.5. In the above setup it is simple: for example, if the HAProxy connection agent fails, it will not be marked. It is recommended to maintain regular health checks from 1 to 1 with the agent.

Careful readers (reads) will notice this configuration, and if copied on all nodes, HAProxy will stop sending to readers. This may not be the best solution. But the possible options are to stop the agent and mark the server as UP, use the status socket (socket), or add the primary node as the backup server.

Finally, using pt-ES91en instead of Seconds_Behind_Master from the Percona toolset, you can edit the agent's code and measure the latency of replication.

Related articles: