Snippets Databases
Databases
MySQL Database Optimizer
by: Jeremy Brand
|
August 13, 2000
Version: 1.0.0
Type: Full Script
Category: Databases
License: GNU General Public License
Description: This optimization script can be ran safely without shutting down the MySQL server. It is handy for optimizing tables in a MySQL database. Optimization helps prevent corruption and increases the speed on you indexes. Personally I use this script to optimize the tables that run my forum at http://www.smackdown.com/. I run this using the CGI version of the php binary (that is php configured --without-apache). This program has also been tested and known to work with php 3.0.16 and php 4.0.1 pl2. Note: don't forget to change your HOST, USER, and PASSWORD to your own settings in the "CONFIG" section of this program. Enjoy, -jeremy brand (AKA: Nirvani) jeremy@nirvani.net http://www.nirvani.net/jeremy/
<?php
/**
MySQL Database Optimizer 1.0.0: Optimizes all tables of a given
MySQL database.
Copyright (C) 2000 Jeremy Brand
email: jeremy@nirvani.net
web: http://www.nirvani.net/jeremy/
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
**/
/**
** THIS SCRIPT PERFORMS TABLE LOCKING AND SAFELY OPTIMIZE ALL TABLES
** WITHIN THE GIVEN DATABASE.
**/
/**
** THIS IS TO BE RAN ON THE COMMAND LINE.
** THE FIRST ARGUMENT TO THE SCRIPT IS THE DATABASE NAME
** OF WHICH YOU WANT TO OPTIMIZE.
**
** EXAMPLE:
** shell> php -q this_script.php my_database_name
**/
/** CONFIG CHANGE HERE **/
/*************************************** *********** **/
/** IP or hostname of MySQL server **/ $db_host = '127.0.0.1';
/** MySQL user name **/ $db_user = 'mysql';
/** MySQL password **/ $db_pass = 'mysql_password';
/** Program start delay in seconds **/ $start_delay = 30;
/** PROGRAM STARTS HERE **/
/*******************************/
set_time_limit(0);
function format_time($seconds)
{
$hour = $seconds / 3600;
$total_time = $seconds - ($hour*3600);
$min = $seconds / 60;
$sec = $seconds % 60;
$format = sprintf("%02d",$hour).":".sprintf("%02d",$min).":".sprintf("%02d",$sec);
return $format;
}
if ($argc != 2)
{
print "Usage: php -q ".$argv[0]. " DATABASE_NAME\n\n";
print "EXAMPLE:\n";
print "shell> php -q this_script.php my_database\n\n";
exit();
}
mysql_connect($db_host, $db_user, $db_pass) or die(mysql_error() . "\n\n");
mysql_select_db($argv[1]) or die(mysql_error() . "\n\n");
print "\n";
print "WARNING: YOUR DATABASE WILL BE UNACCESSIBLE DURING THE OPTIMIZE.\n";
print "STARTING OPTIMIZE ON DATABASE '" . $argv[1] . "' in $start_delay SECONDS.\n"; flush();
print "CTRL-C TO ABORT.\n\n"; flush();
for ($i=0; $i<$start_delay; $i++)
{
print "."; flush();
sleep(1);
}
print "\n"; flush();
$q = "SHOW TABLES";
$r = mysql_query($q);
$q = "LOCK TABLES";
while($row = mysql_fetch_row($r))
{
$table[] = $row[0];
$q .= " " . $row[0]." WRITE,";
}
$q = substr($q,0,strlen($q)-1);
mysql_query($q);
print "THE DATABASE '".$argv[1]."' IS LOCKED FOR READ/WRITE.\n\n";
$t1 = time();
while(list($key, $val) = each($table))
{
$b1 = time();
$q = "OPTIMIZE TABLE $val";
print $q; flush();
mysql_query($q) or die("QUERY: \"$q\" " . mysql_error() . "\n\n");
$b2 = time();
$table_time = $b2 - $b1;
print "\t\t(TIME ELAPSED: " . format_time($table_time). ")\n"; flush();
}
$q = "UNLOCK TABLES";
mysql_query($q);
print "\n";
print "THE DATABASE '".$argv[1]."' IS NOW UNLOCKED.\n\n";
$t2 = time();
$total_time = $t2 - $t1;
print "TOTAL TIME ELAPSED: " . format_time($total_time) . "\n\n"; flush();
exit();
?>
PHPBuilder
Policies
PHP Developer
Topics
PHP Developer
Resources
PHP Developer
References
