mysql的undo日志怎么查 mysql binlog undo

MySQL中有六种日志文件，分别是：重做日志（redo log）、回滚日志（undo log）、二进制日志（binlog）、错误日志（errorlog）、慢查询日志（slow query log）、一般查询日志（general log），中继日志（relay log）。

其中重做日志和回滚日志与事务操作息息相关，二进制日志也与事务操作有一定的关系，这三种日志，对理解MySQL中的事务操作有着重要的意义。这里简单总结一下这三者具有一定相关性的日志。

1.重做日志（redo log）

MySQL 在更新数据时，为了减少磁盘的随机 IO，因此并不会直接更新磁盘上的数据，而是先更新 Buffer Pool 中缓存页的数据，等到合适的时间点，再将这个缓存页持久化到磁盘。而 Buffer Pool 中所有缓存页都是处于内存当中的，当 MySQL 宕机或者机器断电，内存中的数据就会丢失，因此 MySQL 为了防止缓存页中的数据在更新后出现数据丢失的现象，引入了 redo log 机制。

当进行增删改操作时，MySQL 会在更新 Buffer Pool 中的缓存页数据时，会记录一条对应操作的 redo log 日志，这样如果出现 MySQL 宕机或者断电时，如果有缓存页的数据还没来得及刷入磁盘，那么当 MySQL 重新启动时，可以根据 redo log 日志文件，进行数据重做，将数据恢复到宕机或者断电前的状态，保证了更新的数据不丢失，因此 redo log 又叫做重做日志。它的本质是保证事务提交后，更新的数据不丢失。——用它来实现事务的持久性。

1.1 作用

确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

1.2 内容

物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序写入redo log file的物理文件中去的。

日志分为物理日志和逻辑日志

• 物理日志就是直接记录数据，记录被修改的page的偏移量，优点就是不依赖原page的内容，用日志的内容可以直接覆盖到磁盘上面，而缺点就是占用的空间太多，比如新增一个btree索引或者一个update操作。
• 逻辑日志只是记录关系表上面的元操作，比如update一行数据，delete一行数据等，优点就是比较简洁而且占用的空间要小，缺点就是需要依赖原page内容，而且会有部分执行和操作一致性的问题。

1.3 物理文件

默认情况下，对应的物理文件位于数据库的data目录下的 ib_logfile1&ib_logfile2

• innodb_log_group_home_dir 指定日志文件组所在的路径，默认./ ，表示在数据库的数据目录下。
• innodb_log_files_in_group 指定重做日志文件组中文件的数量，默认2
• 关于文件的大小和数量，由以下两个参数配置

• innodb_log_file_size 重做日志文件的大小。
• innodb_mirrored_log_groups 指定了日志镜像文件组的数量，默认1

1.3 产生时机

事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中。

1.4 释放时机

当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。

1.6 写盘时机

很重要一点，redo log是什么时候写盘的？

前面说了是在事物开始之后逐步写盘的。之所以说重做日志是在事务开始之后逐步写入重做日志文件，而不一定是事务提交才写入重做日志缓存，原因就是，重做日志有一个缓存区Innodb_log_buffer，默认大小为8M，Innodb存储引擎先将重做日志写入innodb_log_buffer中。

然后可以通过以下三种方式将innodb日志缓冲区的日志刷新到磁盘

1. Master Thread 每秒一次执行刷新Innodb_log_buffer到重做日志文件。
2. 每个事务提交时会将重做日志刷新到重做日志文件。
3. 当重做日志缓存可用空间 少于一半时，重做日志缓存被刷新到重做日志文件

由此可以看出，重做日志通过不止一种方式写入到磁盘，尤其是对于第一种方式，Innodb_log_buffer 到重做日志文件是 Master Thread 线程的定时任务。因此重做日志的写盘，并不一定是随着事务的提交才写入重做日志文件的，而是随着事务的开始，逐步开始的。

另外引用《MySQL技术内幕 Innodb 存储引擎》上的原话：

即使某个事务还没有提交，Innodb存储引擎仍然每秒会将重做日志缓存刷新到重做日志文件。这一点是必须要知道的，因为这可以很好地解释再大的事务的提交（commit）的时间也是很短暂的。

2.回滚日志（undo log）

数据库事务四大特性中有一个是原子性，具体来说就是原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。

实际上，原子性底层就是通过undo log实现的。undo log主要记录了数据的逻辑变化，比如一条INSERT语句，对应一条DELETE的undo log，对于每个UPDATE语句，对应一条相反的UPDATE的undo log，这样在发生错误时，就能回滚到事务之前的数据状态

2.1 作用

记录了事务发生之前的数据状态（不包括select） ，如果修改数据时出现异常，可以用undo log来实现回滚操作（保持原子性）。同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。

2.2 内容

逻辑格式的日志，可以简单认为就是执行过的事务中的sql语句。但又不完全是sql语句这么简单，而是包括了执行的sql语句（增删改）反向的信息，也就意味着

• delete对应着delete本身和其反向的insert
• update对应着update执行前后的版本的信息
• insert对应着delete和insert本身的信息

在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。

2.3 物理文件

MySQL5.6之前，undo表空间位于共享表空间的回滚段中，共享表空间的默认的名称是ibdata，位于数据文件目录中。

MySQL5.6之后，undo表空间可以配置成独立的文件，但是提前需要在配置文件中配置，完成数据库初始化后生效且不可改变undo log文件的个数。如果初始化数据库之前没有进行相关配置，那么就无法配置成独立的表空间了。
　　　　
关于MySQL5.7之后的独立undo 表空间配置参数如下

• innodb_undo_directory = /data/undospace/ --undo独立表空间的存放目录
• innodb_undo_logs = 128 --回滚段为128KB
• innodb_undo_tablespaces = 4 --指定有4个undo log文件

如果undo使用的共享表空间，这个共享表空间中又不仅仅是存储了undo的信息，共享表空间的默认为与MySQL的数据目录下面，其属性由参数 innodb_data_file_path 配置。

默认情况下undo文件是保持在共享表空间的，也即ibdatafile文件中，当数据库中发生一些大的事务性操作的时候，要生成大量的undo信息，全部保存在共享表空间中的。因此共享表空间可能会变的很大，默认情况下，也就是 undo 日志使用共享表空间的时候，被“撑大”的共享表空间是不会也不能自动收缩的。

因此，mysql5.7之后的“独立undo 表空间”的配置就显得很有必要了。

2.4 产生时机

事务开始之前，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性

2.5 释放时机

当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

3.二进制日志（binlog）

binlog 以事件的形式记录了所有的 DDL 和 DML 语句（因为它记录的是操作而不是数据值，属于逻辑日志），可以用来做主从复制和数据恢复。跟 redo log 不一样，它的文件内容是可以追加的，没有固定大小限制。

在开启了 binlog 功能的情况下，我们可以把 binlog 导出成SQL语句，把所有的操作重放一遍，来实现数据的恢复。binlog 的另一个功能就是用来实现主从复制，它的原理就是从服务器读取主服务器的 binlog，然后执行一遍。

3.1 作用

1. 用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。
2. 用于数据库的基于时间点的还原。

3.2 内容

bin log有三种格式,分别为STATMENT、ROW、和MIXED。

• STATMENT：基于SQL语句的复制(statement-based-replication,SBR),所有涉及到数据修改的sql语句都会记录到bin log中

• 优点 ：不需要记录每一行的变化，减少bin log日质量，节约IO，所以性能最好.
• 缺点:可能会在某些情况下导致主从数据不一致，例如执行sysdate()、sleep;

• ROW：基于行变化的复制(row-based replication,RBR),不需要记录每一条sql语句信息,仅需要记录哪一条数据被修改了.

• 优点：不会出现某些情况下的存储过程、函数、触发器调用无法被正确复制和回复的情况.
• 缺点：日志数量会增多,尤其是是在执行alter table的时候日志会暴涨

• MIXED：顾名思义就是以上两种的混合使用模式(mixed-based replication,MBR),一般的复制使用STATEMENT，而对于STATEMENT无法复制的则使用ROW模式。

因此可以基于binlog做到类似于oracle的闪回功能，其实都是依赖于binlog中的日志记录。

3.3 物理文件

配置文件的路径为log_bin_basename，binlog日志文件按照指定大小，当日志文件达到指定的最大的大小之后，进行滚动更新，生成新的日志文件。

对于每个binlog日志文件，通过一个统一的index文件来组织。

3.4 产生时机

事务提交的时候，一次性将事务中的sql语句（一个事物可能对应多个sql语句）按照一定的格式记录到binlog中。

这里与redo log很明显的差异就是redo log并不一定是在事务提交的时候刷新到磁盘，redo log是在事务开始之后就开始逐步写入磁盘。

因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的，但是在开启了bin_log的情况下，对于较大事务的提交，可能会变得比较慢一些。这是因为binlog是在事务提交的时候一次性写入的造成的，这些可以通过测试验证。

3.5 释放时机

binlog的默认是保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。

=> undo log 和 redo log

undo是在事务开始之前保存的被修改数据的一个版本，产生undo日志的时候，同样会伴随类似于保护事务持久化机制的redo log的产生。

• Redo 记录某 数据块 被修改 后 的值，可以用来恢复未写入 data file 的已成功事务更新的数据。-- 保证事务持久性
• Undo 记录某 数据 被修改 前 的值，可以用来在事务失败时进行 rollback；-- 保证事务原子性

比如某一时刻数据库 DOWN 机了，有两个事务，一个事务已经提交，另一个事务正在处理。数据库重启的时候就要根据日志进行前滚及回滚，把已提交事务的更改写到数据文件，未提交事务的更改恢复到事务开始前的状态。

• 当数据 crash-recovery 时，通过 redo log 将所有已经在存储引擎内部提交的事务应用 redo log 恢复
• 所有已经 prepared 但是没有 commit 的 transactions 将会应用 undo log 做 roll back

问题一：可不可以只用 undo 或只用 redo？

1. 假设只有 undo-log：那么就必须保证提交前刷脏完成，否则宕机时有些修改就在内存中丢失了，破坏了持久性。（这样带来了一个问题，那就是前面提到的性能差）
2. 假设只有 redo-log：那么就不能随心所欲地在事务提交前刷脏，即无法支持大事务。（假如、某张表有 100 亿的 8 字节整数数据，就算不考虑其他东西带来的损耗，光 update 整张表至少要消耗 80G 的内存。如前所述，有了 undo-log，就可以随便刷脏）

问题二：说了这么多，undo+redo 有什么示例吗？

示例一：假设有A、B两个数据，值分别为1,2。现在要将A修改成3，B修改成4。

A.事务开始.
B.记录A=1到undo log.
C.修改A=3.
D.记录A=3到redo log.
E.记录B=2到undo log.
F.修改B=4.
G.记录B=4到redo log.
H.将redo log写入磁盘。
I.事务提交

示例二：update过程分析。一个更新操作的流程，这是一个简化的过程（name原值是zhangsan）。

update user set name='penyuyan' where id=1;

1. 事务开始，从内存或磁盘取到这条数据，返回给Server 的执行器；
2. 执行器修改这一行数据的值为penyuyan；
3. 记录 name=zhangsan 到 undo log；
4. 记录 name=penyuyan 到 redo log；
5. 调用存储引擎接口，在内存（Buffer Pool）中修改 name=zhangsan；
6. 事务提交

=> redo/undo log 和 binlog

二进制日志的作用之一是还原数据库的，这与redo/undo log很类似，但两者区别还是挺多的，大致如下：

• 层次不同

• redo/undo 是 innodb 引擎层维护的，是保证事务的持久性的，是事务层面的。
• binlog 是 mysql server 层维护的，跟采用何种引擎没有关系，记录的是所有引擎的更新操作的日志记录。虽然都有还原的意思，但是其保护数据的层次是不一样的。

• 记录内容不同

• redo/undo 记录的是 每个页/每个数据 的修改情况，属于物理日志+逻辑日志结合的方式（redo log 是物理日志，undo log 是逻辑日志）。
• binlog 记录的都是事务操作内容，binlog 有三种模式：Statement（基于 SQL 语句的复制）、Row（基于行的复制） 以及 Mixed（混合模式）。不管采用的是什么模式，当然格式是二进制的。

• 记录时机不同

• redo/undo 在 事务执行过程中会不断的写入。
• binlog 是在事务最终提交前写入的。binlog 什么时候刷新到磁盘跟参数 sync_binlog 相关。

关于事务提交时，redo log 和 binlog的写入顺序，为了保证主从复制时候的主从一致（当然也包括使用binlog进行基于时间点还原的情况），是要严格一致的，

MySQL通过两阶段提交过程来完成事务的一致性的，也即redo log和binlog的一致性的，理论上是先写redo log，再写binlog，两个日志都提交成功（刷入磁盘），事务才算真正的完成。参考链接…