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 Copied
- Within the mysql config set ‘require_secure_transport=ON’
Problem Copied
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 Copied
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