Analyzing the root causes of Index file corruption in MySQL and methods to perform database recovery

MySQL is an open source database that is freely available under GNU General public license. It offers developers a range of storage engines, including Berkeley DB, InnoDB, Heap, and MyISAM. It incorporates adequate security measures and also provides regular security patches to keep the software up-to-date. When it comes to performance, it clearly dominates over the other databases. However, MySQL databases may get corrupt in the event of a virus infection, hardware malfunction, or an unexpected system shutdown. In such circumstances, you lose access to the vital information stored in the database. To overcome this problem, you need to go for MySQL recovery through a reliable third-party utility.

For instance, you might receive the following error message on your screen while trying to access a MySQL table:

“No more room in index file”

Cause: The above error occurs when you try to access a damaged or corrupt MySQL table. This would render all valuable records in the table inaccessible. At this stage, you may use an updated database backup to restore the table. However, if the backup is unavailable, you should follow the below mentioned resolution steps.

Resolution: For resolving the issue, follow the below given steps:

• Firstly, ensure that you have full administrative rights on the database that you are trying to access.
• Use the following commands to verify the integrity of the database table:

CHECK TABLE tablename;

myisamchk *.MYI

mysqlcheck *.MYI

• Use the 'REPAIR' command with appropriate options to repair corruption:

REPAIR TABLE table_name

• Now, use the 'EXIT' command to quit the database. Restart the MySQL server.

If the problem still persists, you should take help of an efficient MySQL repair utility. These software embed a range of advanced and highly robust mechanisms to efficiently scan corrupt MySQL databases and restore valuable objects, such as tables, primary keys, indexes, constraints, etc.

These third-party tools provide preview of all recoverable objects and allow saving log of the recovery process in a separate text file. They support all the latest MySQL versions, including MySQL 3.x, 4.x, 5.x, and 6.x. Furthermore, they are compatible with Windows 7, Vista, Server 2003, XP, and 2000.

How to check & verify InnoDB TableSpace File Corruption

MySQL database offers an offline InnoDB File Checksum Utility (innochecksum) to check the checksums for innodb files. When you run innochecksum utility on the mysql database then it will read and print checksums for innodb tablespace files. After calculating checksum it will compare the calculated checksum with stored checksum, if you found checksum mismatch between calculated checksum and stored checksum that means pages are corrupt.

Process of innochecksum Utility:

Read innoDB tablespace files-->Calculate checksum-->Compare checksum-->Report checksum mismatch

Why developed this tool: There are two main motives to develop this utility that are as:

• To speed up verifying the integrity of tablespace files after power outages.
• To verify the integrity after file copies.

Use as soon as Possible: As we know that checksum mismatch will shutdown the running mysql server so use this utility to as soon as possible rather than waiting production database will encounter the damaged pages.

Note: innochecksum does not work on tablespace files that the server already has open. For those types of files you can use CHECK Table command to check tables within the tablespace.

Recover Tablespace: When innochecksum utility finds checksum mismatch for the innodb tablespace files then it is recommended you to restore the tablespace from backup or start mysql server and use mysqldump to make a backup of table’s within the tablespace.

How to restore without Backup: If you don't have backup of corrupt tablespace then you should try any third party MySQL repair utility that will restore corrupt tablespace of mysql database.

Attention: Always maintain a backup for any database to avoid data loss when disaster will strike to the database.

Summary: innochecksum is an offline innodb file checksum utility that calculates, compares and reports checksum mismatch for innodb database. You can restore corrupt tablespace from backup or using any third party MySQL repair utility.