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