MySQL database management system contains a storage engine called InnoDB. Starting version 5.5, InnoDB replaced MyISAM in MySQL. Both are reliable storage engines for the DBMS, differing in their locking implementation— InnoDB locks a particular row in the table and MyISAM locks the entire MySQL table. Which storage engine a table is supposed to use can be specified while creating the table.
Why
InnoDB Force Recovery is needed
If your MySQL server suffers a crash, all you need to do to recover it is restart the MySQL server. In such a situation, InnoDB will automatically check the logs and perform a roll-forward of the database to the present. In the case of minor corruption, this strategy may be sufficient and you may choose to run a SELECT statement to dump the database tables, but serious corruption may result in crashing of SELECT statement that you’re using to try and recover whatever data you can. It may even cause InnoDB background operations to crash or a crash of InnoDB roll-forward recovery.
In the event of serious corruption, therefore, it is advisable to use the innodb_force_recovery option. This option forces the InnoDB storage engine to start running all the while stopping background operations from executing, in order to allow you to dump your database tables.
Running
InnoDB Force Recovery
To run InnoDB force recovery you need to execute the following command in the [mysqld] section of your option file before the server is restarted:
[mysqld]
innodb_force_recovery = 1
Note: While running innodb_force-recovery, you should take care of the following:
- The
value of innodb_force_recovery should be set to a number greater than 0 only in
emergency situations to allow InnoDB to start and users to dump their tables.
- Before
setting the value to greater than 0, ensure that you have a backup of your
database.
- If
you set innodb_force_recovery to a value of 4 or more, it can permanently
corrupt the database files.
- The
value of innodb_force_recovery is 0 by default which allows for normal database
startup without forced recovery.
- Permissible
non-zero for the command range from 1 to 6 and a larger value includes the
functionality of lesser values.
After running innodb_force_recovery with a value of 3 or less if you’re able to dump your database tables, your tables are still safe since only some of the data on the corrupt pages will be lost. However, if you are pushed to run the command with a value of 4 and above, you are at a high risk of data loss since data files can become permanently corrupt. Thus, it is advisable that you use a setting of 4 or higher on a production server instance only if you’ve tested it successfully on a separate physical copy of your database. Furthermore, a setting of 6 should almost never be used since after its execution the database pages are considered to be in an obsolete state.
Because of this, MySQL as a safety measure
makes its InnoDB engine prevent INSERT, DELETE, or UPDATE operations in the
event that innodb_force_recovery is running with a setting greater than 0.
From the above descriptions, it is clear that executing the innodb_force_recovery is often full of risks. And if it is not executed, in situations of severe MySQL database corruption, recovery can become very difficult. This scenario presents the need for an alternative mechanism for corrupted MySQL database recovery, which is not as risky and technical demanding as innodb_force_recovery.
The
best alternative of InnoDB Force Recovery
At present, the most viable and efficient alternative to InnoDB Force Recovery is Stellar Repair for MySQL. This advanced software repairs corrupt MySQL database and allow safe recovery of all inaccessible database objects in their original format. One of the product’s primary capabilities includes repair InnoDB and MyISAM tables of MySQL database.
Powered by advanced algorithms and an interactive GUI, this tool makes it extremely simple to restore keys, tables, table properties, data types, views, and triggers from MySQL corrupt database files.
Here’s how you can use this software to repair MySQL database:
- Download, install and launch Stellar Repair for MySQL
- From the software’s main screen, select the database to be repaired
- Click OK to start the repair process
- Once the scanning completes, all recoverable objects will be listed down in the software’s left-hand panel. You can select any object for preview from here
- To save the repaired database, click on the Save button from the main menu
- Choose an output format form MySQL, CSV, HTML, and XLS
- When prompted, provide the User Login Information and click Save to proceed
To
sum it up
So there you have it, the simplest way to
repair and restore a corrupted MySQL database. Although InnoDB is usually an
effective technique to perform the task, it is full of risks. Thus, going with
a proven and risk-free option like an automated MySQL recovery tool is the best
solution.
Was this article helpful?
