Tuesday, October 4, 2011

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.

No comments:

Post a Comment