MySQL 8.0 fails to start after abrupt power loss: InnoDB reports missing .ibd and tablespace id mismatch

Environment

Single MySQL 8.0 instance on Ubuntu 20.04 using InnoDB file-per-table (innodb_file_per_table=ON), dataset stored on an ext4 partition, server experienced sudden power loss during heavy write activity; no recent logical backups taken, but nightly filesystem-level snapshots exist from the host.

Symptoms

After power outage the MySQL service does not come online; mysqld logs show repeated InnoDB errors referencing missing .ibd files and 'Tablespace id mismatch' followed by mysqld aborting; attempting to start with default configuration exits before networking is available.

Observations

Mysqld error log contains lines like 'InnoDB: Tablespace id mismatch' and 'InnoDB: Could not find .ibd file for table dbname/tablename', trying to start with innodb_force_recovery=1..6 allows server binary to start in read-only modes at force_recovery >=4, but many queries fail or return NULL; the underlying ext4 partition shows no reported corruption by fsck and the .ibd files are present for some tables while missing for others.

Already attempted

Tried incremental recovery with innodb_force_recovery values starting at 1 and increasing; copied datadir to a separate directory before tests; ran mysqlcheck against tables when server started with high force_recovery but it returned errors; verified filesystem integrity with sudo fsck -n; tried to import a .sql dump but mysqld would not run reliably without force_recovery.

Question

What safe, reversible recovery strategy should I use to restore as many InnoDB tables as possible without worsening corruption, and what are the recommended steps to export data when mysqld will only start with innodb_force_recovery enabled?

Post timeline

COMMUNITY RESPONSES

Verified community troubleshooting

0 replies

No answers yet..

Showing 0 of 0 community responses

AI DIAGNOSTICS

Incident analysis

Updated 1w ago• Trouble ID

Most likely causes

Incomplete flush of InnoDB tablespace metadata to disk at power loss causing internal dictionary mismatch 90%

Cached writes in the OS or controller not persisted leading to inconsistent state 85%

Hardware issues with the storage device (controller, power) during heavy I/O 60%

Summary

Abrupt power loss during heavy writes can cause InnoDB tablespace metadata and .ibd files to become inconsistent or partially written, resulting in tablespace id mismatches and missing file errors. innodb_force_recovery is useful to get the engine to a read-only state for extracting data, but higher recovery levels disable many InnoDB operations and risk producing incomplete exports. The presence of some .ibd files but missing others suggests either file-level deletion, filesystem-level write ordering issues, or that the InnoDB internal data dictionary refers to tablespaces that were not flushed to disk before the crash.

Suggested Fixes

Diagnostic step 1

Immediately make a full block-level copy of the MySQL datadir and all related files (using dd or LVM snapshot) and work only on copies; do not attempt in-place aggressive repairs.

Diagnostic step 2

Start mysqld on the copy with progressively higher innodb_force_recovery values (1 → 6) only until the server is stable enough to export data; prefer the lowest level that allows SELECTs.

Diagnostic step 3

When mysqld is readable, perform logical exports (mysqldump with --single-transaction where possible) starting with smaller, critical tables; verify exported SQL for completeness.