A Simple Approach to MySQL Database Recovery Using Binary Backups

The InnoDB storage engine of MySQL has the capability to provide a transaction safe database. However, due to some unexpected power failures and internal system malfunctions, the risks of database crash or corruption increase. You may get the following error on your screen while accessing some of the pages of your website:

'ERROR 2006 (HY000): MySQL server has gone away'

Additionally, if you issue a simple query to retrieve records from a database table, you get the same error. This signals corruption in your MySQL database at the backend.

The above error relates to the following two error messages:

'ERROR 1030 (HY000): Got error -1 from storage engine'

'ERROR 1030 (HY000): Got error 28 from storage engine'

The latter one can be resolved by freeing up space on your hard drive and then doing some optimization tasks. However, the former indicates a serious database corruption. In order to fix this, you should have a database backup (binary or SQL dump). You should follow the given series of steps to work around this problem:

• Initially, make changes to your 'my.cnf' file. Add the following line to [mysqld] section:

'innodb_force_recovery=4'

'4' value denotes that no background process can make changes to the MySQL database while the recovery process is on.

• Now stop the MySQL server using '/etc/init.d/mysqld stop' and copy the backup of your database to the MYSQL data folder.. Binary backup would usually comprise ibdata, ib_logfile0, ib_logfile1, and your database directory.
• Restart the 'mysqld' service and verify all the tables. At this stage, you can try running some queries on your database. If the results are fine and no error is encountered, the process ran successfully. You can also view the log file in order to ensure that the process was completed.
• Comment the line that you added to your '/etc/my.cnf' file and again start mysqld.
• Finally, optimize all your database tables.

If the above procedure fails to fix database corruption, you can take help of commercial third-party MySQL database repair tools. These utilities are dedicated to perform complete recovery of your MySQL database, retrieving all inaccessible objects, such as tables, views, keys, triggers, etc. They provide preview of all database objects and allow you to verify your tables prior to recovery. They also support recovery of MySQL tables created on Linux platform. Moreover, these tool are designed with a self-descriptive interface to make the overall process facile and user-friendly.