mmrwoods/MySQL_maintenance.pl

## MySQL_maintenance.pl
#!/usr/bin/perl -w


# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#
#	MySQL_maintenance.pl
#
#	By Dan Buettner - drbuettner-at-gmail.com
#	12 Nov 2005
#
#	Purpose:  To automate regular checking, optimization and repair
#	of MySQL database tables.
#	Multi-threaded operation should compress maintenance window
#	for many installations, especially with multiple processors
#	and fast disk drives.
#
#	Note: please submit feedback to the above email address.
#	I want this script to be a great tool in a MySQL admin's toolbox.
#
#	Change history:
#
#	12 Nov 2005 - Dan Buettner - Original version written.
#
#	19 Dec 2005	- Dan Buettner - Compacted check,optimize,repair routines
#	into one, and improved comments.
#
#	29 Jun 2006 - Dan Buettner - Added support for limiting table types /
#	storage engines to be checked.
#
#	5 July 2006 - Dan Buettner - Added support for sending results via email
#	using SMTP, as well as the ability to specifically include database
#	and table names
#
#	10 July 2006 - Dan Buettner - Fixed bug with multiple email recipients
#
#	19 Feb 2007 - Dan Buettner - added ability to do SMTP authorization
#
#	21 Feb 2007 - Dan Buettner - skipped VIEWs, added report of data stats
#
#	18 Oct 2007 - Dan Buettner - fixed email address (removed @ symbol) - oops
#
#	30 Jan 2012 - Mark Woods - read MySQL options from user's my.cnf if exists
#
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #


use strict;
use DBI;
use Time::HiRes qw(tv_interval gettimeofday);
use Net::SMTP;
use threads;
use threads::shared;
use Thread::Queue::Any;
use Getopt::Long;

my $host					= '';
my $port					= 3306;
my $username				= '';
my $password				= '';
my $optimizeTables			= 0;
my $repairTables			= 0;
my $checkType				= 'QUICK';

my @masterDatabases			= ();
my @masterTables			= ();

my @excludeMasterDatabases	= ();
my $excludeDBs				= '';

my @includeMasterDatabases	= ();
my $includeDBs				= '';

my @excludeMasterTables		= ();
my $excludeTables			= '';

my @includeMasterTables		= ();
my $includeTables			= '';

my @includeTableTypes		= ();
my $includeTypes			= '';
my $tableTypes				= 'MyISAM,InnoDB';

my $help					= 0;
my $smtpHost				= '';
my $emailFrom				= '';
my $emailPassword			= '';
my $emailTo					= '';
my $subject					= '';

my $tableCount				= 0;
my $databaseCount			= 0;
my $rowCount				= 0;
my $dataLength				= 0;
my $indexLength				= 0;

my $errorCount				= 0;
share($errorCount);
my $threadCount				= 3;
share($threadCount);
my $threadsRunning			= 0;
share($threadsRunning);
my @results					= ();
share(@results);
my $workQueue;

my $junk 	= GetOptions(	"repair"			=> \$repairTables,
							"optimize"			=> \$optimizeTables,
							"host=s"			=> \$host,
							"port=s"			=> \$port,
							"user=s"			=> \$username,
							"password=s"		=> \$password,
							"check-type=s"		=> \$checkType,
							"threads=i"			=> \$threadCount,
							"exclude-dbs=s"		=> \$excludeDBs,
							"include-dbs=s"		=> \$includeDBs,
							"exclude-tables=s"	=> \$excludeTables,
							"include-tables=s"	=> \$includeTables,
							"table-types=s"		=> \$tableTypes,
							"smtp-host=s"		=> \$smtpHost,
							"email-from=s"		=> \$emailFrom,
							"email-password=s"	=> \$emailPassword,
							"email-to=s"		=> \$emailTo,
							"subject=s"			=> \$subject,
							"help"				=> \$help);


# display help screen and exit, if needed
if($help)  {
        PrintUsage();
        exit;
}

# go do the work
Main();
# the end


# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#
#	sub PrintUsage
#	Print usage screen and exit
#
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
sub PrintUsage {
	print <<END_OF_INFO;

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
MySQL_maintenance.pl
By Dan Buettner - drbuettner-at-gmail.com

Multi-threaded table maintenance tool for MySQL database installations.
Options are as follows:
--host=<host>            MySQL host address (name or IP)
--user=<username>        MySQL user to log in as - needs proper db privileges.
--password=<password>    MySQL password
--check-type=<type>      Check type (fast, quick, medium, extended).  Default: quick
--table-types=<type>     Comma-separated table types to check (default InnoDB,MyISAM)
--optimize               Optimize tables (default: no)
--repair                 Repair table if error found during check (default: no)
--threads=n              Number of worker threads (default 3). Total threads = n+1
--exclude-dbs=<list>     Comma-separated list of database names/patterns to not check
--exclude-tables=<list>  Comma-separated list of table names/patterns to not check
--include-dbs=<list>     Comma-separated list of database names/patterns to check
--include-tables=<list>  Comma-separated list of table names/patterns to check
--smtp-host=<host>       Hostname or IP address of SMTP server to send mail through
--email-from=<email>     Email address email should appear to be "from"
--email-password=<pass>  Password to use in conjunction with --email-from for SMTP AUTH
--email-to=<email>       Comma-separated addresses for email to go to
--subject=xyz            Subject line of message, default "MySQL maintenance on <host>"

Example:
./MySQL_maintenance.pl --host=mysql.domain.com --user=user --password=pass \
     --check-type=extended --repair --optimize --threads=5 --exclude-dbs=test,test1
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #

END_OF_INFO
}


# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#
#	sub Main
#	Control thread.  Interrogate the server for database and table names,
#	and queue them up to be checked.  Exclude those that need to be excluded,
#	include those that need to be included, based on command line arguments.
#	Optimize and repair operations will be queued up by a worker thread
#	based on check results.
#
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
sub Main {
	my $startTime;
	my $endTime;
	my $elapsedTime;
	my $dbh;
	my @data;
	my @databases;
	my $database;
	my @tables;
	my $table;
	my $tableType;
	my $sth;
	my %workerThreads = ();
	my $timestamp;
	my($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst);
	my $result;
	my $message = '';

	$startTime = time;
	$dbh = ConnectMySQL($host, $port, $username, $password);
	$workQueue	= Thread::Queue::Any->new();

	($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst) = localtime($startTime);
	$timestamp  = sprintf("%04d-%02d-%02d %02d:%02d:%02d", $year + 1900, $mon + 1, $mday, $hour, $min, $sec);
	$message .= "$timestamp: MySQL_maintenance got started.\n";
	$message .= "Checking tables on host '$host' with '$checkType' option.\n";
	$message .= "Optimizing tables.\n" if ($optimizeTables);
	$message .= "Repairing tables.\n" if ($repairTables);
	$message .= "Running with $threadCount threads.\n\n";

	# construct pattern matching for databases and tables to include/exclude, table types to include
	@excludeMasterDatabases = split ',', $excludeDBs;
	if ($#excludeMasterDatabases >= 0) {
		$excludeDBs = join '$|^', @excludeMasterDatabases;
		$excludeDBs = '^' . $excludeDBs . '$';
	}
	@excludeMasterTables = split ',', $excludeTables;
	if ($#excludeMasterTables >= 0) {
		$excludeTables = join '$|^', @excludeMasterTables;
		$excludeTables = '^' . $excludeTables . '$';
	}
	@includeMasterDatabases = split ',', $includeDBs;
	if ($#includeMasterDatabases >= 0) {
		$includeDBs = join '$|^', @includeMasterDatabases;
		$includeDBs = '^' . $includeDBs . '$';
	}
	@includeMasterTables = split ',', $includeTables;
	if ($#includeMasterTables >= 0) {
		$includeTables = join '$|^', @includeMasterTables;
		$includeTables = '^' . $includeTables . '$';
	}
	@includeTableTypes = split ',', $tableTypes;
	if ($#includeTableTypes >= 0) {
		$includeTypes = join '$|^', @includeTableTypes;
		$includeTypes = '^' . $includeTypes . '$' . '|^NULL$|^$';
	}

	# issue a flush tables command, as otherwise certain corruptions may not be recognized
	# (what's cached in RAM could be good while what's on disk is bad)
	$sth = $dbh->prepare('FLUSH TABLES');
	$sth->execute;
	$sth->finish;

	# get a list of local databases
	$sth = $dbh->prepare('SHOW DATABASES');
	$sth->execute;
	while (@data = $sth->fetchrow_array) {
		push @databases, $data[0];
	}
	$sth->finish;
	foreach $database (sort @databases) {
		# include or exclude this database if it matches input
		next if (($#excludeMasterDatabases >= 0) and ($database =~ /$excludeDBs/i));
		next if (($#includeMasterDatabases >= 0) and ($database !~ /$includeDBs/i));

		$databaseCount++;

		# find tables of the appropriate names and table types
		$sth = $dbh->prepare("SHOW TABLE STATUS FROM $database");
		$sth->execute;
		while (my $hash = $sth->fetchrow_hashref) {
			$table = $hash->{'Name'};
			$tableType = $hash->{'Engine'};

			# skip views
			next if (uc($hash->{'Comment'}) eq 'VIEW');

			# include or exclude this table if it matches input
			next if (($#excludeMasterTables >= 0) and ($table =~ /$excludeTables/i));
			next if (($#includeMasterTables >= 0) and ($table !~ /$includeTables/i));

			# exclude this table if it is not one of the specified table types/storage engines
			next if ($tableType !~ /$includeTypes/i);

			$tableCount++;
			if (defined ($hash->{'Rows'})) {
				$rowCount += $hash->{'Rows'};
			}
			$dataLength += $hash->{'Data_length'};
			$indexLength += $hash->{'Index_length'};

			my %checkItem = ();
			$checkItem{'database'}		= $database;
			$checkItem{'table'}			= $table;
			$checkItem{'operation'}		= 'CHECK';
			$checkItem{'checklevel'}	= uc($checkType);
			$workQueue->enqueue(%checkItem);
		}
		$sth->finish;
	}

	# create worker threads only after we have queued up work to do
	# (otherwise they would exit immediately)
	foreach my $counter (1..$threadCount) {
		$workerThreads{$counter} = threads->create(\&WorkerThreadSub, $counter);
		$threadsRunning++;
	}

	# the main thread needs to block while other threads are still running
	# (before declaring "all done")
	while ($threadsRunning > 0) {
		sleep 1;
	}

	$endTime = time;
	$elapsedTime = $endTime - $startTime;
	$dbh->disconnect;

	# add data statistics summary
	$message .= "Examined $rowCount rows - $tableCount tables - $databaseCount databases.\n";
	$message .= "Total data size:  $dataLength bytes\n";
	$message .= "Total index size: $indexLength bytes\n";

	# add finished time / elapsed time info
	($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst) = localtime($endTime);
	$timestamp  = sprintf("%04d-%02d-%02d %02d:%02d:%02d", $year + 1900, $mon + 1, $mday, $hour, $min, $sec);
	$message .= "\n$timestamp: MySQL_maintenance finished.   $elapsedTime seconds elapsed.\n\n";

	# sort the result array and print contents.
	# this will give a listing of normal results in alphabetical order
	$message .= FormatResults("Database/table name", "Operation", "Time(sec)", "Status") . "\n";
	foreach $result (sort @results) {
		$message .= $result . "\n";
	}

	print $message;

	$subject = "MySQL maintenance on $host" if ($subject eq '');
	if ($errorCount == 0) {
		$subject .= ' -- all OK';
	} elsif ($errorCount == 1) {
		$subject = "ERROR -- $subject -- $errorCount ERROR";
	} else {
		$subject = "ERROR -- $subject -- $errorCount ERRORS";
	}

	if ($smtpHost ne '') {
		SendEmailMessage($smtpHost, $subject, $message, $emailFrom, $emailPassword, $emailTo);
	}
}


# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#
#	sub WorkerThreadSub
#	The life of a worker thread happens here - checking, repairing
#	and optimizing tables popped off the work queue.
#	A successful check operation will lead to an optimize, if designated.
#	A failed check operation will lead to a repair, if designated.
#	A successful repair will lead to an optimize, if designated.
#	A failed repair will not lead to an optimize.
#
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
sub WorkerThreadSub {
	my($threadID) = @_;

	my $dbh;

	$dbh = ConnectMySQL($host, $port, $username, $password);

	# pop work items off the queue
	while (my %workItem = $workQueue->dequeue_nb) {

		my $tableOK;
		my $result;
		my $warning = '';
		my $elapsedTime;
		my $operation;

		$operation = $workItem{'operation'};
		$operation = $workItem{'checklevel'} if ($workItem{'operation'} =~ /CHECK/i);

		($tableOK, $result, $elapsedTime) = HandleTableOperation($dbh, $workItem{'database'}, $workItem{'table'}, $operation);

		if ($workItem{'operation'} =~ /CHECK/i) {
			# handle table check operations results
			# if we encountered a problem, and repair tables is on,
			# queue a repair operation
			if (($repairTables) and (! $tableOK)) {
				my %item = ();
				$item{'database'}	= $workItem{'database'};
				$item{'table'}		= $workItem{'table'};
				$item{'operation'}	= 'REPAIR';
				$workQueue->enqueue( %item );
			}

			# DO NOT ATTEMPT TO OPTIMIZE (YET) IF TABLE NOT OK
			# now that we're done with the check and no error,
			# queue this table to be optimized if so directed
			if ($optimizeTables && $tableOK) {
				my %item = ();
				$item{'database'}	= $workItem{'database'};
				$item{'table'}		= $workItem{'table'};
				$item{'operation'}	= 'OPTIMIZE';
				$workQueue->enqueue( %item );
			}
		}
		elsif ($workItem{'operation'} =~ /REPAIR/i) {
			#  handle repair operations results
			# DO NOT ATTEMPT TO OPTIMIZE IF TABLE NOT OK
			# now that we're done with the repair and no error,
			# queue this table to be optimized if so directed
			if ($tableOK && $optimizeTables) {
				my %item = ();
				$item{'database'}	= $workItem{'database'};
				$item{'table'}		= $workItem{'table'};
				$item{'operation'}	= 'OPTIMIZE';
				$workQueue->enqueue( %item );
			}
		}
		elsif ($workItem{'operation'} =~ /OPTIMIZE/i) {
			# handle optimize operations results
			# (nothing special at this time)
		}

		$result = FormatResults("$workItem{'database'}.$workItem{'table'}", $workItem{'operation'},
			sprintf("%.3f", $elapsedTime), $result);

		$result .= '   :(' if (! $tableOK);
		$errorCount++ if (! $tableOK);
		push @results, $result;
	}

	$dbh->disconnect;
	$threadsRunning--;
}


# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#
#	sub FormatResults
#	Return a pretty printable string.
#
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
sub FormatResults {
	my(@columns) = @_;

	my $result;

	$result = sprintf("%-60s %-10s %10s  %s", @columns);

	return $result;
}


# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#
#	sub HandleTableOperation
#	The operations to check, repair and optimize tables happen here.
#	Returned status from the MySQL engine determines success or failure.
#	Needs to be fleshed out with result codes and error conditions.
#
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
sub HandleTableOperation {
	my($dbh, $database, $table, $checkType) = @_;

	my @data;
	my $sth;
	my $sql;
	my $tableOK = 0;
	my $msgType;
	my $msgText;
	my $returnText = '';
	my $junk;
	my $startTime;
	my $elapsedTime;

	$sql = "CHECK TABLE $database.$table $checkType";
	$sql = "OPTIMIZE TABLE $database.$table" if ($checkType =~ /optimize/i);
	$sql = "REPAIR TABLE $database.$table" if ($checkType =~ /repair/i);
	$sth = $dbh->prepare($sql);
	$startTime = [gettimeofday];
	$sth->execute;
	if ($sth->errstr) {
		if ($sth->errstr =~ /Access denied/i) {
			$tableOK = 1;
			$returnText = 'Access denied';
		}
	}
	$elapsedTime = tv_interval($startTime, [gettimeofday]);
	while (@data = $sth->fetchrow_array) {
		($junk, $junk, $msgType, $msgText) = @data;

		if (($msgType =~ /error|note/i) and ($msgText =~ /(engine|handler) for the table doesn't support/i)) {
			# we tried to check a table that does not support that operation.
			# declare success in the absence of failure, and move on.
			$tableOK = 1;
		}
		elsif ($msgType =~ /error/i) {
			$tableOK = 0;
		}
		elsif ($msgType =~ /status/i) {
			if ($msgText =~ /^OK|Table is already up to date/i) {
				$tableOK = 1 ;
				$msgText = 'OK';
			}
		}
		$returnText .= "$msgText ";
	}
	$sth->finish;


	return($tableOK, $returnText, $elapsedTime);
}


# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#
#	sub ConnectMySQL
#	Establish a database handle and return it.  Exit with error if appropriate.
#
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
sub ConnectMySQL {
	my($host, $port, $username, $password) = @_;
        my $datasource = "DBI:mysql:mysql:$host;port=$port";
        if (-e "$ENV{HOME}/.my.cnf") {
                $datasource .= ";mysql_read_default_file=$ENV{HOME}/.my.cnf";
        }
        my $dbh = DBI->connect($datasource, $username, $password);
	if (! $dbh) {
		print DBI::errstr, "\n";
		print "Failed to connect to MySQL host $host with username $username.\n";
		exit(0);
	}
	return $dbh;
}


# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#
#	sub SendEmailMessage
#	Send email message through SMTP server.
#
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
sub SendEmailMessage {
	my($smtphost, $subject, $message, $from, $password, $recipientsList) = @_;

	my $smtp;
	my @recipients;

	@recipients = split /,|\ /, $recipientsList;

	$smtp = Net::SMTP->new( $smtphost, Timeout => 15);
	if (defined ($smtp)) {
		if ($password ne '') {
			$smtp->auth($from, $password);
		}
		$smtp->mail( $from );
		$smtp->to( @recipients );
		$smtp->data();
		$smtp->datasend( "Subject: $subject\n" );
		$smtp->datasend( "\n$message\n" );
		$smtp->dataend();
		$smtp->quit();
	}
}
	#!/usr/bin/perl -w


	# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
	#
	# MySQL_maintenance.pl
	#
	# By Dan Buettner - drbuettner-at-gmail.com
	# 12 Nov 2005
	#
	# Purpose: To automate regular checking, optimization and repair
	# of MySQL database tables.
	# Multi-threaded operation should compress maintenance window
	# for many installations, especially with multiple processors
	# and fast disk drives.
	#
	# Note: please submit feedback to the above email address.
	# I want this script to be a great tool in a MySQL admin's toolbox.
	#
	# Change history:
	#
	# 12 Nov 2005 - Dan Buettner - Original version written.
	#
	# 19 Dec 2005 - Dan Buettner - Compacted check,optimize,repair routines
	# into one, and improved comments.
	#
	# 29 Jun 2006 - Dan Buettner - Added support for limiting table types /
	# storage engines to be checked.
	#
	# 5 July 2006 - Dan Buettner - Added support for sending results via email
	# using SMTP, as well as the ability to specifically include database
	# and table names
	#
	# 10 July 2006 - Dan Buettner - Fixed bug with multiple email recipients
	#
	# 19 Feb 2007 - Dan Buettner - added ability to do SMTP authorization
	#
	# 21 Feb 2007 - Dan Buettner - skipped VIEWs, added report of data stats
	#
	# 18 Oct 2007 - Dan Buettner - fixed email address (removed @ symbol) - oops
	#
	# 30 Jan 2012 - Mark Woods - read MySQL options from user's my.cnf if exists
	#
	# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #


	use strict;
	use DBI;
	use Time::HiRes qw(tv_interval gettimeofday);
	use Net::SMTP;
	use threads;
	use threads::shared;
	use Thread::Queue::Any;
	use Getopt::Long;

	my $host = '';
	my $port = 3306;
	my $username = '';
	my $password = '';
	my $optimizeTables = 0;
	my $repairTables = 0;
	my $checkType = 'QUICK';

	my @masterDatabases = ();
	my @masterTables = ();

	my @excludeMasterDatabases = ();
	my $excludeDBs = '';

	my @includeMasterDatabases = ();
	my $includeDBs = '';

	my @excludeMasterTables = ();
	my $excludeTables = '';

	my @includeMasterTables = ();
	my $includeTables = '';

	my @includeTableTypes = ();
	my $includeTypes = '';
	my $tableTypes = 'MyISAM,InnoDB';

	my $help = 0;
	my $smtpHost = '';
	my $emailFrom = '';
	my $emailPassword = '';
	my $emailTo = '';
	my $subject = '';

	my $tableCount = 0;
	my $databaseCount = 0;
	my $rowCount = 0;
	my $dataLength = 0;
	my $indexLength = 0;

	my $errorCount = 0;
	share($errorCount);
	my $threadCount = 3;
	share($threadCount);
	my $threadsRunning = 0;
	share($threadsRunning);
	my @results = ();
	share(@results);
	my $workQueue;

	my $junk = GetOptions( "repair" => \$repairTables,
	"optimize" => \$optimizeTables,
	"host=s" => \$host,
	"port=s" => \$port,
	"user=s" => \$username,
	"password=s" => \$password,
	"check-type=s" => \$checkType,
	"threads=i" => \$threadCount,
	"exclude-dbs=s" => \$excludeDBs,
	"include-dbs=s" => \$includeDBs,
	"exclude-tables=s" => \$excludeTables,
	"include-tables=s" => \$includeTables,
	"table-types=s" => \$tableTypes,
	"smtp-host=s" => \$smtpHost,
	"email-from=s" => \$emailFrom,
	"email-password=s" => \$emailPassword,
	"email-to=s" => \$emailTo,
	"subject=s" => \$subject,
	"help" => \$help);


	# display help screen and exit, if needed
	if($help) {
	PrintUsage();
	exit;
	}

	# go do the work
	Main();
	# the end



	# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
	#
	# sub PrintUsage
	# Print usage screen and exit
	#
	# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
	sub PrintUsage {
	print <<END_OF_INFO;

	# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
	MySQL_maintenance.pl
	By Dan Buettner - drbuettner-at-gmail.com

	Multi-threaded table maintenance tool for MySQL database installations.
	Options are as follows:
	--host=<host> MySQL host address (name or IP)
	--user=<username> MySQL user to log in as - needs proper db privileges.
	--password=<password> MySQL password
	--check-type=<type> Check type (fast, quick, medium, extended). Default: quick
	--table-types=<type> Comma-separated table types to check (default InnoDB,MyISAM)
	--optimize Optimize tables (default: no)
	--repair Repair table if error found during check (default: no)
	--threads=n Number of worker threads (default 3). Total threads = n+1
	--exclude-dbs=<list> Comma-separated list of database names/patterns to not check
	--exclude-tables=<list> Comma-separated list of table names/patterns to not check
	--include-dbs=<list> Comma-separated list of database names/patterns to check
	--include-tables=<list> Comma-separated list of table names/patterns to check
	--smtp-host=<host> Hostname or IP address of SMTP server to send mail through
	--email-from=<email> Email address email should appear to be "from"
	--email-password=<pass> Password to use in conjunction with --email-from for SMTP AUTH
	--email-to=<email> Comma-separated addresses for email to go to
	--subject=xyz Subject line of message, default "MySQL maintenance on <host>"

	Example:
	./MySQL_maintenance.pl --host=mysql.domain.com --user=user --password=pass \
	--check-type=extended --repair --optimize --threads=5 --exclude-dbs=test,test1
	# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #

	END_OF_INFO
	}



	# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
	#
	# sub Main
	# Control thread. Interrogate the server for database and table names,
	# and queue them up to be checked. Exclude those that need to be excluded,
	# include those that need to be included, based on command line arguments.
	# Optimize and repair operations will be queued up by a worker thread
	# based on check results.
	#
	# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
	sub Main {
	my $startTime;
	my $endTime;
	my $elapsedTime;
	my $dbh;
	my @data;
	my @databases;
	my $database;
	my @tables;
	my $table;
	my $tableType;
	my $sth;
	my %workerThreads = ();
	my $timestamp;
	my($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst);
	my $result;
	my $message = '';

	$startTime = time;
	$dbh = ConnectMySQL($host, $port, $username, $password);
	$workQueue = Thread::Queue::Any->new();

	($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst) = localtime($startTime);
	$timestamp = sprintf("%04d-%02d-%02d %02d:%02d:%02d", $year + 1900, $mon + 1, $mday, $hour, $min, $sec);
	$message .= "$timestamp: MySQL_maintenance got started.\n";
	$message .= "Checking tables on host '$host' with '$checkType' option.\n";
	$message .= "Optimizing tables.\n" if ($optimizeTables);
	$message .= "Repairing tables.\n" if ($repairTables);
	$message .= "Running with $threadCount threads.\n\n";

	# construct pattern matching for databases and tables to include/exclude, table types to include
	@excludeMasterDatabases = split ',', $excludeDBs;
	if ($#excludeMasterDatabases >= 0) {
	$excludeDBs = join '$\|^', @excludeMasterDatabases;
	$excludeDBs = '^' . $excludeDBs . '$';
	}
	@excludeMasterTables = split ',', $excludeTables;
	if ($#excludeMasterTables >= 0) {
	$excludeTables = join '$\|^', @excludeMasterTables;
	$excludeTables = '^' . $excludeTables . '$';
	}
	@includeMasterDatabases = split ',', $includeDBs;
	if ($#includeMasterDatabases >= 0) {
	$includeDBs = join '$\|^', @includeMasterDatabases;
	$includeDBs = '^' . $includeDBs . '$';
	}
	@includeMasterTables = split ',', $includeTables;
	if ($#includeMasterTables >= 0) {
	$includeTables = join '$\|^', @includeMasterTables;
	$includeTables = '^' . $includeTables . '$';
	}
	@includeTableTypes = split ',', $tableTypes;
	if ($#includeTableTypes >= 0) {
	$includeTypes = join '$\|^', @includeTableTypes;
	$includeTypes = '^' . $includeTypes . '$' . '\|^NULL$\|^$';
	}

	# issue a flush tables command, as otherwise certain corruptions may not be recognized
	# (what's cached in RAM could be good while what's on disk is bad)
	$sth = $dbh->prepare('FLUSH TABLES');
	$sth->execute;
	$sth->finish;

	# get a list of local databases
	$sth = $dbh->prepare('SHOW DATABASES');
	$sth->execute;
	while (@data = $sth->fetchrow_array) {
	push @databases, $data[0];
	}
	$sth->finish;
	foreach $database (sort @databases) {
	# include or exclude this database if it matches input
	next if (($#excludeMasterDatabases >= 0) and ($database =~ /$excludeDBs/i));
	next if (($#includeMasterDatabases >= 0) and ($database !~ /$includeDBs/i));

	$databaseCount++;

	# find tables of the appropriate names and table types
	$sth = $dbh->prepare("SHOW TABLE STATUS FROM $database");
	$sth->execute;
	while (my $hash = $sth->fetchrow_hashref) {
	$table = $hash->{'Name'};
	$tableType = $hash->{'Engine'};

	# skip views
	next if (uc($hash->{'Comment'}) eq 'VIEW');

	# include or exclude this table if it matches input
	next if (($#excludeMasterTables >= 0) and ($table =~ /$excludeTables/i));
	next if (($#includeMasterTables >= 0) and ($table !~ /$includeTables/i));

	# exclude this table if it is not one of the specified table types/storage engines
	next if ($tableType !~ /$includeTypes/i);

	$tableCount++;
	if (defined ($hash->{'Rows'})) {
	$rowCount += $hash->{'Rows'};
	}
	$dataLength += $hash->{'Data_length'};
	$indexLength += $hash->{'Index_length'};

	my %checkItem = ();
	$checkItem{'database'} = $database;
	$checkItem{'table'} = $table;
	$checkItem{'operation'} = 'CHECK';
	$checkItem{'checklevel'} = uc($checkType);
	$workQueue->enqueue(%checkItem);
	}
	$sth->finish;
	}

	# create worker threads only after we have queued up work to do
	# (otherwise they would exit immediately)
	foreach my $counter (1..$threadCount) {
	$workerThreads{$counter} = threads->create(\&WorkerThreadSub, $counter);
	$threadsRunning++;
	}

	# the main thread needs to block while other threads are still running
	# (before declaring "all done")
	while ($threadsRunning > 0) {
	sleep 1;
	}

	$endTime = time;
	$elapsedTime = $endTime - $startTime;
	$dbh->disconnect;

	# add data statistics summary
	$message .= "Examined $rowCount rows - $tableCount tables - $databaseCount databases.\n";
	$message .= "Total data size: $dataLength bytes\n";
	$message .= "Total index size: $indexLength bytes\n";

	# add finished time / elapsed time info
	($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst) = localtime($endTime);
	$timestamp = sprintf("%04d-%02d-%02d %02d:%02d:%02d", $year + 1900, $mon + 1, $mday, $hour, $min, $sec);
	$message .= "\n$timestamp: MySQL_maintenance finished. $elapsedTime seconds elapsed.\n\n";

	# sort the result array and print contents.
	# this will give a listing of normal results in alphabetical order
	$message .= FormatResults("Database/table name", "Operation", "Time(sec)", "Status") . "\n";
	foreach $result (sort @results) {
	$message .= $result . "\n";
	}

	print $message;

	$subject = "MySQL maintenance on $host" if ($subject eq '');
	if ($errorCount == 0) {
	$subject .= ' -- all OK';
	} elsif ($errorCount == 1) {
	$subject = "ERROR -- $subject -- $errorCount ERROR";
	} else {
	$subject = "ERROR -- $subject -- $errorCount ERRORS";
	}

	if ($smtpHost ne '') {
	SendEmailMessage($smtpHost, $subject, $message, $emailFrom, $emailPassword, $emailTo);
	}
	}



	# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
	#
	# sub WorkerThreadSub
	# The life of a worker thread happens here - checking, repairing
	# and optimizing tables popped off the work queue.
	# A successful check operation will lead to an optimize, if designated.
	# A failed check operation will lead to a repair, if designated.
	# A successful repair will lead to an optimize, if designated.
	# A failed repair will not lead to an optimize.
	#
	# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
	sub WorkerThreadSub {
	my($threadID) = @_;

	my $dbh;

	$dbh = ConnectMySQL($host, $port, $username, $password);

	# pop work items off the queue
	while (my %workItem = $workQueue->dequeue_nb) {

	my $tableOK;
	my $result;
	my $warning = '';
	my $elapsedTime;
	my $operation;

	$operation = $workItem{'operation'};
	$operation = $workItem{'checklevel'} if ($workItem{'operation'} =~ /CHECK/i);

	($tableOK, $result, $elapsedTime) = HandleTableOperation($dbh, $workItem{'database'}, $workItem{'table'}, $operation);

	if ($workItem{'operation'} =~ /CHECK/i) {
	# handle table check operations results
	# if we encountered a problem, and repair tables is on,
	# queue a repair operation
	if (($repairTables) and (! $tableOK)) {
	my %item = ();
	$item{'database'} = $workItem{'database'};
	$item{'table'} = $workItem{'table'};
	$item{'operation'} = 'REPAIR';
	$workQueue->enqueue( %item );
	}

	# DO NOT ATTEMPT TO OPTIMIZE (YET) IF TABLE NOT OK
	# now that we're done with the check and no error,
	# queue this table to be optimized if so directed
	if ($optimizeTables && $tableOK) {
	my %item = ();
	$item{'database'} = $workItem{'database'};
	$item{'table'} = $workItem{'table'};
	$item{'operation'} = 'OPTIMIZE';
	$workQueue->enqueue( %item );
	}
	}
	elsif ($workItem{'operation'} =~ /REPAIR/i) {
	# handle repair operations results
	# DO NOT ATTEMPT TO OPTIMIZE IF TABLE NOT OK
	# now that we're done with the repair and no error,
	# queue this table to be optimized if so directed
	if ($tableOK && $optimizeTables) {
	my %item = ();
	$item{'database'} = $workItem{'database'};
	$item{'table'} = $workItem{'table'};
	$item{'operation'} = 'OPTIMIZE';
	$workQueue->enqueue( %item );
	}
	}
	elsif ($workItem{'operation'} =~ /OPTIMIZE/i) {
	# handle optimize operations results
	# (nothing special at this time)
	}

	$result = FormatResults("$workItem{'database'}.$workItem{'table'}", $workItem{'operation'},
	sprintf("%.3f", $elapsedTime), $result);

	$result .= ' :(' if (! $tableOK);
	$errorCount++ if (! $tableOK);
	push @results, $result;
	}

	$dbh->disconnect;
	$threadsRunning--;
	}



	# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
	#
	# sub FormatResults
	# Return a pretty printable string.
	#
	# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
	sub FormatResults {
	my(@columns) = @_;

	my $result;

	$result = sprintf("%-60s %-10s %10s %s", @columns);

	return $result;
	}



	# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
	#
	# sub HandleTableOperation
	# The operations to check, repair and optimize tables happen here.
	# Returned status from the MySQL engine determines success or failure.
	# Needs to be fleshed out with result codes and error conditions.
	#
	# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
	sub HandleTableOperation {
	my($dbh, $database, $table, $checkType) = @_;

	my @data;
	my $sth;
	my $sql;
	my $tableOK = 0;
	my $msgType;
	my $msgText;
	my $returnText = '';
	my $junk;
	my $startTime;
	my $elapsedTime;

	$sql = "CHECK TABLE $database.$table $checkType";
	$sql = "OPTIMIZE TABLE $database.$table" if ($checkType =~ /optimize/i);
	$sql = "REPAIR TABLE $database.$table" if ($checkType =~ /repair/i);
	$sth = $dbh->prepare($sql);
	$startTime = [gettimeofday];
	$sth->execute;
	if ($sth->errstr) {
	if ($sth->errstr =~ /Access denied/i) {
	$tableOK = 1;
	$returnText = 'Access denied';
	}
	}
	$elapsedTime = tv_interval($startTime, [gettimeofday]);
	while (@data = $sth->fetchrow_array) {
	($junk, $junk, $msgType, $msgText) = @data;

	if (($msgType =~ /error\|note/i) and ($msgText =~ /(engine\|handler) for the table doesn't support/i)) {
	# we tried to check a table that does not support that operation.
	# declare success in the absence of failure, and move on.
	$tableOK = 1;
	}
	elsif ($msgType =~ /error/i) {
	$tableOK = 0;
	}
	elsif ($msgType =~ /status/i) {
	if ($msgText =~ /^OK\|Table is already up to date/i) {
	$tableOK = 1 ;
	$msgText = 'OK';
	}
	}
	$returnText .= "$msgText ";
	}
	$sth->finish;


	return($tableOK, $returnText, $elapsedTime);
	}



	# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
	#
	# sub ConnectMySQL
	# Establish a database handle and return it. Exit with error if appropriate.
	#
	# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
	sub ConnectMySQL {
	my($host, $port, $username, $password) = @_;
	my $datasource = "DBI:mysql:mysql:$host;port=$port";
	if (-e "$ENV{HOME}/.my.cnf") {
	$datasource .= ";mysql_read_default_file=$ENV{HOME}/.my.cnf";
	}
	my $dbh = DBI->connect($datasource, $username, $password);
	if (! $dbh) {
	print DBI::errstr, "\n";
	print "Failed to connect to MySQL host $host with username $username.\n";
	exit(0);
	}
	return $dbh;
	}



	# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
	#
	# sub SendEmailMessage
	# Send email message through SMTP server.
	#
	# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
	sub SendEmailMessage {
	my($smtphost, $subject, $message, $from, $password, $recipientsList) = @_;

	my $smtp;
	my @recipients;

	@recipients = split /,\|\ /, $recipientsList;

	$smtp = Net::SMTP->new( $smtphost, Timeout => 15);
	if (defined ($smtp)) {
	if ($password ne '') {
	$smtp->auth($from, $password);
	}
	$smtp->mail( $from );
	$smtp->to( @recipients );
	$smtp->data();
	$smtp->datasend( "Subject: $subject\n" );
	$smtp->datasend( "\n$message\n" );
	$smtp->dataend();
	$smtp->quit();
	}
	}