I can't remember form where I got this script but.. I have 2 versions.. this one which will "optimize" all the db's.. and another which replaces the "optimize" with "analyze":
to 'repair' run the script with repair as an option
#!/usr/local/bin/php
<?
# Please setup your MySQL Config before continuing
# It is found right here at the top of this file...
#########################################################
# MYSQL CONFIG
#########################################################
global $db_user, $db_password, $db, $db_host;
$db_user = "root";
$db_password = "PASSWORD";
$db = "server";
$db_host = "localhost";
# No additional includes are needed to run this script!
# All functions & config are in this file
# CONFIG SHOWN ABOVE
# FUNCTIONS SHOWN AT END OF SCRIPT
#########################################################
$dbname = isset($_GET["dbname"])?$_GET["dbname"]:'all'; # Try not to edit this, ok?
$repair = $_GET["repair"]; # Make this 1 if you want to repair instead
global $DoWhat;
if ($repair==1) {
$DoWhat="REPAIR";
} else {
$DoWhat="OPTIMIZE";
}
#########################################################
# PERMISSIONS required for script to work
#########################################################
# Must have access to MySQL, using the login info above
#
# Must be able to: Show Databases, Show Tables from php
#
# Must be able to: $DoWhat TABLE $db.$table;
#
# Must be able to: Repair Table $db.$table;
#########################################################
#################
# USAGE
#################
# TO OPTIMIZE YOUR ENTIRE SERVER (the $db_host) above
# optimizedb.php?dbname=all
# TO OPTIMIZE A SINGLE DATABASE
# optimizedb.php?dbname=mydatabase
# TO OPTIMIZE A SINGLE TABLE
# optimizedb.php?dbname=mydatabase.mytable
#
############################
# ACTUAL SCRIPT
############################
if (strstr($dbname,".")) {
# Optimize Single Table Only
$optresult = SmartQuery("ARR $DoWhat TABLE $dbname;");
$tbname = $optresult[0];
$tbstatus = $optresult[3];
# echo ForceEcho() . "[<font color=darkgreen>$tbstatus</font>] <b>$tbname</b><br>";
echo "$tbname: $tbstatus\n";
} else if ($dbname=="") {
echo "No usage commands provided!<br><br>
optimizedb.php usage commands:<br><br>
# TO OPTIMIZE YOUR ENTIRE SERVER
# optimizedb.php?dbname=all
# TO OPTIMIZE A SINGLE DATABASE
# optimizedb.php?dbname=mydatabase
# TO OPTIMIZE A SINGLE TABLE
# optimizedb.php?dbname=mydatabase.mytable
";
} else {
if ($dbname=="all") {
# Optimize Entire Server
$dbs = SmartQuery("BIGARR show databases;");
# echo "<b>Optimizing " . count($dbs) . " databases: DO NOT STOP YOUR SERVER!!!<br>" . ForceEcho() . ForceEcho() . ForceEcho() . ForceEcho() . ForceEcho() . "<br><br></b>" . ForceEcho() . ForceEcho() . ForceEcho();
echo "Optimizing " . count($dbs) . " databases: DO NOT STOP YOUR SERVER!!!"; echo "\n\n";
for ($y=0; $y < count($dbs); $y++) {
$dbname=$dbs[$y][0];
$optlocations = OptimizeDatabase($dbname);
for ($x=0; $x < count($optlocations); $x++) {
$tbname = $optlocations[$x][0][0];
$tbstatus = $optlocations[$x][0][3];
if ($tbname=="") {
} else {
if ($tbstatus=="") {
} else {
if (strstr($tbstatus,"doesn't exist")) {
} else {
# echo ForceEcho() . "[<font color=darkgreen>$tbstatus</font>] <b>$tbname</b><br>" . ForceEcho() . ForceEcho() . ForceEcho();
echo "$tbname: $tbstatus\n";
}
}
}
}
}
} else {
# Optimize One Database
$optlocations = OptimizeDatabase($dbname);
for ($x=0; $x < count($optlocations); $x++) {
$tbname = $optlocations[$x][0][0];
$tbstatus = $optlocations[$x][0][3];
# echo ForceEcho() . "[<font color=darkgreen>$tbstatus</font>] <b>$tbname</b><br>";
echo "$tbname:$tbstatus\n";
}
}
}
###################################################################
# OPTIMIZE DATABASE FUNCTION
###################################################################
# Don't edit this!
# Don't edit this!
# Don't edit this!
# Don't edit this!
###################################################################
# Also shows some usage of the SmartQuery Multi-Dimensional Arrays
###################################################################
function OptimizeDatabase($dbname) {
# Gets all of the tables in $dbname, and optimizes them..
global $DoWhat;
$tables = SmartQuery("BIGARR SHOW TABLES FROM `$dbname`;");
$optresult = '';
for ($x=0; $x < count($tables); $x++) {
$table = $tables[$x][0];
$optresult[$x] = SmartQuery("BIGARR $DoWhat TABLE `$dbname`.$table;");
}
return $optresult; # returns format like this: $optresult[$x][0][3] thats the status...
}
#################################################
# SMART QUERY - MULTIDIMENSIONAL ARRAY SUPPORT
#################################################
# usage:
# to return a count: $recordcount = SmartQuery("SELECT count(*) from database.table;");
# to return a single info: $record = SmartQuery("SELECT FirstName from database.table WHERE LastName='jones';");
# to return 1 row as an array: $record = SmartQuery("ARR SELECT * from database.table WHERE LastName='jones';");
# $somethingCol1 = $record[0];
# $somethingCol2 = $record[1];
# to return all rows as a multi-dimensional arrays: $record = SmartQuery("BIGARR SELECT * from database.table WHERE LastName='jones';");
# $somethingRow1Col1 = $record[0][0];
# $somethingRow1Col2 = $record[0][1];
# $somethingRow2Col1 = $record[1][0];
# $somethingRow2Col2 = $record[1][1];
# Don't edit this!
# Don't edit this!
# Don't edit this!
# Don't edit this!
# Don't edit this!
function SmartQuery ($QueryString) {
global $db, $db_host, $db_user, $db_password;
$con = mysql_connect($db_host,$db_user,$db_password);
if (!$con) {
die("MySQL Database Connection Problem: " . mysql_error() . "\n *");
exit;
}
if (is_array($QueryString)) {
# Array of queries...
for ($x=0; $x < count($QueryString)+1; $x++) {
$arrquery = $QueryString[$x]; # The Query ($x)
if ($arrquery=="") {
# No query
} else {
# has query
if (strtolower(substr($arrquery,0,3))=="arr") {
# Returns a normal ARR of 1 row
$arrquery=trim(substr($arrquery,3));
$returnarray=true;
$returnbig=false;
} else {
if (strtolower(substr($arrquery,0,6))=="bigarr") {
# Returns a big ARR of all rows
$arrquery=trim(substr($arrquery,6));
$returnarray=true;
$returnbig=true;
} else {
$returnarray=false;
$returnbig=false;
}
}
$query = $arrquery;
mysql_select_db("$db");
$mysql_result = mysql_query($query, $con);
if ($returnbig==true) {
# Output large array - special
$ret[0] = mysql_fetch_row($mysql_result); # set first row
for ($y=1; $y < mysql_num_rows($mysql_result); $y++) {
# Multi-dimensional arrays
$ret[$y] = mysql_fetch_row($mysql_result);
}
$buildret[$x]=$ret;
} else {
# Normal output
$ret = mysql_fetch_row($mysql_result);
if ($returnarray==true) {
$buildret[$x] = $ret;
} else {
$buildret[$x]=$ret[0];
}
}
} # check empty
} # for x (array queries
return $buildret;
# END OF ARRAY QUERIES
} else {
# BEGIN OF SINGLE QUERY
# Normal query
if (strtolower(substr($QueryString,0,3))=="arr") {
# Returns a normal ARR of 1 row
$QueryString=trim(substr($QueryString,3));
$returnarray=true;
$returnbig=false;
} else {
if (strtolower(substr($QueryString,0,6))=="bigarr") {
# Returns a big ARR of all rows
$QueryString=trim(substr($QueryString,6));
$returnarray=true;
$returnbig=true;
} else {
$returnarray=false;
$returnbig=false;
}
}
$query = $QueryString;
mysql_select_db("$db");
#error_reporting(0);
$mysql_result = mysql_query($query, $con);
if ($returnbig==true) {
# Output large array - special
@$ret[0] = mysql_fetch_row($mysql_result); # set first row
for ($x=1; $x < @mysql_num_rows($mysql_result); $x++) {
$ret[$x] = mysql_fetch_row($mysql_result);
}
return $ret;
} else {
# Normal output
$ret = mysql_fetch_row($mysql_result);
if ($returnarray==true) {
return $ret;
} else {
return $ret[0];
}
}
}
}
# Forces the screen to echo some information back, by flooding it with spaces. Do not use this in a loop with 1 million or more, or it could fill your entire screen up to a GB of data.
# use this to force status onto the screen.
# You can edit this :P
# You can edit this :P
# You can edit this :P
# You can edit this :P
function ForceEcho() {
/*
return "
.";
*/
}
?>
