InnoDB force Recovery, There are many reasons available to get database corrupted. Make sure that you maintain a good backup schedule by running a cpanel backup in case of serious corruption or data loss issue or refer with Mysql knowledgebase.

InnoDB corruption can cause all of the databases running on that server to be inaccessible. Unfortunately some times you need to recover MySQL data from ibdata1. It’s many reasons why your getting corrupted Innodb files that cannot automatically be restored of the mysqld daemon. We can recover innodb table corruption in your  cpanel server.

corrupted innodb errors

If you receive the following error when you start mysql

Starting MySQL..
The server quit without updating PID file (/var/lib/mysql/server.hostname.com.pid).[FAILED] or MySQL server PID file could not be found!

You may receive the following mysql error logs.

tail -f /var/lib/mysql/server.hostname.com.pid

InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.

How to fix Forcing Innodb Recovery?

However, if errors persist with innodb_force_recovery in place, then you are going to have to proceed with a dump and restore.

Check the innodb log file size

-rw-rw—- 1 mysql mysql 15242880 Oct 18 11:50 ib_logfile0
-rw-rw—- 1 mysql mysql 15242880 Oct  2 02:10 ib_logfile1
So the size is 15242880

Then edit /etc/my.cnf and force recovery and log size.

innodb_force_recovery=4
innodb_log_file_size=15242880

Restart Mysql service now and database should start, but with innodb_force_recovery in my.cnf all Insert and Update operation will be ignored.

/etc/init.d/mysql start

Once you have restarted the MySQL server with ‘innodb_force_recovery’ in place, you have to dump the corrupted databases.

Single Database

mysqldump database_name > database_name.sql

All Databases

mysqldump –all-databases > all_the_bases.sql

Once you have the databases dumped successfully, stop MySQL completely and move the ib* files from /var/lib/mysql/ out of the directory.

mkdir /var/lib/old_innodb

mv /var/lib/mysql/ib* /var/lib/old_innodb

Remove ‘innodb_force_recovery’ ‘innodb_log_file_size‘ Two lines from /etc/my.cnf and start MySQL service. Then Restore database into their respective database.

mysql  database_name < database_name.sql

All Databases

mysql < all_the_bases.sql

Once restore completed, try to repair all the database.

mysqlcheck –all-databases –repair

Thats all.. check your mysql logs.

A simple query for finding all of your InnoDB tables in case you want to specifically target the corruption.

SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES

innodb force recovery options

1 (SRV_FORCE_IGNORE_CORRUPT)

Let the server run even if it detects a corrupt page. Try to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

2 (SRV_FORCE_NO_BACKGROUND)

Prevent the main thread from running. If a crash would occur during the purge operation, this recovery value prevents it.

3 (SRV_FORCE_NO_TRX_UNDO)

Do not run transaction rollbacks after recovery.

4 (SRV_FORCE_NO_IBUF_MERGE)

Prevent insert buffer merge operations. If they would cause a crash, do not do them. Do not calculate table statistics.

5 (SRV_FORCE_NO_UNDO_LOG_SCAN)

Do not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.

6 (SRV_FORCE_NO_LOG_REDO)

Do not do the log roll-forward in connection with recovery.

The database must not otherwise be used with any nonzero value of innodb_force_recovery. As a safety measure, InnoDB prevents users from performing INSERT, UPDATE, or DELETE operations when innodb_force_recovery is greater than 0.
WHERE engine = ‘innodb’;

0.00 avg. rating (0% score) - 0 votes
Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive