Performing Crash Recovery and Media Recovery after Facing Corruption in InnoDB tables of MySQL Database

MySQL is known for its ease-of-use, performance, and scalability. The versions prior to MySQL 5.5 have MyISAM as their default database storage engine. In MySQL 5.5, the default settings have been changed as InnoDB becomes the default database storage engine. This change has brought a handful of significant improvements in the lives of database users and administrators. Some of the major benefits of using InnoDB tables include ACID transactions, Referential integrity, and Crash recovery.

With the increased hard drive and memory capacity, performance and data availability have become the topmost user priorities. Some of the real world benefits of using InnoDB tables are listed below:

• In the event of server crash due to any software or hardware issue, you just need to restart the database. InnoDB can finalize and undergo all the changes to your database that were left uncommitted due to the crash.
• InnoDB maintains a buffer pool to store table and index data. When some information needs to be processed, it is directly fetched from this buffer speeding up data access and retrieval.
• With InnoDB, you can split your data into different tables and enforce referential integrity constraints on these tables. When you update or delete data in one table, the associated data in other tables is automatically updated or deleted.
• If you have corrupt data on disk or in memory, InnoDB has a checksum mechanism to warn you before using this type of data.

Despite these benefits, various environmental factors may cause corruption in your InnoDB database. Let us have a glance at some of the potential database corruption scenarios:

• A disk failure instance may lead to database corruption or loss of files
• An issue in the disk subsystem could fake flush of files to persistent storage. If you face a power outage or system crash at this stage, it may result in serious corruption.
• Bugs in the MySQL or InnoDB code
• Hardware failures

In a majority of such cases, InnoDB performs crash recovery. It has numerous 'undo logs' to store all incomplete database transactions. During crash recovery, these transactions are rolled back to make the database logically consistent.

In the event of an operating system or disk crash, you can go for media recovery. In order to perform media recovery, you require an updated backup of the database files and MySQL's archived binlog files. The 'binlog' is needed to redo all database operations after the backup was performed. 

You may like to read: Innodb table corruption & recovery