Understanding MySQL’s Logical Architecture and Its Crucial Logs

MySQL Logical Architecture

MySQL's logical architecture consists of three layers:

First layer: handles client connections, authentication, and security checks.

Second layer (

server

): parses, optimizes, and executes SQL statements.

Third layer: storage engine, responsible for storing and retrieving data.

The server layer does not manage transactions; transactions are implemented by the storage engine. In MySQL, the InnoDB engine is the most widely used transactional storage engine, so the discussion below focuses on InnoDB.

MySQL Data Update Process

The update flow involves three main logs:

redo log

,

bin log

and

undo log

. These logs record changes before they are flushed to disk, ensuring durability and recoverability.

redo log (Redo Log)

The redo log is a transaction log of the InnoDB storage engine. MySQL uses a buffer pool to cache frequently accessed pages; modified pages become dirty pages and are periodically flushed to disk. If MySQL crashes before dirty pages are flushed, data loss can occur.

To prevent this, InnoDB writes changes to the redo log first. The redo log uses Write‑Ahead Logging (WAL): the log is written and synced to disk before the actual data page is considered committed.

Note: When a transaction is in the prepare state, it is not fully committed until the bin log is also written to disk, after which the state changes to commit .

During a crash, MySQL replays the redo log to recover unflushed changes.

Fixed Size and Circular Write

Redo logs have a fixed size and are written in a circular fashion. When the log fills, it wraps around, overwriting the oldest entries that have already been flushed to the buffer pool.

The log maintains a

write pos

(current LSN) and a

checkpoint

(LSN of the last flushed page). The region between them contains unflushed modifications; when

write pos

catches up to

checkpoint

, the checkpoint advances to free space.

Note: If the redo log becomes full, MySQL must ensure that all pages referenced by the log have been flushed before erasing old records; otherwise performance degrades.

Crash‑Safe

Because the redo log records every page modification, InnoDB can recover from crashes by replaying the log, guaranteeing durability.

undo log (Rollback Log)

The undo log, also part of InnoDB, records the original state of rows before they are modified. It enables transaction rollback and ensures atomicity.

Example: updating a row records an opposite statement in the undo log. If the transaction fails, the undo log restores the original values.

Undo logs only store the state before the transaction starts; subsequent modifications are recorded in the redo log, while the undo log is used solely for rollback.

Rollback vs. Forward

Uncommitted transactions have dirty pages that may already be flushed. On crash, rolled‑back changes are undone, while forward recovery re‑applies changes from the redo log.

During recovery, MySQL first performs forward recovery (applying redo log) and then rollback (using undo log).

bin log (Binary Log)

The binary log is a server‑level logical log that records all DDL and DML statements (excluding SELECT, SHOW, etc.). It is essential for master‑slave replication and point‑in‑time recovery.

<code>mysql&gt; SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+</code>

When enabled, the bin log writes each statement to a binary file. The default maximum size is 1 GB; the

max_binlog_size

variable can adjust this.

<code>mysql&gt; SHOW BINARY LOGS;
+-----------------+-----------+
| Log_name        | File_size |
+-----------------+-----------+
| mysq-bin.000001 |      8687 |
| mysq-bin.000002 |      1445 |
| ...             |       ... |
+-----------------+-----------+</code>

In replication, the master writes changes to the bin log; the slave's I/O thread reads the log, writes it to a

relay log

, and the SQL thread replays the statements.

Master‑Slave Synchronization

The slave requests a position from the master, receives the bin‑log data, stores it in the relay log, and then executes the statements to keep data consistent.

Point‑in‑Time Recovery

By restoring a backup and replaying bin‑log events up to a specific timestamp, MySQL can recover to any point in time.

relay log (Relay Log)

The relay log has the same format as the bin log. It acts as an intermediate file on the slave: the I/O thread writes bin‑log events to the relay log, and the SQL thread reads and executes them.

slow query log

The slow query log records queries whose execution time exceeds

long_query_time

(default 10 seconds). It is useful for performance tuning.

<code>mysql&gt; SHOW VARIABLES LIKE 'slow_query%';
+---------------------+--------------------------------------------------------+
| Variable_name       | Value                                                  |
+---------------------+--------------------------------------------------------+
| slow_query_log      | OFF                                                    |
| slow_query_log_file | /usr/local/mysql/data/slow.log                         |
+---------------------+--------------------------------------------------------+</code>

Enabling the log and lowering the threshold captures more queries:

<code>mysql&gt; SET GLOBAL slow_query_log = ON;
mysql&gt; SET GLOBAL long_query_time = 0.001;</code>

general query log

The general query log records every client connection and every SQL statement executed by the server. Because it can grow rapidly, it is disabled by default.

<code>mysql&gt; SHOW VARIABLES LIKE 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+</code>

When enabled, the log file location can be queried and the log will contain detailed command information.

error log

The error log records server start/stop events, diagnostic messages, and errors. It is always enabled.

<code>mysql&gt; SHOW VARIABLES LIKE 'log_error';
+---------------+----------------------------------------------+
| Variable_name | Value                                        |
+---------------+----------------------------------------------+
| log_error     | /usr/local/mysql/data/server.err             |
+---------------+----------------------------------------------+</code>

Besides errors, the log also records information such as InnoDB initialization, buffer‑pool setup, and other startup details.

Conclusion

MySQL is a core middleware in many applications. Mastering its logical architecture and the seven key logs—redo, undo, bin, relay, slow query, general, and error—provides a solid foundation for reliable, high‑performance database operations.