Hi,

I had this discussion with haproxy somewhere that I can't find now.

The haproxy mechanism is a really simple, particularly dumb one.  This should be fixed on haproxy side, but doing that is non-trivial.  And really should rather just implement a check plugin mechanism, which the haproxy guys are basically saying but then they can't vet and control the code, and I'm like, but that's not your problem, SEGVs and stuff in plugins really aught not to be your problem.  Nor should crazy delays, and those plugins can be executed in separate threads, or even processes regardless (ie, execute a sub-process so that you eliminate the risk of other people's code affecting stability of haproxy itself - similar to squid's authenticator and acl processors).  For example, a process can be spawned that outputs to it's stdout simple lines like "up" or "down", and gets passed the server address and port.  If the process dies, server is assumed down.  On process startup server is assumed down.  But alas ... this isn't done, and we haven't sat down to write and submit that code yet.

What we already had in place was a real-time mysql monitoring tool that executes a pre-defined query at configurable (down to microseconds) interval.  And if that fails, it declares mysql as down.  And then keep trying to reconnect, once connected, execute the query, and declare as up.  Well, would start in the down state.

We'd then supply external scripts to be executed on up/down.  We originally used this to merely start or stop a ucarp instance, and this would then promote and demote mysql instances as needed.  We later realised this can do more interesting things, like modify haproxy configs (use sed to comment/uncomment server lines and reload haproxy), or update ipsets for firewalls (monitor is on the mariadb server itself in order to only have one monitor rather than from every haproxy "client") so that connections are *rejected* from the clients resulting in haproxy declaring dead.  That way you merely need to monitor for tcp connectability from haproxy side, reducing the haproxy test to a simple "is tcp available".  We've since implemented similar strategies for a bunch of other protocols (like smtp, imap, and pop3 as well).

I'm not in a position to share the full code base, but in short, I can share the main working loop (and it looks similar for other protocols):

    while (true) {
        last_error = 0;
        while (!mysql_real_connect(&mysql, mysql_host, mysql_user, mysql_pass, NULL, mysql_connport, NULL, 0)) {
            if (last_error != mysql_errno(&mysql)) {
                last_error = mysql_errno(&mysql);
                log(LOG_NOTICE, "mysql_real_connect: %s", mysql_error(&mysql));
            }   
            usleep(check_interval);
        }   

        if (!check_mysql_query(&mysql))
            continue;

        log(LOG_NOTICE, "MySQL up.");
        notify("start");

        while (!mysql_ping(&mysql) && check_mysql_query(&mysql))
            usleep(check_interval);

        log(LOG_NOTICE, "MySQL down.");
        notify("stop");
    }   

This was originally written for MySQL, long before we switched to MariaDB, but since it works against both servers we just never bothered to change variable or process names :).

Kind regards,
Jaco

Hello.

mariadb 11.8.1

I want to use haproxy with check option.

Using in haproxy `option mysql-check` works but maridb logs:

[Warning] Aborted connection 24971 to db: 'unconnected' user: 'unauthenticated' host: '10.0.1.165' (This connection closed normally without authentication)

This is confusing as I don't know that this is regular check from haproxy, or someone tried to connect.

So I want to use in haproxy `option mysql-check user haproxy-check post-41`.

But this fails with error: [Warning] Access denied for user 'haproxy-check'@'10.0.1.164' (using password: NO)

and haproxy shows the mariadb server as DOWN.

Because in haproxy there is no way to specify a password for the user to connect to the instance of mariadb, I tried to allow passwordless access for the haproxy-check user from the specified network and minimum grants. But this does not help to allow access without password. Tried also to allow access for this user from any host and givin full access and also authentication fails.

Is there an option in mariadb that does not allow paswordless authentication?

_______________________________________________
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-leave@lists.mariadb.org