How MySQL Handles Crash Recovery and Data Rollback with Logs

Introduction

In this article we discuss how MySQL deals with fault recovery and data rollback, outlining basic requirements such as data persistence, relational storage, fast lookup, and crash recovery.

Data persistence : data saved to disk survives restarts.

Relational storage : deciding whether to append or locate specific records.

Fast lookup : random access versus sequential file search.

Fault recovery and rollback : ensuring writes survive power loss and enabling recovery from accidental deletions or modifications.

MySQL Architecture

MySQL consists of a service layer and a storage engine layer; after separating the storage engine you can choose engines such as InnoDB, MyISAM, Memory, etc. The default engine since MySQL 5.5 is InnoDB.

MySQL Log System

MySQL has three main logs: redo log, binary log, and undo log.

Binary log

The binary log (binlog) records logical SQL statements, e.g. insert into table set xx = xx, and is written in an append‑only fashion.

Redo log

Redo log is provided by the InnoDB engine and records physical changes to data pages. It works like a “blackboard” that buffers updates before they are flushed to the data files, implementing Write‑Ahead Logging (WAL).

Example: a tavern’s ledger and blackboard illustrate how changes are first written to a temporary area and later persisted.

InnoDB uses a circular redo‑log buffer (e.g., four 1 GB files). The write position advances as new records are added; the checkpoint marks the position that can be overwritten after its data have been flushed to disk.

Undo log

Undo log stores the inverse of each SQL operation, enabling rollback. For example, updating a row’s age from 32 to 45 generates an undo entry that would set the age back to 32.

Undo log is also essential for MVCC in InnoDB.

Data Recovery in MySQL

To recover a table accidentally deleted at noon, you typically restore the latest full backup to a temporary database and replay the binary log up to the point before the deletion.

Crash‑Safe Recovery

InnoDB’s transaction support requires distinguishing committed from uncommitted changes; this is achieved through a two‑phase commit involving redo log and binlog.

Two‑Phase Commit

Simple UPDATE execution flow

The executor fetches the row by primary key using the storage engine.

The executor modifies the row value in memory.

The engine writes the change to redo log in the prepare state.

The executor writes the corresponding SQL to the binary log.

The engine commits the redo log, moving it to the commit state.

Separating redo log writes into prepare and commit stages ensures consistency between redo log and binlog.

Without two‑phase commit, a crash could leave redo log and binlog out of sync, causing restored databases to diverge from the original state.

Summary

MySQL’s recovery relies on binlog (logical) and redo log (physical) with circular buffering, periodic full backups, and two‑phase commit to guarantee crash‑safe behavior.

Binary log records SQL statements; redo log records physical page changes.

Full backups plus binlog replay enable point‑in‑time recovery.

Two‑phase commit ensures committed transactions are safely persisted.