Master MySQL Internals: Architecture, Logs, and Recovery Explained

Before diving in, here is a quick overview of MySQL's logical architecture.

MySQL Logical Architecture

MySQL's logical architecture can be divided into three layers:

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

Second layer: the server layer, responsible for SQL parsing, analysis, optimization, and execution.

Third layer: the storage engine, which stores and retrieves data.

Note that 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.

Remember! The diagram above shows the basic MySQL data‑update process, which involves the redo log , bin log , and undo log .

Redo Log (Redo Log)

The redo log is a transaction log belonging to the InnoDB storage engine. It records modifications to each page, not individual rows, allowing recovery of physical data pages after a crash. Redo logging uses Write‑Ahead Logging (WAL): the log must be written to disk before the actual data page is considered committed.

When a transaction commits, InnoDB writes the changes to the redo log and then calls fsync to flush the log to disk. The dirty pages in the Buffer Pool are flushed asynchronously by a background thread.

Note: At commit time the redo log is in the prepare state; it becomes commit only after the bin log is also flushed to disk.

This design ensures that even if MySQL crashes before dirty pages are flushed, the redo log can be replayed on restart to restore the data.

Fixed Size

Redo logs have a fixed size and are written in a circular fashion. When the log fills, it wraps around, overwriting old records that have already been flushed to disk.

The write pos shows the current log sequence number (LSN) being written, while the checkpoint marks the LSN up to which the log has been flushed to disk.

Crash‑Safe

Because of the redo log, InnoDB is crash‑safe: on startup MySQL checks the LSN of data pages against the redo log and replays any unapplied changes.

Undo Log (Rollback Log)

The undo log is also part of InnoDB and records the original state of rows before they are modified, enabling transaction rollback and guaranteeing atomicity.

Example: updating a row records an opposite UPDATE statement in the undo log so that the change can be undone if the transaction fails.

Undo logs only store the original version of a row when the transaction starts; subsequent modifications are recorded in the redo log, while undo logs handle rollback and redo logs handle forward roll (roll‑forward).

Bin Log (Binary Log)

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

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

Binary logs are written sequentially; when a file reaches the configured max_binlog_size (default 1 GB), a new file is created.

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

Bin logs are used for master‑slave replication and for restoring data to a specific point in time.

Master‑Slave Synchronization

In replication, the master writes changes to the bin log; the slave's I/O thread reads the bin log from a given position, writes it to a relay log , and the SQL thread replays the statements.

Point‑in‑Time Recovery vs. Crash Recovery

Redo log: InnoDB‑level, physical page changes, circular write, used for crash recovery.

Bin log: Server‑level, logical SQL statements, append‑only, used for point‑in‑time recovery and replication.

Relay Log (Relay Log)

The relay log has the same format as the bin log. The slave writes the master’s bin log entries to the relay log, and its SQL thread parses the relay log to execute the statements.

Slow Query Log

The slow query log records queries whose execution time exceeds long_query_time . It is disabled by default.

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

Enabling the log and lowering long_query_time (e.g., to 0.001 s) makes MySQL start recording slow queries.

<code>mysql> SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected
mysql> SET GLOBAL long_query_time=0.001;
Query OK, 0 rows affected</code>

General Query Log

The general query log records every client connection and all SQL statements. It is also disabled by default because of its large size.

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

When enabled, the log file contains detailed information about each executed command.

<code>mysql> SET GLOBAL general_log=ON;
Query OK, 0 rows affected
mysql> SHOW VARIABLES LIKE 'general_log_file';
+------------------+---------------------------------------------------+
| Variable_name    | Value                                             |
+------------------+---------------------------------------------------+
| general_log_file | /usr/local/mysql/data/iZ2zebfzaequ90bdlz820sZ.log |
+------------------+---------------------------------------------------+</code>

Error Log

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

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

Besides errors, the log also contains information about InnoDB buffer pool initialization, tablespace creation, and other startup details.

Conclusion

MySQL is a core middleware in many projects; mastering its basic usage is just the beginning. Understanding the seven types of logs—redo, undo, bin, relay, slow query, general query, and error—provides a solid foundation for deeper exploration and reliable database operation.