MySQL db configuration

[lce411]

I have been trying to configure a MySQL stand-alone server, to use with an existing Nagios installation, but I have failed multiple times. Does anyone have or know where I can find a complete set of instructions for configuring the stand-alone MySQL server and directing the Nagios data from the local db to the new MySQL db? I have scoured the Internet and asked numerous people on numerous message boards and no one seems to have a complete set of instructions. I'm sure people have done this setup a hundred times already, but I can't find any documentation for it. Any help would be greatly appreciated?

[abrist]

What are you trying to use the database for? Historical data/checks? Nagios cfg files? Something else?
NDOutils for historical data
NagiosQL for config files

[lce411]

I am trying to offload (or at least start anew) the Nagios database. I would like all future checks to be stored on a MySQL server, so the Nagios server doesn't have disk space issues, or any other resource issues. This is in a test environment so migrating the current historical data isn't too important, but I will do it if I can get some guidance on how to do it. My main concern right now is getting Nagios and MySQL to start talking and have the web GUI for Nagios to be able to access the MySQL db to present the current status of the checks we have configured.

I currently have a fresh, updated RHEL 5.8 server built and ready to go. I guess my initial question would be, is there a specific version or MySQL I should install? The current version on the Nagios server (which has the local db) is version 5.0.95, which is the version that was pulled down via yum. I know newer versions are available, but I don't know if there is a particular version that is needed for this setup to work.

Once MySQL is installed and I create the 'nagios' db, do I grant user 'nagios' full permission to the MySQL server or the Nagios server (i.e grant all on nagios.* to 'nagios'@'ustc-mysql'; versus grant all on nagios.* to 'nagios'@'ustc-nagios';)?

Once that is done, I suspect I need to test access from the Nagios server to the MySQL server with "mysql -u nagios -p". Which config files should be edited? I thought I had done this correctly but was unable to connect.

Once connectivity, has been verified, the 'nagios' db needs to be filled with the appropriate tables, for the data to be stored. I've been told this is don'e with ndoutils, but have been unable to get an install of that to be successful.

Those are the problems I have had so far. I am working in VMware so any mistakes can easily be cleared by reverting to a snapshot.

[abrist]

Well, start with the ndoutils, follow the install documentation. Once you run into specific issue we can help you work through them.
http://nagios.sourceforge.net/docs/ndou ... OUtils.pdf
Install instructions from the NDO README:

Code: Select all

----------
NDO README
----------


***************
!! IMPORTANT !!
***************
This code is still an alpha/beta quality, so expect problems if you intend to use
it.  Make sure that you aren't using it with your only production installation of
Nagios, or it could take down the Nagios process if the NDOMOD module segfaults.
Nagios could segfault silently and you might never know that Nagios crashed...



****************
ABOUT THIS ADDON
****************

The NDOUTILS (Nagios Data Output Utils) addon allows you to move status and event 
information from Nagios to a database for later retrieval and processing.

This addon consists of several parts.  Here are the most interesting ones...

1.  The NDOMOD event broker module.  This module is intended to be loaded
    by the Nagios process at runtime.  Its only role is to dump all events and
    data from Nagios to a TCP socket or a regular file or Unix domain socket on 
    the local filesystem somewhere.  If you want realtime transfer of data to MySQL,
    dump the data to a TCP or Unix domain socket.  If you want delayed transfer of
    data into MySQL (i.e. you need to transfer the data to another host first),
    dump the data to a regular file.

2.  The NDO2DB daemon.  This standalone daemon reads input (that was produced
    by the NDOMOD broker module) from a TCP or Unix domain socket, parses that 
    data, and then dumps it into one or more MySQL databases.  The daemon is 
    capable of handling multiple client connections simultaneously, so you can 
    have multiple instances of the NDOMOD module writing to the same TCP or Unix
    domain socket at the same time.

3.  The FILE2SOCK utility.  This simple utility reads data from a standard file
    and dumps it to either a TCP or a Unix domain socket.  This is useful if you 
    are having the NDOMOD module write to a standard file that you later want to 
    send to the NDO2DB daemon.  If the module and the daemon are running on 
    different machines, you can periodically use SSH to transfer the file from the 
    monitoring machine to the machine running the NDO2DB daemon, and then use the 
    FILE2SOCK utility to send the contents of that file to the TCP socket or Unix
    domain socket that the NDO2DB daemon is reading.

4.  The LOG2NDO utility.  This utility is used for importing historical log
    archives from NetSaint and Nagios and sending them to the NDO2DB daemon. 
    It takes a single log file as its input and can output data to either a
    TCP socket, a Unix domain socket or standard output.




**********************
COMPILING INSTRUCTIONS
**********************

Use the following commands to compile the NDO broker module, NDO2DB daemon, and
additional utilities:

	./configure
	make

If the configure script is unable to locate your MySQL or PostgreSQL development
libraries, you may need to help it out by using the --with-mysql-lib or
--with-pgsql-lib options.  Here's an example:

	./configure --with-mysql-lib=/usr/lib/mysql



*****************************
INITIALIZING THE SQL DATABASE
*****************************

Before you start using the NDO utilities, you should create the database where
you will be storing all Nagios-related information.

NOTE:  As of 02/12/2006, only MySQL databases are supported.  PostgreSQL support
       will likely be added in the future.


1.  Create a database for storing the data (e.g. 'nagios')

2.  Create a username/password that has at least the following privileges for
    the database:

	SELECT, INSERT, UPDATE, DELETE

3.  Run the DB installation script in the db/ subdirectory of the NDO distribution
    to create the necessary tables in the database.

	cd db
	./installdb

4.  Make sure the database name, prefix, and username/password you just created
    and setup match the variable specified in the NDO2DB config file (see below).



***********************************
INSTALLING THE NDOMOD BROKER MODULE
***********************************

NOTE:
There are two different versions of the NDOMOD module that 
get compiled, so make sure you use the module that matches the
version of Nagios you are running, and adjust the directions given
below to fit the name of the module version you're using.  

	ndomod-2x.o = NDOMOD module for Nagios 2.x
	ndomod-3x.o = NDOMOD module for Nagios 3.x (unstable)


1.  Copy the compiled NDOMOD module to your Nagios installation:

	cp src/ndomod-2x.o /usr/local/nagios/bin/ndomod.o

    The command above assumes that you are using Nagios 2.x, and thus
    are installing the 2.x version of the NDOMOD module.

2.  Copy the sample NDOMOD config file to your Nagios installation
    after modifying it to suit your needs:

	cp config/ndomod.cfg /usr/local/nagios/etc


3.  Add a line similiar to the following to the *main* Nagios config 
    file (usually /usr/local/nagios/etc/nagios.cfg):
      
	broker_module=/usr/local/nagios/bin/ndomod.o config_file=/usr/local/nagios/etc/ndomod.cfg

    The config directive above will cause Nagios to load the NDOMOD
    event broker the next time it starts.  Of course, this requres that
    you compiled Nagios with support for the event broker in the first
    place. :-)


4.  Make sure you have a line similar to the following in the *main* Nagios
    config file (usually /usr/local/nagios/etc/nagios.cfg):

	event_broker_options=-1

    That directive will cause the Nagios daemon to send data to the NDOMOD
    module.  Without that option, NDOMOD won't get any information.




****************************
INSTALLING THE NDO2DB DAEMON
****************************

NOTE:
There are two different versions of the NDO2DB daemon that 
get compiled, so make sure you use the daemon that matches the
version of Nagios you are running, and adjust the directions given
below to fit the name of the daemon you're using.  

	ndo2db-2x.o = NDO2DB daemon for Nagios 2.x
	ndo2db-3x.o = NDO2DB daemon for Nagios 3.x


1.  Copy the compiled NDO2DB daemon to your Nagios installation:

	cp src/ndo2db-2x /usr/local/nagios/bin/ndo2db

    The command above assumes that you are using Nagios 2.x, and thus
    are installing the 2.x version of the NDO2DB daemon.

2.  Copy the sample NDO2DB config file to your Nagios installation
    after modifying it to suit your needs (pay attention to the DB
    config settings).

	cp config/ndo2db.cfg /usr/local/nagios/etc

3.  Start the daemon running!  An init script will be developed soon...

	/usr/local/nagios/bin/ndo2db -c /usr/local/nagios/etc/ndo2db.cfg




************************
TUNING KERNEL PARAMETERS
************************

NDOUTILS uses a single message queue to communicate between the broker 
module and the NDO2DB daemon. Depending on the operating system, there 
may be parameters that need to be tuned in order for this communication 
to work correctly. The discussion below applies specifically to Linux, 
but may apply generally to other Unices as well.

There are three Linux kernel parameters that determine the resources 
provided to the messaging subsystem:
	* kernel.msgmax is the maximum size of a single message in a 
		message queue
	* kernel.msgmni is the maximum number of messages allowed in any 
		one message queue
	* kernel.msgmnb is the total number of bytes allow in all messages
		in any one message queue

To see the current values for any of these parameters, cat 
/proc/sys/kernel/msg{max|mni|mnb}.

In order for NDOUTILS to work at all, kernel.msgmax must be greater than
the size of the queue_msg struct (currently 1026 bytes). Most Linux
distributions set kernel.msgmax to a default of 65536.

If there are insufficient resources for sending messages between the 
broker and the daemon, you will see an entry similar to the following 
in your logs. (This is logged via the syslog facility, using the level 
LOG_ERR and the default facility.)

	ndo2db: Warning: Retrying message send. This can occur because 
	you have too few messages allowed or too few total bytes 
	allowed in message queues. You are currently using 16 of 16 
	mesages and 65536 of 65536 bytes in the queue.  See README for 
	kernel tuning options.

If you see this entry, the message will likely eventually be sent, 
but retrying uses system resources, and there is the possibility that
more messages will queued than can be handled, causing the broker 
module to stall.

If you are close to or have exceeded the number of messages, you may
need to increase kernel.msgmni. If you are close to or have exceeded
the number of bytes in the queue, you may need to increase 
kernel.msgmnb. In some cases you may need to increase both.

A conservative approach would be to double the necessary value, stop 
and restart both the NDO2DB daemon and Nagios Core, and watch for any 
further messages. Note that if NDO2DB is started after Nagios Core, 
you may see the warning above as the broker module first attempts to 
flush its backlog of messages.

To increase a value, echo the value to /proc/sys/kernel/msgmni or 
/proc/sys/kernel/msgmnb as appropriate.

For example, to increase the number of messages allowed in the queue
to 32, use the command 'echo 32 > /proc/sys/kernel/msgmni' (without
the quotes).

Once you have determine the correct parameters, you can make them
permanent by editing /etc/sysctl.conf. Add or update the line of
the form 'kernel.msg{mni|mnb} = <value>' with the value(s) determined
above. The next time the system is booted, the values of the 
parameters in /etc/sysctl.conf will be loaded.

[lce411]

I think this is part of my confusion. NDOUtils is installed on our Nagios server, to pass the data to the local db it's using. So do I still need to install NDOUtils on the MySQL server? When I tried this before, I was running the ./install db from /tmp/ndoutils.x.x.x/db/ directory and it was asking for switches to specify the user, password, host, and db, but nothing I put in when be accepted, even though I had granted user 'nagios' full permissions within the db. Does Nagios need to be installed locally on the MySQL server, so there is a 'nagios' user on the actual box and not just within the db?

[abrist]

Well, you should be able to use ndo2db to connect to a remote database. The remote database system does not need nagios, but does need to a nagios DB user set up with the proper permissions.

[lce411]

Abrist, I appreciate the help. Does it matter what version of MySQL i use? The local Nagios db is using mysql.5.0.9. Should I use the latest and greatest on the new MySQL server or should I match the one currently being used? I'm only asking to make sure the config files will work.

[abrist]

It should not matter too much as long as the versions are close.

[lce411]

When I grant permissions to the 'nagios' user within the db, should the hostname be 'localhost' or 'ustc-mysql', which is the name of my MySQL server? Or should I use the IP of the MySQL server?

[lce411]

I went with localhost, for the moment. I'm assuming I can change it later if I need to. I just tried to run 'mysql -u nagios -p' from my Nagios server and I got this error:

Code: Select all

[root@ustc-nagios ~]# mysql -u nagios -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

I checked the Naigos server and I do not have a '/var/mysql' directory, but there is a my.cnf file with it listed as the socket location. On the MySQL server, I have the directory and a mysql.socket file in it, but I do not have a my.cnf file. At this point, what needs to be done/changed? I am unable to log into the MySQL server from Nagios at the moment.