Recovery methodsTechnical

InnoDB Tablespaces, Redo Logs and Crash Recovery

InnoDB data files and redo logs together describe one transactional state — a data page can legitimately lag behind the true committed state of the database until the redo log has been applied to catch it up.

Tablespaces, pages and a write-ahead redo log

MySQL documents InnoDB recovery around tablespaces, data pages, a redo log, undo information, transactions, log sequence numbers (LSNs) and checkpoints. The redo log is a write-ahead log of modifications to data pages: changes are durably recorded there before they are considered committed, which is exactly what makes crash recovery possible afterward. After an unclean shutdown, InnoDB's recovery sequence discovers tablespaces, applies redo from the last checkpoint forward, and rolls back whatever transactions never completed — an uncleanly stopped InnoDB database can be entirely crash-recoverable without being “corrupt” in any meaningful sense.

LSNs link a page's on-disk state to its redo history

Log sequence numbers increase as redo records are written, and individual pages carry LSN-related state that crash recovery uses to know exactly how far forward, from the last checkpoint, redo needs to be replayed. A page on disk can be legitimately older than the database's true committed state until that replay runs; if the data files and redo logs being worked from come from inconsistent copies or different points in time, crash recovery itself can fail outright or produce an unusable result, because the two are only meaningful together as one paired state.

Never delete redo logs as a generic corruption fix

MySQL explicitly warns against removing InnoDB redo logs to speed up or work around recovery. The reason is direct: unflushed but already-committed modifications can exist only in the redo log, the data files themselves may still represent an older checkpoint state, and deleting the log can permanently prevent those already-committed changes from ever being applied. Data files and redo logs describe one transactional generation together — discarding one half of that pair does not simplify recovery, it removes part of what recovery actually depends on.

A live, unquiesced file-by-file copy can be internally inconsistent

Copying a live InnoDB data directory file by file, without first quiescing writes or using a proper hot-backup mechanism, can produce a set of files that never coexisted as one valid checkpoint state — every file can copy successfully with no I/O errors at all, and the resulting set can still fail to form a state that InnoDB's own crash recovery is able to make sense of.

Forced recovery is for extraction, not for continued production use

innodb_force_recovery exists for severe corruption scenarios where the server otherwise cannot start at all. Its role is primarily to get the server running just enough to dump or extract data out of it — it does not certify that the resulting instance is safe to keep running in production. The higher recovery modes should never be presented as routine repair; they are a deliberately narrow tool for getting data out of an instance that is otherwise unusable.

Secondary indexes are usually less risky to lose than clustered data

Corruption confined to a secondary index can be considerably less serious than corruption in the clustered index or primary table data, the system tablespace, the data dictionary, or redo/system metadata — the same general pattern that applies in SQL Server. Damage to a derived structure is frequently reconstructable from the authoritative row data it was built from; damage to that authoritative row data itself has no equivalent fallback.

Doublewrite can rescue a torn page write

InnoDB has historically used a doublewrite mechanism to protect against incomplete or torn page writes: a page is written to a doublewrite buffer area first, and only then to its final tablespace location, so that if a crash tears the final write, a valid copy may still exist in the doublewrite buffer to recover from. Exact doublewrite behavior varies meaningfully by MySQL version and configuration, which is why it belongs in advanced diagnosis rather than broad, version-agnostic claims about how any given instance will behave.

Backup and binary logs enable point-in-time recovery

A physical backup combined with binary logs supports point-in-time recovery, restoring a known-good base state and then replaying logged transactions forward to a specific moment — the same general shape as SQL Server's log roll-forward, expressed through MySQL's own backup and binlog mechanisms.

Do not delete the redo log to fix a startup failure

Removing InnoDB redo logs to make a database start again can permanently discard already-committed changes that exist nowhere else. Preserve the full data directory, including redo logs, before attempting any recovery mode.