Opsview - Monitoring MySql with require_secure_transport enabled

When monitoring a secure/encrypted connected Mysql 8 system plugin check_mysql_performance requires additional config for this to function a on Centos/RHEL 7 systems.

NOTE: For Ubuntu/Debian these steps are not required for the plugin to work.

Prerequisites

• Within the mysql config set 'require_secure_transport=ON'

Problem

DBI connect('host=xx.xxx.xxx.xxx','opsview',...) failed: 
Connections using insecure transport are prohibited while --require_secure_transport=ON. at
/opt/opsview/monitoringscripts/plugins/check_mysql_performance line 495.

Process

Make the following change the plugin `check_mysql_performance`. This will allows the -D option to be used correctly.

my $dsn;
if ( defined $np->opts->H ) {
    $dsn = 'host=' . $np->opts->H;
    if ( defined $np->opts->P ) {
        $dsn .= ';port=' . $np->opts->P;
    }
    if ( defined $np->opts->tls and $np->opts->tls ) {
        $dsn .= ';mysql_ssl=1';
        if ( defined $np->opts->ca_path ) {
            $dsn .= ';mysql_ssl_verify_server_cert=1;mysql_ssl_ca_file='
              . $np->opts->ca_path;
        }
    }
}
else {
    $dsn = $np->opts->D;
}

if ( defined $np->opts->H ) {      ##### This line is the change to the code ####
    if ( defined $np->opts->P ) {
        $0 = "$0 -H " . $np->opts->H . " -P " . $np->opts->P . " args hidden";
    }
    else {
        $0 = "$0 -H " . $np->opts->H . " args hidden";
    }
}

Valid certificates need to be used that have the FQDN defined within them, this part is critical for the plugin to successfully work. Certificates need to be checked on the host making the call and the mysql server itself

Its also possible to specify the ip address as well using the SAN (subject Alternative Name).
https://www.digicert.com/faq/subject-alternative-name.htm

Certificates

Included in the following notes are steps on how to generate Root CA and self signed SSL certificates using Opsview PKI package.

https://knowledge.opsview.com/docs/pki

If unsure then use 'openssl' to view the certificate on the client side (note Subject: line)

Client

openssl x509 -in /tmp/client.pem -text -noout

Certificate:
Data:
Version: 3 (0x2)
Serial Number:
f8:99:b6:32:ac:10:69:f2:9e:db:40:e6:bf:15:fd:b2
Signature Algorithm: sha256WithRSAEncryption
Issuer: CN=os.opsview.com
Validity
Not Before: Jul 20 15:50:18 2022 GMT
Not After : Jul 17 15:50:18 2032 GMT
Subject: CN=test-ct7-673.os.opsview.com

Certificate Client Generation: Can use the one from the collector creating the connection to the mysql server, this file is located in path '/opt/opsview/etc/ssl/opsview@collector.example.com'.

Server

openssl s_client -connect <DATABASE_IP>:3306 | grep 'Subject:'

Certificate Server Generation: /opt/opsview/pki/bin/pki server-cert mysql.server.example.com

Plugin

The plugin then needs to be called using this format

./check_mysql_performance -D "host=<fqdn>;port=3306;mysql_ssl=1;mysql_ssl_verify_server_cert=1;mysql_ssl_ca_file=/tmp/ca.pem;mysql_ssl_client_cert=/tmp/client.pem;" -u '<username>' -p '<password>' --metricname=Aborted_connects -w 20 -c 30