Optimizing Database With Cron?

[Rainquake]

Does anyone know how to setup a scheduled job with Cron to optimize a database?

 

Thanks

[Rainquake]

Nevermind, I finally found something in Google after 2 hours of searching with multiple combinations of keywords. If anyone ever wonders how to do this in the future, here is the why and how.

 

Why?: Maybe your database builds up pretty high overhead pretty quick.

 

How?:

 

1. Create a directory outside your public_html folder called "cron_scripts". I created mine at my account root level.

2. Using your favorite editor, copy and paste the following code, modifying the "host", "dbusername" and "dbpassword" to whatever is appropriate for your account:

 

><?php
$db = mysql_connect("host", "dbusername", "dbpassword"); 
$sql = "SHOW DATABASES"; 
$dbs_result = mysql_query($sql, $db); 
if(mysql_num_rows($dbs_result)) 
{ 
while($dbs_row=mysql_fetch_assoc($dbs_result)) 
{ 
$database = $dbs_row["Database"]; 
echo "\n\nOptimizing database $database : \n"; 
mysql_select_db($database, $db); 
$sql = "SHOW TABLE STATUS"; 
$tbls_result = mysql_query($sql, $db); 
if(mysql_num_rows($tbls_result)) 
{ 
while($tbls_row=mysql_fetch_assoc($tbls_result)) 
{ 
$TableName = "`".$tbls_row["Name"]."`";  
$sql = "OPTIMIZE TABLE ".$TableName; 
echo "\n".$sql; 
mysql_query($sql, $db); 
} 
} 
} 
} 
echo "\n\n"; 
mysql_close($db);
?>

 

3. Name the file "optimize.php" and upload to the "cron_scripts" directory we created earlier.

4. Now just go into cPanel and create a Cron Job with what ever schedule you want and a command line of the following nature, again changing "youraccount" to whatever is appropriate for your account:

 

>/usr/local/bin/php /home/youraccount/cron_scripts/optimize.php

 

And that's all folks! I hope this helps someone else in the future.

[TCH-Bruce]

Nice, thanks for showing how to do it.