Fast Table Repairs for myisam

SQL table corruption can happen from time to time for a number of reasons. As we have scaled ExchangeDefender, I ran into a number of situations in which the data inserts were incomplete, or some maintenance tasks (large delete queries) crashed the table. This in turn would affect processes that produced reports and BI based on that data and really create a ton of pain and complaints.

So how does one repair crashed myisam tables?

myisamchk -r /var/lib/mysql/database/table.MYI

This process rebuilds the indexes and the entire table, but is very reactive in nature, you have to be aware of the crashed table in order to repair it.

There is a more maintenance-conscious process called mysqlcheck:

mysqlcheck –auto-repair -A -u dbuser -p database

This is a very thorough (read: slow as @#%@) process that will check all your tables and at the end of it proceed with the repair. Fantastic. Unless you have thousands and thousands of tables that you want to check quickly. This process on our dev box took a day and a half to run. Here is one that executes in less than 10 minutes in just a few lines of php:

$myquery = mysql_query(“show keys from $table”);
if(mysql_error() != “”) { backup / repair / report / log }

Stick that into a loop that goes through all your tables and you’re set.