深入MySQL：探索redo、undo和binlog日志类型

发表时间: 2022-03-27 15:17

前言

可以说mysql的多数特性都是围绕日志文件实现，而其中最重要的有以下三种

redo 日志
undo 日志
binlog 日志

1 redo日志

innodb 为了提高磁盘I/O读写性能，存在一个 buffer pool 的内存空间，数据页读入会缓存到 buffer pool，事务的提交则实时更新到 buffer pool，而不实时同步到磁盘（innodb 是按 16KB 一页同步的，一事务可涉及多个数据页，实时同步会造成浪费，随机I/O）。事务暂存在内存，则存在一致性问题，为了解决系统崩溃，保证事务的持久性，我们只需把事务对应的 redo 日志持久化到磁盘即可（redo 日志占用空间小，顺序写入磁盘，顺序I/O）

Mini-Transaction (MTR)

sql 语句在执行的时候，可能会修改多个页面，还会更新聚簇索引和二级索引的页面，过程产生的redo会被分割成多个不可分割的组（Mini-Transaction）。MTR怎么理解呢？如一条 insert 语句可能会使得页分裂，新建叶子节点，原先页的数据需要复制到新数据页里，然后将新记录插入，再添加一个目录项指向新建的页子。这对应多条 redo 日志，它们需要在原子性的 MTR 内完成

redo 日志刷盘时机

MTR 产生的 redo 日志先会被复制到一个 log buffer 里(类似 buffer pool)。而同步到磁盘的时机如下：

当 log buffer 的总容量达到 50% ，则刷新日志到磁盘
事务提交时，也需要将同步到磁盘
后台线程，每一秒同步一次
关闭 mysql 服务
做 checkpoit 的时候 redo 的空间是有限的。若 redo 日志对应的数据页如果被同步到磁盘，则 redo 日志也可被回收利用了。这回收的过程称之为 checkpoint

2 undo 日志

事务需要保证原子性，也是说事务中的操作要么全部完成，要么什么也不做。如果事务执行到一半，出错了怎么办-回滚。但是怎么回滚呢，靠 undo 日志。undo 日志就是我们执行sql的逆操作

undo 日志有两个作用：提供回滚和多个行版本控制(MVCC)
数据页里一行数据的格式如下，其中 roll_point 会指向一个undo 日志

undo 日志一般会在事务提交时被删除，但是如果 undo 日志为 MVCC 服务则暂时保留
一个事务会产生多个 undo 日志，mysql有专门的 undo 页保存 undo 日志。innodb 会为每一个事务单独分配 undo 页链表（最多分配 4 个链表）

事务ID 和 trx_id

mysql 会在内存中维护一个全局变量，每当为某个事务分配 trx_id，则先分配再自增 1
对于只读事务，只有在它第一次创建的临时表执行增删改操作时，才会为事务分配 trx_id
对于读写事务，只有它在执行增删改操作时（包扣临时表），才会为事务分配 trx_id

roll_pointer

update、delete 语句对应的 undo 日志都会带 trx_id、roll_point 两个属性字段。多条 sql 并发执行时 undo 日志会根据 trx_id 顺序用 roll_point 连成 undo 日志版本链。MVCC 的原理则是靠 undo 日志版本链实现的

3 binlog日志

binlog 文件会随服务的启动创建一个新文件
flush logs 可以手动刷新日志，生成一个新的 binlog 文件
show master status 可以查看 binlog 的状态
reset master 可以清空 binlog 日志文件
mysqlbinlog 工具可以查看 binlog 日志的内容
执行dml，mysql会自动记录 binlog

binlog 格式

binlog有三种格式：Statement、Row以及Mixed。

Statement 每一条增删改数据的 sql 都会记录在 binlog 中优点：不需要记录每一行的变化，减少了binlog 日志量，节约了IO，提高性能缺点：由于记录的只是执行语句，为了这些语句能在 slave 上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息。另外 mysql 的复制，像一些特定函数功能，slave 可与 master 要保持一致会有很多相关问题
Row 5.1.5 版本的MySQL才开始支持 row level 的复制，它不记录 sql 语句上下文相关信息，仅保存哪条记录被修改优点：binlog 中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容
Mixed 在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement 格式无法完成主从复制的操作，则采用 row 格式保存binlog

binlog 相关操作

查看binlog日志文件内容

[root@root log]# mysqlbinlog 'log.000001'/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#181214 14:44:48 server id 1  end_log_pos 120 CRC32 0x79b6cd10 	Start: binlog v 4, server v 5.6.40-log created 181214 14:44:48 at startupROLLBACK/*!*/;BINLOG 'YDIUXA8BAAAAdAAAAHgAAAAAAAQANS42LjQwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABgMhRcEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAARDNtnk='/*!*/;# at 120#181214 14:45:20 server id 1  end_log_pos 199 CRC32 0x10dec193 	Query	thread_id=1	exec_time=0	error_code=0SET TIMESTAMP=1544827520/*!*/;SET @@session.pseudo_thread_id=1/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=1075838976/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;BEGIN/*!*/;# at 199#181214 14:45:20 server id 1  end_log_pos 303 CRC32 0x9ec5f952 	Query	thread_id=1	exec_time=0	error_code=0use `test`/*!*/;SET TIMESTAMP=1544827520/*!*/;insert into t1 values('8','7')/*!*/;# at 303#181214 14:45:20 server id 1  end_log_pos 334 CRC32 0xfd659542 	Xid = 10COMMIT/*!*/;# at 334#181214 14:45:35 server id 1  end_log_pos 413 CRC32 0x43929486 	Query	thread_id=1	exec_time=0	error_code=0SET TIMESTAMP=1544827535/*!*/;BEGIN/*!*/;# at 413#181214 14:45:35 server id 1  end_log_pos 517 CRC32 0x4f1284f2 	Query	thread_id=1	exec_time=0	error_code=0SET TIMESTAMP=1544827535/*!*/;insert into t1 values('9','7')/*!*/;# at 517#181214 14:45:35 server id 1  end_log_pos 548 CRC32 0x67231f2b 	Xid = 20COMMIT/*!*/;# at 548#181214 14:45:39 server id 1  end_log_pos 627 CRC32 0x82b39b3e 	Query	thread_id=1	exec_time=0	error_code=0SET TIMESTAMP=1544827539/*!*/;BEGIN/*!*/;# at 627#181214 15:00:48 server id 1  end_log_pos 1646 CRC32 0x7e89c8dc 	StopDELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

查看binlog具体记录

mysql> show binlog events in 'log.000001';+------------+------+-------------+-----------+-------------+---------------------------------------------+| Log_name   | Pos  | Event_type  | Server_id | End_log_pos | Info                                        |+------------+------+-------------+-----------+-------------+---------------------------------------------+| log.000001 |    4 | Format_desc |         1 |         120 | Server ver: 5.6.40-log, Binlog ver: 4       || log.000001 |  120 | Query       |         1 |         199 | BEGIN                                       || log.000001 |  199 | Query       |         1 |         303 | use `test`; insert into t1 values('8','7')  || log.000001 |  303 | Xid         |         1 |         334 | COMMIT /* xid=10 */                         || log.000001 |  334 | Query       |         1 |         413 | BEGIN                                       || log.000001 |  413 | Query       |         1 |         517 | use `test`; insert into t1 values('9','7')  || log.000001 |  517 | Xid         |         1 |         548 | COMMIT /* xid=20 */                         || log.000001 |  548 | Query       |         1 |         627 | BEGIN                                       || log.000001 |  627 | Query       |         1 |         732 | use `test`; insert into t1 values('10','7') || log.000001 |  732 | Xid         |         1 |         763 | COMMIT /* xid=30 */                         || log.000001 |  763 | Query       |         1 |         842 | BEGIN                                       || log.000001 |  842 | Query       |         1 |         947 | use `test`; insert into t1 values('11','7') || log.000001 |  947 | Xid         |         1 |         978 | COMMIT /* xid=40 */               +------------+------+-------------+-----------+-------------+---------------------------------------------+23 rows in set (0.00 sec)

redo log 和 binlog 区别

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。redo log 是物理日志，记录的是“在某个数据页上做了什么修改”
binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”
redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志

redo log 记录事务是两阶段提交的

如果 redo 不是两阶段提交；redo 先写，binlog 后写，会导致依赖 binlog 同步的从库数据缺失。binlog 先写，redo log 后写，则会导致从库多出未提交的脏修改。主从库数据会不一致

redo log 、bin log 和崩溃恢复

redolog 中的事务如果经历了二阶段提交中的prepare阶段，则会打上 prepare 标识，如果经历commit阶段，则会打上commit标识（此时redolog和binlog均已落盘）。崩溃恢复逻辑如下：

按顺序扫描 redo log，如果 redo log 中的事务既有 prepare 标识，又有 commit 标识，就直接提交（复制redo log disk中的数据页到磁盘数据页）
如果 redo log 事务只有 prepare 标识，没有 commit 标识，则说明当前事务在 commit 阶段crash了，binlog 中当前事务是否完整未可知，此时拿着 redolog 中当前事务ID（redolog 和 binlog 中事务落盘的标识），去查看 binlog 中是否存在此ID 如果binlog中有当前事务ID，则提交事务（复制 redolog disk 中的数据页到磁盘数据页）如果binlog中没有当前事务ID，则回滚事务（使用undolog来删除 redolog 中的对应事务）

欢迎指正文中错误

作者：潜行前行
链接：
https://juejin.cn/post/7079619665583931400