Advanced Search

cronjob to optimize MySQL database

/ Published in: SQL
Save to your folder(s)
Cronjob and PHP examples below

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. ## CODE
  2.  
  3. mysql -u<username> -p<password> <DATABASE name> -e "OPTIMIZE TABLE `table1`, `table2`, `etc`;"
  4.  
  5. ## NOTE: Something LIKE this may help. Dan W  made this script FROM his own
  6. ## knowledge, AND a little bit OF research ON Google. This script can ONLY 
  7. ## OPTIMIZE one DATABASE. IF you need TO OPTIMIZE more than one, you will 
  8. ## have TO ADD a few more LINES OF code IN there.
  9.  
  10. ## CODE
  11.  
  12. <?php
  13.  
  14. $server = 'localhost';
  15. $username = 'mysql_username';
  16. $password = 'mysql_password';
  17. $database = 'mysql_database_name';
  18.  
  19. ### connects TO the DATABASE, OR dies WITH error
  20. $connection = mysql_connect($server,$username,$password);
  21. IF (!$connection)
  22. {
  23.   die( mysql_error() );
  24. }
  25.  
  26. ### selects the db OF choice, OR dies WITH error
  27. $db_selection = mysql_select_db($database, $connection);
  28. IF (!$db_selection)
  29. {
  30.   die( mysql_error() );
  31. }
  32.  
  33. ### selects ALL TABLES IN the db OF choice, OR dies WITH error
  34. $alltables = mysql_query("SHOW TABLES") OR die ( mysql_error() );
  35.  
  36. ### loops through ALL OF the TABLES AND optimizes each, OR dies WITH error
  37. while ( $table = mysql_fetch_array($alltables) )
  38. {
  39.   mysql_query("OPTIMIZE TABLE `".$table."`") OR die( mysql_error() );
  40. }
  41.  
  42. ### closes the mysql connection
  43. mysql_close($connection);
  44.  
  45. ?>
  46.  
  47. ## Be sure TO edit the VARIABLES at the top TO the VALUES that are 
  48. ## appropriate FOR you. WHEN you GO TO run your cron job, again just 
  49. ## do "php /full/path/to/script.php" AND since optimizing TABLES may 
  50. ## LOCK down your DB FOR a SECOND, run the script at a TIME WHEN you 
  51. ## don't expect traffic.

URL: http://forums.asmallorange.com/topic/12948-cron-job-to-optimize-database/

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.