Desi Programmer @ Work

MySQL offline and online tables repairing

I had a DB crash today. MySQL's tables crashed and I needed to repair them.

MySQL provides three ways of repairing its MyISAM tables:

  1. "Repair Table" SQL query. (e.g. "Repair Table TableName;" from MySQL Shell)
  2. Using the mysqlcheck utility. (e.g. "mysqlcheck -uuser -p DBName TableName" from command line shell)
  3. Using myisamchk utlity (e.g. "cd /path/to/mysql/files/DBName; myisamchk TableName" from command line shell)

The first two methods are online methods, that is you can perform them while MySQL server is running and apart from your corrupt tables other services keep running smoothly. However these processes can be painfully slow since the server must serve data and keep its integrity while performing the repairs.

However mysqlchk is an offline method; it requires that the server must be down. It opens the server files itself and examines them on its own. This method is quite faster. For me, the online method took more than an hour while the offline method completed its job in less than two minutes. It was worth shutting down the MySQL server.