Mysql database InnoDB table Damage Repair process sharing _mysql

Suddenly received the MySQL alarm, from the library of the database hung, has been in constant restart, open the error log, found that a table is broken. InnoDB table corruption cannot repair MyISAM command operations through repair table. Now record the resolution process, the next encounter will not be so hectic.

Processing process:
Once encountered the alarm, directly open the error log, the information inside:

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): # #很多十六进制的代码 .... 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) Innodb:page May was a index Page where index ID is InnoDB: (index "PRIMARY" of Tabl E "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 this your operating Innodb:system has corrupted its own file cache Innodb:and rebooting Computer REMOves the Innodb:error. Innodb:if The corrupt page is a index page innodb:you can also try to fix the corruption innodb:by dumping, dropping, and reimporting innodb:the corrupt table.
You can use the CHECK innodb:table to scan your the 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 be on:we do not allow innodb:database Modifications by the user.
Shut down innodb:mysqld and edit my.cnf so this newraw is replaced raw, and innodb:with ... is innodb_force_. 130509 20:39:35 [Warning] Invalid (old) table or database name ' #sql2 -19c4-5 '

It is clear from the error log that there is a problem and what to do with it. At this point the database will be restarted every few s, so you can almost say you can't access the database. So immediately thought to repair the InnoDB table.
I've seen similar articles in Performance's blog before.

The idea was to make sure that the database was normal before it was repaired, not so unusually endlessly restarted. So you modified one of the parameters of the configuration file:innodb_force_recovery

Innodb_force_recovery affects the recovery status of the entire InnoDB storage engine. The default is 0, which means that all

innodb_force_recovery can be set to 1-6 when recovery is required, and large numbers contain the effect of all previous digits. When you set the parameter value greater than 0, you can select,create,drop the table, but operations such as insert,update or delete are not allowed.

1 (srv_force_ignore_corrupt): ignores the checked to corrupt page.
2 (Srv_force_no_background): Prevents the main thread from running, as the main thread needs to perform a full purge operation, resulting in crash.
3 (Srv_force_no_trx_undo): Do not perform transaction rollback operation.
4 (Srv_force_no_ibuf_merge): does not perform a merge operation that inserts a buffer.
5 (Srv_force_no_undo_log_scan): Do not check the value of the log, InnoDB the storage engine will be uncommitted transactions as committed.
6 (Srv_force_no_log_redo): Do not perform roll forward operation. 

Because the error log prompts for a bad page, causing the database to crash, so here set the innodb_force_recovery to 1, ignoring the bad page check. After restarting the database, normal, no error message appears. Locate the table where the error message appears:
(index "PRIMARY" of Table "Maitem".) Email_status ")

The primary key index (clustered key) for the data page is corrupted. This situation is much worse than the two-level index of the data (secondary indexes), which can be fixed by using the Optimize Table command, but this and more difficult to recover table directories (table dictionary) It's better to be damaged.

Operation Steps:
Because the damaged place is only in the part of the index, so when using Innodb_force_recovery = 1 to run InnoDB, the operation is as follows:

Executing check,repair table is invalid
ALTER TABLE email_status engine =myisam; #也报错了 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  #和原表结构一样, just InnoDB changed to MyISAM. Insert 

The data
into the email_status_bak select * from Email_status;

Delete the original table:
drop table email_status;

After commenting out the Innodb_force_recovery, reboot. Rename
:
rename table Edm_email_status_bak to Email_status;

The last time the memory engine
ALTER TABLE edm_email_status engine = InnoDB

Summary:
Here is an important knowledge of the  innodb_force_recovery  parameters of the understanding, if you encounter data corruption or even other damage. Perhaps the above method is not possible, you need to try another method: INSERT INTO TB select * from TA limit X, or even dump out, and then load back.