Recipe 9.8. Taking Care of Your Database

Problem

You want to keep your MySQL database running smoothly.

Solution

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

The four option flags at the end of the command (-Avor) instruct mysqlcheck to run on All your databases, echo the results verbosely to your screen, optimize tables, and repair any problems it finds.

Discussion

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.

Problem

Solution

Discussion

See Also