Fun MySQL fact of the day: redo logs

Over the last few weeks, we've considered how useful the MySQL binary log can be, but you may be thinking, "if the binary log doesn't get written until commit, how does MySQL undo a transaction if it crashes?". And, well, if you remember back through March and April, you'll recall that transactions are handled by a table's storage engine, not MySQL. For example, by the InnoDB storage engine. With that said, let's get one thing out of the way: while the binary log can be used to perform point-in-time recovery, it is not actively used in crash recovery.

Instead, InnoDB has its own write-ahead log called the "redo log", which, effectively, stores the changes that have been made to a page and/or record. By default, the log file is represented by two files, ib_logfile0 and ib_logfile1, which InnoDB writes to circularly. Now, unlike the binary log, the changes in the redo log are forward-only and contain no information about the current state of the database.

While this is a lot of fun crammed into not a lot of content, there are plenty of missing details that will help tie everything together later. In the meantime, I'll ask you to ponder how you would keep two write-ahead log files in sync in the face of a crash.