Restore MySQL databases with innodb_force_recovery

h3. Why use the innodb_force_recovery option?

{color:#333333}A higher rate of database restore failures have been observed in MySQL 5.7 and higher versions, after innodb_file_per_table became the default tablespace option. This is due to the {color}{color:#172b4d}data consistency standards that InnoDB adheres to.{color}{color:#333333} In short{color}{color:#172b4d}, w{color}{color:#172b4d}hen an attempt is made to restore a database, the database files are first used to start a temporary MySQL instance. This instance{color} can {color:#172b4d}fail to start when only a subset of databases are restored from the original instance. InnoDB attempts to locate the other database tablespaces referenced in the logs, and fails to find them if they were not included in the restore{color}{color:#172b4d}. Upon failing to find the referenced database tablespaces that were not included in the restore, the temporary instance fails to start.{color}

{color:#172b4d}The database restore failure may log an error similar to the following : {color}

*"Failed to start temporary database instance"*


{color:#212529}Fortunately, MySQL has provided the innodb_force_recovery option, which{color}{color:#333333} {color}{color:#212529}instructs InnoDB to start up in a recovery mode, allowing various portions of the InnoDB start-up process to be skipped. {color}


{note}{color:#212529}NOTE{color}
{color:#212529}Due to the destructive nature of higher innodb_force_recovery levels, please exercise caution when selecting the level. We recommend starting with the lowest value of 1 and increasing the value only if required.{color}{note}


h3. How to Restore MySQL databases with innodb_force_recovery option

1. Follow the steps in [this article|ServerBackupManager:Restore a MySQL database to the original location] to select your databases and begin the restore.

2. From the "Temporary Instance" page of the database restore wizard, locate the "Additional Options" section at the bottom.

3. In the "Option" column, enter innodb_force_recovery 

4. In the "Value" column, enter a value between 1 and 6

5. Click "Add Another" to save your entry and add a new line

The finished entry should resemble this screenshot :