02-MySQL 日志

官网保平安：https://www.mysql.com/
MySQL 思维导图：https://www.processon.com/view/link/63bc2c8ea82ed9463ba99f38

日志

MySQL 有哪些日志 ★★★★★

  • undo log：用于支持事务的原子性、一致性，用于支持事务回滚以及 MVCC。
  • redo log：用于支持事务的持久化，用于崩溃恢复，只有 InnoDB 存储引擎实现。
  • 查询日志：记录所有对数据库请求的信息。
  • 慢查询日志：将运行时间超过阈值的所有SQL语句都记录到慢查询的日志文件中。
  • binlog：记录对数据库执行更改的所有操作。用于主从同步和数据恢复，Server 层实现，所有的存储引擎都可以用 binlog。
  • 中继日志：用于主从复制中从机复制主机信息。

binlog

binlog 日志的三种格式

binlog 日志有三种格式：

  • Statement：基于 SQL 语句的复制(statement-based replication, SBR)，每一条会修改数据的 SQL 都会记录在 binlog 中。

    • 优点：不需要记录每一行的变化，减少了 binlog 日志量，节约了 IO，提高性能。
    • 缺点：由于记录的只是执行语句，为了这些语句能在备库上正确运行，还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在备库得到和在主库端执行时候相同的结果。

  • Row：基于行的复制(row-based replication, RBR)，不记录 SQL 语句上下文相关信息，仅保存哪条记录被修改。

    • 优点：binlog 中可以不记录执行的 SQL 语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以 rowlevel 的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定情况下的存储过程、或 function、或 trigger 的调用和触发无法被正确复制的问题。
    • 缺点：可能会产生大量的日志内容。

  • Mixed：混合模式复制(mixed-based replication, MBR)。
    实际上就是 Statement 与 Row 的结合。一般的语句修改使用 statment 格式保存 binlog，如一些函数，statement 无法完成主从复制的操作，则采用 row 格式保存 binlog，MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式。

redo log

redo log 和 undo log 的区别

  • redo log：记录的是物理级别上的页修改操作，比如页号，偏移量，写入的数据，主要是为了保证数据的可靠性。
  • undo log：记录的是逻辑操作日志，比如对某一行数据进行了 insert 操作，那么 undo log 就记录一条与之相反的 delete 操作。主要用于事务的回滚和一致性非锁定读。

为什么需要 redo log

redo log 主要用于 MySQL 异常重启后的一种数据恢复手段，确保了数据的一致性。其实是为了配合 MySQL 的 WAL 机制。

InnoDB 是以页为单位来管理存储空间的，任何的增删改差操作最终都会操作完整的一个页。

当我们想要修改 DB 上某一行数据的时候，InnoDB 是把数据从磁盘读取到内存的缓冲池(buffer pool)上进行修改。这个时候数据在内存中被修改，与磁盘中相比就存在了差异，我们称这种有差异的数据为脏页。

InnoDB 对脏页的处理不是每次生成脏页就将脏页刷新回磁盘，因为此时的刷新是一个随机 IO，这样会产生海量的 IO 操作，严重影响 InnoDB 的处理性能。

但是如果不立即刷新的话，数据此时还在内存中，如果此时发生系统崩溃最终数据会丢失的，因此权衡利弊，引入了 redo log，也就是说修改完后不立即刷新，而是记录一条日志，日志内容就是记录哪个页面，多少偏移量，什么数据发生了什么变更。这样即使系统崩溃，也可以根据 redo log 进行数据恢复。

注意，redo log 是循环写入固定大小的文件。

什么是 WAL 技术，有什么优点

WAL（Write-Ahead Logging），就是先写日志，再写磁盘。MySQL 执行更新操作后，会先把记录写入 redo log buffer（用户空间） ，再保存到内核空间的缓冲区 OS-buffer 中，后续某个时间点再一次性将多个操作记录写到 redo log file（刷盘）。这种先写日志，再写磁盘的技术，就是 WAL。

好处是不用每一次操作都实时把数据写盘，就算 crash 后也可以通过 redo log 恢复，所以能够实现快速响应 SQL 语句。

redo log 写入方式

redo log 包括两部分内容，分别是内存中的日志缓冲(redo log buffer)和磁盘上的日志文件(redo log file)。

redo log buffer 写入到 redo log，是经过 OS buffer 中转的。其实可以通过参数 innodb_flush_log_at_trx_commit 进行配置，参数值含义如下：

  • 0：称为延迟写，事务提交时不会将 redo log buffer 中日志立即写入到 OS buffer，而是每秒写入 OS buffer 并调用写入到 redo log 中。
  • 1：称为实时写，实时刷，事务每次提交都会将 redo log buffer 中的日志写入 OS buffer 并保存到 redo log file 中。
  • 2：称为实时写，延迟刷。每次事务提交写入到 OS buffer，然后是每秒将日志写入到 redo log file。

为什么 redo log 具有的 crash-safe 能力是 binlog 无法替代的？

  • binlog 是 server 层的数据，多个引擎共享这个日志，不可能为了记录一个 binlog 的刷盘记录专门开发相应的功能。

  • redo log 是引擎层数据，是由引擎自己实现的。redo log 可确保 InnoDB 判断哪些数据已经刷盘，哪些数据还没有，binlog 不行。

    • 已经刷入磁盘的数据都会被标记一个 lsn 值，而 redo log 也有一个 lsn 值，这两个值可以判断有哪些数据没有被更新，但是 binlog 没有。

  • redo log 可确保 InnoDB 判断哪些数据已经刷盘，哪些数据还没有

    • redo log 和 binlog 有一个很大的区别就是，一个是循环写，一个是追加写。也就是说 redo log 只会记录未刷盘的日志，已经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。binlog 是追加日志，保存的是全量的日志。
    • 当数据库 crash 后，想要恢复未刷盘但已经写入 redo log 和 binlog 的数据到内存时，binlog 是无法恢复的。虽然 binlog 拥有全量的日志，但没有一个标志让 InnoDB 判断哪些数据已经刷盘，哪些数据还没有。
    • 但 redo log 不一样，只要刷入磁盘的数据，都会从 redo log 中抹掉，因为是循环写！数据库重启后，直接把 redo log 中的数据都恢复至内存就可以了。

  • 如果 redo log 写入失败，说明此次操作失败，事务也不可能提交

    • redo log 每次更新操作完成后，就一定会写入日志，如果写入失败，说明此次操作失败，事务也不可能提交。
    • redo log 内部结构是基于页的，记录了这个页的字段值变化，只要 crash 后读取 redo log 进行重放，就可以恢复数据。
    • 这就是为什么 redo log 具有 crash-safe 的能力，而 binlog 不具备

写 redo log 同样也是一次磁盘的写操作，凭什么说性能更高

  • 顺序写。
    将 修改信息 写入到 redo log 当中，只需要数据顺序写入 redo log 中，这是一次顺序写磁盘。
    而将数据页刷到磁盘中，因为一个修改操作可能会同时修改多个数据页，这些数据页又不是连续的，意味着随机写磁盘。

  • 大小。
    对于 MySQL 来说，一个数据页是 16kb，一次性更新多个数据页，可能需要随机写入几百 k 的数据。
    对于 redo log，一次修改可能只需要写入几 k 的数据，数据量相对于刷数据页的方式是大大减少的。

写 redo log 是磁盘的顺序写，小数据量，而刷数据页到磁盘可能就意味着随机写，而且还是大数据量的。所以写 redo log 的性能可能比刷数据页的性能高，大约高 100 倍。

redo log 的执行流程 ★★★

  1. 当我们想要修改 DB 上某一行数据的时候，InnoDB 先判断数据页是否在内存中。
    • 若为否，则从磁盘读取数据到内存中，返回数据行。
    • 若是数据页在内存中，则直接返回数据行。
  2. 执行数据更新操作，然后把数据写入内存，同时把 redo log 写入到内存。
  3. 执行 commit 操作（此 commit 是 SQL 命令操作，而不是数据的 commit 状态）。
  4. 执行 commit 命令之后，进行两段提交操作。
    1. 把内存中的 redo log 写入到磁盘中，此时 redo log 处于 prepare 状态。
    2. 把 bin log 写入到磁盘。
    3. 提交事务，把数据写入到磁盘，此时事务处于 commit 状态。
  5. 结束。

什么是两阶段提交

MySQL 将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入 binlog，这就是两阶段提交。

两阶段提交就是让 redo log 和 binlog 这两个状态保持逻辑上的一致。redo log 用于恢复主机故障时的未更新的物理数据，binlog 用于备份操作。两者本身就是两个独立的个体，要想保持一致，就必须使用分布式事务的解决方案来处理。

为什么需要两阶段提交呢? ★★★

如果不用两阶段提交的话，可能会出现这样情况：

  • 先写 redo log，crash 后 bin log 备份恢复时少了一次更新，与当前数据不一致。

  • 先写 binlog，crash 后，由于 redo log 没写入，事务无效，所以后续 binlog 备份恢复时，数据不一致。

两阶段提交就是为了保证 redo log 和 binlog 数据的安全一致性。只有在这两个日志文件逻辑上高度一致了。你才能放心的用 redo log 帮你将数据库中的状态恢复成 crash 之前的状态，使用 binlog 实现数据备份、恢复、以及主从复制。

binlog 默认都是不开启的！也就是说，如果你根本不需要 binlog 带给你的特性（比如数据备份恢复、搭建 MySQL 主从集群），那你根本就用不着让 MySQL 写 binlog，也用不着什么两阶段提交，只用一个 redo log 就够了。无论你的数据库如何 crash，redo log 中记录的内容总能让你 MySQL 内存中的数据恢复成 crash 之前的状态。

当数据库 crash 后，如何恢复未刷盘的数据到内存中

根据 redo log 和 binlog 的两阶段提交，未持久化的数据分为几种情况：

  • change buffer 写入，redo log fsync 到磁盘但未 commit，binlog 未 fsync 到磁盘，这部分数据丢失。
  • change buffer 写入，redo log fsync 到磁盘但未 commit，binlog 已 fsync 到磁盘，先从 binlog 恢复 redo log，再从 redo log 恢复 change buffer。
  • change buffer 写入，redo log 和 binlog 都已经 fsync 到磁盘，直接从 redo log 里恢复。

如何判断 binlog 和 redo log 是否达成了一致

当 MySQL 写完 redo log 并将它标记为 PREPARE 状态时，会在 redo log 中记录一个 XID，它全局唯一的标识着这个事务。

而当你设置 sync_binlog=1 时，做完了上面第一阶段写 redo log 后，MySQL 就会更新 binlog 并且会将其刷新到磁盘中。binlog 结束的位置上也有一个 XID。只要这个 XID 和 redo log 中记录的 XID 是一致的，MySQL 就会认为 binlog 和 redo log 逻辑上一致。就上面的场景来说就会 commit，而如果仅仅是 redo log 中记录了XID，binlog 中没有，MySQL 就会 RollBack。

对于处于 PREPARE 状态的事务，存储引擎既可以提交，也可以回滚，这取决于目前该事务对应的 binlog 是否已经写入硬盘。这时就会读取最后一个 binlog 日志文件，从日志文件中找一下有没有该 PREPARE 事务对应的 XID 记录，如果有的话，就将该事务提交，否则就回滚。

MySQL 怎么知道 binlog 是完整的?

一个事务的 binlog 是有完整格式的：

  • statement 格式的 binlog，最后会有 COMMIT。
  • row 格式的 binlog，最后会有一个 XID event。

MySQL 是如何保证数据不丢失的

  • 只要 redo log 和 binlog 保证持久化磁盘就能确保 MySQL 异常重启后恢复数据。
  • 在恢复数据时，redo log 状态为 commit 则说明 binlog 也成功，直接恢复数据；如果 redo log 是 prepare，则需要查询对应的 binlog 事务是否成功，若 binlog 也成功则执行，若 binlog 失败则回滚。
#MySQL #面试
02-MySQL 日志
https://flepeng.github.io/interview-41-数据库-41-MySQL-02-MySQL-日志/
作者
Lepeng
发布于
2020年8月8日
许可协议