MySQL INNODB Table Corruption repair process Sharing

I suddenly received an alarm from MySQL. I crashed from the database and restarted continuously. I opened the error log and found that a table was broken. If the innodb table is damaged, you cannot use repair table or other commands to repair myisam. Now, record the solution process and we will not be so confused next time.

Processing Process:
When an alert is triggered, open the error log with the following information:

InnoDB: Database page upload uption on disk or a failedInnoDB: file read of page 30506. innoDB: You may have to recover from a backup.130509 20:33:48 InnoDB: Page dump in ascii and hex (16384 bytes): # many hexadecimal code ............ InnoDB: End of page dump130509 20:37:34 InnoDB: Page checksum 1958578898, prior-to-4.0.14-form checksum 3765017239 InnoDB: stored checksum 3904709694, prior-to-4.0.14-form stored checksum 3765017239 InnoDB: page lsn 5 614270220, low 4 bytes of lsn at page end 614270220 InnoDB: Page number (if stored to page already) 30506, InnoDB: space id (if created with> = MySQL-4.1.1 and stored already) 19 InnoDB: Page may be an index page where index id is 54 InnoDB: (index "PRIMARY" of table "maitem ". "email_status") InnoDB: Database page upload uption on disk or a failedInnoDB: file read of page 30506. innoDB: You may have to recover from a backup. innoDB: It is also possible that your operatingInnoDB: system has upted its own file cacheInnoDB: and rebooting your computer removes theInnoDB: error. innoDB: If the specified upt page is an index pageInnoDB: you can also try to fix the specified uptioninnodb: by dumping, dropping, and reimportingInnoDB: the specified upt table. you can use CHECKInnoDB: TABLE to scan your table for partition uption. innoDB: See also http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.htmlInnoDB : About forcing recovery. innoDB: A new raw disk partition was initialized orInnoDB: innodb_force_recovery is on: we do not allowInnoDB: database modifications by the user. shut downInnoDB: mysqld and edit my. cnf so that newraw is replacedInnoDB: with raw, and innodb_force _... is removed.130509 20:39:35 [Warning] Invalid (old ?) Table or database name' # sql2-19c4-5'

From the error log, you can clearly know where the problem occurs and how to handle it. At this time, the database is restarted every several seconds, so it can be said that you cannot access the database. So I was about to fix the innodb table.
I have read similar articles on Performance blog before.

At that time, we thought of ensuring that the database is normal before the restoration, rather than an endless restart of such exceptions. Therefore, a parameter of the configuration file is modified:Innodb_force_recovery

Innodb_force_recovery affects the recovery of the entire InnoDB Storage engine. The default value is 0, indicating that all innodb_force_recovery can be set to 1-6 when recovery is required. A large number contains the influence of all the preceding numbers. When the set parameter value is greater than 0, select, create, and drop operations can be performed on the table, but insert, update, or delete operations are not allowed.1 (srv_force_ignore_0000upt): Ignore the checked upt page. 2 (SRV_FORCE_NO_BACKGROUND): prevents the main thread from running. If the main thread needs to execute the full purge operation, crash may occur. 3 (SRV_FORCE_NO_TRX_UNDO): no transaction rollback is performed. 4 (SRV_FORCE_NO_IBUF_MERGE): Insert buffer merging is not performed. 5 (SRV_FORCE_NO_UNDO_LOG_SCAN): If you do not view redo logs, the InnoDB Storage engine considers uncommitted transactions as committed. 6 (SRV_FORCE_NO_LOG_REDO): do not roll back.

The error log shows a bad page, which causes the database to crash. Therefore, set innodb_force_recovery to 1 and ignore the bad page. After the database is restarted, it is normal and no error message is displayed. Find the table with the error message:
(Index "PRIMARY" of table "maitem". "email_status ")

The primary key index of the data page is damaged. This situation is much worse than that of the Data secondary index (secondary indexes), because the latter can be repaired by using the optimize table command, however, this is better than the case where the more difficult table dictionary is broken.

Procedure:
Because the damaged part is only the index part, when innodb_force_recovery = 1 is used to run InnoDB, the operation is as follows:

Run the check and repair table statements. alter table email_status engine = myisam; # An error is returned because the mode is innodb_force_recovery =1. ERROR 1025 (HY000): Error on rename of '...' to '...' (errno:-1)

Create a table:Create table email_status_bak # has the same structure as the original table, but changed INNODB to MYISAM.Import DataInsert into email_status_bak select * from email_status; Delete the original table: drop table email_status; comment out innodb_force_recovery and restart. Rename: rename table edm_email_status_bak to email_status; the storage engine alter table edm_email_status engine = innodb

Summary:
An important knowledge point here is the understanding of the innodb_force_recovery parameter. If data corruption or other damages occur. The above method may not work. You need to try another method: insert into tb select * from ta limit X; or even dump out and load it back.