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

redo log

Video Explanation

1. Implements transaction durability. redo log is flushed to disk first, then dirty pages are flushed to disk

Physical Storage Characteristics

1. File Structure

   • Composed of fixed-size files (such as ib_logfile0, ib_logfile1)
   • Circular write (ring buffer) design
   • Physical writes are always appended to the end of the file

2. Write Mode

1
2

// Pseudo code representing physical write
write(log_file, log_record, log_size); // Always append write

Write-Ahead Logging (WAL):

• Update data pages in Buffer Pool, these data pages become dirty pages.
• Write physical changes to data pages (such as “page number X, offset Y write data Z”) sequentially to redo log buffer.
• When executing COMMIT, behavior is determined by configuration parameter innodb_flush_log_at_trx_commit (usually default value is 1)

• Asynchronous dirty page flush to disk, background threads flush data from BufferPool to hard disk

undo log

1. Implements transaction rollback, ensures transaction atomicity
2. Combined with ReadView implements MVCC, maintains version chain for records

Binlog

Basic Concepts

Binlog (Binary Log) is a binary log implemented at MySQL Server layer, recording all SQL statements that modify data (logical log), written when transactions commit.

Core Features

1. Recorded Content:

   • All DDL (CREATE/ALTER etc.) and DML (INSERT/UPDATE etc.) statements
   • Stored as events, including metadata like execution time, error codes

2. Comparison with Redo Log:

   	Binlog	Redo Log
   Layer	Server layer	InnoDB engine layer
   Type	Logical log (SQL statements)	Physical log (page modifications)
   Purpose	Master-slave replication/data recovery	Crash recovery

3. Three Formats:

   • STATEMENT: Records original SQL (may cause master-slave inconsistency, if slave execution environment differs slightly from master, may lead to data inconsistency)
   • ROW: Records row data changes (default recommended)
   • MIXED: Mixed mode

Master-Slave Replication

server-id is the unique identifier for MySQL instances, both master and slave must have it and cannot be duplicated, used to distinguish different nodes.

Master opens a thread to handle binlog distribution, called Binlog Dump thread.

Slave has two key threads: one is I/O thread, responsible for establishing connection with master, requesting master binlog, then writing to its own relay log; the other is SQL thread, reading content from relay log written by I/O thread, executing SQL statements on slave to update data.

Master Parameters

log-bin enables binary logging, binlog-format sets log format

Slave Parameters

1. Master_Host is the master’s IP
2. Master_User is the user
3. Master_Port is the port
4. Master_Log_File is the master binlog filename