首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >MySQL的三大日志

MySQL的三大日志

作者头像
苏三说技术
发布2025-07-09 09:47:27
发布2025-07-09 09:47:27
13400
代码可运行
举报
文章被收录于专栏:苏三说技术苏三说技术
运行总次数:0
代码可运行

大家好,我是苏三,又跟大家见面了。

前言

飞机失事靠黑匣子还原真相,MySQL崩溃靠三大日志保障数据安全。

作为一个工作多年的程序员,我见过太多因日志配置不当引发的灾难:数据丢失、主从同步中断、事务回滚失败...

今天,我将用最通俗的方式,带你彻底掌握MySQL三大日志的底层原理,希望对你会有所帮助。

一、引子:一个数据丢失的教训

事故现场:某电商平台数据库服务器宕机后,发现最近2小时订单数据丢失。

问题根源:  错误配置导致redo log刷盘失效:

代码语言:javascript
代码运行次数:0
运行
复制

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 0     |  -- 应设为1
+--------------------------------+-------+

核心结论

  1. 日志系统是MySQL的安全气囊
  2. 不理解日志机制,等于在数据安全上裸奔

二、Redo Log:保证持久性的守护神

2.1 核心作用:崩溃恢复

WAL原则(Write-Ahead Logging)

图片
图片

2.2 物理结构解析

循环写入机制

图片
图片

关键参数

代码语言:javascript
代码运行次数:0
运行
复制
-- 查看日志配置
SHOW VARIABLES LIKE 'innodb_log%';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_log_file_size      | 50331648| -- 单个日志文件大小
| innodb_log_files_in_group | 2       | -- 日志文件数量
| innodb_log_buffer_size    | 16777216| -- 缓冲区大小
+---------------------------+---------+
2.3 刷盘策略实战
代码语言:javascript
代码运行次数:0
运行
复制
// JDBC事务提交示例
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");
    
    // 核心配置:刷盘策略
    conn.setClientInfo("innodb_flush_log_at_trx_commit", "1");
    conn.commit(); // 触发redo log刷盘
} catch (SQLException e) {
    conn.rollback();
}

刷盘策略对比

参数值

安全性

性能

适用场景

0

低(每秒刷)

最高

可丢失数据的缓存

1

最高(实时)

最低

金融交易系统

2

中(OS缓存)

较高

常规业务系统

三、Undo Log:事务回滚的时光机

3.1 MVCC实现原理

多版本控制流程

图片
图片
3.2 回滚操作源码级解析
代码语言:javascript
代码运行次数:0
运行
复制
-- 事务回滚示例
START TRANSACTION;
UPDATE users SET name='张三' WHERE id=; 
-- 在undo log中记录:
-- | 事务ID | 行ID | 旧值 | 回滚指针 |
-- | 101    | 1    | '李四'| 0x7F8A9B|

ROLLBACK; -- 根据undo log恢复数据
3.3 长事务引发的灾难

问题场景

代码语言:javascript
代码运行次数:0
运行
复制
-- 查询运行超过60秒的事务
SELECT * FROM information_schema.innodb_trx 
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > ;

严重后果

  1. Undo Log暴涨占用磁盘空间
  2. 历史版本链过长导致查询性能下降

解决方案

代码语言:javascript
代码运行次数:0
运行
复制
@Transactional(timeout = ) // 单位:秒
public void updateOrder(Order order) {
    // 业务逻辑
}

Spring Boot项目可以设置事务超时时间。

四、Binlog:主从复制的桥梁

4.1 三种格式深度对比

格式

特点

数据安全

复制效率

STATEMENT

记录SQL语句

ROW

记录行变化

MIXED

自动切换模式

ROW格式的优势

代码语言:javascript
代码运行次数:0
运行
复制
-- 原始SQL
UPDATE users SET status= WHERE age>;

-- ROW格式binlog实际记录
/* 修改前镜像 */
id:1, status:0, age:35
id:2, status:0, age:40
/* 修改后镜像 */
id:1, status:1, age:35
id:2, status:1, age:40

4.2 主从复制全流程剖析

图片
图片

4.3 数据恢复实战

场景:误删全表数据 恢复步骤

代码语言:javascript
代码运行次数:0
运行
复制
# 1. 解析binlog找到删除位置
mysqlbinlog --start-position=763 --stop-position=941 binlog.000001 > recovery.sql

# 2. 提取回滚SQL
grep -i 'DELETE FROM users' recovery.sql

# 3. 生成反向补偿语句
sed 's/DELETE FROM/INSERT INTO/g' recovery.sql > rollback.sql

# 4. 执行恢复
mysql -u root -p < rollback.sql

五、三大日志协同工作图

更新语句执行流程

图片
图片

两阶段提交关键点

  1. redo log prepare 与 binlog 写入的原子性
  2. 崩溃恢复时的决策逻辑:
    • binlog完整:提交事务
    • binlog不完整:回滚事务

六、生产环境优化指南

6.1 参数调优模板

my.cnf 关键配置:

代码语言:javascript
代码运行次数:0
运行
复制
[mysqld]
# Redo Log
innodb_log_file_size = 2G        # 建议4个日志文件
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 # 保留7天
sync_binlog = 1                   # 每次提交刷盘

6.2 监控指标清单

代码语言:javascript
代码运行次数:0
运行
复制
-- 关键监控SQL
SELECT
/* Redo Log */
  (SELECT VARIABLE_VALUE 
   FROM performance_schema.global_status 
   WHERE VARIABLE_NAME='Innodb_os_log_written') AS redo_written,
   
/* Undo Log */
  (SELECTSUM(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;

6.3 常见问题解决方案

问题1:redo log文件设置过小导致频繁checkpoint。

现象

代码语言:javascript
代码运行次数:0
运行
复制
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_log_waits | 542   | -- 值>0表示存在等待
+------------------+-------+

解决

代码语言:javascript
代码运行次数:0
运行
复制
# 动态调整(需重启生效)
SET GLOBAL innodb_log_file_size = ; 

问题2:大事务导致binlog暴涨。

预防方案

代码语言:javascript
代码运行次数:0
运行
复制
// 事务拆分示例
public void batchProcess(List<Order> orders) {
    int batchSize = ; // 每100条一个事务
    for (int i=; i<orders.size(); i+=batchSize) {
        transactionTemplate.execute(status -> {
            List<Order> subList = orders.subList(i, Math.min(i+batchSize, orders.size()));
            processBatch(subList);
            return null;
        });
    }
}

七、总结

  1. Redo Log是生命线
    • 配置原则:innodb_flush_log_at_trx_commit=1 + 足够大的日志文件
    • 监控重点:Innodb_log_waits 应趋近于0
  2. Undo Log是后悔药
    • 及时清理:开启 innodb_undo_log_truncate
    • 避免长事务:监控 information_schema.innodb_trx
  3. Binlog是复制基石
    • 格式选择:金融级系统必须用ROW格式
    • 同步策略:主从复制时 sync_binlog=1

数据库的可靠性不是偶然发生的,而是通过三大日志的精密协作实现的。

当你下次执行COMMIT时,请记住背后有三个强大的守护者在为你工作:

  1. Redo Log确保你的数据不会丢失
  2. Undo Log保证你的操作可以撤销
  3. Binlog让数据在集群间流动

敬畏日志,就是敬畏数据安全!

代码语言:javascript
代码运行次数:0
运行
复制
最后欢迎加入苏三的星球,你将获得:商城微服务实战、AI开发项目课程、苏三AI项目、秒杀系统实战、商城系统实战、秒杀系统实战、代码生成工具、系统设计、性能优化、技术选型、底层原理、Spring源码解读、工作经验分享、痛点问题、面试八股文等多个优质专栏。
本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2025-07-08,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 苏三说技术 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 大家好,我是苏三,又跟大家见面了。
  • 前言
  • 一、引子:一个数据丢失的教训
  • 二、Redo Log:保证持久性的守护神
    • 2.1 核心作用:崩溃恢复
    • 2.2 物理结构解析
      • 2.3 刷盘策略实战
    • 三、Undo Log:事务回滚的时光机
      • 3.1 MVCC实现原理
      • 3.2 回滚操作源码级解析
      • 3.3 长事务引发的灾难
  • 四、Binlog:主从复制的桥梁
    • 4.1 三种格式深度对比
    • 4.2 主从复制全流程剖析
    • 4.3 数据恢复实战
  • 五、三大日志协同工作图
  • 六、生产环境优化指南
    • 6.1 参数调优模板
    • 6.2 监控指标清单
    • 6.3 常见问题解决方案
  • 七、总结
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档