Benjo’s Reign = Brain :)

<?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;
}
 
?>

6 Responses to “php system script for maintaining mysql databases and tables”

1. Benjo Says:
   June 12th, 2008 at 20:39

   maybe someone will get with more ideas of how to extend this script…

2. php system script for maintaining mysql databases and tables Says:
   June 12th, 2008 at 20:47

   […] JR Raphael wrote an interesting post today onHere’s a quick excerptUse it in cron and have a peace with tables, indexes and keys […]

3. Tess Says:
   October 28th, 2008 at 23:11

   Good post.

4. Borgscan Says:
   December 7th, 2008 at 14:09

   Correct me if I’m wrong, but wouldn’t your tables remain locked if your script dies here:

   $repair = mysql_query(”REPAIR TABLE “.$col_value.” QUICK”) or die(’Query failed: ‘ . mysql_error());

   I’ve been trying to figure out a way around this and the only one I can think of is to use an error handler that also unlocks the table. e.g:

   …or die((mysql_errno(), mysql_error(), $repair, realpath(__FILE__), __LINE__);

   $unlock = mysql_query(”UNLOCK TABLES”) or die(’Query failed: ‘ . mysql_error());

   Then your error handler could be someting like:

   function sql_error($errno, $error, $query, $file, $line)
   {
   define(”SQL_ERRNO”, $errno);
   define(”SQL_ERROR”, $error);
   define(”SQL_QUERY”, $query);
   define(”SQL_FILE”, $file);
   define(”SQL_LINE”, $line);

   mysql_query(”UNLOCK TABLES”);
   trigger_error(”sql”, E_USER_ERROR);
   }

   function error_handler($errno, $error, $file, $line)
   {
   if ($error == “sql”)
   {
   echo “An Error Has Occurred[$errno] “.SQL_ERROR.”Query: “.SQL_QUERY.”File: “.SQL_FILE.”Line: “.SQL_LINE.”PHP: “.PHP_VERSION.” (”.PHP_OS.”)”;
   }
   else
   {
   echo “An Error Has Occurred“;
   }
   }

5. Benjo Says:
   December 7th, 2008 at 22:24

   Borgscan I must say you have pretty deep thoughts, but I think that the error handler in this script is an overhead.
   As posted on http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html the lock is released with a dropped connection - eg. if the script dies or terminates in any way other than hangs as a zombie process.
   Don’t get me wrong, your approach is very good and programs should have errors handled in every way.

6. Borgscan Says:
   December 12th, 2008 at 14:22

   Thanks for the information that it unlocks the if the connection is dropped, I didn’t know that.

   I made a slight mistake when calling the error handler too, it should be:

   …or die(sql_error((mysql_errno(), mysql_error(), $repair, realpath(__FILE__), __LINE__));

Leave a Reply