MySQL utilizes several types of logs to ensure data integrity, facilitate recovery, and enible high-performance replication. Understanding these logs is essential for database administration and performance tuning. This article examines the core logging components within the MySQL architecture, specifically focusing on the InnoDB storage engine and the server layer.

1. Redo Log (The Recovery Log)

The Redo Log is a physical log specific to the InnoDB storage engine. It records physical changes made to data pages (e.g., "modified offset X in page Y"). Its primary purpose is to ensure durability (the 'D' in ACID) by allowing the database to recover from crashes.

• Mechanism: Changes are first written to the Redo Log Buffer in memory and then flushed to the Redo Log File on disk based on a specific strategy.
• Write Pattern: It uses a circular buffer design, meaning it overwrites old logs once the file is full.
• WAL (Write-Ahead Logging): To improve performance, MySQL writes to the log sequentially instead of performing random IO to data files during every transaction.

Flush Strategies (innodb_flush_log_at_trx_commit)

This parameter controls how the log buffer is flushed to the disk:

• 0: Logs are written to the buffer and flushed to disk once per second. High performance, but risks losing 1 second of data if the MySQL service crashes.
• 1 (Default): Logs are written and synced to disk at every transaction commit. Most secure, ensuring zero data loss.
• 2: Logs are written to the OS cache at every commit but flushed to disk once per second. Safe against MySQL crashes, but risks data loss if the entire OS crashes.

Two-Phace Commit (2PC)

To keep the Redo Log and Binary Log consistent, InnoDB uses a two-phase commit process:

1. Prepare Phase: The Redo Log is written and marked as "Prepare."
2. Commit Phase: The Binary Log is written, and then the Redo Log status is updated to "Commit."

-- Inspect Redo Log configuration
SHOW VARIABLES WHERE Variable_name LIKE 'innodb_log_%';

2. Undo Log (The Rollback Log)

The Undo Log records how to "undo" a change. It is a logical log used for transaction rollbacks and Multi-Version Concurrency Control (MVCC).

• INSERT: Records the primary key; rollback deletes the row.
• UPDATE: Records the old version of the row; rollback restores the old values.
• DELETE: Records the row content; rollback re-inserts the data.

-- Check Undo Log settings
SHOW VARIABLES LIKE '%innodb_undo%';

3. Binary Log (Binlog)

The Binary Log is a server-level logical log that records all DDL and DML operations (excluding SELECT and SHOW). It is primarily used for Master-Slave replication and Point-in-Time recovery.

• Write Format:
  • STATEMENT: Records the exact SQL executed.
  • ROW: Records the actual data changes (most reliable for replication).
  • MIXED: Uses STATEMENT by default but switches to ROW for non-deterministic functions.

-- View Binary Log status
SHOW MASTER STATUS;
SHOW VARIABLES LIKE 'sync_binlog';

4. Relay Log

The Relay Log exists on the replica (slave) server. The I/O thread on the slave pulls Binary Log events from the master and saves them into the Relay Log. The SQL thread then reads these logs and applies the changes to the slave dattabase.

5. Monitoring and Diagnostic Logs

Slow Query Log

Records SQL statements that take longer than long_query_time to execute. It is an invaluable tool for performance optimization.

-- Enable slow query logging
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2.0; -- Set threshold to 2 seconds

Error Log

Contains information about the MySQL server's startup, shutdown, and any critical errors encountered during runtime. This is the first place to look when the database fails to start.

General Query Log

Records every single command received by the server. Due to the high I/O overhead, it is typically disabled in production environments and only used for debugging.

Common Database Concepts

• Dirty Pages: Data pages in memory that have been modified but not yet written to disk.
• Checkpointing (Flushing): The process of writing dirty pages from the buffer pool to the data files on disk.
• Sequential vs. Random IO: Logging (Redo/Binlog) generally uses sequential IO (fast), while updating data files often requires random IO (slower).

Log Analysis Utilities

• mysqldumpslow: A built-in tool to summarize slow query logs.
• pt-query-digest: Part of the Percona Toolkit, providing advanced analysis of slow, general, and binary logs.
• mysqlbinlog: A utility to read and process binary log files for recovery.