How to maintenance and optimize MySQL Databases With cronjobs Automated ?

How to maintenance and optimize MySQL Databases With cronjobs Automated ?

How to do it in such an automated way to Maintenance and Optimize databases and tables so to save time.

 

hi. you probably have a larga database.
well i recomend you use mysql 5.1 and read more about mysql event(avaible only for mysql 5.1) on this page http://dev.mysql.com/tech-resources/articles/mysql-events.html

if you want to create a cronjob in third party sowftare let's say a php script try searching on google you'll find lots and lots of tutorials.

i've changed to mysql 5.1 one motnh ago and i optimised my queries very good, almost 3 times faster than 5.0
Also if you have large database you should try to split your database into partition depending on your database structure. read more on this page http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

 

Yes, I already use mysql 5.1.30

 

Is this command correct in cronjobs, and I did not get email says its true or its excuted so i do not know if its truely working and right command :

0 1 * * * mysqlcheck -Aao –auto-repair -u ROOTUSERNAME -p[PASSWORD of root username] > /dev/null

Code (markup):

Also found a way to use cronjob with PHP .. but not sure its correct way to be used with cron :

<?php



dbConnect()



$alltables = mysql_query("SHOW TABLES");



while ($table = mysql_fetch_assoc($alltables))

{



   foreach ($table as $db => $tablename)

   {

       mysql_query("OPTIMIZE TABLE '".$tablename."'")

           or die(mysql_error());

   }

   

}



?>

Code (markup):