Repair of INNODB Table Corruption in MySQL database

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 commands such as repairtable to repair myisam. Now, record the solution process and we will not be so confused next time. Handling process: After an alarm is triggered, the error log is directly opened,

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. Handling process: After an alarm is triggered, the error log is directly opened,

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 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 ):

# Many hexadecimal code

......

......

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 upload uption 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 upted its own file cache

InnoDB: and rebooting your computer removes

InnoDB: error.

InnoDB: If the specified upt page is an index page

InnoDB: you can also try to fix the specified uption

InnoDB: by dumping, dropping, and reimporting

InnoDB: the specified upt table. You can use CHECK

InnoDB: TABLE to scan your table for processing uption.

InnoDB: See also

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'

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. A large number contains the effects 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:

Check and repair table execution are invalid.

Alter table email_status engine = myisam; # An error is also reported 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 Data

Insert 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;

Last back to 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.