7.12.5.4. Configuring HAProxy using the native MySQL Check

  • BRIDGE MODE - ❌ NOT RECOMMENDED

    When the Connector is in Bridge mode, using the native test mysql-check will generate errors in the connector logs (as well as in the MySQL error log with MySQL versions 8+).

  • PROXY MODE -

    When the Connector is in Proxy mode, using the native test mysql-check can be done in two ways, with a MySQL user name specified (supported), and without a MySQL user name (deprecated).

    With a User Name - ✅ RECOMMENDED - If you specify a username, the check consists of sending two MySQL packets, one Client Authentication packet, and one QUIT packet, to correctly close the MySQL session. HAProxy then parses the MySQL Handshake Initialization packet and/or Error packet. The Tungsten Connector will attempt to connect to a data source in the cluster - if none is available, the check will fail and HAProxy will consider this Tungsten Connector as a dead backend. This makes it a useful test which does not produce errors or aborted connects on the server.

    Without a user Name - ❌ NOT RECOMMENDED - If you do not specify a username (this config is deprecated by HAProxy), the check only consists of parsing the MySQL Handshake Initialization packet or Error packet. The Connector will be considered as up as long as it is running, even if no database backend is available. Not recommended due to deprecation.

Warning

The mysql-check method (with or without a user specified) does NOT check for database presence nor database consistency. To do this, we must use an external check script (via systemd, see Section 7.12.5.2, “Configuring HAProxy with a Check Script Via systemd”)

Here is a practical example for deploying the HAProxy's native mysql-check option.

#---------------------------------------------------------------------
# backend
#---------------------------------------------------------------------
listen connector
    bind *:3306
    mode tcp
    option tcpka   # enables keep-alive both on client and server side
    balance roundrobin
    option mysql-check user haproxy post-41
    server conn1 db1:3306 check inter 5s rise 1 fall 1 weight 3 maxconn 5000
    server conn2 db2:3306 check inter 5s rise 1 fall 1 weight 3 maxconn 5000
    server conn3 db3:3306 check inter 5s rise 1 fall 1 weight 3 maxconn 5000

Important

This solution requires adding a user to MySQL with an empty password.

An example of using mysql-check without a username is not provided here because this functionality is deprecated by HAProxy and not recommended.

The first example uses a % to specify a source IP wildcard during user creation:

mysql> CREATE USER 'haproxy'@'%' IDENTIFIED WITH mysql_native_password;

For deployments that require MySQL to specify an IP address, which address you choose depends on the Connector mode you are using - Bridge or Proxy.

  • Bridge Mode

    In Bridge mode, the IP address seen by the database server will be that of the calling application server (ip_of_application_host).

    The below example shows the user creation for Bridge mode deployments that require MySQL to specify an IP address:

    mysql> CREATE USER 'haproxy'@'{ip_of_application_host}' IDENTIFIED WITH mysql_native_password;
  • Proxy Mode

    In Proxy mode, the IP address seen by the database server will be that of the Tungsten Connector host (ip_of_connector_host).

    This example shows the user creation for Proxy mode deployments that require MySQL to specify an IP address:

    mysql> CREATE USER 'haproxy'@'{ip_of_connector_host}' IDENTIFIED WITH mysql_native_password;

    In proxy Mode, the user.map file must also be updated as follows:

    haproxy - cluster_name_here

    In the above example, replace cluster_name_here with the name of the actual cluster service. Note that a hyphen (-) is used to indicate a blank password.

    Note

    MySQL 8 will let you create a user with an empty password provided you don’t have a password‐validation plugin rejecting it. By default, MySQL 8 does not install or enforce the validate_password plugin, so an empty‐string password is allowed. If you see an error when doing this, it usually means validate_password (or another authentication plugin) has been enabled and is enforcing a minimum length or complexity—disabling or tuning that plugin will then allow you to use '' as the password.