Understanding MySQL Binlog Cache and Event Generation

1. Preparation

Set the MySQL configuration to binlog_format = ROW and binlog_rows_query_log_events = OFF , then create a test table t_binlog with columns id , i1 , and str1 . An example transaction inserts a row and commits.

binlog_format = ROW
binlog_rows_query_log_events = OFF

CREATE TABLE `t_binlog` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `i1` int DEFAULT '0',
  `str1` varchar(32) DEFAULT '',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

BEGIN;
INSERT INTO `t_binlog` (`i1`, `str1`) VALUES (100, 'MySQL 核心模块揭秘');
COMMIT;

2. Parsing binlog

After executing the SQL, the binlog can be parsed with mysqlbinlog to reveal four events: Query_log_event , Table_map_log_event , Write_rows_log_event , and Xid_log_event . The BEGIN statement itself does not generate a binlog event, while the INSERT generates the three events listed.

cd
mysqlbinlog binlog.000395 \
  --base64-output=decode-rows -vv

3. Binlog cache

MySQL uses two caches per transaction: stmt_cache for non‑transactional tables and trx_cache for transactional tables (InnoDB). Only trx_cache is relevant here; it consists of a memory buffer (default 32 KB, controlled by binlog_cache_size ) and a temporary file in the OS /tmp directory (named with the ML prefix). The total size of both levels is limited by max_binlog_cache_size .

4. Generating binlog

When a DML statement modifies data, the server first creates a Table_map_log_event containing the table name and ID, then writes a Write_rows_log_event for the actual row changes. The transaction starts with a Query_log_event whose content is BEGIN , and ends with a COMMIT that produces an Xid_log_event . If a single row’s binlog exceeds 8192 bytes, it gets its own event, governed by binlog_row_event_max_size .

5. Writing to trx_cache

All binlog events are first written to the trx_cache buffer. If the buffer lacks space, the partially filled buffer is flushed to the temporary file, the buffer is cleared, and the remaining event data is written in 4096‑byte chunks (IO_SIZE) to the temporary file; any leftover bytes (< 4096) stay in the buffer. This process repeats until the entire event is stored.

6. Summary

The trx_cache operates in two levels—an in‑memory buffer and a temporary file. During a transaction, every binlog event is first placed in the buffer; when the buffer fills, its contents are flushed to the temporary file, ensuring that events belonging to the same transaction remain contiguous in the binlog file.