DBA Notes: 2011/09/20

Cheng Li

MYSQL Repair Table

MySQL tables can become corrupt for several reasons, such as hardware failure, operating system bugs, viruses and bugs in MySQL. It is a good idea to always backup your information in case it can't be restored. You can try to repair the table. Before repair the table, you should make a backup of the whole database or the target table to avoid further damage.

Step1: Backup table

Mysql>BACK TABLE phpbb_posts_text TO '/backup/ phpbb_posts_text_bk/';

Step 2: Check table for error

Using inbuilt MySQL commands, like CHECK TABLE, myisamchk, and mysqlcheck.

Use phpMyAdmin tools or Mysql Command line to check table.

Mysql>Check table phpbb_posts_text;

Step3: Repair table

If corruption is detected, using REPAIR TABLE command to fix it. (Be noted, sometimes, check table can’t return the expected corruption. But, unfortunately, corruptions still happens! )

Use phpMyAdmin tools or Mysql command line to repair table

Mysql>repair table phpbb_posts_text;

Step 4: Restore from recent backup.

In case problem persists, restore MySQL database from recent backup.

Mysql> RESTORE TABLE FROM '/backup/ phpbb_posts_text_bk/';

Step 5: Use third-party MYSQL recovery software

When all else fail, use third-party MySQL recovery software to repair and restore the database.

Conclusion:

MYSQL经常需要执行表修复，让人不得不感慨innodb从Oracle引进tablespace的价值了。这样在表和操作系统之间架构了表空间一层保护和管理。而且表修复是非常危险的，常常引起数据丢失，甚至系统瘫痪。一定要先进行备份，才可以执行表修复。

Reference:

http://dev.mysql.com/doc/refman/5.1/en/repair-table-speed.html

http://filerepairtools.weebly.com/mysql-recovery-software.html

http://mysqlrepair.blogspot.com/