Nagios : CRITICAL – cannot connect to information_schema. Access denied for user

The error would be as follows while check_nrpe tries to check the mysql service in agent server.

============
CRITICAL – cannot connect to information_schema. Access denied for user nagios@nagiosIP.x.x (using password: YES)
============

The issue because nagios user was lagging access privilege to the DB which needs to be corrected. But please note that we should just give only access privilege to the nagios user for the purpose of checking/monitoring, do not give all other permissions/privileges to it.

Go to mysql prompt in agent server and give privilege for nagios user.

==================
mysql>use mysql;

mysql>grant all privileges on *.* to ‘nagios’@’nagiosip.x.x.x’ identified by password ‘d3fault’; (I got the password d3fault from nagios configuration file)

If you get error setting password ‘d3fault’ like “need to set 41 digit hexadecimal”, then convert the password to hexadecimal like below:

mysql> select password(‘d3fault’);
+——————————————-+
| password(‘d3fault’) |
+——————————————-+
| *DEC4F44D877B5BDC6434C9C5AFDD7BFA89D637E9 |
+——————————————-+
1 row in set (0.00 sec)

mysql>grant all privileges on *.* to ‘nagios’@’nagiosip.x.x.x’ identified by password ‘*DEC4F44D877B5BDC6434C9C5AFDD7BFA89D637E9’;

we should flush privileges since nagios user just only need to check it and doesn’t need other privileges literally.

mysql>flush privileges;

It should be displayed as follows with ‘N’ for every field. If you still seeis ‘Y’ everywhere, then try the following.

mysql>revoke all privileges on *.* from ‘nagios’@’nagiosip.x.x.x’; (:: nagiosip.x.x.x would be replaced with your exact nagios server IP)

Then it will be fine.

mysql> select * from mysql.user where User=’nagios’;
+————–+——–+——————————————-+————-+————-+————-+————-+————-+———–+————-+—————+————–+———–+————+—————–+————+————+————–+————+———————–+——————+————–+—————–+——————+——————+—————-+———————+——————–+——————+————+————–+————————+———-+————+————-+————–+—————+————-+—————–+———————-+——–+———————–+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+————–+——–+——————————————-+————-+————-+————-+————-+————-+———–+————-+—————+————–+———–+————+—————–+————+————+————–+————+———————–+——————+————–+—————–+——————+——————+—————-+———————+——————–+——————+————+————–+————————+———-+————+————-+————–+—————+————-+—————–+———————-+——–+———————–+
| nagiosip.x.x.x | nagios | *DEC4F44D877B5BDC6434C9C5AFDD7BFA89D637E9 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | | NULL |
+————–+——–+——————————————-+————-+————-+————-+————-+————-+———–+————-+—————+————–+———–+————+—————–+————+————+————–+————+———————–+——————+————–+—————–+——————+——————+—————-+———————+——————–+——————+————+————–+————————+———-+————+————-+————–+—————+————-+—————–+———————-+——–+———————–+
1 row in set (0.00 sec)
==================

You are good to go now. Just go the page of MYSQL service in nagios and click on “Re-schedule the next check of this service”, the alert will be fine.

Nagios warning would also be fine.

————————————–
***** Nagios *****

Notification Type: RECOVERY

Service: MYSQL
Host: server.servername.com
Address: agentip.x.x.x
State: OK

Date/Time: ———–

Additional Info:

OK – 0.60 seconds to connect as nagios
—————————————-

Leave a Reply

Your email address will not be published. Required fields are marked *