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.

How to Repair Corrupt Table of MySQL Database?

MySQL database has tables to store data. All Big and small organizations use MySQL database for storing the employee's records.  A database administrator can do various operations on table like insert, delete, retrieve, arrange, and many more. Tables are very prone to corruption, there may be various reasons for corruption like Unexpected server shutdown, faulty hardware, virus attach, and more.

Let’s consider a scenario, wherein a database administrator is trying to access MySQL database table and got below error message.

“undef error - DBD::mysql::db selectrow_array failed: Table 'emplyee' is
marked as crashed and should be repaired [for Statement "SELECT LENGTH(thedata)
FROM attach_data WHERE id = ?"] at height/Formula.pm line 344
Height::Attachment::datasize('Bugzilla::Attachment=HASH(0x9df119c)') called ” 

The above error message states that employee's table is corrupt and need to repair.

How to Fix: A database administrator can fix the above error message by the two methods.

1. Repair Table using PHPMyAdmin
2. Repair Table using myisamchk command 

1. Repair Table using PHPMyAdmin: If you have installed PHPMyAdmin on your MySQL server then use this method and follow the following steps:

• Login to PHPMyAdmin
• Choose database that has corrupt table.
• Go to the main panel.
• Go to the list of tables and select the table that you want to repair.
• And finally click on the “Repair Table” button.
• You have done it.

2. Repair Table using myisamchk Command: If you have not installed the PHPMyAdmin on your MySQL server then you can repair corrupt MySQL table via storage engine of MySQL database. Run below script of myisamchk command with r option to repair the corrupt MySQL database table. 

# myisamchk -r profiles.MYI
recovering (with sort) MyISAM-table 'profiles.MYI'
Data records: 80
Fixing index 1
Fixing index 2
Fixing index 3

Last Minute: Sometimes, PHPMyAdmin and myisamchk command are not able to fix badly corrupt MySQL database. At this time, it is recommended you to try any third party MySQL database repair software to fix the corrupt table.

Note: Always try free demo version of any software before complete download.