myrep.php

<?php 
// myrep.php -- automated MySQL repairer
// 
// Modified by Jeff Beeman, October, 2010
// -- Added table optimization
// -- Reformatted code syntax
//
// Original:
// (C) Copyright 2008 David Cutting
// Released under the GNU GPL v3
// See http://www.purplepixie.org/myrep for information
//
$myrep_version="0.01";

// Defaults
$repair=false;
$check=true;
$optimize=false;
$warnings=false;
$database="";
$server="127.0.0.1";
$username="root";
$password="";

$sql=0;

function UsageExit() {
  global $myrep_version;
  echo "myrep.php ".$myrep_version." - MySQL Repair Script\n\n";
  echo "Usage: php myrep.php [options] -d database\n\n";
  echo "Options:\n";
  echo " -r --repair      Do a repair if required or not checked\n";
  echo " -o --optimize    Optimize tables\n";
  echo " -n --nocheck     Don't check just do a repair (if -r is set)\n";
  echo " -w --warnings    Repair tables with warnings (ignored if -n is set)\n";
  echo " -u --username X  Login with username of X\n";
  echo " -p --password X  Login with password of X\n";
  echo " -s --server X    Connect to server X\n";
  echo " -d --database X  Use database X (required)\n";
  echo "\n";
  echo "Examples:\n";
  echo " php myrep.php -r -u root -p secret -d somedb\n";
  echo " Connect to database somedb as root:secret on 127.0.0.1 (not\n";
  echo " specified) and check the tables, repairing if required\n";
  echo "\n";
  echo " php myrep.php -r -n -u someuser -p secret -s 1.2.3.4 -d somedb\n";
  echo " Connect to database somedb on 1.2.3.4 as someuser:secret and\n";
  echo " repair all the tables in the database without checking first\n";
  echo "\n";
  echo " php myrep.php -r -o -u someuser -p secret -s 1.2.3.4 -d somedb\n";
  echo " Connect to database somedb on 1.2.3.4 as someuser:secret and\n";
  echo " check and optimize the tables, repairing if required.\n";
  echo "\n";
  echo "See http://www.purplepixie.org/myrep for more information\n";
  echo "\n";
  exit();
}

function YesNo($val) {
  if ($val) return "Yes";
  return "No";
}

function DoQuery($q,$fatal=true) {
  global $sql;
  $result=mysql_query($q,$sql);
  if (mysql_errno($sql)>0) {
    echo "** SQL Query Returned Error\n";
    echo "** Query: ".$q."\n";
    echo "** Error: ".mysql_error($sql)." (".mysql_errno($sql).")\n";
    if ($fatal) {
      mysql_close($sql);
      echo "This error is fatal.\n";
      exit();
    }
  }
  return $result;
}

if ($argc<3) UsageExit();

for ($a=1; $a<$argc; $a++) {
  switch ($argv[$a]) {
    case "-r": case "--repair": $repair=true;
      break;

    case "-n": case "--nocheck": $check=false;
      break;

    case "-o": case "--optimize": $optimize=true;
      break;

    case "-w": case "--warnings": $warnings=true;
      break;

    case "-u": case "--username": $username=$argv[++$a];
      break;

    case "-p": case "--password": $password=$argv[++$a];
      break;

    case "-s": case "--server": $server=$argv[++$a];
      break;

    case "-d": case "--database": $database=$argv[++$a];
      break;

    default:
      UsageExit();
      break;
  }
}

if ($database=="") UsageExit();

echo "myrep.php ".$myrep_version." -- MySQL Repair Script\n";
echo "Connection      : mysql://".$username.":x@".$server."/".$database."\n";
echo "Repair          : ".YesNo($repair)."\n";
echo "Optimize        : ".YesNo($optimize)."\n";
echo "Check Tables    : ".YesNo($check)."\n";
echo "Act on Warnings : ".YesNo($warnings)."\n";
echo "\n";

$sql=mysql_connect($server,$username,$password);

if (!$sql) {
  echo "Error: Failed to connect to MySQL server ".$server." as ".$username."\n";
  echo mysql_error()."\n";
  exit();
}
  
$res=mysql_select_db($database,$sql);

if (!$res) {
  echo "Error: Failed to select database ".$database." on server ".$server." as ".$username."\n";
  echo mysql_error($sql)."\n";
  mysql_close($sql);
  exit();
}
  
echo "Connected to ".$database." on ".$server." as ".$username."\n";

$q="SHOW TABLES";
$r=DoQuery($q);

$tables=mysql_num_rows($r);
echo "Found ".$tables." tables in database\n\n";

while ($row=mysql_fetch_array($r)) {
  $table=$row[0];
  $haswarnings=false;
  $failed=false;
  echo "Table: ".$table."\n";
  if ($check) {
    echo "- checking table ".$table."...\n";
    $q="CHECK TABLE `".$table."`";
    $check=DoQuery($q);
    while ($line=mysql_fetch_array($check)) {
      if ($line['Msg_type']=="warning") {
        $haswarnings=true;
        echo "- warning: ".$line['Msg_text']."\n";
      }
      else if ($line['Msg_type']=="status") {
        if ($line['Msg_text']!="OK") $failed=true;
        echo "- status: ".$line['Msg_text']."\n";
      }
      else {
        echo $line['Msg_type'].": ".$line['Msg_text']."\n";
      }
    }
    mysql_free_result($check);
    echo "- check results: Warnings ".YesNo($haswarnings).", Failed ".YesNo($failed)."\n";
    if ($failed) echo "- Table should be repaired\n";
    else if ($haswarnings) echo "- Table may need repair (warnings)\n";
    else echo "- Table does not need repair\n";
    echo "\n";
  }
    
  // Repair Logic...
  // If repair AND either no checks or failed or has warnings&haswarnings
  if ( $repair && ( (!$check) || $failed || ($haswarnings && $warnings) ) ) {
    echo "- repairing table ".$table."...\n";
    $q="REPAIR TABLE ".$table;
    $repair=DoQuery($q);
    while ($row=mysql_fetch_array($repair)) {
      echo "- ".$row['Msg_type'].": ".$row['Msg_text']."\n";
    }
    mysql_free_result($repair);
  }
  echo "\n";

  // Optimize logic...
  if ($optimize) {
    echo "- optimizing table ".$table."...\n";
    $q="OPTIMIZE TABLE ".$table;
    $optimize=DoQuery($q);
    while ($row=mysql_fetch_array($optimize)) {
      echo "- ".$row['Msg_type'].": ".$row['Msg_text']."\n";
    }
    mysql_free_result($optimize);
  }
  echo "\n";
}
mysql_free_result($r);
mysql_close($sql);
?>