redo log

WAL write-ahead Logging: Logs are written before disks. When a record is updated, InnoDb writes the record to a redo log, updates memory, and updates the operation to disk when appropriate.

Innodb’s redolog is a fixed size. If the redolog is full, it pauses all other operations to make space and continues writing to redolog. Redolog is sequential. Although redo logs are also on disk, they take less time and are more efficient than random I/O, which updates data directly.

Write pos is the current position, and checkpoint is the position to flush, and update the record to the data file before flushing.

So crash-safe.

binlog

Binlog is server layer, redolog is InnoDB engine layer. So binlog works with all the engines.

Redolog is a physical log that records “what changes were made on a data page”, and binlog is a logical log that records the original logic of the statement, such as “Add 1 to the C field on the line ID=2

Redo is a circular write, and binlog is an appending.

As in the update process:

  1. The executor takes the row given by the engine, adds 1 to it, for example, N+1 to get a new row of data, and then calls the engine interface to write the new row of data.
  2. The engine updates the data to memory and logs the update to the redo log. The redo log is prepared. The executor is then told that the execution is complete and the transaction can be committed at any time.
  3. The executor generates a binlog of this operation and writes the binlog to disk.
  4. The executor calls the commit transaction interface of the engine, and the engine changes the redo log to the commit state.

Two-phase commit

Redolog prepares before committing.

If the database can be restored within half a month, then the binlog must be in half a month, and the system regularly backup the whole database.

The operation of restoring to a certain time:

Find the most recent full backup and restore from it to the temporary repository.

Retrieve the binlog from the backup time and place it at the time you want to restore it.

If not two-phase commit:

If you write redolog, then you crash before you write binlog, so binlog doesn’t have this sentence, and you end up with different expectations.

Redolog has not yet written to the database, so the actual value of the database has not changed, but the binlog has changed, so it is equivalent to an extra transaction in the restored table.