10 Steps: MySQL Monitoring through Nagios: Install & Configure
Nagios is a powerful monitoring system and here we will learn how to monitor MySQL through Nagios. We will be installing Nagios, required plugins and configuring it to monitor MySQL Database Server.
Let’s unleash the power step by step:
Installing and configuring Nagios
Step-1 : Install required stuff:
yum install httpd
yum install gcc
yum install glibc*
yum install gd*
Step-2 :Create Nagios user account and group
useradd nagios
passwd nagios
groupadd nagcmd
usermod -G nagcmd nagios
usermod -G nagcmd apache
Step-3: Downloads:
Create directory:
mkdir NagiosSetup
cd NagiosSetup
Download nagios
wget -X Get "http://sourceforge.net/projects/nagios/files/nagios-3.x/nagios-3.2.1/nagios-3.2.1.tar.gz/download"
Download Nagios Plugins:
wget -X Get "http://sourceforge.net/projects/nagiosplug/files/nagiosplug/1.4.15/nagios-plugins-1.4.15.tar.gz/download"
Follow up articles:
1: Installing Percona Monitoring Tools for MySQL Nagios.
2: Adding remote MySQL Host to Nagios Setup.
Step-4: Install Nagios
tar -xzvf nagios-3.2.1.tar.gz
cd nagios-3.2.1
./configure --with-command-group=nagcmd
make all
make install
make install-config
make install-commandmode
make install-init
chkconfig --add nagios
[If you miss step “make install-init” you may get:: error reading information on service nagios: No such file or directory ]
Configure Nagios Web Interface:
make install-webconf
htpasswd -c /usr/local/nagios/etc/htpasswd.users nagiosadmin
[specify password for nagios admin]
Step-5: Install plugins
tar xvf nagios-plugins-1.4.11.tar.gz
cd nagios-plugins-1.4.11
./configure --with-nagios-user=nagios --with-nagios-group=nagios
make
make install
Step-6: Verify Installation, Starting nagios for the first time
service nagios start
Browse: http://localhost/nagios
Here if you get Error:
“You don’t have permission to access /nagios/ on this server.”
Check /etc/httpd/conf/httpd.conf for DirectoryIndex.
If it’s not having index.php add it as follows:
vi /etc/httpd/conf/httpd.conf
DirectoryIndex index.php index.html index.html.var
Make sure you do restart apache(httpd) and nagios every time you change the config file. You must have php installed.
Monitoring MySQL:
Step-7: Download, Extract and install the MySQL Plugin:
wget http://labs.consol.de/wp-content/uploads/2010/10/check_mysql_health-2.1.3.tar.gz
tar -zxvf check_mysql_health-2.1.3.tar.gz
cd check_mysql_health-2.1.3
./configure --prefix=/usr/local/nagios --with-nagios-user=nagios --with-nagios-group=nagios --with-perl=/usr/bin/perl
make
make install
Step-8: Create database user:
grant usage, replication client on *.* to 'nagios'@'localhost' identified by 'nagios';
Step-9: Provide email address for nagiosadmin:
[Change contacts.cfg file accordingly.]
vi /usr/local/nagios/etc/objects/contacts.cfg
define contact{
contact_name nagiosadmin ; Short name of user
use generic-contact ; Inherit default values from generic-contact template (defined above)
alias Kedar ; Full name of user
email kedar@nitty-witty.com ; <<***** CHANGE THIS TO YOUR EMAIL ADDRESS ******
}
Step-10: Configuring Nagios to Monitor MySQL Server
vi /usr/local/nagios/etc/nagios.cfg
add following line:
cfg_file=/usr/local/nagios/etc/objects/mysqlmonitoring.cfg
Define check_mysql_health command as follows:
vi /usr/local/nagios/etc/objects/commands.cfg
define command{
command_name check_mysql_health
command_line $USER1$/check_mysql_health -H $ARG4$ --username $ARG1$ --password $ARG2$ --port $ARG5$ --mode $ARG3$
}
Enter services to be monitored in mysqlmonitoring.cfg:
vi /usr/local/nagios/etc/objects/mysqlmonitoring.cfg
Add:
define service{
use local-service
host_name localhost
service_description MySQL connection-time
check_command check_mysql_health!nagios!nagios!connection-time!127.0.0.1!3306!
}
define service{
use local-service
host_name localhost
service_description MySQL slave-io-running
check_command check_mysql_health!nagios!nagios!slave-io-running!127.0.0.1!3306!
}
define service{
use local-service
host_name localhost
service_description MySQL slave-sql-running
check_command check_mysql_health!nagios!nagios!slave-sql-running!127.0.0.1!3306!
}
Here we’ve monitored 3 services: Connection-time, io thread and sql thread (replication) status. You can monitor more parameters described here: http://labs.consol.de/nagios/check_mysql_health/
Note: Every time you change configuration file, verify before starting nagios using command:
/usr/local/nagios/bin/nagios -v /usr/local/nagios/etc/nagios.cfg
Finally start nagios service and you’re done with nagios installation and configuration for monitoring MySQL.
Download PDF
I hope you’ve found this useful.
Thanks for sharing. Its working perfectly with out error.
You have been way to generous with the database permissions, do *not* do this:
grant all privileges on *.* to 'nagios'@'localhost' identified by 'nagios';This gives your nagios permission to do *anything* to *all* of your databases. Rather than this you should only give as much permission as is required to do the checks you want. For instance, if all you want is the database statistics and to ensure that a user is able to log into the database you could do this:
use mysql;
insert into user (User, Host, Password) VALUES ('nagios', 'localhost', password('nagios'));
flush privileges;
This will allow your nagios user to log into mysql and access the database statistics (thread, qps, etc.) but not have any access to your databases. If you must provide access to a database you should only grant SELECT privileges, you certainly don’t want your nagios user to be doing INSERTS or worse yet dropping your database!
Ben,
I definitely agree with you & thanks for pointing that out. I shouldn’t have been lazy!!
USAGE privileges will suffice what you’re conveying.
GRANT USAGE, REPLICATION CLIENT on *.* to 'nagios'@'localhost' IDENTIFIED BY 'nagios';Or for selects as you specified:
GRANT USAGE, SELECT, REPLICATION CLIENT on *.* to 'nagios'@'localhost' IDENTIFIED BY 'nagios';Thanks again.
thank you. good article!
Thanks for reading pixel 🙂
Hi,
Please let me know the steps which needs to be done on mysql server and the steps which needs to be done on nagios server.
Regards,
Krishna Rajesh
Hi Krishna,
The only step required in this process here is of creating nagios user on mysql server.
Regards.
Hi Kedar,
Can we have a centralized server with Nagios installed and monitor mysql instances running on several hosts?
What will be the load on the server if we monitor about 100 instances from this centralized server?
Thanks in advance,
Mohan.D
Hi Mohan,
Sorry for the delayed reply.
Well that’d be an ideal way of doing it.
A well configured server Nagios won’t take much load.
Thanks for visiting,
Kedar.
How to change the alert level for no. of thereads connected like critical n warning level now its 10 n 20 seems , i need to change to 50 n 100
Great write up, thanks for that, however I believe the plugin link (labs.consol.de/wp-content/uploads/2010/10/check_mysql_health-2.1.3.tar.gz) is broken? Or is it just me? I’d love to have this up and running! Thanks again.
It’s just you Chris 🙂
Hi
I want to change the WARNING and CRITICAL parameter in check_mysql_health.
Ex. Set 1 for WARNING and 3 for CRITICAL
How can change it?
Kedar,
Really useful article yar…..Thanks………….
I have checked this in my nagios server..It’s working cool…..
Hi please add the package net-snmp which is the main for monitoring..Otherwise you will get a
Error :
“Return code of 127 is out of bounds – plugin may be missing”
Solution:
yum install net-snmp*
Packages are below
net-snmp-libs
net-snmp-utils
net-snmp-devel
net-snmp
Hello All,
I am totally new on Linux. And recently I have configured a Nagios 3.4.1 server on Ubuntu 12.04 Desktop version. My Nagios server is running and showing output on web console as well. At the moment I have configured two check_commands (Check_ping and Check_snmp) in which check_ping is working fine and showing output in web console. But check_snmp is not working and my host on web console showing this service as a “critical” service.
And the status Information section of this critical service is showing this message “Service Check Timed Out”.
I have try to find solution on internet but nothing works, maybe as I am totally new so I am missing some configure required for snmp.
For your information:
/usr/local/nagios/libexec is shwoing check_snmp
On my server snmpd is running (I have checked with service snmpd status command)
and I have already installed and complied nagios-plugin-1.4.15 (with the help of commands available in nagios documentation)
Could any one suggest me how could I fix this issue. And if possible can someone share with me the step-by-step setting of SNMP for all the parameters in Nagios/Ubuntu. So that I can cross check my snmp settings.
Many Thanks in advance.
Sunil
Hi ALL,
I configured nagios successfully and didnt get any error and nagios home page loading fine but remaining pages like services hosts, ets not loading and shows “The server encountered an internal error or misconfiguration and was unable
to complete your request.
Please contact the server administrator, root@localhost and inform them of
the time the error occurred, and anything you might have done that may have
caused the error.
More information about this error may be available in the server error
log.”
i issue below command but didn’t get any warning or errors …what could be the reason..
/usr/local/nagios/bin/nagios -v /usr/local/nagios/etc/nagios.cfg
Apache/2.2.15 (Red Hat) Server at localhost Port 80 am facing this issue
Please date your articles.
hmmm! Some how the theme is not showing the Year (only date/month)… I’d try to fix this.
Thank you Mark!
Very helpful post with good explanation.
Kedar Can you help me with how to configure nagios for mysql monitoring using percona plugin?
I would be very thankful to you
Hi,
As per above steps i configured MySQL on nagios 4.1.1v and i am getting output on in background ” ./check_mysql_health -H 127.0.0.1 –port 3306 –username root –password root123 –mode threads-connected ”
But I am unable to get out output on nagios monitoring tool, It showing
” MySQL-Connection-time OK 01-20-2016 12:25:52 0d 0h 0m 11s 1/4 Please select a mode ”
Here “command.cfg” i defined :-
define command{
command_name check_mysql_health
command_line $USER1$/check_mysql_health -H $ARG1$ –username $ARG1$ –password $ARG2$ –port $ARG5$ –mode $ARG3$
}
And on localhost.cfg Service defined :-
define service{
use local-service
host_name NAME
service_description MySQL-Connection-Time
check_command check_mysql_health!root!root123!3306!connection-time!
}
Help me to resolve this issue
Hi! great guide It’s very useful for me.
All works fine except the services with name like slave: I have this error:
Command:
/usr/local/nagios/libexec/check_mysql_health –H 127.0.0.1 –port 3306 –username root –password Mikelodeon24- –mode slave-io-running
Error:
_______________________________________________________________________
CRITICAL – unable to get replication info
_______________________________________________________________________
I have the same problem with slave-sql-running also in WEB-GUI…
Whats are slave-io-running and slave-sql-running?
I’m await your anwser,thanks a lot
I’m sorry to keep you waiting so long! Well, so the error appears that it is not able to grab the “replication info” possibly connectivity issue?
Did you try
mysql -uroot -pYourPwd -h 127.0.0.1 -e 'show slave status\G'About your question of io/sql-running… The replication works using two threads…
IO-Thread –> Fetches binary-log from Master & store it as relay-log.
– Let’s say there’s a network issue then you’d get this thread terminated and see slave-io-running as false.
SQL-Thres –> Applies the received relay-log on Slave.
– This will stop in case of sql error and you’d see slave-sql-running as false.
Hope you’d have already figured things out but if not, this helps.
Regards,
Kedar.
Great Article… I found it here -> http://www.filewatcher.com/m/check_mysql_health-2.1.3.tar.gz.115861-0.html
Thanks
This is the nice blog! It is very useful for me to understand the concept of mysql monitoring. I read in this blog that how the data gets changed and who is able to change it and how they all do this. MySQL monitoring is the way to secure your data from an unauthorized user to access your sensitive data.