MySQL InnoDB Recovery: Complete Guide to Recover Data from Corrupted .ibd Files

1. Understanding InnoDB Architecture

Before diving into recovery techniques, it's essential to understand how InnoDB stores data. This knowledge is crucial for effective recovery planning.

1.1 InnoDB File Structure

InnoDB uses several types of files to store data:

1.2 InnoDB Page Structure

InnoDB organizes data into pages (default 16KB). Each page contains a header, trailer, and data area. The header includes checksums for corruption detection.

InnoDB Page Layout (16KB default):
┌─────────────────────────────────────────┐
│ File Header (38 bytes)                  │
│   - Checksum, Page Number, LSN          │
├─────────────────────────────────────────┤
│ Page Header (56 bytes)                  │
│   - Page type, record count, etc.       │
├─────────────────────────────────────────┤
│ Infimum + Supremum Records (26 bytes)   │
├─────────────────────────────────────────┤
│ User Records                            │
│   - Actual row data                     │
├─────────────────────────────────────────┤
│ Free Space                              │
├─────────────────────────────────────────┤
│ Page Directory                          │
├─────────────────────────────────────────┤
│ File Trailer (8 bytes)                  │
│   - Checksum verification               │
└─────────────────────────────────────────┘

1.3 File-Per-Table vs Shared Tablespace

-- Check current setting
SHOW VARIABLES LIKE 'innodb_file_per_table';

-- File locations with file-per-table:
/var/lib/mysql/
├── ibdata1                    # System tablespace
├── ib_logfile0                # Redo log
├── ib_logfile1                # Redo log
└── mydb/
    ├── customers.ibd          # customers table data
    ├── orders.ibd             # orders table data
    └── products.ibd           # products table data

2. Types of InnoDB Corruption

Understanding the type of corruption helps determine the best recovery approach.

2.1 Page Checksum Errors

This is the most common corruption type. The page's stored checksum doesn't match the calculated checksum, indicating data modification after write (disk error, memory corruption, or incomplete write).

2.2 Log Sequence Number (LSN) Errors

The page's LSN is higher than the redo log's current LSN. This usually happens after:

• Copying datafiles without proper backup procedures
• Restoring from inconsistent backups
• VM snapshots without quiescing the database

2.3 Tablespace ID Mismatch

The internal tablespace ID in the .ibd file doesn't match what InnoDB expects. Common after copying .ibd files between servers or after improper ALTER TABLE operations.

2.4 Data Dictionary Corruption

The InnoDB data dictionary (in ibdata1) doesn't match the actual .ibd files on disk. This can occur after disk failures, improper file deletions, or DDL interruptions.

2.5 Redo Log Corruption

The redo logs (ib_logfile*) are corrupted or inconsistent with the datafiles. Crash recovery cannot proceed.

3. Diagnosing InnoDB Corruption

3.1 Checking MySQL Error Log

The first step is always to examine the MySQL error log:

# Find error log location
mysql -e "SHOW VARIABLES LIKE 'log_error';"

# Common locations:
# /var/log/mysql/error.log (Debian/Ubuntu)
# /var/log/mysqld.log (CentOS/RHEL)
# /var/lib/mysql/*.err (Windows)

# Search for corruption messages
grep -i "corrupt\|error\|warning" /var/log/mysql/error.log | tail -100

3.2 Using innochecksum

innochecksum is a command-line utility that checks InnoDB file integrity without starting MySQL.

# Check a single .ibd file
innochecksum /var/lib/mysql/mydb/orders.ibd

# Check with verbose output
innochecksum -v /var/lib/mysql/mydb/orders.ibd

# Check ibdata1
innochecksum /var/lib/mysql/ibdata1

# Sample output for corrupted file:
page 127 invalid (fails checksum)
page 128 invalid (fails checksum)
Checksum mismatch in page 127
Checksum mismatch in page 128

3.3 Using CHECK TABLE

If MySQL can start, use CHECK TABLE to verify table integrity:

-- Check single table
CHECK TABLE mydb.orders;

-- Check with extended verification
CHECK TABLE mydb.orders EXTENDED;

-- Check all tables in a database
mysqlcheck -u root -p --check mydb

-- Check all databases
mysqlcheck -u root -p --check --all-databases

3.4 Reading InnoDB Status

-- Check InnoDB status for errors
SHOW ENGINE INNODB STATUS\G

-- Look for sections:
-- LATEST DETECTED DEADLOCK
-- LATEST FOREIGN KEY ERROR
-- SEMAPHORES
-- BACKGROUND THREAD
-- FILE I/O

4. Native MySQL Recovery Methods

4.1 innodb_force_recovery Parameter

The innodb_force_recovery parameter forces InnoDB to start despite corruption. Each level enables more aggressive recovery (and disables more features).

# Step 1: Stop MySQL
sudo systemctl stop mysql

# Step 2: Edit configuration
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# Add under [mysqld]:
innodb_force_recovery = 1  # Start with 1, increase if needed

# Step 3: Start MySQL
sudo systemctl start mysql

# Step 4: If it starts, dump data immediately
mysqldump -u root -p --all-databases > recovery_dump.sql

# Step 5: If level 1 fails, try higher levels (2, 3, 4, 5, 6)
# Remove the parameter after recovery!

4.2 Recovering from Binary Logs

If you have binary logs enabled, you can replay transactions:

# Find binary log files
ls -la /var/lib/mysql/mysql-bin.*

# View binary log contents
mysqlbinlog /var/lib/mysql/mysql-bin.000042

# Replay to point-in-time
mysqlbinlog --stop-datetime="2025-12-31 12:00:00" \
  /var/lib/mysql/mysql-bin.000042 | mysql -u root -p

4.3 Discarding and Importing Tablespaces

For tablespace mismatch errors, you can discard and re-import:

-- On the destination server, create table structure
CREATE TABLE orders (...);

-- Discard the tablespace
ALTER TABLE orders DISCARD TABLESPACE;

-- Copy the .ibd file from source
cp /backup/orders.ibd /var/lib/mysql/mydb/orders.ibd
chown mysql:mysql /var/lib/mysql/mydb/orders.ibd

-- Import the tablespace
ALTER TABLE orders IMPORT TABLESPACE;

5. DBRECOVER: Advanced Recovery

When native MySQL recovery methods fail—MySQL won't start even with innodb_force_recovery=6, or the data is too corrupted for mysqldump— DBRECOVER for MySQL provides a last-resort solution.

5.1 How DBRECOVER Works

Key capabilities:

• Checksum Bypass: Ignores page checksum errors to extract readable data
• Schema Auto-Detection: Detects column types from .frm files or page data patterns
• Partial Page Recovery: Extracts valid rows from partially corrupted pages
• No MySQL Required: Works on standalone .ibd files without a running server
• Multiple Export Formats: SQL INSERT, CSV, or direct database load

5.2 Recovery Workflow with DBRECOVER

# Stop MySQL (if running)
sudo systemctl stop mysql

# Copy data directory
mkdir /recovery
cp -r /var/lib/mysql/mydb /recovery/
cp /var/lib/mysql/ibdata1 /recovery/  # If needed for dictionary

# Linux
./dbrecover-mysql.sh

# Windows
dbrecover-mysql.bat

# In DBRECOVER GUI:
# File → Open IBD File → Select orders.ibd

# Option A: Load .frm file (MySQL 5.x)
# Schema → Load FRM File → Select orders.frm

# Option B: Enter CREATE TABLE statement
# Schema → Enter SQL Definition → Paste CREATE TABLE...

# The tool shows:
# - Total pages scanned
# - Valid pages vs corrupted pages
# - Recoverable row count
# - Data preview grid

# Export options:
# - SQL INSERT statements
# - CSV file
# - Direct database connection

# Example SQL output:
INSERT INTO orders (id,customer_id,total,created_at) 
VALUES (1,100,299.99,'2025-01-15 10:30:00');
...

# Create new database
mysql -u root -p -e "CREATE DATABASE mydb_recovered;"

# Import recovered data
mysql -u root -p mydb_recovered < recovered_orders.sql

# Verify data
mysql -u root -p -e "SELECT COUNT(*) FROM mydb_recovered.orders;"

5.3 Handling Missing Schema

In MySQL 8.0+, .frm files are replaced by SDI (Serialized Dictionary Information) embedded in the .ibd file. DBRECOVER can extract SDI automatically. For MySQL 5.x without .frm files, DBRECOVER uses heuristic column detection:

# DBRECOVER auto-detection output:
Column Analysis for orders.ibd:
  Column 1: INTEGER (4 bytes, likely PRIMARY KEY)
  Column 2: INTEGER (4 bytes, likely FOREIGN KEY)
  Column 3: DECIMAL (5 bytes, precision 10, scale 2)
  Column 4: DATETIME (8 bytes)
  Column 5: VARCHAR (variable, max ~255)

# You can adjust column definitions before export

6. Common Recovery Scenarios

6.1 DROP DATABASE Recovery

When a database is accidentally dropped, the files may still exist on disk until overwritten. Act immediately!

# IMMEDIATELY stop MySQL to prevent overwrites
sudo systemctl stop mysql

# Check if files still exist
ls -la /var/lib/mysql/dropped_db/

# If files are gone, try data recovery tools
# (photorec, testdisk, extundelete)

# Once .ibd files are recovered, use DBRECOVER to extract data

6.2 TRUNCATE TABLE Recovery

# In DBRECOVER:
# 1. Open the .ibd file
# 2. Use "Scan → Deep Scan for Deleted Records"
# 3. Review recovered rows
# 4. Export valid data

6.3 Corrupted ibdata1 Recovery

If ibdata1 (system tablespace) is corrupted, MySQL cannot start. DBRECOVER can work with individual .ibd files even when ibdata1 is damaged.

# With file-per-table mode, you can recover tables individually:
# 1. Copy all .ibd files to recovery location
# 2. Open each .ibd file in DBRECOVER
# 3. Provide schema (from backup, documentation, or auto-detect)
# 4. Export data from each table

6.4 Redo Log Corruption

When ib_logfile corruption prevents MySQL from starting:

# WARNING: This loses uncommitted transactions!
# Step 1: Backup everything
cp -r /var/lib/mysql /backup/mysql_$(date +%Y%m%d)

# Step 2: Try innodb_force_recovery=6 (skips redo logs)
# If MySQL starts, dump all data immediately

# Step 3: If MySQL won't start, use DBRECOVER on .ibd files directly

7. Prevention Best Practices

7.1 Backup Strategy

# Enable binary logging for point-in-time recovery
[mysqld]
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 14

# Regular backups with mysqldump
mysqldump --single-transaction --routines --triggers \
  --all-databases > backup_$(date +%Y%m%d).sql

# Or use Percona XtraBackup for hot backups
xtrabackup --backup --target-dir=/backup/$(date +%Y%m%d)

7.2 InnoDB Configuration

[mysqld]
# Enable file-per-table (easier recovery)
innodb_file_per_table = ON

# Enable checksums
innodb_checksum_algorithm = crc32

# Double write buffer (protects against partial page writes)
innodb_doublewrite = ON

# Flush settings for durability
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

7.3 Monitoring

# Monitor for corruption warnings
tail -f /var/log/mysql/error.log | grep -i "corrupt\|error"

# Regular integrity checks (during maintenance window)
mysqlcheck --check --all-databases

# Monitor disk space
df -h /var/lib/mysql

8. Frequently Asked Questions

Q: Can I recover data from a dropped database?

Possibly, if you act quickly. Stop MySQL immediately to prevent file overwrites, then use data recovery tools to find deleted .ibd files. Once recovered, use DBRECOVER to extract the data. Success rate depends on how much disk activity occurred after the DROP.

Q: What's the difference between .ibd and ibdata1 recovery?

.ibd files contain individual table data (with file-per-table mode), while ibdata1 contains the system tablespace (data dictionary, undo logs). Recovering individual .ibd files is easier because you can work table-by-table. ibdata1 corruption is more severe as it affects the entire database structure.

Q: Do I need the .frm files for recovery?

For MySQL 5.x, .frm files contain table schema definitions. Without them, DBRECOVER must detect column types heuristically, which works but requires manual verification. MySQL 8.0+ stores schema in SDI format inside the .ibd file itself.

Q: How long does recovery take?

Recovery time depends on file size and corruption extent. A 1GB .ibd file typically scans in 1-5 minutes. Export time varies based on row count and export format.

Q: Will DBRECOVER recover 100% of my data?

Recovery rate depends on corruption severity. Pages with valid checksums typically recover 100%. Corrupted pages may have partial data loss. DBRECOVER reports recovery statistics so you know exactly what was recovered.

Need Expert Assistance?

For complex MySQL InnoDB recovery scenarios where standard methods have failed, our database recovery experts are available 24/7.

Download DBRECOVER for MySQL →

Contact us at [email protected]