MySQL日志系统

概述

MySQL的日志系统是保证数据库ACID特性和数据安全的重要机制。主要包括Redo Log（重做日志）、Undo Log（回滚日志）和Binlog（二进制日志），它们在事务处理、崩溃恢复和主从复制中发挥关键作用。

Redo Log（重做日志）

作用和原理

WAL机制

Write-Ahead Logging：先写日志，再写数据页
目的：保证事务的持久性，支持崩溃恢复
位置：InnoDB存储引擎层

sql

-- 查看Redo Log配置
SHOW VARIABLES LIKE 'innodb_log%';

-- 重要参数
innodb_log_file_size = 256M      -- 单个日志文件大小
innodb_log_files_in_group = 2    -- 日志文件数量
innodb_log_buffer_size = 16M     -- 日志缓冲区大小

-- 查看Redo Log配置
SHOW VARIABLES LIKE 'innodb_log%';

-- 重要参数
innodb_log_file_size = 256M      -- 单个日志文件大小
innodb_log_files_in_group = 2    -- 日志文件数量
innodb_log_buffer_size = 16M     -- 日志缓冲区大小

工作流程

java

// Redo Log写入流程示意
public class RedoLogProcess {
    
    public void executeTransaction() {
        // 1. 事务开始，生成LSN（Log Sequence Number）
        long lsn = generateLSN();
        
        // 2. 修改数据页在内存中的副本
        modifyPageInBuffer();
        
        // 3. 将修改记录写入Redo Log Buffer
        writeToRedoLogBuffer(lsn, changeRecord);
        
        // 4. 事务提交时，Redo Log Buffer刷盘
        flushRedoLogToDisk();
        
        // 5. 后台线程异步将脏页刷盘
        asyncFlushDirtyPages();
    }
}

// Redo Log写入流程示意
public class RedoLogProcess {
    
    public void executeTransaction() {
        // 1. 事务开始，生成LSN（Log Sequence Number）
        long lsn = generateLSN();
        
        // 2. 修改数据页在内存中的副本
        modifyPageInBuffer();
        
        // 3. 将修改记录写入Redo Log Buffer
        writeToRedoLogBuffer(lsn, changeRecord);
        
        // 4. 事务提交时，Redo Log Buffer刷盘
        flushRedoLogToDisk();
        
        // 5. 后台线程异步将脏页刷盘
        asyncFlushDirtyPages();
    }
}

刷盘策略

sql

-- innodb_flush_log_at_trx_commit参数控制刷盘策略

-- 0: 每秒刷盘一次（性能最好，安全性最差）
SET GLOBAL innodb_flush_log_at_trx_commit = 0;

-- 1: 每次事务提交都刷盘（默认，安全性最好）
SET GLOBAL innodb_flush_log_at_trx_commit = 1;

-- 2: 每次提交写入OS缓存，每秒刷盘（折中方案）
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

-- innodb_flush_log_at_trx_commit参数控制刷盘策略

-- 0: 每秒刷盘一次（性能最好，安全性最差）
SET GLOBAL innodb_flush_log_at_trx_commit = 0;

-- 1: 每次事务提交都刷盘（默认，安全性最好）
SET GLOBAL innodb_flush_log_at_trx_commit = 1;

-- 2: 每次提交写入OS缓存，每秒刷盘（折中方案）
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

循环写入

sql

-- Redo Log采用循环写入方式
-- 当写满时，会覆盖最早的日志
-- 需要确保被覆盖的日志对应的数据页已经刷盘

-- 查看Redo Log状态
SHOW ENGINE INNODB STATUS\G

-- 关键信息：
-- Log sequence number: 当前LSN
-- Log flushed up to: 已刷盘的LSN
-- Pages flushed up to: 已刷盘的数据页对应的LSN

-- Redo Log采用循环写入方式
-- 当写满时，会覆盖最早的日志
-- 需要确保被覆盖的日志对应的数据页已经刷盘

-- 查看Redo Log状态
SHOW ENGINE INNODB STATUS\G

-- 关键信息：
-- Log sequence number: 当前LSN
-- Log flushed up to: 已刷盘的LSN
-- Pages flushed up to: 已刷盘的数据页对应的LSN

Undo Log（回滚日志）

作用和原理

主要功能

事务回滚：撤销未提交事务的修改
MVCC实现：提供事务的一致性读视图
崩溃恢复：回滚未完成的事务

sql

-- 查看Undo Log配置
SHOW VARIABLES LIKE 'innodb_undo%';

-- 重要参数
innodb_undo_tablespaces = 2      -- Undo表空间数量
innodb_undo_log_truncate = ON    -- 是否自动截断Undo日志
innodb_max_undo_log_size = 1G    -- Undo表空间最大大小

-- 查看Undo Log配置
SHOW VARIABLES LIKE 'innodb_undo%';

-- 重要参数
innodb_undo_tablespaces = 2      -- Undo表空间数量
innodb_undo_log_truncate = ON    -- 是否自动截断Undo日志
innodb_max_undo_log_size = 1G    -- Undo表空间最大大小

Undo Log类型

sql

-- Insert Undo Log
-- 记录插入操作，只需要记录主键值
INSERT INTO users (id, name) VALUES (1, 'John');
-- Undo记录：DELETE FROM users WHERE id = 1;

-- Update Undo Log  
-- 记录更新前的完整记录
UPDATE users SET name = 'Jane' WHERE id = 1;
-- Undo记录：UPDATE users SET name = 'John' WHERE id = 1;

-- Delete Undo Log
-- 记录删除前的完整记录
DELETE FROM users WHERE id = 1;
-- Undo记录：INSERT INTO users (id, name) VALUES (1, 'John');

-- Insert Undo Log
-- 记录插入操作，只需要记录主键值
INSERT INTO users (id, name) VALUES (1, 'John');
-- Undo记录：DELETE FROM users WHERE id = 1;

-- Update Undo Log  
-- 记录更新前的完整记录
UPDATE users SET name = 'Jane' WHERE id = 1;
-- Undo记录：UPDATE users SET name = 'John' WHERE id = 1;

-- Delete Undo Log
-- 记录删除前的完整记录
DELETE FROM users WHERE id = 1;
-- Undo记录：INSERT INTO users (id, name) VALUES (1, 'John');

MVCC实现

java

// MVCC读取流程示意
public class MVCCReader {
    
    public Record readRecord(long recordId, ReadView readView) {
        Record currentRecord = getCurrentRecord(recordId);
        
        // 检查记录的事务ID是否对当前事务可见
        if (isVisible(currentRecord.getTrxId(), readView)) {
            return currentRecord;
        }
        
        // 不可见，通过Undo Log构造历史版本
        Record historyRecord = buildHistoryRecord(currentRecord, readView);
        return historyRecord;
    }
    
    private Record buildHistoryRecord(Record record, ReadView readView) {
        long undoLogPtr = record.getRollPtr();
        
        while (undoLogPtr != null) {
            UndoLogRecord undoLog = getUndoLog(undoLogPtr);
            
            if (isVisible(undoLog.getTrxId(), readView)) {
                return undoLog.buildRecord();
            }
            
            undoLogPtr = undoLog.getNextUndoPtr();
        }
        
        return null; // 记录对当前事务不可见
    }
}

// MVCC读取流程示意
public class MVCCReader {
    
    public Record readRecord(long recordId, ReadView readView) {
        Record currentRecord = getCurrentRecord(recordId);
        
        // 检查记录的事务ID是否对当前事务可见
        if (isVisible(currentRecord.getTrxId(), readView)) {
            return currentRecord;
        }
        
        // 不可见，通过Undo Log构造历史版本
        Record historyRecord = buildHistoryRecord(currentRecord, readView);
        return historyRecord;
    }
    
    private Record buildHistoryRecord(Record record, ReadView readView) {
        long undoLogPtr = record.getRollPtr();
        
        while (undoLogPtr != null) {
            UndoLogRecord undoLog = getUndoLog(undoLogPtr);
            
            if (isVisible(undoLog.getTrxId(), readView)) {
                return undoLog.buildRecord();
            }
            
            undoLogPtr = undoLog.getNextUndoPtr();
        }
        
        return null; // 记录对当前事务不可见
    }
}

Undo Log清理

sql

-- 查看Undo Log使用情况
SELECT 
    tablespace_name,
    file_name,
    file_size/1024/1024 AS size_mb
FROM information_schema.FILES 
WHERE tablespace_name LIKE 'innodb_undo%';

-- Purge线程负责清理不再需要的Undo Log
-- 清理条件：没有事务需要该版本的数据

-- 查看Purge进度
SHOW ENGINE INNODB STATUS\G
-- 关键信息：
-- History list length: 未清理的Undo Log数量
-- Purge done for trx's n:o < xxx: 已清理到的事务号

-- 查看Undo Log使用情况
SELECT 
    tablespace_name,
    file_name,
    file_size/1024/1024 AS size_mb
FROM information_schema.FILES 
WHERE tablespace_name LIKE 'innodb_undo%';

-- Purge线程负责清理不再需要的Undo Log
-- 清理条件：没有事务需要该版本的数据

-- 查看Purge进度
SHOW ENGINE INNODB STATUS\G
-- 关键信息：
-- History list length: 未清理的Undo Log数量
-- Purge done for trx's n:o < xxx: 已清理到的事务号

Binlog（二进制日志）

作用和原理

主要功能

主从复制：主库的Binlog传输到从库进行重放
数据恢复：通过Binlog进行点时间恢复
数据审计：记录所有数据变更操作

sql

-- 开启Binlog
SET GLOBAL log_bin = ON;
SET GLOBAL binlog_format = ROW;

-- 查看Binlog配置
SHOW VARIABLES LIKE 'binlog%';
SHOW VARIABLES LIKE 'log_bin%';

-- 重要参数
log_bin = /var/log/mysql/mysql-bin    -- Binlog文件路径
binlog_format = ROW                   -- 日志格式
max_binlog_size = 1G                  -- 单个文件最大大小
binlog_expire_logs_seconds = 604800   -- 日志保留时间（7天）

-- 开启Binlog
SET GLOBAL log_bin = ON;
SET GLOBAL binlog_format = ROW;

-- 查看Binlog配置
SHOW VARIABLES LIKE 'binlog%';
SHOW VARIABLES LIKE 'log_bin%';

-- 重要参数
log_bin = /var/log/mysql/mysql-bin    -- Binlog文件路径
binlog_format = ROW                   -- 日志格式
max_binlog_size = 1G                  -- 单个文件最大大小
binlog_expire_logs_seconds = 604800   -- 日志保留时间（7天）

Binlog格式

sql

-- STATEMENT格式：记录SQL语句
-- 优点：日志量小
-- 缺点：可能导致主从不一致

-- ROW格式：记录行的变化
-- 优点：主从一致性好
-- 缺点：日志量大

-- MIXED格式：混合模式，自动选择
-- MySQL根据语句类型自动选择格式

-- STATEMENT格式：记录SQL语句
-- 优点：日志量小
-- 缺点：可能导致主从不一致

-- ROW格式：记录行的变化
-- 优点：主从一致性好
-- 缺点：日志量大

-- MIXED格式：混合模式，自动选择
-- MySQL根据语句类型自动选择格式

Binlog操作

sql

-- 查看Binlog文件列表
SHOW BINARY LOGS;

-- 查看当前Binlog文件和位置
SHOW MASTER STATUS;

-- 查看Binlog内容
SHOW BINLOG EVENTS IN 'mysql-bin.000001' FROM 4 LIMIT 10;

-- 使用mysqlbinlog工具解析
-- mysqlbinlog --start-datetime="2023-01-01 00:00:00" \
--             --stop-datetime="2023-01-01 23:59:59" \
--             mysql-bin.000001

-- 刷新Binlog（生成新文件）
FLUSH BINARY LOGS;

-- 清理Binlog
PURGE BINARY LOGS TO 'mysql-bin.000010';
PURGE BINARY LOGS BEFORE '2023-01-01 00:00:00';

-- 查看Binlog文件列表
SHOW BINARY LOGS;

-- 查看当前Binlog文件和位置
SHOW MASTER STATUS;

-- 查看Binlog内容
SHOW BINLOG EVENTS IN 'mysql-bin.000001' FROM 4 LIMIT 10;

-- 使用mysqlbinlog工具解析
-- mysqlbinlog --start-datetime="2023-01-01 00:00:00" \
--             --stop-datetime="2023-01-01 23:59:59" \
--             mysql-bin.000001

-- 刷新Binlog（生成新文件）
FLUSH BINARY LOGS;

-- 清理Binlog
PURGE BINARY LOGS TO 'mysql-bin.000010';
PURGE BINARY LOGS BEFORE '2023-01-01 00:00:00';

两阶段提交

java

// MySQL两阶段提交流程
public class TwoPhaseCommit {
    
    public void commitTransaction() {
        // Phase 1: Prepare阶段
        // 1. 写入Redo Log，标记为prepare状态
        writeRedoLogPrepare();
        
        // 2. 写入Binlog
        writeBinlog();
        
        // Phase 2: Commit阶段  
        // 3. 写入Redo Log，标记为commit状态
        writeRedoLogCommit();
        
        // 如果在步骤2和3之间崩溃，恢复时：
        // - 如果Binlog完整，提交事务
        // - 如果Binlog不完整，回滚事务
    }
}

// MySQL两阶段提交流程
public class TwoPhaseCommit {
    
    public void commitTransaction() {
        // Phase 1: Prepare阶段
        // 1. 写入Redo Log，标记为prepare状态
        writeRedoLogPrepare();
        
        // 2. 写入Binlog
        writeBinlog();
        
        // Phase 2: Commit阶段  
        // 3. 写入Redo Log，标记为commit状态
        writeRedoLogCommit();
        
        // 如果在步骤2和3之间崩溃，恢复时：
        // - 如果Binlog完整，提交事务
        // - 如果Binlog不完整，回滚事务
    }
}

日志相关配置优化

性能优化

sql

-- Redo Log优化
innodb_log_file_size = 512M           -- 增大日志文件，减少checkpoint频率
innodb_log_buffer_size = 32M          -- 增大日志缓冲区
innodb_flush_log_at_trx_commit = 2    -- 根据业务需求调整

-- Binlog优化
binlog_cache_size = 1M                -- 增大Binlog缓存
sync_binlog = 1000                    -- 调整同步频率（安全性vs性能）
binlog_group_commit_sync_delay = 100  -- 组提交延迟
binlog_group_commit_sync_no_delay_count = 10  -- 组提交事务数

-- Redo Log优化
innodb_log_file_size = 512M           -- 增大日志文件，减少checkpoint频率
innodb_log_buffer_size = 32M          -- 增大日志缓冲区
innodb_flush_log_at_trx_commit = 2    -- 根据业务需求调整

-- Binlog优化
binlog_cache_size = 1M                -- 增大Binlog缓存
sync_binlog = 1000                    -- 调整同步频率（安全性vs性能）
binlog_group_commit_sync_delay = 100  -- 组提交延迟
binlog_group_commit_sync_no_delay_count = 10  -- 组提交事务数

监控指标

sql

-- 查看日志相关状态
SHOW GLOBAL STATUS LIKE 'Innodb_log%';
SHOW GLOBAL STATUS LIKE 'Binlog%';

-- 重要指标
Innodb_log_waits              -- 等待日志缓冲区的次数
Innodb_log_writes             -- 日志写入次数
Innodb_log_write_requests     -- 日志写入请求次数
Binlog_cache_use              -- 使用Binlog缓存的事务数
Binlog_cache_disk_use         -- 使用临时文件的事务数

-- 查看日志相关状态
SHOW GLOBAL STATUS LIKE 'Innodb_log%';
SHOW GLOBAL STATUS LIKE 'Binlog%';

-- 重要指标
Innodb_log_waits              -- 等待日志缓冲区的次数
Innodb_log_writes             -- 日志写入次数
Innodb_log_write_requests     -- 日志写入请求次数
Binlog_cache_use              -- 使用Binlog缓存的事务数
Binlog_cache_disk_use         -- 使用临时文件的事务数

故障恢复

崩溃恢复流程

java

// MySQL崩溃恢复流程
public class CrashRecovery {
    
    public void recover() {
        // 1. 扫描Redo Log，找到最后一个checkpoint
        long checkpointLSN = findLastCheckpoint();
        
        // 2. 从checkpoint开始重做所有已提交的事务
        redoCommittedTransactions(checkpointLSN);
        
        // 3. 扫描Undo Log，回滚所有未提交的事务
        rollbackUncommittedTransactions();
        
        // 4. 清理资源，启动正常服务
        cleanup();
    }
    
    private void redoCommittedTransactions(long fromLSN) {
        // 重做阶段：应用Redo Log中的所有变更
        // 确保已提交事务的修改不会丢失
    }
    
    private void rollbackUncommittedTransactions() {
        // 回滚阶段：使用Undo Log回滚未提交的事务
        // 确保未提交事务的修改被撤销
    }
}

// MySQL崩溃恢复流程
public class CrashRecovery {
    
    public void recover() {
        // 1. 扫描Redo Log，找到最后一个checkpoint
        long checkpointLSN = findLastCheckpoint();
        
        // 2. 从checkpoint开始重做所有已提交的事务
        redoCommittedTransactions(checkpointLSN);
        
        // 3. 扫描Undo Log，回滚所有未提交的事务
        rollbackUncommittedTransactions();
        
        // 4. 清理资源，启动正常服务
        cleanup();
    }
    
    private void redoCommittedTransactions(long fromLSN) {
        // 重做阶段：应用Redo Log中的所有变更
        // 确保已提交事务的修改不会丢失
    }
    
    private void rollbackUncommittedTransactions() {
        // 回滚阶段：使用Undo Log回滚未提交的事务
        // 确保未提交事务的修改被撤销
    }
}

基于Binlog的恢复

bash

# 全量备份恢复
mysql -u root -p database_name < full_backup.sql

# 增量恢复（使用Binlog）
mysqlbinlog --start-position=4 --stop-position=1000 mysql-bin.000001 | mysql -u root -p

# 基于时间点的恢复
mysqlbinlog --start-datetime="2023-01-01 10:00:00" \
            --stop-datetime="2023-01-01 10:30:00" \
            mysql-bin.000001 | mysql -u root -p

# 跳过错误事件
mysqlbinlog --start-position=4 --stop-position=999 mysql-bin.000001 | mysql -u root -p
mysqlbinlog --start-position=1001 mysql-bin.000001 | mysql -u root -p

# 全量备份恢复
mysql -u root -p database_name < full_backup.sql

# 增量恢复（使用Binlog）
mysqlbinlog --start-position=4 --stop-position=1000 mysql-bin.000001 | mysql -u root -p

# 基于时间点的恢复
mysqlbinlog --start-datetime="2023-01-01 10:00:00" \
            --stop-datetime="2023-01-01 10:30:00" \
            mysql-bin.000001 | mysql -u root -p

# 跳过错误事件
mysqlbinlog --start-position=4 --stop-position=999 mysql-bin.000001 | mysql -u root -p
mysqlbinlog --start-position=1001 mysql-bin.000001 | mysql -u root -p

最佳实践

1. 日志配置

Redo Log：设置合适的大小，避免频繁checkpoint
Binlog：选择合适的格式，ROW格式更安全
Undo Log：配置自动清理，避免表空间过大

2. 监控和维护

定期检查日志文件大小和磁盘空间
监控日志写入性能指标
设置合理的日志保留策略

3. 备份策略

结合全量备份和Binlog进行增量备份
定期测试恢复流程
保证Binlog的完整性和连续性

4. 性能调优

根据业务特点调整刷盘策略
使用SSD存储提高日志写入性能
合理配置日志缓冲区大小

总结

MySQL的日志系统是保证数据库可靠性的核心机制。Redo Log保证持久性，Undo Log支持回滚和MVCC，Binlog支持复制和恢复。理解这些日志的工作原理和相互关系，对于数据库的性能优化、故障恢复和运维管理都至关重要。