Checking all MySQL tables

It's well known that MyISAM tables are prone to corruption and need to be regularly checked and repaired. Moreover, in a production environment, it can be beneficial to run a daily check of all tables and mail news of any errors to an appropriate developer/DBA.

There are two options for checking MySQL tables. The most effective method is to run the myisamchk utility directly on the index files (.MYI) of the tables in question (some simple shell expansion makes this easy):

However, this proses a problem in that you must ensure that no other programs are accessing the tables while they are being checked. Hence they must be locked, or better still, the MySQL daemon stopped before running any checks. Perversely, if this is not done, the act of checking the tables can corrupt them.

Another option is to use the CHECK TABLE syntax in SQL (which does not pose a risk of corruption). There are various scripts (written in PHP and bash) posted on the CHECK TABLE manual page but this operation can be done easily through a single line:

This dynamically creates a list of "CHECK TABLE ..." commands which is piped into MySQL for execution.

For checking a selection of tables rather than all, use the LIKE operator when selecting the tables to check:

This only checks the tables that start 'User'. Note that the awk program has an extra clause to ensure that the first line of MySQL output is skipped.