Recover Crashed InnoDB Tables Safely: MySQL Guide

Controlled MySQL InnoDB recovery process using a protected data copy

An InnoDB recovery incident is different from repairing a MyISAM table. REPAIR TABLE and mysqlcheck --repair do not rebuild damaged InnoDB pages. The safe objective is to preserve the original files, recover from a verified backup when possible, or start the damaged instance in a restricted mode only long enough to export readable data.

Every restart and write can change the evidence. If the database is commercially important and no tested backup exists, stop experimenting on the only copy. Create a storage snapshot or an offline copy and work from that copy.

Confirm that this is an InnoDB problem

Application messages such as “table is marked as crashed” are not enough to select a recovery method. Identify the storage engine and read the database error log.

If the service is running, check the affected table:

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database_name'
  AND TABLE_NAME = 'table_name';

Review recent MySQL or MariaDB messages. On systemd-based servers:

systemctl status mysqld --no-pager
journalctl -u mysqld -n 250 --no-pager

Use mysql or mariadb as the unit name when appropriate. Depending on the distribution, the database error log may be in /var/log/mysql/, /var/log/mariadb/ or the data directory.

Evidence of an InnoDB recovery problem can include page-checksum errors, assertion failures, repeated crash-recovery failures, missing tablespaces or an inability to open the InnoDB system tablespace. Each points to a different incident, so retain the complete log rather than one copied line.

Choose the least destructive recovery path

Use this order:

Restore the affected database from a verified recent backup.
If the server remains stable, export the affected database or tables immediately.
If normal startup fails, create an offline copy and test recovery on the copy.
Use innodb_force_recovery only to make data readable for export.
Import the recovered data into a clean instance and validate it.

Do not make the forced-recovery instance the new production database.

Preserve an offline copy

Before changing InnoDB recovery settings:

Stop the database cleanly if it is still possible.
Record the exact MySQL or MariaDB product and version.
Snapshot the storage volume or copy the complete data directory while the service is stopped.
Copy all configuration files and the full error log.
Verify that the copy has a realistic size and can be read.

The data directory is a unit. It can contain shared system tablespaces, redo logs, undo tablespaces, the system schema and file-per-table tablespaces. Saving only the visible .ibd file may not be enough to recover the table.

Do not delete ibdata1, redo logs or undo files to “force” a clean start. Those files may be required to interpret the data you are trying to rescue.

Export immediately if MySQL still starts

When the service is stable enough to read data, prefer a logical export before changing anything. For a complete instance, a common starting point is:

mysqldump --all-databases --routines --events --triggers --single-transaction --quick > all-databases-recovery.sql

Adjust authentication and options for the installed product. Large or damaged instances may need database-by-database or table-by-table exports. A single transaction is useful only when the server can maintain it reliably.

After exporting, check the command's exit status and stderr, inspect the dump for expected schemas, and perform a test import into a separate instance. A dump file existing on disk does not prove it is complete.

Use innodb_force_recovery only for extraction

When InnoDB cannot complete normal startup, its emergency recovery mode may allow the server to open enough data for a logical dump.

Add the option temporarily under the server section of the correct configuration file:

[mysqld]
innodb_force_recovery=1

Start with 1. If the copied instance still cannot start, stop it and increase the value by one. Do not jump directly to 6.

MySQL defines levels from 1 through 6. Higher values disable more InnoDB background work and make the instance progressively less safe. The MySQL documentation warns that values of 4 or greater can permanently corrupt data files. Recovery mode is for reading and exporting whatever remains accessible, not for continuing production writes.

Use the lowest level that allows startup, then export the most important databases first. The official behavior and risks are documented in Forcing InnoDB Recovery.

When a normal table dump fails

A corrupt page may make a full table scan stop at the same row every time. Possible extraction strategies depend on the schema and corruption location:

Dump unaffected databases and tables first.
Export the damaged table in primary-key ranges.
Select specific columns when a large BLOB or secondary index triggers the failure.
Use SELECT ... INTO OUTFILE for readable ranges when the client dump cannot continue.
Rebuild from an application-level source when the table is derived or disposable.

Keep a written record of missing ranges and failed tables. Partial recovery should never be presented as a complete restore.

Tablespace import can sometimes recover a file-per-table .ibd file, but it requires a matching table definition and compatible tablespace metadata. It is not a universal alternative to a backup and should be tested on a separate instance.

Rebuild on a clean database instance

After extracting as much data as possible:

Provision a clean, supported MySQL or MariaDB instance.
Remove any temporary recovery option from its configuration.
Import the logical dumps.
Recreate users and privileges through supported statements or administration tools.
Validate row counts and application-critical records.
Run application read and write tests.
Verify scheduled jobs, replication and backups.

Do not copy a damaged data directory over the clean instance and call it restored. The clean rebuild is what separates recovered logical data from the corrupted storage state.

Mistakes that commonly make recovery harder

Avoid these actions on the original copy:

Running mysqlcheck --repair and assuming it repairs InnoDB
Increasing innodb_force_recovery directly to 6
Leaving recovery mode enabled after data extraction
Deleting InnoDB redo logs or ibdata1
Initializing MySQL over the existing data directory
Changing ownership or SELinux labels recursively without identifying the cause
Upgrading or switching database products during the recovery attempt
Restarting repeatedly without preserving each new error sequence

Prevent the next recovery emergency

A backup strategy must prove that data can be restored. Use automated backups with retention outside the server, protect credentials, monitor backup failures and run scheduled test restores. For larger databases, combine suitable physical backups with logical exports of critical schemas.

Monitor disk health, filesystem capacity, database crash loops and replication lag. Review database upgrades before packages change, and keep enough free storage for temporary files and recovery work.

If this is an active data-loss incident, emergency server support can assess the surviving data before high-risk changes are made. Routine backup verification and database health checks belong in ongoing Linux server management.

Practical support, not another hosting package

Want an engineer to manage the server behind this problem?

iServerSupport provides monitoring, maintenance, security, and incident response for infrastructure you already control.

Explore server management Talk to an engineer