Revision: 40495
Updated Code
at February 3, 2011 01:04 by sdxxx
Updated Code
## CODE mysql -u<username> -p<password> <database name> -e "OPTIMIZE TABLE `table1`, `table2`, `etc`;" ## NOTE: Something like this may help. Dan W made this script from his own ## knowledge, and a little bit of research on Google. This script can only ## optimize one database. If you need to optimize more than one, you will ## have to add a few more lines of code in there. ## CODE <?php $server = 'localhost'; $username = 'mysql_username'; $password = 'mysql_password'; $database = 'mysql_database_name'; ### connects to the database, or dies with error $connection = mysql_connect($server,$username,$password); if (!$connection) { die( mysql_error() ); } ### selects the db of choice, or dies with error $db_selection = mysql_select_db($database, $connection); if (!$db_selection) { die( mysql_error() ); } ### selects all tables in the db of choice, or dies with error $alltables = mysql_query("SHOW TABLES") or die ( mysql_error() ); ### loops through all of the tables and optimizes each, or dies with error while ( $table = mysql_fetch_array($alltables) ) { mysql_query("OPTIMIZE TABLE `".$table."`") or die( mysql_error() ); } ### closes the mysql connection mysql_close($connection); ?> ## Be sure to edit the variables at the top to the values that are ## appropriate for you. When you go to run your cron job, again just ## do "php /full/path/to/script.php" and since optimizing tables may ## lock down your DB for a second, run the script at a time when you ## don't expect traffic.
Revision: 40494
Initial Code
Initial URL
Initial Description
Initial Title
Initial Tags
Initial Language
at February 3, 2011 01:02 by sdxxx
Initial Code
##CODE mysql -u<username> -p<password> <database name> -e "OPTIMIZE TABLE `table1`, `table2`, `etc`;" ##NOTE: Something like this may help. Dan W made this script from his own knowledge, and a little bit of research on Google. This script can only optimize one database. If you need to optimize more than one, you will have to add a few more lines of code in there. CODE <?php $server = 'localhost'; $username = 'mysql_username'; $password = 'mysql_password'; $database = 'mysql_database_name'; ### connects to the database, or dies with error $connection = mysql_connect($server,$username,$password); if (!$connection) { die( mysql_error() ); } ### selects the db of choice, or dies with error $db_selection = mysql_select_db($database, $connection); if (!$db_selection) { die( mysql_error() ); } ### selects all tables in the db of choice, or dies with error $alltables = mysql_query("SHOW TABLES") or die ( mysql_error() ); ### loops through all of the tables and optimizes each, or dies with error while ( $table = mysql_fetch_array($alltables) ) { mysql_query("OPTIMIZE TABLE `".$table."`") or die( mysql_error() ); } ### closes the mysql connection mysql_close($connection); ?> Be sure to edit the variables at the top to the values that are appropriate for you. When you go to run your cron job, again just do "php /full/path/to/script.php" and since optimizing tables may lock down your DB for a second, run the script at a time when you don't expect traffic.
Initial URL
http://forums.asmallorange.com/topic/12948-cron-job-to-optimize-database/
Initial Description
Cronjob and PHP examples below
Initial Title
cronjob to optimize MySQL database
Initial Tags
mysql
Initial Language
SQL