Understanding MySQL Architecture and Log Mechanisms for ACID Transactions

1 Introduction

In today's data‑driven era, database systems are the core component for information storage and management, directly affecting application stability and user experience. MySQL, one of the most popular open‑source relational database management systems, is widely used in many Internet applications, yet many developers and DBAs know little about its internal mechanisms, especially transaction handling and log management.

The ACID properties (Atomicity, Consistency, Isolation, Durability) are essential for reliable database operations. To achieve these, MySQL introduces several log mechanisms—Undo Log, Redo Log, and Binlog—that not only guarantee correct transaction execution and high availability but also provide a solid foundation for data recovery and replication.

This article delves into the core mechanisms of Undo Log, Redo Log, and Binlog, explaining how they cooperate to protect ACID properties, from query optimization to data modification, helping readers master MySQL transaction management.

2 MySQL Architecture

The architecture can be roughly divided into four layers: Connection layer, Server (service) layer, Storage engine layer, and File system layer.

Connection layer: Handles client connections, performs authentication, and stores connection information in a pool for reuse.

Server layer: Responsible for SQL parsing, optimization, query cache, and built‑in functions.

Storage engine layer: Provides pluggable storage engines that directly interact with data files and logs on disk.

File system layer: Contains log files, data files, and other MySQL‑related programs. The server and storage engine layers form the core of the architecture.

3 Server Layer Functions

3.1 Query Parsing and Optimization

Syntax parsing: Converts client SQL into an internal structure and checks syntax.

Query rewrite: Optimizes certain queries into equivalent, more efficient forms.

Query optimization: The optimizer selects the best execution plan based on statistics, including join order and index choice.

3.2 Query Cache

The server can cache results of frequently executed queries. In production environments the cache is usually disabled because it requires the exact same SQL, connection, protocol, and character set to hit. Starting with MySQL 8.0, the query cache is removed.

3.3 SQL Execution

After parsing and optimization, the server passes the execution plan to the storage engine, which performs the actual data operations.

4 Core Components of the Server Layer

The server layer has three core components: parser, optimizer, and executor.

4.1 Parser

The parser transforms a user’s SQL statement into an internal representation through three sub‑stages:

Lexical analysis: Tokenizes the SQL into identifiers, operators, keywords, etc.

Syntactic analysis: Builds a parse tree or abstract syntax tree (AST) and validates syntax.

Semantic analysis: Checks object existence, permissions, and data‑type compatibility.

The parser thus produces a structured representation ready for optimization.

4.2 Optimizer

The optimizer generates the most efficient execution plan based on the parse tree and metadata.

4.2.1 Logical Optimization

Logical transformations include eliminating redundant sub‑queries, rewriting OR conditions to UNION, merging queries, and removing unnecessary ORDER BY or DISTINCT clauses.

4.2.2 Physical Optimization

Physical decisions involve choosing join algorithms (nested‑loop, hash, sort‑merge), selecting appropriate indexes, and determining sorting methods. The optimizer uses a cost‑based model to evaluate and pick the lowest‑cost plan.

4.3 Executor

The executor carries out the plan, performing permission checks, table scans, join operations, sorting, aggregation, and returning results or committing modifications.

4.3.1 Permission Check

Before execution, the executor verifies that the user has the required privileges.

4.3.2 Plan Execution

It follows the optimizer’s plan, invoking storage‑engine APIs for data reads/writes.

4.3.3 Transaction Management

For data‑modifying statements (INSERT, UPDATE, DELETE), the executor interacts closely with Undo Log, Redo Log, and Binlog to ensure ACID compliance.

5 Execution Process of MySQL Query Statements

A typical query follows these steps:

Connection establishment and authentication via the connector.

Query cache check (deprecated in MySQL 8.0).

Parsing and preprocessing (syntax, object existence).

Optimization to generate the best execution plan.

Executor invokes the storage engine to perform the query.

Storage engine reads/writes the required data.

If cache hit, results are returned directly; otherwise, the query result is returned after execution.

6 Execution Process of MySQL Modification Statements

Example: UPDATE table SET name='Zhang San' WHERE id=10;

Locate the row with id=10 via the storage engine.

Read the row into the buffer pool (or load from disk).

Record an Undo Log entry for rollback.

Modify the row’s name field and write the new record via the storage engine.

Write the change to the Redo Log buffer and mark the transaction as prepare .

Generate a Binlog entry and write it to disk.

Commit the transaction.

Mark the Redo Log entry as committed.

Further detailed steps involve buffer‑pool page handling, double‑write buffering, and final disk flushes.

7 Undo Log, Redo Log and Binlog Implement ACID

7.1 Overview of ACID Properties

Atomicity: All operations in a transaction succeed or all fail.

Consistency: Transactions move the database from one consistent state to another.

Isolation: Concurrent transactions do not interfere with each other.

Durability: Once committed, changes survive crashes.

7.2 Undo Log (Rollback Log)

Undo Log records the original state of data before modification, enabling rollback and supporting MVCC. It stores reverse operations (e.g., INSERT → DELETE) and forms a version chain linked by roll_pointer . The Undo pages reside in the Buffer Pool for fast access and are reclaimed after all snapshot reads complete.

7.3 Redo Log (Redo Log)

Redo Log records the new values after a transaction modifies data, ensuring durability through Write‑Ahead Logging (WAL).

7.3.1 Redo Log Execution Process

Read original data into the Buffer Pool and mark the page as dirty.

Write modification details to the Redo Log Buffer.

On commit, flush the buffer to the Redo Log file (append‑only).

Periodically flush dirty pages to disk.

7.3.2 Redo Log File Writing Mechanism

Redo Log uses a fixed‑size circular buffer. When the file is full, it wraps around. Two pointers manage the process: write pos (current write location) and checkpoint (earliest safe reclaim point).

7.3.3 Redo Log Buffer Flush Strategy

The innodb_flush_log_at_trx_commit setting controls when the buffer is flushed:

=0 : Flushes once per second; highest performance, lowest durability.

=1 (default): Flushes on every commit; highest durability, lower performance.

=2 : Writes to OS cache on commit; durability is OS‑dependent, performance is intermediate.

7.4 Binlog (Binary Log)

Binlog records all statements that change the database state, supporting backup/recovery and master‑slave replication.

7.4.1 Binlog Flush Timing

During a transaction, changes are first written to a Binlog Cache in the server layer. On commit, the entire cache is flushed to the Binlog file atomically. The sync_binlog variable controls how often an fsync is performed:

0 : Write only; OS decides when to flush.

1 : Write and immediately fsync ; strongest durability.

N>1 : Write each commit, fsync after N commits; balances performance and safety.

8 Conclusion

Through this deep analysis we have understood the three major MySQL log mechanisms—Undo Log, Redo Log, and Binlog—and their crucial roles in guaranteeing transaction ACID properties. Proper configuration and management of these logs are essential skills for DBAs and developers to optimize performance, ensure data safety, and build highly available database architectures.

About the Author

Zhu Hongxu, Java Development Engineer at XianKeHui

References

[1] "MySQL in Practice 45 Lectures"

[2] https://zhuanlan.zhihu.com/p/451007506

[3] https://zhuanlan.zhihu.com/p/667283776