mysql中创建日志表就是创建触发器嘛 mysql写日志机制

文章目录

• WAL 机制
• 三种日志的对比
• redo log

• 理解redo log的作用
• redo log的写入机制

• binlog

• 理解 binlog的作用
• binlog 的写入机制

• undo log

• 理解 undo log 的作用

• 应用

• redo log与binlog在事务中的应用
• 使用 undo log 和 redo log恢复崩溃

WAL 机制

在学习 MySQL 的时候，难免会听说过 WAL（Write-Ahead Logging 预写式日志）机制，说白了就是先写日志，再写数据。当我们需要修改表中的数据的时候，只需要先修改内存中的值，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序 I/O, 而不像随机 I/O 需要在磁盘的多个地方移动磁头，所以记日志比在磁盘上记数据要快得多。日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。目前大多数存储引擎都是这样实现的，我们通常称之为预写式日
(Write-Ahead Logging), 修改数据需要写两次磁盘。如果对数据的修改已经记录到日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够根据日志自动恢复这部分修改的数据。

MySQL 的 WAL（Write-Ahead Logging） 机制：先写（Write-Ahead ）日志，再写磁盘。相比于直接写磁盘，其优势在于：

• redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快；
• 组提交机制(可以简单理解为是累计一段时间然后一次多些几条记录到磁盘，并不是一次写一条)，可以大幅度降低磁盘的 IOPS 消耗。

三种日志的对比

在 MySQL 中有好几种日志，常见的有 binlog，redo log，undo log。它们都属于MySQL的日志，但是这几个日志之间有什么关系呢，它们分别承担什么作用呢？这可能就有点令人迷糊了。

redo log 是 innodb 引擎特有的，常译作 重做日志。它是物理日志，记录了在某个数据页上进行了什么修改。这个数据页对应着数据硬盘上的实际存储地址。InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB。redo log是一块循环写的空间，从头写到尾以后，如果需要继续写，会覆盖之前写的日志。

binlog（binary log），常译作归档日志，所谓的归档日志，及所有的操作都会被记录然后存放起来。binlog 是MySQL server 层的引擎，与引擎无关，这就意味着不管是使用 Innodb 引擎，还是使用 MyISAM 引擎都可以使用。binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如 给 ID=2 这一行的 c 字段加 1。binl og 是可以追加写入的。“追加写” 是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。binlog 通常有三种格式，一种是 Row 格式（将每一行的修改记录到记录到binlog中），一种是 Statement 格式（相当于是记录了操作的SQL语句），另一种则是 Mixed 模式，即混合了Row 格式和 Statement 格式。

undo log，常译作 回滚日志。当变动数据写入磁盘前，必须先记录 Undo Log，写明修改哪个位置的数据、从什么值改成什么值，以便在事务回滚或者崩溃恢复时，根据 undo Log 对提前写入的数据变动进行擦除。说白了，undo log 中记录的是数据的历史版本。在一个事务中，我们将一条记录，更新一百万次，每次做加一操作，值从1累加到了1000001。undo log 记录的是把 2 改成 1，把 3 改成 2 。因此1000001的前一条undo log就是 把1000001 改成 1000000，可以看作依次减一，然后回到最初的状态（如下图所示）。当事务崩溃，需要回滚时，就依次执行混滚日志，混滚到最初的状态。

redo log

理解redo log的作用

先来看看 redo log 。
在林晓斌老师的《MySQL实战45讲》中有一个很生动的例子来讲述 redo log，特摘录如下。课程链接戳此

在《孔乙己》这篇文章中，酒店掌柜有一个粉板，专门用来记录客人的赊账记录。如果赊账的人不多，那么他可以把顾客名和账目写在板上。但如果赊账的人多了，粉板总会有记不下的时候，这个时候掌柜一定还有一个专门记录赊账的账本。

如果有人要赊账或者还账的话，掌柜一般有两种做法：

• 一种做法是直接把账本翻出来，把这次赊的账加上去或者扣除掉；
• 另一种做法是先在粉板上记下这次的账，等打烊以后再把账本翻出来核算。

在生意红火柜台很忙时，掌柜一定会选择后者，因为前者操作实在是太麻烦了。首先，你得找到这个人之前赊账的那条记录，如果账本很厚，密密麻麻几十页，掌柜要找到这个人之前赊账的记录，然后再上面进行修改，可能还得带上老花镜慢慢找，找到之后再拿出算盘计算，最后再将新的结果写回到账本上，可想而知这种方式有多低效。

同样的，我们在操作MySQL数据库的时候，每修改一条记录，都需要从磁盘中去读取对应的数据页（注意：MySQL从磁盘中读取数据并不是一行一行的进行读取，而是按一个数据页，16K为基本单位进行读取），找到要修改的这条记录的具体位置，然后再修改数据，就和孔乙己中的那个老板要带着老花眼镜从厚厚的账本翻某人的欠账记录一样然后进行修改一样，是非常费时费力的。因此，MySQL 的设计者就用了类似酒店掌柜粉板（日志）的思路来提升更新效率，比如当我们需要更新数据的时候，并不需要从厚厚的账本（磁盘）中找出对应记录，然后进行修改，而是将对数据库的修改按顺序写到粉板（日志）上，等到有空的时候再将粉板（日志）中记录的修改腾到账本（磁盘）上。

需要注意的是，redo log 也是记录到磁盘上，我们的数据也是在磁盘上。先写 redo log到磁盘和直接写数据到磁盘有什么区别？

写redo log的区域是一个特殊的区域，整个写入是 顺序写，也就是按照顺序挨个写下去的。而将记录写到磁盘，这是 随机写入 的，MySQL存储的数据在我们的磁盘上并不是连续存储的。因此我们插入或者修改某条记录的时候，得先找到某条记录对应的地址，然后再去进行修改，这和孔乙己中的老板翻账本记账一样，用笔记账的时间，不管是在粉板还是在账本上都差不多，不同的是在账本上记账，你得翻来翻去，找到之前那个人记账的记录进行修改。

redo log 是 InnoDB 引擎所特有的，所以我们如果在使用InnoDB引擎创建表时，如果数据库发生异常重启，之前提交的记录都不会丢失（我们称这种能力为 crash-safe 的能力）。具体的内容可以参考如下应用 章节中 redo log 与 binlog在事务中的应用 。在MySQL 5.1 之前，默认的存储引擎是MyISAM，MyISAM 不支持事务，其中一个重要的原因是因为 MyISAM 没有redo log 这样的机制。可见，redo log 是 InnoDB 引擎实现事务的一个重要保障。

redo log的写入机制

事务在执行过程中，生成的 redo log 是要先写到 redo log buffer 的，然后写入文件系统，最后再是持久化到磁盘。日志写到 redo log buffer 是很快的，wirte 到 page cache 速度相比前一步也差不多，但是持久化到磁盘的速度就慢多了。

InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，它有三种可能取值：参考

• 设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
• 设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；
• 设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。

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

binlog

理解 binlog的作用

binlog 译为 归档日志，其中记录了数据库中所有的逻辑操作，如果DBA的承诺是半个月内的数据可以恢复，那就意味着 binlog 中记录了至少半个月内的操作，因此，如果数据库中的数据发生不一致的情况，可以使用 binlog 回退到半个月内任意时刻的状态。通常使用 binlog 来进行备份，或者使用binlog 来进行主备之间的数据同步（如下图所示）。

备份时可以使用 mysqldump 这个工具来进行数据备份，比如：

mysqldump -u 用户 -p 密码 数据库 > 备份文件

binlog 的写入机制

事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。

图中的 write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。

图中的 fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS。

write 和 fsync 的时机，是由参数 sync_binlog 控制的：

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

undo log

理解 undo log 的作用

在一个事务中，对数据进行修改时，需要先在 undo log 中记录该数据的历史版本数据。比如要执行的操作是将 将2加1变为3，那么先在 undo log 中记录的可能就是 将3减1变为2，这样当事务崩溃时，就可以通过执行 undo log，回退到最初的状态。

我们知道，在 MySQL 的可重复读（RR）隔离条件下，通过 MVCC（多版本并发控制） 保证了可重复读，其中便使用到了 undo log。undo log 中除了记录了数据的历史状态以外，还记录了 row trx_id，即操作该行数据的事务 id，然后就用 undo log 中的 的trx_id与当前这个事务的 trx_id 进行比较，从而决定当前的事务可以看到哪个状态的数据，通过遍历 undo log，就可以回退到之前的任一状态。

应用

redo log与binlog在事务中的应用

假如我们需要执行

mysql> update T set c=c+1 where ID=2;

整个的执行过程如下图所示：

可以发现，只有当 redo log 和 binlog 都成功写入磁盘，整个事务才算是执行成功，这时，即便修改的的数据还没有真正落盘，只要日志已经落盘了，则可以进行恢复。

使用 undo log 和 redo log恢复崩溃

当执行事务的过程中遇到了崩溃，会经历如下几个阶段来进行恢复

• 分析阶段（Analysis）：该阶段从 undo log 的最后一次检查点（check point，可理解为在这个点之前所有应该持久化的变动都已安全落盘）开始扫描日志，找出所有没有 End Record （当事务的日志落盘，数据也落盘，则处于 End Record状态）的事务，组成待恢复的事务集合（一般包括 Transaction Table 和 Dirty Page Table）。
• 重做阶段（Redo）：该阶段依据分析阶段中，产生的待恢复的事务集合来重演历史（Repeat History），找出所有包含 Commit Record（事务的日志落盘，但是数据没有落盘，则处于 Commit Record 状态） 的日志，将它们写入磁盘，写入完成后增加一条 End Record，然后移除出待恢复事务集合。
• 回滚阶段（Undo）：该阶段处理经过分析、重做阶段后剩余的恢复事务集合，此时剩下的都是需要回滚的事务（被称为 Loser），根据 Undo Log 中的信息回滚这些事务。

如下图所示：write pos 是当前记录的位置，一边写一边后移。write pos 与 check point 之间则为可以写入的区域，当 write pos 追上了 check point 则表示 redo log 落盘的速度太慢了。这时候就得停止写入了。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到磁盘。