Easy PHP Script for mySQL Database Optimization – Full Host Optimize

by Hiroshi on January 11, 2013

in database

Just tried it and its great. Give this script hostname, user and password and it will optimize all the databases in given host. Here it is:

Optimize mySQL Databases by Using PHP Script

<?php
$server = "hostname";
$user = "user";
$pwd = "password";
 $link = mysql_connect($server, $user, $pwd);
if (!$link) {
  die('Could not connect: ' . mysql_error());
}
$q= mysql_query("SHOW DATABASES") or die(mysql_error());
 while ($dbName = mysql_fetch_array($q)) {
   if ($dbName[0] != "information_schema") {
     echo "<br><br>";
	 echo " + Selecting " . $dbName[0] . "\n";
     echo "<br>";
	 $db_selected = mysql_select_db($dbName[0], $link);
        if (!$db_selected) {
        die ('Can\'t use $dbName[0] : ' . mysql_error());
     }
     $alltables = mysql_query("SHOW TABLES") or die(mysql_error());
     while ($tableName = mysql_fetch_array($alltables)) {
        echo " - Optimizing " . $tableName[0] . "\n";
        echo " , ";
		mysql_query("OPTIMIZE TABLE `".$tableName[0]."`") or die(mysql_error());
     }
   } else {
      echo "<br><br>";
	  echo " + Skipping " . $dbName[0] . "\n";
	  echo "<br>";
   }
}
echo "<br><br><div align='center'><strong>All tables were successfully optimized...</strong></div><br><br>";
// mysql_close($link);
?>

Related Posts

Previous post:

Next post: