避坑必看！MySQL 三大日志（redo/undo/binlog）底层原理全拆解，事务一致性再也不懵

-- 创建测试数据库
CREATEDATABASEIFNOTEXISTS log_demo;
USE log_demo;

-- 创建测试表（InnoDB引擎，必须用InnoDB，MyISAM无redo log）
CREATETABLEIFNOTEXISTS user_info (
    idINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOTNULL,
    balance INTNOTNULLDEFAULT0
) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;

-- 插入初始数据
INSERTINTO user_info (username, balance) VALUES ('zhangsan', 1000);

-- 开启事务
START TRANSACTION;
-- 修改数据（将zhangsan的余额改为2000）
UPDATE user_info SET balance = 2000 WHERE username = 'zhangsan';
-- 此时未提交事务，修改仅写入redo log buffer和Buffer Pool，未刷盘

USE log_demo;
SELECT * FROM user_info WHERE username = 'zhangsan';

-- 开启事务
START TRANSACTION;
-- 修改数据
UPDATE user_info SET balance = 2000 WHERE username = 'zhangsan';
-- 提交事务（触发redo log刷盘）
COMMIT;

-- 故意宕机，重启MySQL后查询
SELECT * FROM user_info WHERE username = 'zhangsan';

USE log_demo;

-- 开启事务
STARTTRANSACTION;
-- 插入一条数据
INSERTINTO user_info (username, balance) VALUES ('lisi', 1500);
-- 修改zhangsan的余额
UPDATE user_info SET balance = 2500WHERE username = 'zhangsan';
-- 查看当前数据（事务内可见）
SELECT * FROM user_info;
-- 回滚事务
ROLLBACK;
-- 再次查看数据（恢复到事务开始前）
SELECT * FROM user_info;

-- 事务1：开启事务，修改数据
START TRANSACTION;
UPDATE user_info SET balance = 3000 WHERE username = 'zhangsan';
-- 不提交事务

-- 事务2：开启事务，读取数据（此时事务1未提交，读取的是历史版本）
START TRANSACTION;
SELECT * FROM user_info WHERE username = 'zhangsan';
-- 结果：balance = 2000（undo log 保存的历史版本）
COMMIT;

-- 事务1提交
COMMIT;

-- 事务3：读取数据
START TRANSACTION;
SELECT * FROM user_info WHERE username = 'zhangsan';
-- 结果：balance = 3000（最新版本）
COMMIT;

SHOW VARIABLES LIKE 'log_bin';
-- 结果：ON 表示开启，OFF 表示关闭

SHOW BINARY LOGS;
-- 结果：显示所有binlog文件，包括文件名、大小、创建时间

-- 执行修改操作
USE log_demo;
START TRANSACTION;
UPDATE user_info SET balance = 3500 WHERE username = 'zhangsan';
COMMIT;

-- 查看最新的binlog文件（替换为实际文件名）
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

# 解析指定binlog文件，输出为SQL格式（替换为实际文件名）
mysqlbinlog --base64-output=decode-rows -v mysql-bin.000001

# 事务开始
BEGIN
# 数据行变更（Row格式）：将zhangsan的balance从3000改为3500
### UPDATE log_demo.user_info
### WHERE
###   id=1
###   username='zhangsan'
###   balance=3000
### SET
###   balance=3500
# 事务提交
COMMIT

# 解析binlog文件，查找delete操作（替换为实际文件名）
mysqlbinlog --base64-output=decode-rows -v mysql-bin.000001 | grep -i delete

# 回放binlog，恢复误删数据（从起始位置前1位开始，到结束位置前1位结束，避免重复执行误删操作）
mysqlbinlog --start-position=100 --stop-position=155 mysql-bin.000001 | mysql -u root -p

USE log_demo;
SELECT * FROM user_info WHERE username = 'lisi';
-- 结果：lisi 的记录被恢复，说明binlog 恢复成功

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.jam.demo</groupId>
    <artifactId>mysql-log-demo</artifactId>
    <version>1.0.0</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.2.4</version>
        <relativePath/>
    </parent>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <mybatis-plus.version>3.5.5.1</mybatis-plus.version>
        <fastjson2.version>2.0.45</fastjson2.version>
        <swagger.version>3.0.0</swagger.version>
    </properties>

    <dependencies>
        <!-- Spring Boot Web -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- Spring Boot 事务 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <!-- MyBatis-Plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>

        <!-- MySQL 驱动 -->
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!-- Lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.30</version>
            <scope>provided</scope>
        </dependency>

        <!-- FastJSON2 -->
        <dependency>
            <groupId>com.alibaba.fastjson2</groupId>
            <artifactId>fastjson2</artifactId>
            <version>${fastjson2.version}</version>
        </dependency>

        <!-- Google Guava -->
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>33.1.0-jre</version>
        </dependency>

        <!-- Swagger3 -->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-boot-starter</artifactId>
            <version>${swagger.version}</version>
        </dependency>

        <!-- 测试 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

spring:
  datasource:
    url:jdbc:mysql://localhost:3306/log_demo?useUnicode=true&characterEncoding=utf8mb4&useSSL=false&serverTimezone=Asia/Shanghai
    username:root
    password:root（替换为自己的MySQL密码）
    driver-class-name:com.mysql.cj.jdbc.Driver
# 事务配置
transaction:
    default-timeout:30000

# MyBatis-Plus 配置
mybatis-plus:
mapper-locations:classpath:mapper/**/*.xml
type-aliases-package:com.jam.demo.entity
configuration:
    map-underscore-to-camel-case:true
    log-impl:org.apache.ibatis.logging.stdout.StdOutImpl

# MySQL 日志配置（双1配置）
server:
port:8080

# 自定义配置（binlog 相关）
mysql:
binlog:
    enabled:true
    format:ROW
    sync-binlog:1
innodb:
    flush-log-at-trx-commit:1

package com.jam.demo.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;

/**
 * 用户信息实体类
 * @author ken
 */
@Data
@TableName("user_info")
@ApiModel(value = "UserInfo对象", description = "用户信息表")
publicclass UserInfo implements Serializable {

    privatestaticfinallong serialVersionUID = 1L;

    @ApiModelProperty(value = "主键ID")
    @TableId(type = IdType.AUTO)
    private Integer id;

    @ApiModelProperty(value = "用户名")
    private String username;

    @ApiModelProperty(value = "余额")
    private Integer balance;
}

package com.jam.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.jam.demo.entity.UserInfo;
import org.apache.ibatis.annotations.Mapper;

/**
 * 用户信息Mapper接口
 * @author ken
 */
@Mapper
public interface UserInfoMapper extends BaseMapper<UserInfo> {
}

package com.jam.demo.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.jam.demo.entity.UserInfo;
import io.swagger.annotations.ApiOperation;

/**
 * 用户信息Service接口
 * @author ken
 */
public interface UserInfoService extends IService<UserInfo> {

    /**
     * 转账操作
     * @param fromUsername 转出用户名
     * @param toUsername 转入用户名
     * @param amount 转账金额
     * @return 转账是否成功
     */
    @ApiOperation(value = "转账操作", notes = "实现两个用户之间的转账，保证事务一致性")
    boolean transfer(String fromUsername, String toUsername, Integer amount);
}

package com.jam.demo.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.jam.demo.entity.UserInfo;
import com.jam.demo.mapper.UserInfoMapper;
import com.jam.demo.service.UserInfoService;
import com.google.common.collect.Maps;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;

import java.util.Map;

/**
 * 用户信息Service实现类（编程式事务）
 * @author ken
 */
@Service
@Slf4j
publicclass UserInfoServiceImpl extends ServiceImpl<UserInfoMapper, UserInfo> implements UserInfoService {

    @Autowired
    private UserInfoMapper userInfoMapper;

    @Autowired
    private PlatformTransactionManager transactionManager;

    /**
     * 转账操作，采用编程式事务控制，保证原子性
     * @param fromUsername 转出用户名
     * @param toUsername 转入用户名
     * @param amount 转账金额
     * @return 转账是否成功
     */
    @Override
    public boolean transfer(String fromUsername, String toUsername, Integer amount) {
        // 1. 参数校验
        StringUtils.hasText(fromUsername, "转出用户名不能为空");
        StringUtils.hasText(toUsername, "转入用户名不能为空");
        if (ObjectUtils.isEmpty(amount) || amount <= 0) {
            log.error("转账金额异常，金额：{}", amount);
            thrownew IllegalArgumentException("转账金额必须大于0");
        }
        if (fromUsername.equals(toUsername)) {
            log.error("转出用户名与转入用户名不能相同");
            thrownew IllegalArgumentException("转出用户名与转入用户名不能相同");
        }

        // 2. 编程式事务定义
        DefaultTransactionDefinition transactionDefinition = new DefaultTransactionDefinition();
        // 事务隔离级别：读已提交（MySQL默认）
        transactionDefinition.setIsolationLevel(DefaultTransactionDefinition.ISOLATION_READ_COMMITTED);
        // 事务传播行为： REQUIRED（默认）
        transactionDefinition.setPropagationBehavior(DefaultTransactionDefinition.PROPAGATION_REQUIRED);
        TransactionStatus transactionStatus = transactionManager.getTransaction(transactionDefinition);

        try {
            // 3. 查询转出用户和转入用户
            UserInfo fromUser = userInfoMapper.selectOne(new LambdaQueryWrapper<UserInfo>()
                    .eq(UserInfo::getUsername, fromUsername));
            UserInfo toUser = userInfoMapper.selectOne(new LambdaQueryWrapper<UserInfo>()
                    .eq(UserInfo::getUsername, toUsername));

            // 4. 校验用户是否存在、余额是否充足
            if (ObjectUtils.isEmpty(fromUser)) {
                log.error("转出用户不存在，用户名：{}", fromUsername);
                thrownew RuntimeException("转出用户不存在");
            }
            if (ObjectUtils.isEmpty(toUser)) {
                log.error("转入用户不存在，用户名：{}", toUsername);
                thrownew RuntimeException("转入用户不存在");
            }
            if (fromUser.getBalance() < amount) {
                log.error("转出用户余额不足，用户名：{}，余额：{}，转账金额：{}", fromUsername, fromUser.getBalance(), amount);
                thrownew RuntimeException("转出用户余额不足");
            }

            // 5. 执行转账操作（扣减转出用户余额，增加转入用户余额）
            fromUser.setBalance(fromUser.getBalance() - amount);
            userInfoMapper.updateById(fromUser);
            log.info("转出用户余额扣减成功，用户名：{}，扣减后余额：{}", fromUsername, fromUser.getBalance());

            // 模拟异常（测试事务回滚，可注释）
            // int i = 1 / 0;

            toUser.setBalance(toUser.getBalance() + amount);
            userInfoMapper.updateById(toUser);
            log.info("转入用户余额增加成功，用户名：{}，增加后余额：{}", toUsername, toUser.getBalance());

            // 6. 提交事务（触发binlog和redo log刷盘）
            transactionManager.commit(transactionStatus);
            log.info("转账事务提交成功，转出用户：{}，转入用户：{}，转账金额：{}", fromUsername, toUsername, amount);
            returntrue;
        } catch (Exception e) {
            // 7. 回滚事务（利用undo log 恢复数据）
            transactionManager.rollback(transactionStatus);
            log.error("转账事务回滚，异常信息：{}", e.getMessage(), e);
            returnfalse;
        }
    }
}

package com.jam.demo.controller;

import com.jam.demo.service.UserInfoService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

/**
 * 用户信息Controller
 * @author ken
 */
@RestController
@RequestMapping("/user")
@Api(tags = "用户信息接口", description = "包含转账、查询等操作，验证MySQL三大日志协同")
publicclass UserInfoController {

    @Autowired
    private UserInfoService userInfoService;

    /**
     * 转账接口
     */
    @PostMapping("/transfer")
    @ApiOperation(value = "转账操作", notes = "实现两个用户之间的转账，事务由编程式控制")
    public String transfer(
            @ApiParam(value = "转出用户名", required = true) @RequestParam String fromUsername,
            @ApiParam(value = "转入用户名", required = true) @RequestParam String toUsername,
            @ApiParam(value = "转账金额", required = true, example = "100") @RequestParam Integer amount) {
        boolean result = userInfoService.transfer(fromUsername, toUsername, amount);
        return result ? "转账成功" : "转账失败";
    }
}

package com.jam.demo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import springfox.documentation.oas.annotations.EnableOpenApi;

/**
 * 启动类
 * @author ken
 */
@SpringBootApplication
@MapperScan("com.jam.demo.mapper")
@EnableOpenApi
publicclass MysqlLogDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(MysqlLogDemoApplication.class, args);
    }
}

-- ====================== redo log 核心配置 ======================
-- 双1配置核心1：事务提交必须刷盘redo log，保证持久性
SETGLOBAL innodb_flush_log_at_trx_commit = 1;
-- redo log文件组数量，推荐2-4个，避免单文件过大
SETGLOBAL innodb_log_files_in_group = 2;
-- 单个redo log文件大小，推荐4G（最大不超过512G，最小不低于48M），避免频繁checkpoint导致性能抖动
SETGLOBAL innodb_log_file_size = 4294967296;
-- redo log缓冲区大小，高并发场景推荐64M，减少缓冲区满导致的刷盘阻塞
SETGLOBAL innodb_log_buffer_size = 67108864;

-- ====================== binlog 核心配置 ======================
-- 开启binlog，主从架构与数据恢复必备
SETGLOBAL log_bin = ON;
-- 双1配置核心2：事务提交必须刷盘binlog，保证主从一致性
SETGLOBAL sync_binlog = 1;
-- binlog格式强制使用ROW，生产环境唯一推荐格式，杜绝主从数据不一致
SETGLOBAL binlog_format = ROW;
-- 行级binlog记录模式，MINIMAL仅记录变更的字段，大幅减少binlog体积，推荐生产使用
SETGLOBAL binlog_row_image = MINIMAL;
-- binlog过期自动清理时间，推荐7-30天，根据合规要求调整，避免磁盘占满
SETGLOBAL binlog_expire_logs_seconds = 2592000;
-- 单个binlog文件最大大小，默认1G，高并发场景可调整为2G，减少文件切换开销
SETGLOBAL max_binlog_size = 1073741824;
-- 开启binlog校验，防止网络传输或磁盘损坏导致的binlog损坏
SETGLOBAL binlog_checksum = CRC32;

-- ====================== undo log 核心配置 ======================
-- 开启undo表空间自动截断，解决undo log膨胀问题，MySQL 8.0默认开启
SETGLOBAL innodb_undo_log_truncate = ON;
-- undo表空间最大阈值，超过后触发自动截断，推荐10G
SETGLOBAL innodb_max_undo_log_size = 10737418240;
-- undo表空间数量，高并发场景推荐8个，分散IO压力，减少锁竞争
SETGLOBAL innodb_undo_tablespaces = 8;
-- 回滚段数量，高并发事务场景推荐128个，提升并发处理能力
SETGLOBAL innodb_rollback_segments = 128;

-- 正确清理方式：清理指定时间之前的binlog
PURGE BINARY LOGS BEFORE '2026-04-01 00:00:00';
-- 清理指定文件之前的binlog
PURGE BINARY LOGS TO 'mysql-bin.000010';

-- 全局只读，仅超级管理员可写入
SET GLOBAL super_read_only = ON;

# 解析binlog，过滤误操作语句，记录位置点
mysqlbinlog --base64-output=decode-rows -v --start-datetime="2026-04-07 10:00:00" --stop-datetime="2026-04-07 10:30:00" mysql-bin.000012 > binlog_analysis.sql

# 测试环境回放正常操作，跳过误操作的位置区间
mysqlbinlog --start-position=4 --stop-position=1560 mysql-bin.000012 | mysql -u root -p