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 TABLEphpbb_posts_textTO '/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: