Forum Discussion

Clayton's avatar
Clayton
Icon for Altocumulus rankAltocumulus
Jun 08, 2021

MySQL Monitor (or EAV) with SSL?

We are migrating to MySQL, and I would like to use our F5 LTM to load balancer the ( MySql Enterprsie, 3-node) cluster. I have upgraded to Bigip 15.1.3 (from 14.x.x) per Support as version 15 should have a working MySQL Monitor. However, it still times out connecting. A simple telnet test from the LTM CLI connects to the mysql port, so networking is not an issue.

 

I then had the idea of copying up the client SSL certs and seeing if the local mysql client on the LTM would connect, but I get an error about "ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory"

 

So the built-in MySQL Monitor can't use SSL (what century is this anyway?), and the on-board mysql client apparently has issues with SSL as well.

 

Any way to do this? I'd rather not give my developers a reason to set up some kind of HA Proxy junk. I don't need more infrastructure to maintain.

9 Replies

  • I will check the articles, thank you!

     

    Yes, for the internal client I meant the one in /bin on the F5 itself:

    root@my-f5-ltm:Active:Standalone] ~ # which mysql

    /bin/mysql

     

     

  • And so nobody else has to flounder like I did, here is the script.

    1. Import into File Management -> External Monitor Program File List
    2. Create health monitor -> External
    3. Set Interval and Timeout ( I am using 30/91)
    4. Select the script you created in step 1
    5. Add the following Variables
      1. Name: query , Value: select @@read_only;
      2. Name: result, Value: 0
      3. Name: pass, Value: <password here>

    Value of 0 (zero) indicates Read/Write (Master) node, value of 1 (one) indicates read-only node.

    Here is the script:

    #!/bin/bash
     
    # Name of the pidfile
    pidfile="/var/run/$MONITOR_NAME.$1..$2.pid"
     
    # Send signal to the process group to kill our former self and any children
    # as external monitors are run with SIGHUP blocked
    if [ -f $pidfile ]
    then
       kill -9 -`cat $pidfile` > /dev/null 2>&1
    fi
     
    echo "$$" > $pidfile
     
    # Remove the IPv6/IPv4 compatibility prefix
    node_addr=`echo $1 | sed 's/::ffff://'`
     
    # pass, query, and result are passed as variables
    # from the Health Monitor definition
     
    # Search the data received for the expected expression.
    # Replace "<monitoring username here>" with the user you wish to log into mysql with
    /bin/mysql -sN -u <monitoring username here> -p${pass} -h ${node_addr} -P $2 -e "${query}" 2> /dev/null | grep -E -i ${result} > /dev/null
     
    status=$?
    if [ $status -eq 0 ]
    then
    # Remove the pidfile before the script echoes anything to stdout and is killed by bigd
        rm -f $pidfile
        echo "up"
    fi
     
    # Remove the pidfile before the script ends
    rm -f $pidfile
    • If even setting the Alias Ip addrress and alias port on the monitor to the VIP did not help then yes the external monitor is the best option thanks for sharing the script with the comunity.

  • You may test MSSSQL as it has ssl support as per https://support.f5.com/csp/article/K40700745 .

     

     

     

    Also enable debug as the error could be something else so check the monitor enable debug and read about mysql monitor issues in the bug tracker by searching with "mysql monitor":

     

     

    https://support.f5.com/csp/article/K00170525

     

    https://support.f5.com/csp/article/K12531

     

     

    https://support.f5.com/csp/bug-tracker

  • It seems my instances need to allow more connections or something, after turning off the mysql monitor and leaving it for a while, I am able to connect using the mysql client found on the Bigip CLI. Copied up the CA, client cert, and client key, and can connect that way ..

     

    Trying out the example from the MSSQL link you provided (using a dummy virtual server, and have the monitor point at that)

     

     

    • Yes, you can test using F5 VIP as for MSSQL but for the mysql monitor so it can work with TLS traffic as per K40700745 .

       

       

       

      Also see this article https://support.f5.com/csp/article/K15242 to maybe send smaller number of probes per connecton.

       

       

       

      By F5 internal mysql client you mean "/usr/bin/mysql " that is used primary fo accessing the F5 local mysql database for AVR/ASM/APM statistics? If this is the case you can try making externel linux bash script monitor by using the sql client but it will be hard work and I hope the other previous idea with an F5 VIP helps. But just in case I will share this as examples that can be rewriten:

       

       

       

      https://support.f5.com/csp/article/K71282813

       

       

      https://devcentral.f5.com/s/articles/ltm-external-monitors-the-basics

       

       

      https://support.f5.com/csp/article/K31435017

  • Ok, well.. The monitor using the Virtual as an alias doesn't work if the pool is down. If I add a simple ICMP monitor, and tell the pool to be up if at least 1 monitor succeeds, then the Virtual Server works. Problem is, it negates my use of the pool.

     

    Only one of the mysql servers (3-node Enterprise cluster) will have the Master (read/write) role. I need one pool for read/write, and another for just the read-only nodes.

     

    And manually updating the pools to enable/disable nodes is kind not what I want :)

     

    SO - I will try using an external script, since I am able to use /bin/mysql to connect on the F5 CLI.

    • Check iApp MySQL-PROXY if it helps as it can select the pool with the server that does the writting:

       

       

      https://clouddocs.f5.com/api/irules/MySQL-Proxy.html

       

       

       

      Also don't forget that you can attach monitors under specific pool members not only under the pool, so you can create 3 VIP servers with just one pool member server that the 3 monitors under the main pool but each attached to different pool member will use for TLS/SSL compatibility.

       

       

       

      Other than that the External EAV monitors are the option.

  • Looks like an external script did the trick. Still could not get the built-in mysql monitor to work, even with using the Virtual Server as the Alias.