Checking size of database

Hi,

I have to check the size of a database. No problem, I thought, will use check_mysql_health and it will do its job. But: no way – my database forces connections via SSL, and check_mysql_health does not seem to like that.

So what I did:

  1. RTFM. But there are no options for this use case
  2. Worked with a .cnf, looks like this:
[client]
ssl-ca = mycafile.pem
ssl-mode = 'VERIFY_CA'

[groupName]
host = myhostname
port = 3306

As soon as I use a file, it seems I cannot longer user --hostname, and it always will connect to localhost; that’s why I have this groupName section in my config file. I got this idea from NagiosExchange where klaypigeon struggled similar to me. But she/he was able to solve it that way, while I’m not.

This is what I execute:

./check_mysql_health \
--hostname myhostname \
--username myusername \
--password mypassword \
--database mydatabase \
--mode connection-time \
--environment NAGIOS__SERVICEMYSQL_MYCNF='test.cnf' \
--environment NAGIOS__SERVICEMYSQL_MYCNFGROUP='groupName'

Using 2.5.5-stable (seems to ignore most of my settings):

DBI connect('database=mydatabase;mysql_read_default_file=test.cnf;mysql_read_default_group=groupName','myusername',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at ./check_mysql_health line 1963.

CRITICAL - cannot connect to mydatabase. Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

Using 3.0.0.5-beta (ignores less, but doesn’t work either)

CRITICAL - DBI connect('database=mydatabase;host=myhostname;port=3306','myusername',...) failed: SSL connection is required. Please specify SSL options and retry. at ./check_mysql_health line 4290.

SSL secured database connections are nothing strange, are they? :thinking: I’m not in love with this script in special, so if you have any suggestions to make it work: please let me know.

Cheers :green_heart:
Marianne

Hi Marianne,

it seems that this check script uses perl dbi as default client and after a quick look into source code it seems that it is not built to use secure connections. I found no parameters to use SSL.

Without having tested this, it could be an option to use the switch --method mysql . It seems to use the native mysql client instead of perl DBI. DBI won’t use your .cnf file, IMHO this will be read only by native mysql clients.

Or try other check scripts - check_mysql (monitoring-plugins V2.2) works for me to get several performance values…

Cheers
Manfred

3 Likes

Hi Manfred,

--method mysql could do the trick, I overlooked that. So this is what I’m running in:

STABLE-2.2.2
  • I can define --environment NAGIOS__SERVICEMYSQL_MYCNF='test.cnf'
  • I can use --method mysql and even --environment NAGIOS__SERVICEMYSQL_METH="mysql"
  • But it will connect to localhost
BETA-3.0.0.5
  • There seems to be no NAGIOS__SERVICEMYSQL_MYCNF, and I don’t know where to put my cfg file in
  • I can use --method mysql
  • … but it ignores that and uses DBI
CRITICAL - DBI connect('database=<mydatabase>;host=<myhost>;port=3306','<myuser>',...) failed: SSL connection is required. Please specify SSL options and retry. at ./check_mysql_health line 4290.

I just saw there is a never merged pull request from Feb 2019 implementing SSL, but… uh. As check_mysql will not to what I need I think I will do it by myself now. I’m not happy at all with status codes like fetchrow_array exit bumm

Thank you very much for your approach. It seems there is no real solution at this time :frowning:

Cheers,
Marianne

1 Like

Hi Marianne,

I did not check the source code from check script, but when the wrong host is used with --method mysql,
did you try to set a host name within the [client] section of your my.cnf (global in /etc or in user home dir)?
What’s the section [groupName] in your example above?

Cheers,
Manfred

2 Likes

Hi Manfred,

I’ve spent too much time on debugging this… but you invested the time to write me this nice answer, so I had to try once more again. So this is what I can say:

I have to put a $HOME/.my.cnf in place. Its content has to be similar to this:

[client]
ssl=1
ssl-ca=/root/JustAnotherCertificate.crt.pem

[groupName]
host=<host>
$ ./check_mysql_health \
--method mysql \
--host <host> \
--username <user> \
--password <password>  \
--environment NAGIOS__SERVICEMYSQL_MYCNFGROUP='groupName' \
--mode sql \
--name "select sum(data_length + index_length) / 1024 / 1024 from information_schema.tables where table_schema='icinga'" \
--name2 "database_size" \
--units MB \
--warning 10 \
--critical 20      
WARNING - database_size: 10.890625MB | 'database_size'=10.890625MB;10;20

(And many thanks to @dnsmichi, the SELECT statement is based on his blog post! :kissing_heart:) What does not work is to specify a my.cnf as seen in the example with --environment NAGIOS__SERVICEMYSQL_MYCNF='/path/to/my.cnf': it seems to register that file, but it does not work correctly with it:

new NAGIOS__SERVICEMYSQL_MYCNFGROUP=groupName forces restart
new NAGIOS__SERVICEMYSQL_MYCNF=/path/to/my.cnf forces restart
ERROR 9002 (28000): SSL connection is required. Please specify SSL options and retry.

When I simply put /path/to/my.cnf to $HOME/my.cnf, everything works as expected. And this will be a problem when monitoring more than one database host with different certificates, as the pem file will be hardcoded in .my.cnf.

But indeed, I’m a bit closer to a solution with your help. I still consider it as terrible :smiley: But it’s closer.

Thank you very much,
Marianne

There is a PR to add SSL support over at github, did you see test it?

Yes, saw it, tried it, did not work for me :slight_smile:

Strange for me with MariaDB 10.4 it works flawless, here is what i did.

The SSL configuration of the server

[mysqld]                                                                                                                                                                                
ssl-ca=/etc/mysql-ssl/ca-cert.pem                                                                                                                                                       
ssl-cert=/etc/mysql-ssl/server-cert.pem                                                                                                                                                 
ssl-key=/etc/mysql-ssl/server-key.pem

Restartet server and checked if ssl is activated in mariadb

[root@koelin02 ~]# mysql -u root -p                                                                                                                                                     
Enter password:                                                                                                                                                                         
Welcome to the MariaDB monitor.  Commands end with ; or \g.                                                                                                                             
Your MariaDB connection id is 23                                                                                                                                                        
Server version: 10.4.12-MariaDB MariaDB Server                                                                                                                                          
                                                                                                                                                                                        
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.                                                                                                                    
                                                                                                                                                                                        
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.                                                                                                          
                                                                                                                                                                                        
MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%';                                                                                                                                          
+---------------------+----------------------------------+                                                                                                                              
| Variable_name       | Value                            |                                                                                                                              
+---------------------+----------------------------------+                                                                                                                              
| have_openssl        | YES                              |                                                                                                                              
| have_ssl            | YES                              |                                                                                                                              
| ssl_ca              | /etc/mysql-ssl/ca-cert.pem       |                                                                                                                              
| ssl_capath          |                                  |                                                                                                                              
| ssl_cert            | /etc/mysql-ssl/server-cert.pem   |                                                                                                                              
| ssl_cipher          |                                  |                                                                                                                              
| ssl_crl             |                                  |                                                                                                                              
| ssl_crlpath         |                                  |                                                                                                                              
| ssl_key             | /etc/mysql-ssl/server-key.pem    |                                                                                                                              
| version_ssl_library | OpenSSL 1.0.2k-fips  26 Jan 2017 |                                                                                                                              
+---------------------+----------------------------------+                                                                                                                              
10 rows in set (0.001 sec)                                                                                                                                                              
                                                                                                                                                                                        
MariaDB [(none)]>                                                                                                                                                                       

Created a user that requires a SSL connection

[root@koelin02 ~]# mysql -u root -p                                                                                                                                                     
Enter password:                                                                                                                                                                         
Welcome to the MariaDB monitor.  Commands end with ; or \g.                                                                                                                             
Your MariaDB connection id is 22                                                                                                                                                        
Server version: 10.4.12-MariaDB MariaDB Server                                                                                                                                          
                                                                                                                                                                                        
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.                                                                                                                    
                                                                                                                                                                                        
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.                                                                                                          
                                                                                                                                                                                        
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'remote'@'%' IDENTIFIED BY 'password' REQUIRE SSL;                                                                                     
Query OK, 0 rows affected (0.002 sec)                                                                                                                                                   
                                                                                                                                                                                        
MariaDB [(none)]> FLUSH PRIVILEGES;                                                                                                                                                     
Query OK, 0 rows affected (0.001 sec)                                                                                                                                                   

Now tests from other machine to connect to mariadb, first without SSL.It doesnt work like expected

[root@koelin01 plugins-scripts]# mysql -h koelin02 -u remote -p                                                                                                                         
Enter password:                                                                                                                                                                         
ERROR 1045 (28000): Access denied for user 'remote'@'koelin01.fritz.box' (using password: YES)                                                                                          

Now lets try with SSL

[root@koelin01 plugins-scripts]# mysql --ssl -h koelin02 -u remote -p                                                                                                                   
Enter password:                                                                                                                                                                         
Welcome to the MariaDB monitor.  Commands end with ; or \g.                                                                                                                             
Your MariaDB connection id is 25                                                                                                                                                        
Server version: 10.4.12-MariaDB MariaDB Server                                                                                                                                          
                                                                                                                                                                                        
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.                                                                                                                    
                                                                                                                                                                                        
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.                                                                                                          
                                                                                                                                                                                        
MariaDB [(none)]>                                                                                                                                                                       

Yeah, it works :slight_smile:

Now lets try the check_mysql_health with SSL PR, first without and ssl options set.

[root@koelin01 plugins-scripts]# ./check_mysql_health --hostname koelin02 --user remote --password password --mode open-files                                                           
DBI connect('database=information_schema;host=koelin02;port=3306','remote',...) failed: Access denied for user 'remote'@'koelin01.fritz.box' (using password: YES) at ./check_mysql_heal
th line 1983.                                                                                                                                                                           
CRITICAL - cannot connect to information_schema. Access denied for user 'remote'@'koelin01.fritz.box' (using password: YES)

As expected, it does not work. Now lets try with SSL enabled.

[root@koelin01 plugins-scripts]# ./check_mysql_health --hostname koelin02 --user remote --password password --mysql_ssl 1 --mode open-files                                             
OK - 0.53% of the open files limit reached (22 of max. 4184) | pct_open_files=0.526%;80.000;95.000 open_files=22;3347;3974                                                              
[root@koelin01 plugins-scripts]#                                                                                                                                                        

Works :slight_smile:

If you want i send you the compiled plugin (compiled on a CentOS 7 server)

Regards,
Carsten

4 Likes