MySQL monitoring using SSL

This support forum board is for support questions relating to Nagios XI, our flagship commercial network monitoring solution.
Locked
rajasegar
Posts: 1018
Joined: Sun Mar 30, 2014 10:49 pm

MySQL monitoring using SSL

Post by rajasegar »

Hi,

Can anyone advise how to go about monitoring MySQL 8 DB using SSL.
There is a new implementation of hardening at the MySQL DB to set require_secure_transport = on.

This setting caused all our MySQL based DB monitoring to fail because our checks is not using SSL.

We are currently using check_mysql_health.

Works fine without the secure_transport
[nagios@myucbpnagiapp07 ~]$ /usr/local/nagios/libexec/check_mysql_health -t 60 --hostname 10.11.12.13 --port 3326 --username 'nagios' --password 'abc1234*' --mode 'connection-time' --warning 2 --critical 5

OK - 0.07 seconds to connect as nagios | connection_time=0.0659s;2;5
This is the error message
CRITICAL - Cannot connect to information_schema. Connections using insecure transport are prohibited while --require_secure_transport=ON


Thanks in advance.
5 x Nagios 5.6.9 Enterprise Edition
RHEL 6 & 7
rrdcached & ramdisk optimisation
ssax
Dreams In Code
Posts: 7682
Joined: Wed Feb 11, 2015 12:54 pm

Re: MySQL monitoring using SSL

Post by ssax »

I'm labbing this up and will post an update after investigating.
ssax
Dreams In Code
Posts: 7682
Joined: Wed Feb 11, 2015 12:54 pm

Re: MySQL monitoring using SSL

Post by ssax »

Testing this on MySQL 8:

Code: Select all

[mysqld]
require_secure_transport=1
tls_version=TLSv1.2,TLSv1.3
bind-address=0.0.0.0
port=3306
I'm able to connect with this:

Code: Select all

mysql -h X.X.X.X -uroot -p'password' --ssl -e 'STATUS;'
In order to get the plugin working I had to add this last line to the code (to use SSL/TLS but to ignore certificate validation):
- NOTE: If you make these changes I recommend that you leave the existing plugin and the associated command be and use the latest from here but rename it to something else and use a different command so your other non-ssl ones work as well:

https://labs.consol.de/nagios/check_mys ... l#download

Code: Select all

1939     } else {
1940       $self->{dsn} .= sprintf ";host=%s", $self->{hostname};
1941       $self->{dsn} .= sprintf ";port=%s", $self->{port}
1942           unless $self->{socket} || $self->{hostname} eq 'localhost';
1943       $self->{dsn} .= sprintf ";mysql_socket=%s", $self->{socket}
1944           if $self->{socket};
1945       $self->{dsn} .= sprintf ";%s", "mysql_ssl=1;mysql_ssl_verify_server_cert=0;"
To have the certificate validate properly you would need to do this:

Code: Select all

1939     } else {
1940       $self->{dsn} .= sprintf ";host=%s", $self->{hostname};
1941       $self->{dsn} .= sprintf ";port=%s", $self->{port}
1942           unless $self->{socket} || $self->{hostname} eq 'localhost';
1943       $self->{dsn} .= sprintf ";mysql_socket=%s", $self->{socket}
1944           if $self->{socket};
1945       $self->{dsn} .= sprintf ";%s", "mysql_ssl=1;mysql_ssl_verify_server_cert=1;mysql_ssl_ca_file=/path/to/ca_cert.pem;"
The developer of the plugin would need add that functionality to support SSL/TLS, you may want to submit a feature-request here otherwise it will get reverted on an upgrade:

Code: Select all

https://github.com/lausser/check_mysql_health
rajasegar
Posts: 1018
Joined: Sun Mar 30, 2014 10:49 pm

Re: MySQL monitoring using SSL

Post by rajasegar »

ssax wrote:Testing this on MySQL 8:

Code: Select all

[mysqld]
require_secure_transport=1
tls_version=TLSv1.2,TLSv1.3
bind-address=0.0.0.0
port=3306
I'm able to connect with this:

Code: Select all

mysql -h X.X.X.X -uroot -p'password' --ssl -e 'STATUS;'
In order to get the plugin working I had to add this last line to the code (to use SSL/TLS but to ignore certificate validation):
- NOTE: If you make these changes I recommend that you leave the existing plugin and the associated command be and use the latest from here but rename it to something else and use a different command so your other non-ssl ones work as well:

https://labs.consol.de/nagios/check_mys ... l#download

Code: Select all

1939     } else {
1940       $self->{dsn} .= sprintf ";host=%s", $self->{hostname};
1941       $self->{dsn} .= sprintf ";port=%s", $self->{port}
1942           unless $self->{socket} || $self->{hostname} eq 'localhost';
1943       $self->{dsn} .= sprintf ";mysql_socket=%s", $self->{socket}
1944           if $self->{socket};
1945       $self->{dsn} .= sprintf ";%s", "mysql_ssl=1;mysql_ssl_verify_server_cert=0;"
To have the certificate validate properly you would need to do this:

Code: Select all

1939     } else {
1940       $self->{dsn} .= sprintf ";host=%s", $self->{hostname};
1941       $self->{dsn} .= sprintf ";port=%s", $self->{port}
1942           unless $self->{socket} || $self->{hostname} eq 'localhost';
1943       $self->{dsn} .= sprintf ";mysql_socket=%s", $self->{socket}
1944           if $self->{socket};
1945       $self->{dsn} .= sprintf ";%s", "mysql_ssl=1;mysql_ssl_verify_server_cert=1;mysql_ssl_ca_file=/path/to/ca_cert.pem;"
The developer of the plugin would need add that functionality to support SSL/TLS, you may want to submit a feature-request here otherwise it will get reverted on an upgrade:

Code: Select all

https://github.com/lausser/check_mysql_health
Thanks will try it out once the dev DB env is ready and revert back.
5 x Nagios 5.6.9 Enterprise Edition
RHEL 6 & 7
rrdcached & ramdisk optimisation
ssax
Dreams In Code
Posts: 7682
Joined: Wed Feb 11, 2015 12:54 pm

Re: MySQL monitoring using SSL

Post by ssax »

Great. we'll keep an eye out for your update.
hul1
Posts: 50
Joined: Mon Jan 14, 2019 12:58 pm

Re: MySQL monitoring using SSL

Post by hul1 »

Wondering if this is fixed in nagios XI or if the developers are working on it? I just enabled secure_transport enabled as well and it's reporting this but I can't find a place to pass the ssl_ca_cert required in the command. Should we just create a new checker check_mysql_health_ssl or something to handle this now?
gsmith
Posts: 1253
Joined: Tue Mar 02, 2021 11:15 am

Re: MySQL monitoring using SSL

Post by gsmith »

Hi

That plugin is provided by a third party:
https://exchange.nagios.org/directory/P ... th/details
Nagios® Exchange is the central place where you'll find all types of Nagios projects - plugins, addons, documentation, extensions, and more. This site is designed for the Nagios Community to share its Nagios creations.
So you would need to contact the developer of that plugin.

Nagios does provide a check_mysql, here is the info about it:

Code: Select all

[root@gs-cent8-23-82 libexec]# ./check_mysql -h
check_mysql v2.3.3 (nagios-plugins 2.3.3)
Copyright (c) 1999-2014 Nagios Plugin Development Team
        <devel@nagios-plugins.org>

This program tests connections to a MySQL server


Usage:
 check_mysql [-d database] [-H host] [-P port] [-s socket]
       [-u user] [-p password] [-S] [-l] [-a cert] [-k key]
       [-C ca-cert] [-D ca-dir] [-L ciphers] [-f optfile] [-g group]

Options:
 -h, --help
    Print detailed help screen
 -V, --version
    Print version information
 --extra-opts=[section][@file]
    Read options from an ini file. See
    https://www.nagios-plugins.org/doc/extra-opts.html
    for usage and examples.
 -H, --hostname=ADDRESS
    Host name, IP Address, or unix socket (must be an absolute path)
 -P, --port=INTEGER
    Port number (default: 3306)
 -n, --ignore-auth
    Ignore authentication failure and check for mysql connectivity only
 -s, --socket=STRING
    Use the specified socket (has no effect if -H is used)
 -d, --database=STRING
    Check database with indicated name
 -f, --file=STRING
    Read from the specified client options file
 -g, --group=STRING
    Use a client options group
 -u, --username=STRING
    Connect using the indicated username
 -p, --password=STRING
    Use the indicated password to authenticate the connection
    ==> IMPORTANT: THIS FORM OF AUTHENTICATION IS NOT SECURE!!! <==
    Your clear-text password could be visible as a process table entry
 -S, --check-slave
    Check if the slave thread is running properly.
 -w, --warning
    Exit with WARNING status if slave server is more than INTEGER seconds
    behind master
 -c, --critical
    Exit with CRITICAL status if slave server is more then INTEGER seconds
    behind master
 -l, --ssl
    Use ssl encryptation
 -C, --ca-cert=STRING
    Path to CA signing the cert
 -a, --cert=STRING
    Path to SSL certificate
 -k, --key=STRING
    Path to private SSL key
 -D, --ca-dir=STRING
    Path to CA directory
 -L, --ciphers=STRING
    List of valid SSL ciphers

 There are no required arguments. By default, the local database is checked
 using the default unix socket. You can force TCP on localhost by using an
 IP address or FQDN ('localhost' will use the socket as well).

Notes:
 You must specify -p with an empty string to force an empty password,
 overriding any my.cnf settings.

Send email to help@nagios-plugins.org if you have questions regarding use
of this software. To submit patches or suggest improvements, send email to
devel@nagios-plugins.org

[root@gs-cent8-23-82 libexec]#

If you have question regarding the Nagios provided check_mysql plugin please open a ticket in our new Support system:
We're moving to a new support system!

The Nagios Answer Hub is a place where you can get help with technical questions from our experts. There, you can quickly open tickets and join discussion boards.

Request Nagios Answer Hub access here: https://info.nagios.com/answer-hub-access-new-users

After completing the access form, you will be given access to a portal where new tickets can be created. We will keep the old customer forum sections and ticket system available for current cases to be resolved.
I am locking this topic.

Thanks!
Locked