Understanding MySQL Binary Log (binlog): Architecture, Usage, and Implementation

This article provides a comprehensive introduction to MySQL's binary log (binlog), explaining its fundamental purpose and architecture. Binlog is a binary format file that records SQL statements for database updates, including changes to tables and content, but excludes query operations. By default, binlog logs are in binary format and require the mysqlbinlog tool for parsing and viewing.

The article explains why binlog is essential, primarily for database master-slave replication and incremental data recovery. In MySQL's architecture, which separates SQL and engine layers, binlog serves as a logical log to provide transparency across different storage engines. During data modification requests, the primary server generates binlog containing the modification operations and sends it to replicas, which replay these logs to perform identical changes. Binlog also supports backup point restoration.

The master-slave replication mechanism is detailed, where the master uses a binlog dump thread to notify all slaves of data changes. Slaves have I/O threads that receive binlog events and write them to local relay logs, and SQL threads that read relay logs, convert the content to SQL, and replay the operations. For incremental recovery, MySQL re-executes SQL statements from specified binlog log intervals.

The article covers Write-Ahead Logging (WAL) technology, where write operations are not immediately updated to disk but first written to the log system. For transactional engines like InnoDB, binlog recording only occurs after transaction commit. The sync_binlog parameter controls when binlog is flushed to disk, with implications for data loss during power failures or crashes.

Detailed implementation of the transaction binlog event writing process is explained, including binlog cache (memory for caching events, controlled by binlog_cache_size), temporary files for overflowed events (prefixed with 'ML', controlled by max_binlog_cache_size), binlog files (controlled by max_binlog_size), and various event types like MAP_EVENT, QUERY_EVENT, XID_EVENT, and WRITE_EVENT.

The writing mechanism is described step-by-step: transaction initiation, DML execution with memory allocation for binlog cache, continuous writing of generated events to cache, overflow handling to temporary files, transaction commit writing all data to binlog files, and cleanup processes. Code examples demonstrate the internal implementation of binlog writing functions, showing how data is copied to memory cache, flushed to disk when necessary, and managed through various buffer operations.

The article concludes by summarizing the key learnings about binlog's purpose, usage scenarios, and how binlog logs are generated, providing references to additional resources for deeper understanding.