MySQL Binlog: Recording Modes, Replication, Data Recovery Deep Dive

Binlog

Recording Modes

Binlog basic features:

  • Records all database table structure changes (DDL) and table data modifications (DML)
  • Does not record read-only operations like SELECT and SHOW
  • Three recording formats: STATEMENT, ROW, MIXED

Binlog Core Application Scenarios

Master-Slave Replication

  • Enable Binlog function in master database configuration file
  • Master’s Binlog is real-time transmitted to slave through I/O thread
  • Slave’s SQL thread replays these log events

Data Recovery

  • Parse binary log through mysqlbinlog tool
  • Supports point-in-time recovery

Binlog Recording Mode Explained

ROW Mode (Row Replication Mode)

  • Records details of changes to each row of data
  • Suitable for systems with high data security requirements

STATEMENT Mode (Statement Replication Mode)

  • Records actually executed SQL statements
  • Suitable for systems with many batch processing jobs

MIXED Mode (Mixed Mode)

  • Uses STATEMENT mode by default
  • Automatically switches to ROW mode under specific conditions

File Format

Common Log Event Types

  • Query Event: Records SQL statements
  • Row Event: Row changes
  • Xid Event: Transaction commit event
  • Table Map Event: Table structure information

Write Mechanism

Event Trigger and Generation

  • Event generation triggered based on recording mode and operation

Buffer Management

  • Buffer size controlled by binlog_cache_size parameter, default 32KB

Write Control Parameters

  • sync_binlog=0: Depends on system refresh
  • sync_binlog=1: Flush to disk on each transaction commit

File Operations

-- View status
show variables like 'log_bin'

-- View events
show binary logs;
show master status;

-- Recover data
mysqlbinlog --start-datetime="time" --stop-datetime="time" binlog_file | mysql

-- Delete
purge binary logs to 'filename';