March 4, 2011, 2:30 p.m.
posted by legendcoder
Taking Care of Your Database
You want to keep your MySQL database running smoothly.
Use the mysqlcheck utility from the command-line prompt on your web server periodically, or set up a cron job to run the utility on a regular schedule.
The command to run is:
mysqlcheck -u MySQL username -ppassword -Avor
A damaged database can leave your site dead in the water, especially if you rely on it for your e-commerce sales, forum posts, or blog comments. Corruption can occur for many reasons, although problems are most common when the connection to a high-traffic database is not closed properly by the script that opens it.
The utility mysqlcheck, which should be available with your MySQL installation, can head off problems with your database. One of the advantages of mysqlcheck is that it can perform maintenance on databases while the MySQL server continues to run. However, during its optimization and repair routines, mysqlcheck locks the table it's working on, typically for just a few seconds (depending on the size of the table). For that reason, it's best to run the utility during off-hours or when you know traffic is light.
Database maintenance with mysqlcheck shouldn't be necessary more than once a week. To schedule a weekly tune-up using cron, enter something like the following in your crontab file on the web server:
0 3 * * 0 mysqlcheck -u MySQL username -ppassword -Avor
This command will run mysqlcheck every Sunday at 3:00 a.m.
When using PHP to connect to a MySQL database, you should use the mysql_close( ) function at the end of the script to make sure it closes the connection it has been using.
For more information on using cron, see Recipe 1.8. For more information on using mysqlcheck, see http://dev.mysql.com/doc/mysql/en/using-mysqlcheck.html. For more information on using PHP's mysql_close( ) function, see http://www.php.net/manual/en/function.mysql-close.php.