Tuesday, July 19, 2011

How to Restore Corrupt MySQL Database Tables

MySQL database is a registered product of sun Microsystems. It is platform independent in nature that means a database administrator can install MySQL database on different operating systems like windows, Linux, and others. MySQL database uses tables for storing the data and records. Sometimes these tables get corrupted by the several reasons like virus attach, software malfunction, improper system shutdown, hardware problem, and so on. When you are trying to repair corrupt mysql database by “mysqlicheck” command but unable to fix then you can try lower level of mysqlicheck command.

You can use lower level of mysqlcheck command in following manner:

Stop Server Process: To use lower level command of mysqlicheck, you should have to stop the server process. You can do it by using service MySQL stop query.

Find Data Files: After end of the server process, you will have to find the data files where tables store. The data files usually present in var/lib/mysql/database_name.

 Now, you can run the given following command against the table.

myisamchk -r -v -f --sort_buffer_size=128M --key_buffer_size=128M /var/lib/mysql/database/table.MYI    

Now, corruption in the mysql table has been fixed

Replace Database: You can replace corrupt database/table with repaired database/table.

Note: In most of the cases, above inbuilt command is able to fix the corruption in tables but sometimes fail to repair. Most of the database administrators recommend to try third party MySQL database recovery software to fix corruption in the MySQL table.

PS: Always try demo version of any software before purchase.

4 comments:

  1. it's helpful code in my project thanks for your wonderful help...

    ReplyDelete
  2. Why use MyISAM when InnoDB is better ?

    ReplyDelete
  3. @rautamiekka: R-Tree and FullText indexes are good reasons to use MyISAM.

    For recovery you need to have at least as much free space as the table which you're trying to restore.

    ReplyDelete