MySQL Database Monitor

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

##############################################################################
#
#	MySQL Maintainer v0.9
#
#	Author: Philip Gatt <[email protected]>
#	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 [email protected]. 
#
##############################################################################


######## 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("[email protected]", "[email protected]");
$WARNING_EMAILS = array("[email protected]");

#
#	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: $dbnamenTable: $tablenamenError: $checkerrnStatus: $ar[Msg_text]nn");
				}
				else
				{	
					PG_screen_display ("REPAIRED");
					register_warning ("Database: $dbnamenTable: $tablenamenError: $checkerrnStatus: Repairednn");
				}	
			}
			else
				PG_screen_display ("OK");
			
			PG_screen_display ("n");
		}
		PG_screen_display ("n");
	}
}
send_warnings_and_alerts();
exit();


function PG_fatal($str)
{
	register_alert ("nnFatal Error: $strn");
	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 $emailn";
		}	
		echo $warningstr;
	}	
	if ($alertstr != "")
	{
		reset ($ALERT_EMAILS);
		while (list ($trash, $email) = each ($ALERT_EMAILS))
		{
			mail ($email, $ALERT_SUBJECT, $alertstr);
			echo "Sending warning to $emailn";
		}	
		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;
}

?>