php system script for maintaining mysql databases and tables
Thursday, June 12th, 2008This is one of the php system scripts I wrote and would like to share. It can be extended but it already does its basic stuff - mysql maintenance with email notification.
Use it in cron and have a peace with tables, indexes and keys 
< ?php // which databases to maintain $databases = "provider wp subversion mysql"; $returnval = maintain_mysql($databases); @mail("root","mysql database maintainer", $returnval); /** * mysql table maintainer - optimizes and rebuilds indexes on mysql tables * * @author benjamin.povirk@***** 3.1.07 * @return string * @param databases to maintain */ function maintain_mysql ($databases) { $database = explode(" ", $databases); foreach ($database as $db) { $returnval .= "maintenance on all tables in ".$db." started\r\n"; // Connecting, selecting database $link = mysql_connect('localhost', 'YOUR_USER_HERE', 'YOUR_PASSWORD_HERE') or die('Could not connect: ' . mysql_error()); mysql_select_db($db) or die('Could not select database '.$db); //repair and optimize all tables in current database $query = "SHOW TABLES"; $result = mysql_query($query) or die('Query failed: ' . mysql_error()); while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { foreach ($line as $col_value) { $lock = mysql_query("LOCK TABLE ".$col_value." WRITE") or die('Query failed: ' . mysql_error()); $optimize = mysql_query("OPTIMIZE TABLE ".$col_value) or die('Query failed: ' . mysql_error()); $repair = mysql_query("REPAIR TABLE ".$col_value." QUICK") or die('Query failed: ' . mysql_error()); $unlock = mysql_query("UNLOCK TABLES") or die('Query failed: ' . mysql_error()); } } mysql_free_result($result); mysql_free_result($repair); mysql_free_result($optimize); // Closing connection mysql_close($link); $returnval .= "maintenance on all tables in ".$db." finished successfuly\r\n\r\n"; } return $returnval; } ?>
