Aug 2014

MySQL Status Check in Nagios 1

Nagios can check anything anyone is willing to write it to check. In other words if there is a way to reap results then Nagios can act on those results whether they be a set of strings, numbers or some combination therein. This two part series goes over setting up a very rudimentry MySQL status page check using common tools found on a BSD-Unix, Unix or Linux system (and it not, generally easy enough to install). This first part goes over requisites, assumptions and the status pages themselves. The second part is the Nagios end of things and of course the other cool stuff the creative mind can do with it all.

Pre

  • A working knowledge of configuring Nagios and activating checks, filling out records etc. This series only shows the checks themselves and not adding them to your service checks. Nagios itself has plenty of documentation on that.
  • A fundamental knowledge of MySQL. All that is needed is basic query understanding. Nothing like tuning etc.
  • Fundamental understanding of shell scripting.

The Setup

Not too exotic, we have a MySAL server running on a host that has apache installed along with the appropiate PHP module. This should work in php4 or 5. If there are any notable differences, please let me know.

To keep the status area separated, in this example a subdirectory is setup under the webserver root at /var/www/html/status/ with an index.php page plus a utils.php file. Make sure the utils.php can only be read by the webuser of the system.

It is very important that a read only user be used to query the MySQL database.

utils.php File

The code works in two parts, in utils.php:

  1. Setup hostname, user, pass, dbname and an empty connection variable.
  2. Connect to the db, error out if we can't. Note that the error message is formatted for Nagios to digest.
  3. If we connect, pull the mysql status from the db.
  4. Close and exit.
<?php
class createConnection //create a class for make connection
{
    var $host="somehost"; // the host where mysqld is running
    var $username="my-read-only-luz3r"; // your READ ONLY user
    var $password="jrf_r0x"; // your READ ONLY USERS password
    var $database="pr0n_indexes_4758"; // the database to query
    var $myconn; // Empty connection variable

    function connectToDatabase() { // Connect to a db
        $conn= mysql_connect($this->host,$this->username,
		$this->password);
        if(!$conn){ // testing the connection
            die ("MYSQL STATUS CRITICAL: Cannot connect to the database");
        } else {
            $this->myconn = $conn;
			// Keep this around for trouble shooting stuff
            //echo "Connection established";
				// gather up a ton of info...
                $status = explode('  ', mysql_stat($conn));
                print_r($status); // barf it all out
        }
        return $this->myconn; // done!
    }

    function selectDatabase() { // selecting the database.
        mysql_select_db($this->database);

        if(mysql_error()) { // if error occured display the error message
            mysql_close($this->myconn);
            die ("MYSQL STATUS WARNING: Cannot find database ".$this->database);
        }

         echo "Database selected";
    }

    function closeConnection() { // close the connection
        mysql_close($this->myconn);
        echo "MYSQL STATUS OK for db ".$this->database;
    }
}
?>

Seems like a lot but it is filled up with safety checks and I would not be surprised if I missed a few things!

index.php

Now the easy one. In the body of the index.php file just instantiate and invoke the functions in the utils.php file:

<?php
include 'utils.php';
$connection = new createConnection(); // instantiate a new object
$connection->connectToDatabase(); // connected to the database
$connection->selectDatabase();// Select the DB
$connection->closeConnection(); // close it
?>

Observe the output in the next section, it might not hurt to slap some line breaks in there or wrap the output with formatting. This text is to get an admin up and running; feel free to make it better!

The Output

The output should look something like this which was snagged using the lynx browser:

Array ( [0] => Uptime: 1757933 [1] => Threads: 15 [2] => Questions:
   185760518 [3] => Slow queries: 194 [4] => Opens: 18706 [5] => Flush
   tables: 1 [6] => Open tables: 64 [7] => Queries per second avg: 105.669
   )
   Database selected
   MYSQL STATUS OK for db some_whacko_db

Obviously, some names have been changed to protect the innocent. Suffice to say there is a lot of information there to be screen scraped which is discussed in Part 2. Again notice the formatting.. could be easier to read by mere humans for testing purposes.

Also note that the message strings that are sent back are all Nagios formatted. Nagios' default checks usually have a return string of:

SERVICE_NAME STATUS_NAME for HOST_OR_SUBINFO

It seems redundant but overstyling alerts away from the Nagios norm can be confusing.

Summary & Next Time

Setting up a simple status page check is relatively easy, especially if one already has all the pieces in place. A mkdir here and some php code there and it is off to the diagnostic races. Next time will be the Nagios caller script and the obligatory what other cool stuff could one do and/or improve upon the Authors kludge.