InnoDB and MyISAM are the two storage engines that MySQL Server uses for creating, reading, updating, and deleting data from database. After an unexpected server crash, both storage engines handle the data damage in completely different ways.

InnoDB, the default storage engine since MySQL Server version 5.5 (2010), offers advanced features like automatic crash recovery mechanism, row-level locking, clustered indexes, Adaptive Hash indexing, and foreign keys. On the other hand, MyISAM has no such crash recovery mechanism.

In this article, we will learn exactly what these storage engines do during and after a crash and how to recover tables if the crash recovery fails. If the crashed tables are already damaged, you can also upload the files to an online MySQL database repair and preview the recovered data.

How Crash Recovery Works in InnoDB?

InnoDB is a transaction storage engine that follows the ACID model to protect data. It has features such as commit, rollback, and crash-recovery. In simple terms, it uses the redo log (Write-Ahead Logging mechanism) and undo log to recover automatically from crashes. It does this by replaying committed transactions from the redo log and rolls back incomplete transactions. This ensures the database recovers in a consistent state. Let’s see how InnoDB crash recovery works:

1. Tablespace Discovery (Folder Scanning)

When MySQL restarts after an unexpected crash, it scans the directories specified by innodb_directories to locate all table-space files (.ibd). If any file is missing or inaccessible, the recovery stops immediately to prevent data corruption. You can run the following command to check the internal state of InnoDB storage engine at the moment of execution:

SHOW ENGINE INNODB STATUS\G

2. Using the Redo Logs to Roll Forward Transactions

InnoDB replays all the redo logs from the table-space files. This helps it to bring data pages, including uncommitted transactions, to the state they were in at the time of crash. During this, it also performs the following tasks:

• Check the next AUT0_INCREMENT ID to prevent reuse of table ID
• Find the broken files or indexes before the crash. Once found, it marks them as ‘corrupt’ so that they can be fixed later

3. Use Undo Logs to Roll Back Incomplete Transactions

At this step, InnoDB checks the undo logs to identify the transactions that were active at the time of the crash. The engine need to erase these incomplete transactions using the logs. It takes time to roll back these incomplete transactions (often three to four times the amount of time a transaction was active before it was interrupted). However, you can force the MySQL Server to skip this step by using innodb_force_recovery.

4. InnoDB Change Buffer Merge to the Leaf Pages of Secondary Indexes

In this phase, InnoDB replays redo logs to restore the change buffer within the system table space. It merges operation and counting statistics to the leaf pages of secondary indexes.

5. Purge Deleted-Marked Records

The server run the cleanup tasks, like cleaning up old and deleted data records, in the background.

How MyISAM Recovers Table After Crash?

MyISAM is the non-transactional engine due to which it is more prone to unexpected shutdowns and corruption in tables and can leave tables marked as crashed. It writes data directly to the table file (.MYD) and updates its index file (.MYI) simultaneously as it does not have transaction logs, rollback, and crash recovery features. If the MySQL Server crashes mid-write, this breaks sync, leading to corruption in tables.

You can change the myisam_recover_options startup value to activate automatic recovery. However, it is not effective as log-based recovery that InnoDB performs. If you start mysqld with this startup option, the MyISAM engine follows the process for crash recovery.

The engine automatically tries to check every table to see if it is flagged as crashed or left open during a sudden shutdown. If no errors are found, the server opens the table normally. If an error is detected, it tries to do a table repair at the following levels:

• Level 1
  It attempts a fast repair using sorting, without changing, or recreating the data file.
• Level 2
  If the repair (Level 1) fails due to a data file error, like a duplicate-key error, then the server tries a second repair. In this case, it completely recreates the data file.
• Level 3
  If the repair (Level 2) also fails, then the server tries once more using the old repair method such as write row-by-row without sorting. This method uses less disk space and can fix almost any type of error.

After repair, the amount of data that the MyISAM Server is able to save depends upon the option you have selected while using myisam_recover_options startup setting.

• Without the FORCE Option:
  If the recovery process cannot recover all the rows from previously completed statements, the server aborts the automatic repair entirely and writes an error to the log (e.g., Error: Couldn’t repair table…).
• With the FORCE Option:
  If you have specified the force option, the server forces the repair to finish even if data is lost. It saves whatever rows it can and logs a warning instead (e.g., Warning: Found 344 of 354 rows…).
• BACKUP Option:
  If your myisam_recover_options configuration includes BACKUP, the server automatically creates a backup file in the database directory named tbl_name-datetime.BAK before fixing the table.

If MyISAM tables become corrupted and automatic recovery is either disabled or has failed, you can manually recover them using the REPAIR TABLE and myisamchk commands.

MySQL Crash Recovery: InnoDB vs MyISAM

InnoDB recovers tables’ data when the MySQL Server suddenly loses power or crashes. It handles crashes automatically due to automatic crash recovery mechanism, whereas MyISAM has no such crash recovery mechanism. Unlike MyISAM, it uses redo logs and undo logs to ensure data integrity. During startup after a crash, InnoDB reads the redo logs to apply any completed transactions that were not yet saved to the table files. At the same time, it uses the undo logs to identify and completely reverse any incomplete transactions that were interrupted mid-crash. Also, InnoDB includes a doublewrite buffer feature to protect against partial or torn page writes. Therefore, it provides better recovery after a crash than the MyISAM storage engine.

Here is a comparison table how both engines respond during a crash:

Feature	InnoDB Storage Engine	MyISAM Storage Engine
Recovery type	Built-in crash recovery	Required myisam_recover_options to activate crash recovery
Data Integrity	Transactional engine , which supports ACID (No partial writes)	Non-transactional engine. Does not support advanced features. (High corruption risk)
Torn Page Protection	Yes (Via the Doublewrite Buffer)	No (It includes direct writes, leads to high corruption risk)
Locking level	Row-level	Table-level
Index corruption	Low	High risk of index corruption because it lacks transactional logs.
Recovery speed	Fast	Slow

How to Recover MySQL Database when Crash Recovery Fails?

When MySQL crash recovery fails, you need to repair or restore database manually. The methods to repair MySQL database depends on whether the tables use InnoDB or MyISAM.

Recover InnoDB Table When Automatic Recovery Fails

If automatic recovery fails, you can set the innodb_force_recovery setting to a value from 1 to 6 to start the server. This helps you use the dump and reload method to repair InnoDB tables. The default value of innodb_force_recovery is 0. Setting the value to 1 is usually enough to start the InnoDB engine and dump the tables. However, if the tables are severely corrupted, you may be required to increase the level. Dumping tables with an innodb_force_recovery value of 4 or higher can lead to data loss.

Recover MyISAM Table

If MyISAM automatic recovery is either disabled or has failed, you can manually recover the tables using the REPAIR TABLE and myisamchk commands. Here is the syntax to use the REPAIR TABLE command:

REPAIR [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name [, tbl_name] ...
[QUICK] [EXTENDED] [USE_FRM]

To repair .MYI and .MYD files in tables using the myisamchk, first stop the MySQL Server and run the following command:

myisamchk –recover tbl_name 

After this, start the MySQL Server.

What to Do If the Native Repair Methods Fail to Resolve Corruption?

If the MySQL Server fails to start even at innodb_force_recovery =6 or MyISAM engine fails to repair the tables, then you can opt for a professional MySQL repair tool, such as Stellar Repair for MySQL. It can repair both InnoDB and MyISAM tables of any size or in any state. You can use this tool to recover InnoDB (.frm, .ibdata, .ibd) files and MyISAM (.frm, .MYD, .MYI) tables files. Also, it helps you recover primary keys, unique keys, relations, table properties, views, triggers, and other objects from corrupted MySQL database with complete precision.

Conclusion

MySQL Server can unexpectedly crash due to several reasons. In this article, we have covered InnoDB vs MyISAM crash recovery. InnoDB performs better recovery due to its built-in transactional logs (redo and undo logs) and the protective doublewrite buffer features. If crash recovery fails, you can use a professional MySQL repair tool like Stellar Repair for MySQL. It can repair InnoDB and MyISAM tables without any data loss.