Redo Logs, Undo Logs, Doublewrite, Checkpoints, and Crash Recovery

Learning outcomes

Durability is not “COMMIT writes the whole changed table row to its final data file before returning.” InnoDB decouples logical commit from data-page flushing. It changes pages in memory, records redo needed for crash recovery, keeps undo information for rollback and older snapshots, and flushes dirty pages over time. Checkpoints tell recovery how far durable page state has progressed.

Trace one committed UPDATE

Consider a simple priority change:

USE servicehub_innodb_lab;START TRANSACTION;UPDATE work_orders SET priority=1 WHERE work_order_id=3;COMMIT;SELECT work_order_id,priority FROM work_orders WHERE work_order_id=3;

Conceptually, InnoDB locates the clustered page in the buffer pool (reading it if needed), records undo information describing the prior row version, modifies the in-memory page, generates redo records describing physical/logical changes needed for crash recovery, and commits according to the configured durability policy. The dirty page can be flushed later; commit does not require rewriting the entire final tablespace page before returning.

Observe redo and checkpoint progress

SHOW VARIABLES WHERE Variable_name IN ('innodb_redo_log_capacity','innodb_log_buffer_size',  'innodb_flush_log_at_trx_commit','sync_binlog','innodb_doublewrite');SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_redo_log_current_lsn','Innodb_redo_log_checkpoint_lsn',  'Innodb_redo_log_flushed_to_disk_lsn','Innodb_redo_log_logical_size',  'Innodb_redo_log_physical_size','Innodb_redo_log_enabled');SELECT FILE_ID, START_LSN, END_LSN, SIZE_IN_BYTES, IS_FULL, CONSUMER_LEVELFROM performance_schema.innodb_redo_log_filesORDER BY FILE_ID;

An LSN (log sequence number) is an ever-increasing position in redo history. The distance between current and checkpoint LSN is context for checkpoint pressure; it is not a standalone “bad if above X” metric. Capacity, write rate, flush behavior, and storage throughput determine whether the system is healthy.

Undo is not a second copy of the table

An undo record stores information needed to reverse a clustered-record change and to reconstruct older versions for consistent reads. Undo records live in rollback segments inside undo tablespaces (and special temporary structures for temporary tables). They persist as long as rollback or MVCC visibility requires them, then purge can remove obsolete history.

SELECT TRX_ID, TRX_STATE, TRX_STARTED, TRX_ROWS_MODIFIED,       TRX_ISOLATION_LEVEL, TRX_QUERYFROM information_schema.INNODB_TRXORDER BY TRX_STARTED;SHOW ENGINE INNODB STATUS\G

Lesson 5 will deliberately hold a snapshot open so you can see why committed undo history can remain necessary after the writer itself has committed.

Doublewrite: protect whole-page integrity

Storage hardware and operating systems can fail in the middle of a page write. If a data page is only partially written, redo alone may not always have a valid base page to repair. InnoDB therefore writes page images to its doublewrite area before writing them to their final data-file positions. During recovery, a good doublewrite copy can repair an incomplete page.

SHOW VARIABLES WHERE Variable_name LIKE 'innodb_doublewrite%';

In MySQL 8.4 the doublewrite mechanism is enabled by default in normal configurations. Do not disable it on a production server merely to improve a storage benchmark. Special hardware with verified atomic-write guarantees is an architectural decision, not a generic tuning recipe.

Checkpoints bound recovery work

Redo is circular/reusable storage. InnoDB cannot overwrite redo that is still needed to recover dirty pages. As background flushing makes older changes durable in tablespaces, the checkpoint advances and older redo space becomes reusable. If writes generate redo faster than flushing can advance the checkpoint, InnoDB must increase flushing pressure and can eventually throttle foreground work.

SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_redo_log_current_lsn','Innodb_redo_log_checkpoint_lsn',  'Innodb_buffer_pool_pages_dirty','Innodb_buffer_pool_pages_flushed');START TRANSACTION;UPDATE work_orders SET priority = IF(priority=1,2,1);COMMIT;SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_redo_log_current_lsn','Innodb_redo_log_checkpoint_lsn',  'Innodb_buffer_pool_pages_dirty','Innodb_buffer_pool_pages_flushed');

On a tiny lab the values may barely move, and background threads can advance between queries. That is expected. A useful production graph uses time-series deltas at workload scale rather than one screenshot.

Failure drill: confusing redo with the binary log

A common mistake says, “We have the binary log, so InnoDB redo is redundant.” Binary logs record server change events for replication/PITR; they do not replace InnoDB’s page-level crash-recovery mechanism. Conversely, InnoDB redo is not a retained business-history stream you can use as a normal replica feed.

SHOW VARIABLES LIKE 'log_bin';SHOW VARIABLES LIKE 'binlog_format';SHOW VARIABLES LIKE 'sync_binlog';SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled';

The correct production durability design considers both subsystems when binary logging is enabled, including innodb_flush_log_at_trx_commit and sync_binlog, but does not pretend they are the same log.

Disposable crash-recovery lab with Docker

Do not crash a shared MySQL service. The following is an optional but strongly recommended local lab using a disposable container. Use a throwaway password and do not expose the port beyond your development machine.

docker rm -f mysql-ch07-crash 2>/dev/null || truedocker run --name mysql-ch07-crash -e MYSQL_ROOT_PASSWORD=LabOnly-ChangeMe-2026 -p 33077:3306 -d mysql:8.4docker logs -f mysql-ch07-crash

docker rm -f mysql-ch07-crash 2>$nulldocker run --name mysql-ch07-crash -e MYSQL_ROOT_PASSWORD=LabOnly-ChangeMe-2026 -p 33077:3306 -d mysql:8.4docker logs -f mysql-ch07-crash

mysql -h 127.0.0.1 -P 33077 -uroot -pCREATE DATABASE crash_lab;USE crash_lab;CREATE TABLE durable_demo(id INT PRIMARY KEY, note VARCHAR(100)) ENGINE=InnoDB;INSERT INTO durable_demo VALUES (1,'committed before crash');COMMIT;START TRANSACTION;INSERT INTO durable_demo VALUES (2,'never committed');-- Leave this transaction open.

docker kill mysql-ch07-crashdocker start mysql-ch07-crashdocker logs mysql-ch07-crash

mysql -h 127.0.0.1 -P 33077 -uroot -pSELECT * FROM crash_lab.durable_demo ORDER BY id;SHOW ENGINE INNODB STATUS\G

Expected business outcome: row 1 remains; row 2 does not become committed merely because the process died. Server logs should show InnoDB initialization/recovery activity. This lab proves transactional recovery behavior on your container. It does not prove that a specific page used the doublewrite buffer during that particular crash.

docker rm -f mysql-ch07-crash

Hands-on verification and knowledge check

Durability settings are contracts, not speed switches

The most copied InnoDB tuning advice changes innodb_flush_log_at_trx_commit to reduce synchronous log I/O. That variable is not a generic performance knob: it changes what failures a recently acknowledged commit may survive. The safest teaching baseline is the normal durable configuration, then reason about alternatives only when the application has an explicit recovery point objective and has tested the failure model.

Redo capacity is working space, not backup retention

Increasing redo capacity can let InnoDB absorb bursts with less checkpoint pressure, but redo files are continuously reused as checkpoints advance. They are not an archive of every business change and cannot substitute for backups or binary-log retention. Similarly, shrinking redo capacity may increase flushing pressure; the effect depends on write rate and storage capability.

Crash recovery versus media recovery

InnoDB crash recovery assumes the data directory and required InnoDB files still exist but the server stopped unexpectedly. A lost disk, deleted tablespace, ransomware event, or irrecoverably corrupt filesystem is a different problem that requires backup/restore and potentially point-in-time recovery. The Docker drill demonstrates process-crash semantics, not disaster recovery from missing storage.

Production judgment and bridge

Do not tune durability by copying low-durability benchmark settings. innodb_flush_log_at_trx_commit, storage caches, doublewrite, redo capacity, and binary-log syncing define explicit loss/recovery tradeoffs. Any production change needs a stated recovery point objective, failure testing, and storage guarantees.

Lesson 5 closes the internals loop: undo history is useful only while some transaction may still need it. Purge cleans obsolete history—but long snapshots can prevent that cleanup and create growing history-list pressure.

Authoritative references

• MySQL 8.4 Reference Manual — Redo Log
• MySQL 8.4 Reference Manual — Undo Logs
• MySQL 8.4 Reference Manual — Doublewrite Buffer
• MySQL 8.4 Reference Manual — InnoDB Checkpoints
• MySQL 8.4 Reference Manual — InnoDB Recovery