Optimization of all mysql databases on a server
| From: | itoctopus | Date: | Sat, 24 Mar 2007 18:57:55 +0000 |
| Subject: | Optimization of all mysql databases on a server | ||
| Groups: | php.general | ||
| Request: | Send a blank email to php-general+get-251293@lists.php.net to get a copy of this message | ||
The purpose of this script is to optimize all associated tables in all
databases on a mysql server. This script is working great, you can put in a
CRON and run it every day. Feel free to throw in your 2 cents!
/*
*This script optimizes all the tables in all the databases
*The script flushes the results to the client's PC so that the user will
see what's happening in real-time.
*/
echo("<html><head><title>MySQL
Optimization</title></head><body>");
$Connect = @mysql_connect('localhost', 'root', '')
or die("Couldn't connect to MySQL:<br>" . mysql_error() .
"<br>" .
mysql_errno());
$query = "Show Databases";
$result = @mysql_query($query,$Connect);
$numDatabases = mysql_num_rows($result);
$i = 1;
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo("<b>Optimizing Database: ".strtoupper($row['Database'])."
[$i/$numDatabases]</b><br>\n");
$Db = @mysql_select_db($row['Database'], $Connect)
or die("Couldn't select
database:".strtoupper($row['Database'])."!<br>" . mysql_error().
"<br>" .
mysql_errno());
$resultTables = mysql_query("SHOW TABLES");
$numTables = mysql_num_rows($resultTables);
$j = 1;
while ($table = mysql_fetch_assoc($resultTables)){
foreach ($table as $db => $tablename){
echo("--->Optimizing Table: ".strtolower($tablename)."
[$j/$numTables]...");
flush();
mysql_query("OPTIMIZE TABLE ".$tablename) or die(mysql_error());
echo("Done!<br>\n");
flush();
$j++;
}
}
echo("<b>Database ".strtoupper($row['Database'])." Is Now
Optimized!</b><br>");
echo("________________________________________<br>");
$i++;
}
echo("*****************DONE*******************<br>");
echo("</body></html>");
--
itoctopus - http://www.itoctopus.com