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.
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 todump 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’;
