Master MySQL’s Three Logs: Redo, Undo, and Binlog for Data Safety

Introduction

Like a black box for an aircraft crash, MySQL relies on three logs to ensure data safety. The author shares a real incident where misconfigured logs caused data loss and explains the underlying principles of MySQL’s redo log, undo log, and binlog.

1. Redo Log – The Persistence Guardian

1.1 Core Role: Crash Recovery

MySQL follows the Write‑Ahead Logging (WAL) principle: changes are first written to the redo log before being applied to data pages.

1.2 Physical Structure

Redo log uses a circular write mechanism. Key parameters can be inspected with:

SHOW VARIABLES LIKE 'innodb_log%';

1.3 Flush Policy in Practice

// JDBC transaction example
Connection conn = DriverManager.getConnection(url, user, pwd);
try {
    conn.setAutoCommit(false);
    Statement stmt = conn.createStatement();
    stmt.executeUpdate("UPDATE account SET balance=balance-100 WHERE id=1");
    stmt.executeUpdate("UPDATE account SET balance=balance+100 WHERE id=2");
    // Core configuration: flush policy
    conn.setClientInfo("innodb_flush_log_at_trx_commit", "1");
    conn.commit(); // triggers redo log flush
} catch (SQLException e) {
    conn.rollback();
}

Comparison of innodb_flush_log_at_trx_commit values:

0 – low safety, highest performance, suitable for cache‑only workloads
1 – highest safety, lowest performance, ideal for financial transactions
2 – medium safety, good performance, suitable for general business systems

2. Undo Log – The Time Machine for Rollback

2.1 MVCC Implementation

2.2 Rollback at Source Level

-- Transaction rollback example
START TRANSACTION;
UPDATE users SET name='张三' WHERE id=1;
-- Undo log records: transaction ID, row ID, old value, rollback pointer
ROLLBACK; -- restores data from undo log

2.3 Long‑Transaction Pitfalls

Long‑running transactions cause undo‑log growth and version‑chain bloat, degrading query performance. Solutions include setting transaction timeout and monitoring information_schema.innodb_trx.

@Transactional(timeout = 30) // seconds
public void updateOrder(Order order) {
    // business logic
}

3. Binlog – The Bridge for Master‑Slave Replication

3.1 Three Formats Compared

STATEMENT records raw SQL (low safety, high speed). ROW records row changes (high safety, low speed). MIXED switches automatically (medium safety and speed).

3.2 Replication Workflow

3.3 Data Recovery Practical Steps

# 1. Parse binlog to locate delete position
mysqlbinlog --start-position=763 --stop-position=941 binlog.000001 > recovery.sql
# 2. Extract rollback SQL
grep -i 'DELETE FROM users' recovery.sql
# 3. Generate compensating statements
sed 's/DELETE FROM/INSERT INTO/g' recovery.sql > rollback.sql
# 4. Execute recovery
mysql -u root -p < rollback.sql

4. Production‑Environment Optimization Guide

4.1 Parameter Tuning Template

[mysqld]
# Redo Log
innodb_log_file_size = 2G
innodb_log_files_in_group = 4
innodb_flush_log_at_trx_commit = 1

# Undo Log
innodb_max_undo_log_size = 1G
innodb_undo_log_truncate = ON
innodb_purge_threads = 4

# Binlog
server_id = 1
log_bin = /data/mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800  # keep 7 days
sync_binlog = 1  # flush on each commit

4.2 Monitoring Metrics Checklist

-- Redo Log
SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_os_log_written' AS redo_written;

-- Undo Log
SELECT SUM(DATA_LENGTH) FROM information_schema.TABLES WHERE TABLE_SCHEMA='mysql' AND TABLE_NAME LIKE 'undo%' AS undo_size;

-- Binlog
SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Binlog_cache_disk_use' AS binlog_disk_use;

4.3 Common Issues and Fixes

Problem: small redo log size leads to frequent checkpoints (Innodb_log_waits > 0). SET GLOBAL innodb_log_file_size = 2147483648; Problem: large transactions cause binlog explosion.

public void batchProcess(List<Order> orders) {
    int batchSize = 100; // each transaction handles 100 rows
    for (int i = 0; i < orders.size(); i += batchSize) {
        transactionTemplate.execute(status -> {
            List<Order> subList = orders.subList(i, Math.min(i + batchSize, orders.size()));
            processBatch(subList);
            return null;
        });
    }
}

5. Summary

Redo Log is the lifeline – configure innodb_flush_log_at_trx_commit=1 and keep log files sufficiently large; monitor Innodb_log_waits to stay near zero.

Undo Log is the safety net – enable innodb_undo_log_truncate, avoid long‑running transactions, and watch information_schema.innodb_trx.

Binlog is the replication cornerstone – use ROW format for financial systems and set sync_binlog=1 for durability.

Respect the logs, and you respect data safety.