前言
飞机失事靠黑匣子还原真相，MySQL 崩溃靠三大日志保障数据安全。
作为一个工作多年的程序员，我见过太多因日志配置不当引发的灾难：数据丢失、主从同步中断、事务回滚失败...
今天，我将用最通俗的方式，带你彻底掌握 MySQL 三大日志的底层原理，希望对你会有所帮助。
一、引子：一个数据丢失的教训
事故现场：某电商平台数据库服务器宕机后，发现最近 2 小时订单数据丢失。
问题根源： 错误配置导致 redo log 刷盘失效：
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';+--------------------------------+-------+| Variable_name | Value |+--------------------------------+-------+| innodb_flush_log_at_trx_commit | 0 | -- 应设为1+--------------------------------+-------+
复制代码
核心结论：
日志系统是 MySQL 的安全气囊
不理解日志机制，等于在数据安全上裸奔
二、Redo Log：保证持久性的守护神
2.1 核心作用：崩溃恢复
WAL 原则（Write-Ahead Logging）：
2.2 物理结构解析
循环写入机制：
关键参数：
-- 查看日志配置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 刷盘策略实战
// 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();}
复制代码
刷盘策略对比：
三、Undo Log：事务回滚的时光机
3.1 MVCC 实现原理
多版本控制流程：
3.2 回滚操作源码级解析
-- 事务回滚示例START TRANSACTION;UPDATE users SET name='张三' WHERE id=1; -- 在undo log中记录：-- | 事务ID | 行ID | 旧值 | 回滚指针 |-- | 101 | 1 | '李四'| 0x7F8A9B|
ROLLBACK; -- 根据undo log恢复数据
复制代码
3.3 长事务引发的灾难
问题场景：
-- 查询运行超过60秒的事务SELECT * FROM information_schema.innodb_trx WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;
复制代码
严重后果：
Undo Log 暴涨占用磁盘空间
历史版本链过长导致查询性能下降
解决方案：
@Transactional(timeout = 30) // 单位：秒public void updateOrder(Order order) { // 业务逻辑}
复制代码
Spring Boot 项目可以设置事务超时时间。
四、Binlog：主从复制的桥梁
4.1 三种格式深度对比
ROW 格式的优势：
-- 原始SQLUPDATE users SET status=1 WHERE age>30;
-- ROW格式binlog实际记录/* 修改前镜像 */id:1, status:0, age:35id:2, status:0, age:40/* 修改后镜像 */id:1, status:1, age:35id:2, status:1, age:40
复制代码
4.2 主从复制全流程剖析
4.3 数据恢复实战
场景：误删全表数据
恢复步骤：
# 1. 解析binlog找到删除位置mysqlbinlog --start-position=763 --stop-position=941 binlog.000001 > recovery.sql
# 2. 提取回滚SQLgrep -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
复制代码
五、三大日志协同工作图
更新语句执行流程：
两阶段提交关键点：
redo log prepare 与 binlog 写入的原子性
崩溃恢复时的决策逻辑：binlog 完整：提交事务 binlog 不完整：回滚事务
六、生产环境优化指南
6.1 参数调优模板
my.cnf 关键配置：
[mysqld]# Redo Loginnodb_log_file_size = 2G # 建议4个日志文件innodb_log_files_in_group = 4innodb_flush_log_at_trx_commit = 1
# Undo Loginnodb_max_undo_log_size = 1Ginnodb_undo_log_truncate = ONinnodb_purge_threads = 4
# Binlogserver_id = 1log_bin = /data/mysql-binbinlog_format = ROWbinlog_expire_logs_seconds = 604800 # 保留7天sync_binlog = 1 # 每次提交刷盘
复制代码
6.2 监控指标清单
-- 关键监控SQLSELECT /* 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;
复制代码
6.3 常见问题解决方案
问题 1：redo log 文件设置过小导致频繁 checkpoint。
现象：
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';+------------------+-------+| Variable_name | Value |+------------------+-------+| Innodb_log_waits | 542 | -- 值>0表示存在等待+------------------+-------+
复制代码
解决：
# 动态调整（需重启生效）SET GLOBAL innodb_log_file_size = 2147483648;
复制代码
问题 2：大事务导致 binlog 暴涨。
预防方案：
// 事务拆分示例public void batchProcess(List<Order> orders) { int batchSize = 100; // 每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; }); }}
复制代码
七、总结
1、Redo Log 是生命线：
2、Undo Log 是后悔药：
3、Binlog 是复制基石：
格式选择：金融级系统必须用 ROW 格式
同步策略：主从复制时 sync_binlog=1
数据库的可靠性不是偶然发生的，而是通过三大日志的精密协作实现的。
当你下次执行COMMIT时，请记住背后有三个强大的守护者在为你工作：
Redo Log 确保你的数据不会丢失
Undo Log 保证你的操作可以撤销
Binlog 让数据在集群间流动
文章转载自：苏三说技术
原文链接：https://www.cnblogs.com/12lisu/p/18975908
体验地址：http://www.jnpfsoft.com/?from=001YH
评论