We have over the span of a few months been running R1Soft as our secondary backups. During that time, we have on occasion needed to restore MySQL databases from the R1Soft backups, which almost always seem to fail.
On checking the restore logs, the errors are similar to…
Setting up temporary MySQL instance Executing mysql_start_restore request...Secondary MySQL instance process terminated Secondary MySQL instance is not shutdown; you must do so manually!An exception occurred during the request. Unable to start the secondary MySQL instance. Failed to start temporary database instance Agent reported error during requested operation
The explanation
R1Soft doesn’t take dumps of the MySQL databases, and instead does the following…
Restores the MySQL database files from /var/lib/mysql
Restores the InnoDB specific files, such ib_data
Attempts to create a secondary MySQL instance (so not to effect the existing running MySQL instance)
In many cases though, the process fails at this point, causing the MySQL restore process to drop entirely. The reason for this, is that InnoDB methods have changed over the more recent versions of MariaDB / MySQL, which throws errors in the startup process, and basically gives up.
During the restore process, you are able to set the ‘innodb_force_recovery’ value to 1, which forces the process to complete.
The steps to successfully restore a database through R1Soft, which has failed with errors similar to the above…
Select the Restore Point, and select ‘Browse Databases’
Select the databases you wish to restore, and hit ‘Restore Selected’
You will be presented with the following screen…
Go through the steps, until you reach ‘Temporary Instance (4th Step)
In this screen, at the bottom you will see a section for ‘Additional Options’
In the boxes provided, enter ‘innodb_force_recovery’ in the first box, and ‘1’ in the second box like so…
Continue through the usual restore steps, and voila, the restore process should work fine
