mysql日志系统和事务的理解

本文主要讲的是日志系统，以及日志系统和事务的关系。

事务的特性（acid）

Atomic：原子性，指的是事务是一个不可再分割的单位，要么全部成功，要么全部失败

Consistency：一致性，在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

Isolation：隔离性，事务之间的操作互不影响，通过锁实现

Durability：持久性，事务一旦提交，其结果就是稳定的，即使发生了宕机等，也可以将数据恢复。

数据完整性约束指的是为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。

实现方式：

隔离性：通过锁来实现，此时分为不加锁，乐观锁，悲观锁，行锁，表锁

持久性：通过redo log 来实现

原子性和一致性：通过undo来实现

WAL（Write-Ahead Logging）是一种实现事务日志的标准方法，具体而言就是:

1、修改记录前，一定要先写日志；

2、事务提交过程中，一定要保证日志先落盘，才能算事务提交完成。

通过WAL方式，在保证事务特性的情况下，可以提高数据库的性能。

简单来说，redo log 记录事务修改后的数据, undo log 记录事务前的原始数据。redo恢复提交事务修改的操作，而undo回滚行记录到某个特定的版本。Binlog用于记录整个对库的修改过程，包括所有执行的sql，用于数据库的主从数据同步和数据恢复

mysql的各种操作日志记录过程有三个主要步骤：

     0、记录到mysql进程内存：一般都是先将变更、日志等写入到 MySQL 进程的内存中

1. 写入（Write）：将mysql进程内存中的数据写入到操作系统的文件系统缓存中。这个步骤通常很快，因为它只是将数据移动到了另一个内存区域。

2. 同步（Fsync）：通过调用fsync()（或类似的系统调用），确保操作系统将文件系统缓存中的所有待处理数据都持久化到物理磁盘上。这个步骤是确保数据持久化的关键，它可以保护数据免受系统崩溃或电源故障的影响。

Undo log:发生于事务开始之前，在buffer中根据当前的数据版本生成逻辑格式的日志，能够保存事务发生之前的数据的一个版本，可以用于回滚，undo操作也使用了缓存，在事务提交的时候不会同时将数据和undo日志更新到磁盘，仅仅是先做“写入”，后续交给mysql后台任务处理，例如，通过purge线程来清理无效的undo日志，或者将有效的undo日志同步到磁盘。

Redo log：先在buffer中根据物理数据页面的修改生成物理格式的日志，然后在事务提交之前redo log会持久化，即比undolog和数据先持久化，数据和undolog的持久化会在后面的线程刷新操作过程中执行。redo操作事务提交后只有redo和binlog被持久化（MySQL的默认配置下:sync_binlog=1和innodb_flush_log_at_trx_commit=1）,数据暂时未被持久化，在数据持久化时如果发生了故障，尚有脏页未写入磁盘，在重启mysql服务的时候，可根据redo log进行重做，从而达到事务的持久性这一特性。实际上 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。在prepare和commit之间会记录binLog日志，commit时会同时持久化binlog和redoLog。

Bin log：发生于redolog的prepare和commit之间，用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。同时用于数据库的基于时间点的还原。

对于事务的提交和binlog与redolog的写入来说，是否已经提交取决于binlog的配置。在默认设置下，MySQL使用以下两个参数来控制binlog的行为：

1. sync_binlog：这个参数控制着多少次事务提交后，binlog被同步到磁盘。如果sync_binlog=1，那么每次事务提交时，binlog都会被同步到磁盘。这提供了更高的数据安全性，但可能会对性能产生影响。如果这个值被设置成一个更高的数值，比如sync_binlog=N（N>1），那么每N次事务提交后，binlog才会被同步到磁盘。

2. innodb_flush_log_at_trx_commit：这个参数，如前所述，控制着InnoDB redo log的刷新行为。如果这个参数被设置为1，那么每次事务提交时，InnoDB都会将redo log buffer中的数据写入磁盘并同步。

在使用MySQL的默认配置下，如果sync_binlog=1和innodb_flush_log_at_trx_commit=1，那么当事务提交时，你可以确信binlog已经被写入并同步到磁盘，同时InnoDB的redo log也被同步，从而确保了数据的持久性。

事务提交过程中，主要做了4件事情，

1、刷redo日志，前面我们说到，mysql实现事务一致性和持久性的机制。通过redo日志落盘操作，保证了即使修改的数据页没有及时更新到磁盘，只要日志是完成了，就能保证数据库的完整性和一致性；

2、清理undo段信息，对于innodb存储引擎的更新操作来说，undo段需要purge，这里的purge主要职能是，真正删除物理记录。在执行delete或update操作时，实际旧记录没有真正删除，只是在记录上打了一个标记，而是在事务提交后，purge线程真正删除，释放物理页空间。因此，提交过程中会将undo信息加入purge列表，供purge线程处理。

3、释放锁资源，mysql通过锁互斥机制保证不同事务不同时操作一条记录，事务执行后才会真正释放所有锁资源，并唤醒等待其锁资源的其他事务；

4、清理保存点列表，每个语句实际都会有一个savepoint(保存点)，保存点作用是为了可以回滚到事务的任何一个语句执行前的状态，由于事务都已经提交了，所以保存点列表可以被清理了。

 Undo + Redo事务的简化过程

  假设有A、B两个数据，值分别为1,2，开始一个事务，事务的操作内容为：把A从1修改为3，B从2修改为4，那么实际的记录如下（简化）：

事务开始

记录A=1,到undo log buffer

修改A=3

记录A=3到redo log buffer

Binlog记录 update A=3语句--binglog此时不完整，崩溃恢复时不生效

记录B=2到undo log buffer----此时crash，恢复的话A=1，事务未提交

修改B=4

记录B=4到redo log buffer，Binlog记录 update B=4语句----redoLog prepare阶段，此时crash，恢复的话A=1，B=2，事务未提交

binlog打上完整标识----此时宕机，恢复的话B=4，因为bingLog已经完整

Redo log 持久化--- 两阶段提交的commit阶段，此时crash，恢复的话B=4，A=3

事务提交，同时 undoLog和A=3、B=4的值持久化

什么时候事务算提交完毕？

事务中bingLog写完整（有单独的完整标识），事务提交完毕，在此之前crash事务没有提交，crash的话事务不生效，未提交，在此之后事务提交完毕，事务提交成功，可恢复。

Redolog和binglog的区别：

binlog的作用之一是还原数据库的，这与redo log很类似，很多人混淆过，但是两者有本质的不同

1，作用不同：redo log是保证事务的持久性的，是事务层面的，binlog作为还原的功能，是数据库层面的（当然也可以精确到事务层面的），虽然都有还原的意思，但是其保护数据的层次是不一样的。

2，内容不同：redo log是物理日志，是数据页面的修改之后的物理记录，binlog是逻辑日志，可以简单认为记录的就是sql语句

3，redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

4，恢复数据时候的效率，基于物理日志的redo log恢复数据的效率要高于语句逻辑日志的binlog

5，redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

数据恢复：

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

• 首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；
• 然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。但是到此步会丢失12点到2点之间的数据操作，所以下面还有一部。
• 然后干掉那个误删操作的sql后，再从误删表时刻开始执行12点到当前时间的binglog。

这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去

关于redoLog分阶段提交（即redo prepare，binglog，redo commit）原因 ？

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

1. 先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，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，与原库的值不同。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致，即导致主从不一致。

要 binlog 没写成功，整个事务是需要回滚的，而 binlog 和写完整后即使 MySQL Crash 了都可以恢复事务并完成提交。要做到这点，就需要把 binlog的完整性事务关联起来，而只有保证了 binlog与redoLog 和事务数据的一致性，才能保证主从数据的一致性。

但是即使使用了分阶段提交，也有可能是binlog写完发生crash，redo没有commit的场景，此时恢复不是和上面的场景2一样吗？？？

先来看一下崩溃恢复时的判断规则。

如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；
如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：a.  如果是，则提交事务；b.  否则，回滚事务。
 这里，上面场景发生 crash 对应的就是 2(a) 的情况，崩溃恢复过程中事务会被提交。
  现在，我们继续延展一下这个问题。
追问 1：MySQL 怎么知道 binlog 是完整的?

回答：一个事务的 binlog 是有完整格式的：
statement 格式的 binlog，最后会有 COMMIT；
row 格式的 binlog，最后会有一个 XID event。
  另外，在 MySQL 5.6.2 版本以后，还引入了 binlog-checksum 参数，用来验证 binlog 内容的正确性。对于 binlog 日志由于磁盘原因，可能会在日志中间出错的情况，MySQL 可以通过校验 checksum 的结果来发现。所以，MySQL 还是有办法验证事务 binlog 的完整性的。

追问 2：redo log 和 binlog 是怎么关联起来的?

回答：它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：
如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。
追问 3：处于 prepare 阶段的 redo log 加上完整 binlog，重启就能恢复，MySQL 为什么要这么设计?
回答：其实，这个问题还是跟我们在反证法中说到的数据与备份的一致性有关。在时刻 B，也就是 binlog 写完以后 MySQL 发生崩溃，这时候 binlog 已经写入了，之后就会被从库（或者用这个 binlog 恢复出来的库）使用。
所以，在主库上也要提交这个事务。采用这个策略，主库和备库的数据就保证了一致性。

下面是理解redoLog的几个问题

redo log 一般设置多大？

回答：redo log 太小的话，会导致很快就被写满，然后不得不强行刷 redo log，这样 WAL 机制的能力就发挥不出来了。

  所以，如果是现在常见的几个 TB 的磁盘的话，就不要太小气了，直接将 redo log 设置为 4 个文件、每个文件 1GB 吧。

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

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

• 如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与 redo log 毫无关系。
• 在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态。

redo log buffer 是什么？是先修改内存，还是先写 redo log 文件？

在一个事务的更新过程中，日志是要写多次的。比如下面这个事务：

begin; insert into t1 ... insert into t2 ... commit;

  这个事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存起来，但又不能在还没 commit 的时候就直接写到 redo log 文件里，所以先写到redo log buffer内存中。而且一直直接写文件到磁盘也会拉低效率。

  所以，redo log buffer 就是一块内存，用来先存 redo 日志的。也就是说，在执行第一个 insert 的时候，数据的内存被修改了，redo log buffer 也写入了日志。

  但是，真正把日志写到 redo log 文件（文件名是 ib_logfile+ 数字），是在执行 commit 语句的时候做的。（这里说的是事务执行过程中不会“主动去刷盘”，以减少不必要的 IO 消耗。但是可能会出现“被动写入磁盘”，比如内存不够、其他事务提交等情况。）。

  单独执行一个更新语句的时候，InnoDB 会自己启动一个事务，在语句执行完成的时候提交。过程跟上面是一样的，只不过是“压缩”到了一个语句里面完成

说明：部分内容借鉴于极客时间 mysql实战45讲。