Posted By

sdxxx on 02/03/11


Tagged

mysql


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

guillermoj88gmailcom


cronjob to optimize MySQL database


 / Published in: SQL
 

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

Cronjob and PHP examples below

  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.

Report this snippet  

You need to login to post a comment.