mysql undo日志怎么查看 mysql binlog undo

mysql的日志分为几大类：错误日志、查询日志、慢查询日志、事务日志（redo log和undo log）、二进制日志（binlog）。

binlog

关于数据库日志，举个简单的例子，我们在硬盘加载到内存之后，对数据进行一系列操作，在还未刷新到硬盘之前，那就得在XXX位置先记录下，然后再进行正常的增删改查操作，最后刷入硬盘。如果未刷入硬盘，在重启之后，先加载之前的记录，那么数据就回来了。

用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog是mysql的逻辑日志(可理解为记录的就是sql语句)，并且由Server层进行记录，使用任何存储引擎的mysql数据库都会记录binlog日志。

用途：

• 主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves并回放来达到master-slave数据一致的目的
• 数据恢复：通过mysqlbinlog工具恢复数据
• 增量备份

查看：

    1.  mysqlbinlog  mysql-bin.000007

    2. 命令行解析 SHOW BINLOG EVENTS  [IN 'log_name']  [FROM pos]    [LIMIT [offset,] row_count]

mysql> show binlog events in 'mysql-bin.000007' from 1190 limit 2\G

格式：STATMENT、ROW和MIXED

• 基于SQL语句的复制(statement-based replication, SBR)，每一条会修改数据的sql语句会记录到binlog中。
• 基于行的复制(row-based replication, RBR)，不记录每条sql语句的上下文信息，记录哪条数据被修改了。
• 基于上述两种模式的混合复制(mixed-based replication, MBR)，一般的复制使用前一模式保存binlog，无法复制的操作使用ROW模式保存binlog。选取规则：如果是采用 INSERT，UPDATE，DELETE 直接操作表的情况，则日志格式根据 binlog_format 的设定而记录；如果是采用 GRANT，REVOKE，SET PASSWORD 等管理语句来做的话，那么无论如何都采用statement模式记录

binlog_format=statment格式的日志内容：show binlog events in 'master.000001';

binlog_format=row格式的日志内容：

这张图还是通过show命令查看，但是还不能真正看到日志的详细内容，需要使用命令：

mysqlbinlog  -vv data/master.000001 --start-position=8900

为什么会有 mixed 这种 binlog 格式的存在场景？

• 有些 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式。
• row 格式的缺点是，很占空间。比如你用一个 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度。
• MySQL 就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。

为何现在越来越多的场景要求把 MySQL 的 binlog 格式设置成 row？

• delete 语句，row 格式的 binlog 会把被删掉的行的整行信息保存起来。如果发现删错数据了，可以直接把 binlog 中记录的 delete 语句转成 insert，把被错删的数据插入回去就可以恢复了。
• 如果你是执行错了 insert 语句，insert 语句的 binlog 里会记录所有的字段信息，这些信息可以用来精确定位刚刚被插入的那一行。这时，你直接把 insert 语句转成 delete 语句，删除掉这被误插入的一行数据就可以了。
• 如果执行的是 update 语句的话，binlog 里面会记录修改前整行的数据和修改后的整行数据。所以，如果你误执行了 update 语句的话，只需要把这个 event 前后的两行信息对调一下，再去数据库里面执行。

用 binlog 来恢复数据的标准做法是，用 mysqlbinlog 工具解析出来，然后把解析结果整个发给 MySQL 执行。类似下面的命令：

将 master.000001 文件里面从第 2738 字节到第 2973 字节中间这段内容解析出来，放到 MySQL 去执行。
mysqlbinlog master.000001  --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

binlog写入机制：

• binlog 的写入逻辑比较简单：事务执行过程中，先把日志写到 binlog cache（write），事务提交的时候，再把 binlog cache 写到 binlog 文件（fsync）中。
• 一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。
• 系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

注：把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。一般情况下，我们认为 fsync 才占磁盘的 IOPS。

刷盘时机由参数 sync_binlog 控制：

• sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
• sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
• sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。MySQL 5.7.7之后版本的默认值为 1。

redo log

产生：

事务的四大特性里面有一个是持久性，具体来说就是只要事务提交成功，那么对数据库做的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态。这一点，mysql是如何保证一致性的呢？

最简单的做法是在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题，主要体现在两个方面：

• Innodb是以页为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！
• 一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！

如果MySQL宕机，而Buffer Pool的数据没有完全刷新到磁盘，就会导致数据丢失，无法保证持久性。所以，mysql设计了redo log，具体来说就是只记录事务对数据页做了哪些修改，相对而言文件更小并且是顺序IO。

基本概念：

redo log包括两部分：一个是内存中的日志缓冲(redo log buffer)，另一个是磁盘上的日志文件(redo log file)。mysql每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file。这种先写日志，再写磁盘的技术就是MySQL里经常说到的WAL(Write-Ahead Logging) 技术。

redo log写入流程： A. redo log buffer -->  B. os buffer  -->  C. redo log file

刷盘时机：

有三种将redo log buffer写入redo log file的时机，可以通过innodb_flush_log_at_trx_commit参数配置。

0：延迟写，大约每秒刷新写入到磁盘数据 。如果出现系统崩溃，可能会出现丢失1秒数据，在流程中的A-B之间。

1：实时写，实时刷，每次提交就写入磁盘，IO 性能差。

2：实时写，延迟刷，即每秒刷，在流程中的B-C之间。

一个没有提交的事务的 redo log 也会被写到磁盘中，三种时机：

• InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。其中，事务执行中间过程的 redo log 也是直接写在 redo log buffer 中。
• redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache。
• 并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。

通常我们说 MySQL 的“双 1”配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog。

记录形式：

redo log实际上记录数据页的变更，而这种变更记录是没必要全部保存，因此redo log实现上采用了大小固定，循环写入的方式，当写到结尾时，会回到开头循环写日志。

LSN （逻辑序列号) 是单调递增的，用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log， LSN 的值就会加上 length。LSN 也会写到 InnoDB 的数据页中，在每个页的头部，值FIL_PAGE_LSN记录该页的LSN，表示页最后刷新时LSN的大小。来确保数据页不会被多次执行重复的 redo log。

write pos 是redo log当前记录的LSN位置，checkpoint 是表示数据页更改记录刷盘后对应redo log所处的LSN位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。write pos到check point之间的部分是redo log空着的部分，用于记录新的记录；check point到write pos之间是redo log待落盘的数据页更改记录，当write pos追上check point时，会先推动check point向前移动，空出位置再记录新的日志。

crash-safe：

启动innodb的时候，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。因为redo log记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如binlog)要快很多。重启innodb时，首先会检查磁盘中数据页的LSN，如果数据页的LSN小于日志中的LSN，则会从checkpoint开始恢复。

还有一种情况，在宕机前正处于checkpoint的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度，此时会出现数据页中记录的LSN大于日志中的LSN，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。

两阶段提交        

 看看下图中update执行流程：

               

其中redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"，它是为了让两份日志之间的逻辑一致。  

组提交（group commit）：

假设有个场景，多个并发事务在prepare阶段，先写的事务，会被选为这组的 leader，开始写盘的时候，这个组里面已经有了三个事务， LSN 也变成了组里最后一个事务的LSN，三个事务同时写入磁盘。

在并发更新场景下，第一个事务写完 redo log buffer 以后，接下来这个 fsync 越晚调用，组员可能越多，节约 IOPS 的效果就越好。这里，MySQL 有个优化机制：

之前说过，binlog分为两步，write 和 fsync，MySQL 为了让组提交的效果更好，把 redo log 做 fsync 的时间拖到了binlog 的 write  之后。

这样两阶段提交就变成：

                                                             

这么一来，binlog 也可以组提交了。在执行图中第 4 步把 binlog fsync 到磁盘时，如果有多个事务的 binlog 已经写完了，也是一起持久化的，这样也可以减少 IOPS 的消耗。不过通常情况下第 3 步执行得会很快，所以 binlog 的 write 和 fsync 间的间隔时间短，导致能集合到一起持久化的 binlog 比较少，因此 binlog 的组提交的效果通常不如 redo log 的效果那么好。

如果你想提升 binlog 组提交的效果，可以通过设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 来实现。

• binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;
• binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。

这两个条件是或的关系，也就是说只要有一个满足条件就会调用 fsync。所以，当 binlog_group_commit_sync_delay 设置为 0 的时候，binlog_group_commit_sync_no_delay_count 也无效了。

到这里有人会问了，WAL 机制是减少磁盘写，可是每次提交事务都要写 redo log 和 binlog，磁盘读写次数也没变少呀？现在你就能理解，WAL 机制主要得益于两个方面：

• redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快；
• 组提交机制，可以大幅度降低磁盘的 IOPS 消耗。

那么，保持两份日志之间逻辑一致，有什么用呢？简单说，当你误操作数据库 或者 给数据库扩容增加读能力的时候，这种一致性能保证数据库数据恢复到误操作之前，或者能达到线上主从一致的目的。下面看看binlog恢复数据的流程：

binlog 会记录所有的逻辑操作，并且是采用“追加写”的形式。如果你的 DBA 承诺说半个月内的数据可以恢复，那么备份系统中一定会保存最近半个月的所有 binlog，同时系统会定期做整库备份。这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一周一备。

当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：

首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；

然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。

两阶段提交怎么保证一致的？或者说如果没有两阶段提交，数据能保证一致吗？

再用上面流程图的例子，假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？

1. 要么是先写 redo log 后写 binlog。redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。

2. 要么先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

现在，可以看看在两阶段提交的不同时刻，MySQL 异常重启会出现什么现象？

如果是在写入 redo log 处于 prepare 阶段之后、写 binlog 之前，发生了崩溃（crash），由于此时 binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog 还没写，所以也不会传到备库

如果binlog 写完，redo log 还没 commit 前发生 crash，那崩溃恢复的时候 MySQL 会怎么处理？如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整，是则提交事务。

追问几个问题：

1. 不引入两个日志，也就没有两阶段提交的必要了。只用 binlog 来支持崩溃恢复，又能支持归档，不就可以了？

历史原因，InnoDB 并不是 MySQL 的原生存储引擎。MySQL 的原生引擎是 MyISAM，设计之初就没有支持崩溃恢复。InnoDB 在作为 MySQL 的插件加入 MySQL 引擎家族之前，就已经是一个提供了崩溃恢复和事务支持的引擎了。

实现上的原因，那就是binlog没有crash-safe能力。

2. 反过来，只用redo log行不行？

一是 redo log没有归档能力，他都是循环写。一个是mysql系统依赖于binlog，MySQL 系统高可用的基础，就是 binlog 复制。

3. 正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？

实际上，redo log 并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在“数据最终落盘，是由 redo log 更新过去”的情况。

4. 为什么 binlog cache 是每个线程自己维护的，而 redo log buffer 是全局共用的？

MySQL 这么设计的主要原因是，binlog 是不能“被打断的”。一个事务的 binlog 必须连续写，因此要整个事务完成后，再一起写到文件里。

而 redo log 并没有这个要求，中间有生成的日志可以写到 redo log buffer 中。redo log buffer 中的内容还能“搭便车”，其他事务提交的时候可以被一起写到磁盘中。

5. 事务执行期间，还没到提交阶段，如果发生 crash 的话，redo log 肯定丢了，这会不会导致主备不一致呢？

不会。因为这时候 binlog 也还在 binlog cache 里，没发给备库。crash 以后 redo log 和 binlog 都没有了，从业务角度看这个事务也没有提交，所以数据是一致的。

6. 如果 binlog 写完盘以后发生 crash，这时候还没给客户端答复就重启了。等客户端再重连进来，发现事务已经提交成功了，这是不是 bug？

不是。你可以设想一下更极端的情况，整个事务都提交成功了，redo log commit 完成了，备库也收到 binlog 并执行了。但是主库和客户端网络断开了，导致事务成功的包返回不回去，这时候客户端也会收到“网络断开”的异常。这种也只能算是事务成功的，不能认为是 bug。

实际上数据库的 crash-safe 保证的是：

如果客户端收到事务成功的消息，事务就一定持久化了；

如果客户端收到事务失败（比如主键冲突、回滚等）的消息，事务就一定失败了；

如果客户端收到“执行异常”的消息，应用需要重连后通过查询当前状态来继续后续的逻辑。此时数据库只需要保证内部（数据和日志之间，主库和备库之间）一致就可以了。

undo log

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

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

• insert Undo Log是INSERT操作产生的undo log，由于是该数据的第一个记录，对其他事务不可见，该Undo Log可以在事务提交后直接删除。
• update Undo Log记录对DELETE和UPDATE操作产生的Undo Log，由于提供MVCC机制，因此不能在事务提交时就删除，而是放入undo log链表，等待purge线程进行最后的删除。

 存储位置：

innodb存储引擎对undo的管理采用段的方式。rollback segment称为回滚段，每个回滚段中有1024个undo log segment。在以前老版本，只支持1个rollback segment，这样就只能记录1024个undo log segment。后来MySQL5.5可以支持128个rollback segment，即支持128*1024个undo操作，还可以通过变量 innodb_undo_logs (5.6版本以前该变量是 innodb_rollback_segments )自定义多少个rollback segment，默认值为128。

Rollback Segment默认存储在共享表空间，即 ibdata文件中，也可设置独立UNDO表空间。当DB写压力较大时，可以设置独立UNDO表空间，需要在初始化数据库实例的时候，指定独立表空间的数量。然后再把UNDO LOG从ibdata文件中分离开来，指定 innodb_undo_directory目录存放，可以制定到高速磁盘上，加快UNDO LOG 的读写性能。

undo及redo如何记录事务

假设有A、B两个数据，值分别为1,2，开始一个事务，事务的操作内容为：把1修改为3，2修改为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.事务提交

 Undo + Redo的设计主要考虑的是提升IO性能，增大数据库吞吐量。可以看出，B D E G H，均是新增操作，但是B D E G 是缓冲到buffer区，只有G是增加了IO操作，为了保证Redo Log能够有比较好的IO性能，InnoDB 的 Redo Log的设计有以下几个特点：

1. 尽量保持Redo Log存储在一段连续的空间上，在系统第一次启动时就会将日志文件的空间完全分配， 以顺序追加的方式记录Redo Log,通过顺序IO来改善性能。
2. 批量写入日志。日志并不是直接写入文件，而是先写入redo log buffer.当需要将日志刷新到磁盘时 (如事务提交),将许多日志一起写入磁盘。
3. 并发的事务共享Redo Log的存储空间，它们的Redo Log按语句的执行顺序，依次交替的记录在一起，以减少日志占用的空间。这样会造成将其他未提交的事务的日志写入磁盘。
4. Redo Log上只进行顺序追加的操作，当一个事务需要回滚时，它的Redo Log记录也不会从Redo Log中删除掉。

怎么进行的恢复？

上面说到未提交的事务和回滚了的事务也会记录Redo Log，因此在进行恢复时,这些事务要进行特殊的的处理。

由于redo log自身的特点，无法做到只重做已经提交了的事务。但是可以做到，重做所有事务包括未提交的事务和回滚了的事务。然后通过Undo Log回滚那些未提交的事务。

InnoDB存储引擎中的恢复机制有几个特点：

在重做Redo Log时，并不关心事务性。 恢复时，没有BEGIN，也没有COMMIT,ROLLBACK的行为。也不关心每个日志是哪个事务的。尽管事务ID等事务相关的内容会记入Redo Log，这些内容只是被当作要操作的数据的一部分。

要将Undo Log持久化，而且必须要在写Redo Log之前将对应的Undo Log写入磁盘。Undo和Redo Log的这种关联，使得持久化变得复杂起来。为了降低复杂度，InnoDB将Undo Log看作数据，因此记录Undo Log的操作也会记录到redo log中。这样undo log就可以象数据一样缓存起来，而不用在redo log之前写入磁盘了。

既然Redo没有事务性，会重新执行被回滚了的事务，同时Innodb也会将事务回滚时的操作也记录到redo log中。回滚操作本质上也是对数据进行修改，因此回滚时对数据的操作也会记录到Redo Log中。一个被回滚了的事务在恢复时的操作就是先redo再undo，因此不会破坏数据的一致性。