InnoDB Corruption Recover Guide (Until 5.7)
(Doc ID 2017846.1)
Last updated on MARCH 19, 2023
Applies to:
MySQL Server - Version 5.0 to 5.7 [Release 5.0 to 5.7]Information in this document applies to any platform.
Purpose
The purpose of this document is to offer a basic guide that goes over some of the methods you can use to deal with some of the most common InnoDB corruption issues, from start to finish. It is fairly lengthy, so to skip sections, you can use your browser's Find to search for the sections by their identifier as shown below (A, B, C).
Please note this document is just until MySQL Server 5.7, since here it's being used mysqlfrm (from Utilities) that does not support MySQL Server 8.0.
Troubleshooting Steps
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Purpose |
| Troubleshooting Steps |
| A - FIRST RESPONSE |
| A.1 - Initial Steps - Stop, Backup, Restart |
| A.2 - If MySQL is Crashing |
| B - IDENTIFYING THE PROBLEM |
| B.1 - Examining the Logs |
| B.1.1 - Page Corruption |
| B.1.2 - InnoDB Time-Traveling & Log Sequence Number Errors |
| B.1.3 - Data Dictionary Errors |
| B.2 - Checking Tables for Errors |
| B.2.1 - Using CHECK TABLE / mysqlcheck |
| B.2.2 - Using innochecksum |
| C - RECOVERING YOUR DATA |
| C.1 - MySQL Utilities / Extracting the CREATE TABLE statement from a .frm file |
| C.1.1 To download and install MySQL Utilities: |
| C.1.2 - To extract a CREATE TABLE statement from a .frm file: |
| C.2 - Corrupted Tables |
| C.2.1 Restore a Table with CREATE .. LIKE |
| C.2.2 - Restore Multiple/All InnoDB Databases and Re-create ibdata/ib_log files |
| C.3 - Log Sequence Number Mismatched/In Future |
| C.3.1 - Dropping and Re-creating the Data |
| C.3.2 - Re-create the ib_logfiles |
| C.3.3 - Perform an Engine Swap |
| C.4 - Data Dictionary Issues |
| C.4.1 How to properly delete a .ibd file |
| C.4.2 - Re-creating Table after .ibd file missing |
| C.4.3 - .frm File Exists but Table Does Not |
| C.4.4 - Orphaned Table or Missing .frm File |
| Conclusion |
| References |