登录/ 注册
 找回密码
 立即注册
关闭

CSDN热搜

程序园 精品问答 技术交流 资源下载
返回列表 发新帖
首页 › 业界区 › 业界 ›MySQL的三大日志

MySQL的三大日志

凌彦慧 8 小时前
前言

飞机失事靠黑匣子还原真相，MySQL崩溃靠三大日志保障数据安全。
作为一个工作多年的程序员，我见过太多因日志配置不当引发的灾难：数据丢失、主从同步中断、事务回滚失败...
今天，我将用最通俗的方式，带你彻底掌握MySQL三大日志的底层原理，希望对你会有所帮助。
一、引子：一个数据丢失的教训

事故现场：某电商平台数据库服务器宕机后，发现最近2小时订单数据丢失。
问题根源：  错误配置导致redo log刷盘失效：
  1. SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
  2. +--------------------------------+-------+
  3. | Variable_name                  | Value |
  4. +--------------------------------+-------+
  5. | innodb_flush_log_at_trx_commit | 0     |  -- 应设为1
  6. +--------------------------------+-------+
复制代码
核心结论：

  • 日志系统是MySQL的安全气囊
  • 不理解日志机制，等于在数据安全上裸奔
二、Redo Log：保证持久性的守护神

2.1 核心作用：崩溃恢复

WAL原则（Write-Ahead Logging）：
1.png

2.2 物理结构解析

循环写入机制：
2.png

关键参数：
  1. -- 查看日志配置
  2. SHOW VARIABLES LIKE 'innodb_log%';
  3. +---------------------------+---------+
  4. | Variable_name             | Value   |
  5. +---------------------------+---------+
  6. | innodb_log_file_size      | 50331648| -- 单个日志文件大小
  7. | innodb_log_files_in_group | 2       | -- 日志文件数量
  8. | innodb_log_buffer_size    | 16777216| -- 缓冲区大小
  9. +---------------------------+---------+
复制代码
2.3 刷盘策略实战
  1. // JDBC事务提交示例
  2. Connection conn = DriverManager.getConnection(url, user, pwd);
  3. try {
  4.     conn.setAutoCommit(false);
  5.     Statement stmt = conn.createStatement();
  6.     stmt.executeUpdate("UPDATE account SET balance=balance-100 WHERE id=1");
  7.     stmt.executeUpdate("UPDATE account SET balance=balance+100 WHERE id=2");
  8.    
  9.     // 核心配置：刷盘策略
  10.     conn.setClientInfo("innodb_flush_log_at_trx_commit", "1");
  11.     conn.commit(); // 触发redo log刷盘
  12. } catch (SQLException e) {
  13.     conn.rollback();
  14. }
复制代码
刷盘策略对比：
参数值安全性性能适用场景0低（每秒刷）最高可丢失数据的缓存1最高（实时）最低金融交易系统2中（OS缓存）较高常规业务系统三、Undo Log：事务回滚的时光机

3.1 MVCC实现原理

多版本控制流程：
3.png

3.2 回滚操作源码级解析
  1. -- 事务回滚示例
  2. START TRANSACTION;
  3. UPDATE users SET name='张三' WHERE id=1;
  4. -- 在undo log中记录：
  5. -- | 事务ID | 行ID | 旧值 | 回滚指针 |
  6. -- | 101    | 1    | '李四'| 0x7F8A9B|
  8. ROLLBACK; -- 根据undo log恢复数据
复制代码
3.3 长事务引发的灾难

问题场景：
  1. -- 查询运行超过60秒的事务
  2. SELECT * FROM information_schema.innodb_trx
  3. WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;
复制代码
严重后果：

  • Undo Log暴涨占用磁盘空间
  • 历史版本链过长导致查询性能下降
解决方案：
  1. @Transactional(timeout = 30) // 单位：秒
  2. public void updateOrder(Order order) {
  3.     // 业务逻辑
  4. }
复制代码
Spring Boot项目可以设置事务超时时间。
四、Binlog：主从复制的桥梁

4.1 三种格式深度对比

格式特点数据安全复制效率STATEMENT记录SQL语句低高ROW记录行变化高低MIXED自动切换模式中中ROW格式的优势：
  1. -- 原始SQL
  2. UPDATE users SET status=1 WHERE age>30;
  4. -- ROW格式binlog实际记录
  5. /* 修改前镜像 */
  6. id:1, status:0, age:35
  7. id:2, status:0, age:40
  8. /* 修改后镜像 */
  9. id:1, status:1, age:35
  10. id:2, status:1, age:40
复制代码
4.2 主从复制全流程剖析

4.png

4.3 数据恢复实战

场景：误删全表数据
恢复步骤：
  1. # 1. 解析binlog找到删除位置
  2. mysqlbinlog --start-position=763 --stop-position=941 binlog.000001 > recovery.sql
  4. # 2. 提取回滚SQL
  5. grep -i 'DELETE FROM users' recovery.sql
  7. # 3. 生成反向补偿语句
  8. sed 's/DELETE FROM/INSERT INTO/g' recovery.sql > rollback.sql
  10. # 4. 执行恢复
  11. mysql -u root -p < rollback.sql
复制代码
五、三大日志协同工作图

更新语句执行流程：
5.png

两阶段提交关键点：

  • redo log prepare 与 binlog 写入的原子性
  • 崩溃恢复时的决策逻辑：

    • binlog完整：提交事务
    • binlog不完整：回滚事务

六、生产环境优化指南

6.1 参数调优模板

my.cnf 关键配置：
  1. [mysqld]
  2. # Redo Log
  3. innodb_log_file_size = 2G        # 建议4个日志文件
  4. innodb_log_files_in_group = 4
  5. innodb_flush_log_at_trx_commit = 1
  7. # Undo Log
  8. innodb_max_undo_log_size = 1G
  9. innodb_undo_log_truncate = ON
  10. innodb_purge_threads = 4
  12. # Binlog
  13. server_id = 1
  14. log_bin = /data/mysql-bin
  15. binlog_format = ROW
  16. binlog_expire_logs_seconds = 604800 # 保留7天
  17. sync_binlog = 1                   # 每次提交刷盘
复制代码
6.2 监控指标清单
  1. -- 关键监控SQL
  2. SELECT
  3.   /* Redo Log */
  4.   (SELECT VARIABLE_VALUE
  5.    FROM performance_schema.global_status
  6.    WHERE VARIABLE_NAME='Innodb_os_log_written') AS redo_written,
  7.    
  8.   /* Undo Log */
  9.   (SELECT SUM(DATA_LENGTH)
  10.    FROM information_schema.TABLES
  11.    WHERE TABLE_SCHEMA='mysql'
  12.    AND TABLE_NAME LIKE 'undo%') AS undo_size,
  13.    
  14.   /* Binlog */
  15.   (SELECT VARIABLE_VALUE
  16.    FROM performance_schema.global_status
  17.    WHERE VARIABLE_NAME='Binlog_cache_disk_use') AS binlog_disk_use;
复制代码
6.3 常见问题解决方案

问题1：redo log文件设置过小导致频繁checkpoint。
现象：
  1. SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';
  2. +------------------+-------+
  3. | Variable_name    | Value |
  4. +------------------+-------+
  5. | Innodb_log_waits | 542   | -- 值>0表示存在等待
  6. +------------------+-------+
复制代码
解决：
  1. # 动态调整（需重启生效）
  2. SET GLOBAL innodb_log_file_size = 2147483648;
复制代码
问题2：大事务导致binlog暴涨。
预防方案：
  1. // 事务拆分示例
  2. public void batchProcess(List<Order> orders) {
  3.     int batchSize = 100; // 每100条一个事务
  4.     for (int i=0; i<orders.size(); i+=batchSize) {
  5.         transactionTemplate.execute(status -> {
  6.             List<Order> subList = orders.subList(i, Math.min(i+batchSize, orders.size()));
  7.             processBatch(subList);
  8.             return null;
  9.         });
  10.     }
  11. }
复制代码
七、总结


  • Redo Log是生命线：

    • 配置原则：innodb_flush_log_at_trx_commit=1 + 足够大的日志文件
    • 监控重点：Innodb_log_waits 应趋近于0

  • Undo Log是后悔药：

    • 及时清理：开启 innodb_undo_log_truncate
    • 避免长事务：监控 information_schema.innodb_trx

  • Binlog是复制基石：

    • 格式选择：金融级系统必须用ROW格式
    • 同步策略：主从复制时 sync_binlog=1

数据库的可靠性不是偶然发生的，而是通过三大日志的精密协作实现的。
当你下次执行COMMIT时，请记住背后有三个强大的守护者在为你工作：

  • Redo Log确保你的数据不会丢失
  • Undo Log保证你的操作可以撤销
  • Binlog让数据在集群间流动
敬畏日志，就是敬畏数据安全！
最后说一句(求关注，别白嫖我)

如果这篇文章对您有所帮助，或者有所启发的话，帮忙关注一下我的同名公众号：苏三说技术，您的支持是我坚持写作最大的动力。
求一键三连：点赞、转发、在看。
关注公众号：【苏三说技术】，在公众号中回复：进大厂，可以免费获取我最近整理的10万字的面试宝典，好多小伙伴靠这个宝典拿到了多家大厂的offer。
本文收录于我的技术网站：http://www.susan.net.cn

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

使用道具 举报
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册
发帖
凌彦慧
8 小时前

0

粉丝关注

9

主题发布

板块介绍填写区域，请于后台编辑
微信扫一扫