General Preventive Maintenance

This section outlines some general strategies to help you maintain the integrity of their databases:

• Enable any auto-recovery capabilities that the server offers.

• Schedule regular preventive maintenance to check your tables.

• Have a policy of making regular database backups, so that you have something to fall back on if your databases are irretrievably lost.

The first two items are discussed here. Backup techniques are covered later in "Making Database Backups."

Using the Server's Auto-Recovery Capabilities

One of your first lines of defense in maintaining database integrity is the MySQL server's crash recovery capabilities. Some of these are automatic and happen at server startup. One is optional and must be enabled explicitly.

When the server starts, it can perform certain types of table checking to help deal with problems resulting from an earlier server or machine crash. MySQL is designed to recover from a variety of problems, so if you do nothing more than restart the server normally, it will make the necessary corrections for you in many cases:

• If the InnoDB storage engine is enabled, it can check for a variety of problems automatically. Committed transactions that are present in the redo logs but not yet flushed to tables are rolled forward (redone). Uncommitted transactions in progress at the time of the crash are rolled back (discarded) using the undo logs. The result is to leave your InnoDB tables in a consistent state, so that their contents reflect all transactions that had been committed up to the point of the crash.

• The BDB storage engine, if enabled, also attempts auto-recovery based on the contents of its log files.

If InnoDB or BDB auto-recovery fails due to a non-recoverable problem, the server exits after writing a message to the error log. To force the server to start up anyway so that you can attempt a manual recovery procedure, see "Coping with InnoDB or BDB Auto-Recovery Problems" later in the chapter.

For MyISAM tables, the server supports an optional form of table recovery. With this capability enabled, the server performs a check each time it opens a MyISAM table. If the table was not closed properly or is marked as crashed, the server checks and repairs it automatically. To enable MyISAM table recovery, start the server with the --myisam-recover=level option. The value of level is a comma-separated list of one or more of the following values: DEFAULT (same as specifying no level), BACKUP (create a backup of the table if the repair will change it), FORCE (force recovery even if more than a row of data will be lost), or QUICK (quick recovery). For example, to force recovery if problems are found, but create a backup first, start the server with --myisam-recover=BACKUP,FORCE.

The --myisam-recover option is useful as a general maintenance strategy because otherwise a MyISAM table that is found to have problems becomes unavailable until you initiate repair manually. But --myisam-recover is especially important if you run the server with the --delay-key-write option or have individual MyISAM tables configured to use delayed key writes. Under those conditions, index changes are not flushed until tables close, which means that indexes for any open delayed-key tables will need repair in the event of a crash.

Scheduling Preventive Maintenance

In addition to enabling auto-recovery, you should consider setting up a schedule of preventive maintenance. This helps detect problems automatically so that you can take steps to correct them. By arranging to check your tables on a regular basis, you'll reduce the likelihood of having to resort to your backups. On Unix, this is most easily accomplished by using a cron job, typically invoked from the crontab file of the account used to run the server. See Chapter 11, "General MySQL Administration," for information about setting up cron jobs.

The mysqlcheck program is useful for checking MyISAM and InnoDB tables while the server is online. Suppose that you want to set up a maintenance job that invokes mysqlcheck. If you run the server as the mysql user, you can set up periodic check from that user's crontab file. Add an entry to the file that looks something like this:

0 3 * * 0 /usr/local/mysql/bin/mysqlcheck
    --all-databases --check-only-changed --silent

The command as shown here takes two lines, but you should enter it all on a single line. (You should also use the path for mysqlcheck that is correct for your system.) The entry tells cron to run mysqlcheck at 3 a.m. every Sunday. You can vary the time or scheduling as desired.

The --all-databases option causes mysqlcheck to check all tables in all databases. This gives you an easy way to use it for maximum effect. To have mysqlcheck check only certain databases or tables, see the program description in Appendix F.

The --check-only-changed option tells mysqlcheck to skip any table that hasn't been modified since it was last checked successfully, and the --silent option suppresses output unless there are errors in the tables. cron jobs typically generate a mail message if a job produces any output at all, and there's little reason to receive mail for table-checking jobs that find no problems. Note that even with --silent you may get some diagnostic output from mysqlcheck if your databases have tables that it doesn't know how to check (for example, BDB tables).

Note: While a table is being checked, it cannot be updated. Don't use this strategy for large tables that need to be updated frequently if you cannot afford to block updates for the duration of the check operation.