PHPBuilder - MySQL Database Monitor



RSS Twitter
Snippets Databases

MySQL Database Monitor

by: Philip Gatt
|
August 16, 2000

Version: 0.9

Type: Full Script

Category: Databases

License: GNU General Public License

Description: This will check the status of all of your MySQL tables in all of your databases. Extremely easy to use. If an error is found, it will repair the table and send you a warning. If it cannot repair the table, it will send you an alert (to your cell phone / pager).



#!/usr/bin/php -q
<?

##############################################################################
#
#	MySQL Maintainer v0.9
#
#	Author: Philip Gatt <pxg@hotmail.com>
#	Version: 0.9
#	Date Created: Aug 15, 2000
#	Last Modified: Aug 15, 2000
#
#	Requires: MySQL v. 3.23.21 or higher (it may work on earlier versions of
#		3.23, but it hasn't been tested)
#
#	What it does: It checks every table in your mysql database, if the table
#		has a problem, it tries to repair it and sends a warning to the emails
#		in the $warning_emails variable. If it cannot repair the table, it sends
#		an alert to the emails in the $alert_emails variable. I recommend putting
#		your cell phone / pager's email address in the alert_emails, and your
#		regular address in the warning_emails.
#
#	Rights: Use this program as you see fit, alter it, do whatever you like, 
#		as long as this message, including the author info, remains unchanged. 
#		Also, please notify me if you are using this, just so I know my work 
#		was appreciated.
#
#	Additional Note: If you need any custom programming, I am available to
#		write custom scripts / programs. No project is too large or small.
#		Please contact me at pxg@hotmail.com. 
#
##############################################################################


######## SCRIPT CONFIGURATION ################################################
#
#	Please carefully read and set all of these options.
# 
#	SKIP_DATABASES is an array of databases not to process
$SKIP_DATABASES = array("skipdatabase1", "skipdatabase2", "skipdatabase3");		// don't check these databases

#
#	Set SILENT_MODE to 1 for silent mode (good for cron jobs), and to 0 to get output to the screen
$SILENT_MODE = 0;

#
#	ALERT_EMAILS and WARNING_EMAILS contain a list of email addresses to send 
#		alerts and warnings to (respectivly)
$ALERT_EMAILS = array("you@youremail.com", "0000000000@messaging.sprintpcs.com");
$WARNING_EMAILS = array("you@youremail.com");

#
#	ALERT_SUBJECT and WARNING_SUBJECT are the message subjects for warning and alert
#		emails
$ALERT_SUBJECT = "MySQL Maintainer Alert";
$WARNING_SUBJECT = "MySQL Maintainer Warning";

#
#	Set your mysql server info here, root is a good account to use because it has
#		access to modify all tables in the database
$MYSQL_SERVER = "localhost";
$MYSQL_USER = "root";
$MYSQL_PASS = "";

#
######## END OF CONFIGURATION ################################################





mysql_connect ($MYSQL_SERVER, $MYSQL_USER, $MYSQL_PASS) or die (PG_fatal(mysql_error()));

#-- get a list of databases
$databasesres = mysql_query ("show databases") or die (PG_fatal(mysql_error()));

#-- cycle through the list of databases
while ($dar = mysql_fetch_array($databasesres))
{
	$dbname = $dar[0];

	#-- if the database is not in the skip array, process it
	if (!PG_in_array($SKIP_DATABASES, $dbname))
	{
	
		PG_screen_display ($dbname . "\n");

		#-- switch to the database
		mysql_select_db ($dbname);

		#-- get a list of tables in this database
		$tablesres = mysql_query ("show tables") or die (PG_fatal(mysql_error()));

		#-- cycle through the list of tables and check them
		while ($tar = mysql_fetch_array($tablesres))
		{
			$tablename = $tar[0];

			#-- display some info
			PG_screen_display (PG_print_sp (" | " . $tablename, 50, " "));
			flush();

			#-- check the table
			$res = mysql_query ("check table $tablename") or die (PG_fatal(mysql_error()));
			$ar = mysql_fetch_array($res);

			#-- if we didn't get an OK as a response, try to fix it
			if ($ar[Msg_text] != "OK")
			{
				$checkerr = $ar[Msg_text];
				PG_screen_display ("ERROR $checkerr --- ");
				flush();
				
				#-- try to fix the table
				$res = mysql_query ("repair table $tar[0]") or die (PG_fatal(mysql_error()));
				$ar = mysql_fetch_array($res);

				#-- if we got an error trying to fix it, send an alert
				if ($ar[Msg_text] != "OK")
				{
					PG_screen_display ("UNABLE TO REPAIR ($ar[Msg_text]) !!!!");
					register_alert ("Database: $dbname\nTable: $tablename\nError: $checkerr\nStatus: $ar[Msg_text]\n\n");
				}
				else
				{	
					PG_screen_display ("REPAIRED");
					register_warning ("Database: $dbname\nTable: $tablename\nError: $checkerr\nStatus: Repaired\n\n");
				}	
			}
			else
				PG_screen_display ("OK");
			
			PG_screen_display ("\n");
		}
		PG_screen_display ("\n");
	}
}
send_warnings_and_alerts();
exit();


function PG_fatal($str)
{
	register_alert ("\n\nFatal Error: $str\n");
	send_warnings_and_alerts();
	exit;
}


function send_warnings_and_alerts ()
{
	global $alertstr;
	global $warningstr;
	global $WARNING_EMAILS;
	global $ALERT_EMAILS;
	global $WARNING_SUBJECT;
	global $ALERT_SUBJECT;
	
	if ($warningstr != "")
	{
		reset ($WARNING_EMAILS);
		while (list ($trash, $email) = each ($WARNING_EMAILS))
		{	
			mail ($email, $WARNING_SUBJECT, $warningstr);
			echo "Sending warning to $email\n";
		}	
		echo $warningstr;
	}	
	if ($alertstr != "")
	{
		reset ($ALERT_EMAILS);
		while (list ($trash, $email) = each ($ALERT_EMAILS))
		{
			mail ($email, $ALERT_SUBJECT, $alertstr);
			echo "Sending warning to $email\n";
		}	
		echo $alertstr;	
	}	
}

function register_alert ($str)
{
	global $alertstr;
	$alertstr .= $str;
	return 1;
}

function register_warning ($str)
{
	global $warningstr;
	$warningstr .= $str;
	return 1;
}

function PG_screen_display ($string)
{
	# displays message to standard output (the screen) if not running in silent mode
	global $SILENT_MODE;
	if (!$SILENT_MODE)
		echo $string;
	return 1;	
}

function PG_in_array ($array, $value)
{
	# this function returns true if $value is found in $array
	$found = 0;
	for ($i = 0; $i < count($array); $i++)
		if ($array[$i] == $value)
		{
			$found = 1;
			break;
		}
	return $found;	
}

function PG_print_sp ($str, $len, $delim = " ")
{
    $res = $str;
	for ($i = strlen($str); $i < $len; $i++)
			$res .= $delim;
	return $res;
}

?>

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
Philip Gatt

Comment:



Comment:

(Maximum characters: 1200). You have characters left.