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

Introduction

Just as a black box helps reconstruct an aircraft accident, MySQL relies on three logs to guarantee data safety. Misconfigured logging can cause data loss, replication breaks, and transaction rollback failures.

1. A Data‑Loss Incident

An e‑commerce platform lost two hours of order data after a server crash because innodb_flush_log_at_trx_commit was set to 0.

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

Core conclusion: the log system is MySQL’s safety airbag; without proper log configuration data safety is compromised.

2. Redo Log – The Durability Guard

2.1 Crash‑Recovery Principle (WAL)

2.2 Physical Structure

2.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 setting: flush policy
    conn.setClientInfo("innodb_flush_log_at_trx_commit", "1");
    conn.commit(); // triggers redo log flush
} catch (SQLException e) {
    conn.rollback();
}

Flush‑policy comparison: 0 – low safety, high performance; 1 – highest safety, lowest performance; 2 – medium safety and performance.

3. Undo Log – The Transaction Time Machine

3.1 MVCC Implementation

3.2 Rollback Source‑Code Analysis

-- 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

3.3 Problems Caused by Long Transactions

Issues: undo log size explosion and long version chains that degrade query performance.

Solution: set transaction timeout, monitor long‑running transactions.

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

4. Binlog – The Bridge for Master‑Slave Replication

4.1 Three Formats Compared

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

4.2 Full Replication Process

4.3 Data Recovery Practical Steps

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

5. Production‑Environment Optimization Guide

5.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
sync_binlog = 1

5.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;

5.3 Common Issues and Fixes

Problem 1: Small redo log causes frequent checkpoints.

SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';
SET GLOBAL innodb_log_file_size = 2147483648; -- 2GB

Problem 2: Large transactions inflate binlog.

// Split transaction example
public void batchProcess(List<Order> orders) {
    int batchSize = 100;
    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;
        });
    }
}

6. Summary

Redo Log is the lifeline; configure innodb_flush_log_at_trx_commit=1, use sufficiently large log files, and monitor Innodb_log_waits (should be near 0).

Undo Log acts as the antidote; enable innodb_undo_log_truncate, avoid long‑running transactions, and watch information_schema.innodb_trx.

Binlog is the replication cornerstone; choose ROW format for financial systems and set sync_binlog=1 for strong consistency.

Respecting these three logs is essential for reliable MySQL data safety.