The repair table command in MySQL is useful to repair database indicies when they become corrupt. A prime example of when an index can become corrupt is when the power is shut off unexpectedly to a server. If the power is shut off after a row has been inserted into the table, but before its index has been updated, the table will become corrupt and unusable.

Unfortunately, there isn’t a “repair all tables” option that I know of. Instead, you can use this simple PHP script to do the trick for you. It simply calls “show databases” to loop through a list of your databases, and then “show tables” so that it knows which tables exist for the repair command. Depending on how many databases you have and the size of their tables, this can take several minutes to run.


<?
### Enter your username and password into the connection string: ###
$dbLink = mysql_connect("localhost", "username", "password") or die("Unable to connect to the database.");

$sql = "show databases";
$query = mysql_query($sql) or die("error fetching database names");

while ($rs=mysql_fetch_array($query)) {

//echo "Database: " . $rs['Database'] . "\n";

mysql_select_db($rs['Database'],$dbLink) or die("Unable to select database: " . $rs['Database']);

$sql = "show tables";
$query2 = mysql_query($sql) or die("error fetching table names");

while ($rs2=mysql_fetch_array($query2)) {
$key = "Tables_in_" . $rs['Database'];
//echo "Table: " . $rs2[$key] . "\n";

$sql = "repair table " . $rs2[$key];
$query3 = mysql_query($sql) or die("Error repairing a table - $sql");
$rs3 = mysql_fetch_array($query3);
echo $rs3['Table'] . " | " . $rs3['Op'] . " | " . $rs3['Msg_type'] . " | " . $rs3['Msg_text'] . "\n";

}

}

echo "Finished!\n";

?>