MySQL database INNODB table damage repair process sharing

All of a sudden, I received an alarm from MySQL, and the database of the library was hung. 1 was restarting all the time, opened the error log, and found that one of the tables was broken. innodb table corruption cannot be fixed by repair table and other command operations of myisam. Now write down the resolution so you won't be in such a rush the next time you meet.

Process:
1. After the alarm, directly open the error log. The information in it is:

InnoDB: Database page corruption on disk or a failed InnoDB: 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): A lot of code in base 106 ... ... InnoDB: End of page dump 130509 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 corruption on disk or a failed InnoDB: file read of page 30506. InnoDB: You may have to recover from a backup. InnoDB: It is also possible that your operating InnoDB: system has corrupted its own file cache InnoDB: and rebooting your computer removes the InnoDB: error. InnoDB: If the corrupt page is an index page InnoDB: you can also try to fix the corruption InnoDB: by dumping, dropping, and reimporting InnoDB: the corrupt table. You can use CHECK InnoDB: TABLE to scan your table for corruption. InnoDB: See also http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. InnoDB: A new raw disk partition was initialized or InnoDB: innodb_force_recovery is on: we do not allow InnoDB: database modifications by the user. Shut down InnoDB: mysqld and edit my.cnf so that newraw is replaced InnoDB: with raw, and innodb_force_... is removed. 130509 20:39:35 [Warning] Invalid (old?) table or database name '#sql2-19c4-5'

It is clear from the error log what went wrong and what to do about it. At this point the database is rebooted every few s, so it's pretty much impossible to access the database. So I immediately thought of fixing the innodb table.
I've read this before on blog on Performance.

The idea was to make sure the database was working until the fix was done, not so unnaturally endless reboots. Therefore, one parameter of the configuration file has been changed: innodb_force_recovery

innodb_force_recovery affects the recovery status of the entire InnoDB storage engine. The default is 0, which means all are executed when recovery is required innodb_force_recovery can be set to 1-6, and large Numbers contain the effects of all the preceding Numbers. When parameter values are set greater than 0, select,create,drop operations can be performed on the table, but insert,update, or delete operations are not allowed. 1(SRV_FORCE_IGNORE_CORRUPT): ignore the checked corrupt page. 2(SRV_FORCE_NO_BACKGROUND): prevents the main thread from running. If the main thread needs to perform full purge, crash will result. 3(SRV_FORCE_NO_TRX_UNDO): does not perform a transaction rollback operation. 4(SRV_FORCE_NO_IBUF_MERGE): do not perform merge operations with insert buffers. 5(SRV_FORCE_NO_UNDO_LOG_SCAN): instead of viewing the redo log, the InnoDB storage engine treats uncommitted transactions as committed. 6(SRV_FORCE_NO_LOG_REDO): do not roll forward. < / pre >

Since a bad page is indicated in the error log and the database crashes, innodb_force_recovery is set to 1 and the detected bad pages are ignored. After restarting the database, it is normal and no error messages appear. Find the table where the error message appears:
(index "PRIMARY" of table "maitem"."email_status")

The primary key index (clustered key index) of the data page was corrupted. This is much worse than a level 2 index of the data (secondary indexes) being corrupted, which can be fixed using the OPTIMIZE TABLE command, but it is better than the more difficult table directory (table dictionary) being corrupted.

Operation steps:
Since the broken place is only in the index part, when InnoDB is run using innodb_force_recovery = 1, the operation is as follows:

check, repair table are invalid alter table email_status engine =myisam; # also reported an error, because the pattern is innodb_force_recovery =1. ERROR 1025 (HY000): Error on rename of '...' to '....' (errno: -1)

Create a table: create table email_status_bak# is the same as the original table structure, only INNODB has been changed to MYISAM. I'm going to take the data in insert into email_status_bak select * from email_status; Delete the original table: drop table email_status; After commenting out innodb_force_recovery, restart. Rename: rename table edm_email_status_bak to email_status; Finally, it goes back to the storage engine alter table edm_email_status engine = innodb

Conclusion:
An important point here is understanding the innodb_force_recovery parameter, if you encounter data corruption or other corruption. Maybe the above method doesn't work, you need to try another method: insert into tb select * from ta limit X; Even dump goes out and load comes back.