MySQL database InnoDB table Damage Repair Process

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 in 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 a 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. 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 does not allow

Innodb:database modifications by the user. Shut down

Innodb:mysqld and edit my.cnf So, 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 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 when recovery is required, all the

The innodb_force_recovery can be set to 1-6, and the large number contains 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:

Execution 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.

Get the data in.

INSERT INTO Email_status_bak select * from Email_status;

Delete the original table:

drop table email_status;

After commenting out the Innodb_force_recovery, reboot.

Renaming:

Rename table Edm_email_status_bak to Email_status;

The last time the storage engine

ALTER TABLE edm_email_status engine = InnoDB

Summarize:

An important point here is the understanding of the Innodb_force_recovery parameter, in the case of 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.