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 ...

Get Web Site Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.