All DML operations (INSERT/UPDATE/DELETE) are first performed in the Buffer Pool.

Redo Log

Video (Chinese)

1. Provides transaction durability. Redo log is flushed first; dirty pages are flushed later.

Physical Storage Characteristics

1. File structure

   • Fixed-size files (e.g., ib_logfile0, ib_logfile1)
   • Circular write (ring buffer)
   • Physical appends are always to the end of file

2. Write pattern

1
2

// Pseudocode for physical writes
write(log_file, log_record, log_size); // always append

Write-Ahead Logging (WAL)

• Update data pages in the Buffer Pool; the page becomes a dirty page.
• Write the physical changes (e.g., “page X, offset Y, write Z”) sequentially to the redo log buffer.
• On COMMIT, behavior depends on innodb_flush_log_at_trx_commit (commonly 1 by default):

• Dirty page flushing is asynchronous; background threads flush Buffer Pool pages to disk.

Undo Log

1. Enables transaction rollback to ensure atomicity
2. Works with ReadView to implement MVCC, maintaining a version chain for each record

Binlog

Basics

Binlog (Binary Log) is the MySQL Server-layer binary log that records all data changes as logical events; entries are written when a transaction commits.

Key Properties

1. What it records:

   • All DDL (CREATE/ALTER/…) and DML (INSERT/UPDATE/DELETE)
   • Stored as events with metadata like execution time and error codes

2. Binlog vs Redo Log

   	Binlog	Redo Log
   Layer	Server layer	InnoDB engine layer
   Type	Logical (SQL/events)	Physical (page changes)
   Purpose	Replication/point-in-time restore	Crash recovery

3. Formats:

   • STATEMENT: records original SQL (may cause replica divergence due to environment differences)
   • ROW: records row-level changes (recommended default)
   • MIXED: hybrid mode

Replication

server-id uniquely identifies a MySQL instance; both primary and replicas must have one and they must be distinct, to differentiate nodes.

The primary opens a thread to distribute binlog events, called the Binlog Dump thread.

On the replica there are two key threads: an I/O thread that connects to the primary, requests binlog events, and writes them to the relay log; and an SQL thread that reads the relay log and replays it to update data on the replica.

Primary parameters

log-bin enables the binary log, and binlog-format sets the format.

Replica parameters

1. Master_Host: primary’s IP
2. Master_User: replication user
3. Master_Port: port
4. Master_Log_File: current binlog file name on the primary