1001-日志第01讲：日志概览
部分资料引用自各路大神，具体见“99.参考资料”。
逻辑日志：简单理解，记录的是sql语句。物理日志：
- MySQL 的数据最终是存储在
数据页中的，物理日志记录的是数据页的变更。- [备注]：MySQL 的数据页大小由系统变量
innodb_page_size来控制。注意区分 MySQL 数据页和操作系统数据页（一般将 MySQL 数据页的大小设置为操作系统数据页的倍数），简单理解 数据页就是磁盘。mysql> SELECT VARIABLE_NAME, CONCAT(VARIABLE_VALUE / 1024 , ' k') VARIABLE_VALUE -> FROM information_schema.GLOBAL_VARIABLES -> WHERE VARIABLE_NAME = 'innodb_page_size'; +------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +------------------+----------------+ | INNODB_PAGE_SIZE | 16 k | +------------------+----------------+
0. MySQL逻辑架构
阅读之前，请各位看官先大致了解一下MySQL的逻辑架构。下面说到的有些东西是在此基础上展开的。 // TODO 待新增文章链接。- 简单说一下，大致分为三层。
- 第一层：连接器，处理客户端连接、授权认证、安全检查等。
- 第二层：server 层（服务器层），负责对 sql 进行解释、分析、优化、执行操作引擎等。
- 第三层：存储引擎，负责 MySQL 中数据的存储和提取。
图1 MySQL 逻辑架构图
图2 MySQL 数据更新流程图
1. redo log（重做日志）
1.1 为啥需要 redo log
- redo log 属于 innodb 存储引擎的
事务日志。由于 redo log 记录的是事务对数据页的变更，所以也属于物理日志。
【情况1】
- 众所周知哈，事务的四大特性之一是
持久性（只要事务提交成功，那么对数据库的修改就被永久保存下来，不可能因为任何原因再回到原来的状态）。- Q：MySQL是怎么保障持久性的呢？
- A：最直接的方法就是在每次提交事务的时候，把该事务涉及到的修改的数据页全部刷新到磁盘上。但是这么干会有很严重的性能问题，主要体现在2个方面：
- 首先，我们要知道的是
innodb是以页为单位与磁盘进行交互的。所以，① 如果一个事务只修改了一个数据页上的几个字节，那么把整个数据页都刷新到磁盘上的话，显得太浪费资源了。② 一个事务可能会涉及到好多数据页，并且这些数据页在物理上是不连续的，这种使用随机 IO 写入的性能太差了。- 为此，MySQL 引入了 redo log，
只记录事务对数据页做了哪些修改，这样的话就可以解决性能问题（相对来讲文件更小，并且还是顺序 IO）。
【情况2】
- MySQL 的数据是存储在磁盘上的，每次读写数据都需要做磁盘 IO 操作，并发场景下会很差的。所以呢，MySQL 为我们提供了一个优化方法，引入了
页缓存（Buffer Pool）。这个缓存中包含了磁盘上的 部分 数据页 的映射，用这种手段来缓解数据库的磁盘压力。【页缓存】
- ① 当从数据库中读取数据时，首先从页缓存中读取，如果页缓存中没有的话，就从磁盘上去读，然后再写入页缓存。
- ② 当往数据库写入数据时，要先写入页缓存，此时页缓存中发生变化的数据页叫做
脏页（即，内存页和磁盘页上的数据不一致时，内存页上发生变化的数据页就叫脏页），buffer pool 中修改完数据后会按照设定的更新策略，定期刷到磁盘上，这个过程称为刷脏页（数据页刷盘）。- Q：如果刷脏页还没完成（也就是说，缓存页中修改的数据还没来得及刷到磁盘上），MySQL 突然 duang 的一下宕机了。这个时候即使重启MySQL，这一部分数据也找不回来了，这样的话，就无法保证事务的持久性。
- A：这里先暂不考虑 redo log buffer（redo log 缓存）。在刷脏页之前会先记录 redo log 日志，再通过异步线程把页缓存上的脏页刷新到磁盘上。这样一来，即使在刷脏页没有完成之前 MySQL 宕机了也没关系，只需要在重启 MySQL 服务时对 redo log 中的更新记录进行重放，重新刷盘就可以了。
1.2 redo log 简介
- redo log 包含了两部分：一部分是 内存中的
日志缓存（redo log buffer），一部分是 磁盘上的日志文件（redo log file）。MySQL 每执行一条 DML 语句，先把记录写入 redo log buffer，再在后续的某个时间点一次性把多个操作记录写到 redo log file 中。这种 先写日志，再写磁盘 的技术就是 MySQL 中经常说到的WAL（Write-Ahead Logging）技术。- 在操作系统中，用户空间（user space）下的缓冲区数据一般情况下都是无法直接写入磁盘的，中间必须经过操作系统的内核空间（kernel space）的缓冲区（OS Buffer）。因此，redo log buffer 写入 redo log file，实际上是先写入 OS Buffer，然后再调用操作系统的 fsync() 方法将其刷新到 redo log file中。
- redo log 生成的日志文件名称为
ib_logfile*，其中【*】指的是 0…N，N是日志文件的最大个数。由 MySQL 变量 【innodb_log_files_in_group】 来控制，用来指定有几个日志组，也就是会生成几个 redo log file。
图3 Innodb Log
1.3 redo log 刷盘时机
- MySQL 系统变量
innodb_flush_log_at_trx_commit可以控制 redo log buffer 写入 redo log file 的时机（这个变量只是控制 commit 动作是否刷新 redo log buffer 到 redo log file 中）。
- 0（延迟写）
- 执行流程：事务提交时，不会将 redo log buffer 中的日志写入 os buffer，而是每秒触发一次，写入 os buffer 并调用 fsync() 写入到 redo log file 中。
- 说明：当 MySQL 崩溃时会丢失一秒的数据。
- 1（实时写，实时刷）
- 执行流程：每个事务在提交时，都会把 redo log buffer 中的日志写入 os buffer 并调用 fsync() 刷新到 redo log file 中。
- 说明：这种方式不会丢失任何数据，但是每次提交都要写入磁盘，IO 性能就能差一些。
- 2（实时写，延迟刷）
- 执行流程：每个事务在提交时，都只会把 redo log buffer 中的日志写入 os buffer 中，然后每秒去调用 fsync() 把 os buffer 中的日志写入到 redo log file 中。
- 说明：这种方式下，如果 MySQL 崩溃，由于已经执行了 重做日志写入磁盘的操作，只是没有做 磁盘 IO 刷新操作（把 os buffer 中的日志写入到 redo log file 中），所以，只要不发生操作系统崩溃，数据就不会丢失。
图5 Redo Log 刷盘
1.5 redo log 记录形式
- 从上面来看，涉及到【图2 MySQL数据更新流程图】的 第1、4、5、8步骤。大体流程如下：
- ① 客户端发送一个数据更新的请求到 MySQL 服务器；
- ② 先写入页缓存（buffer pool），页缓存中发生变化的数据页叫脏页；
- ③ 写记录到 redo log buffer；
- ④ 通过 MySQL 系统变量 innodb_flush_log_at_trx_commit 的设置，按指定的方式将 redo log buffer 最终刷盘到 redo log file；
- ⑤ 启动异步线程，把页缓存上的脏页刷新到数据库磁盘上（刷脏页）。
- 当页缓存上的脏页刷新到数据库磁盘上后，这些记录也就没啥用了。所以，redo log 就采用了
固定大小、循环写入的方式。当写到结尾时，会重新从头开始循环往复的写，就像下面的能量环。
图6 Redo Log 记录环
在 innodb 中，既有 redo log 需要刷盘，还有 数据页 也需要刷盘，redo log 存在的意义主要就是降低对 数据页刷盘 的要求（详情参见【1.1 为啥需要 redo log】的 - 情况2）。- 从【图6 Redo Log 记录环】中可以看出
- ① write pos 表示 redo log 当前记录的
LSN（log sequence number，日志序列号），redo log 已刷盘，但是数据页还未刷盘（刷脏页）。- ② check point 表示 数据页刷盘后对应 redo log 所处的 LSN 位置，这个 LSN 之前的数据已经全部刷新到磁盘上了。
- ③ write pos 到 check point 之间的部分是 redo log 空着的部分，用来记录新的日志。
- ⑤ check point 到 write pos 之间的是 redo log 待刷盘的数据页变更记录（刷脏页），此时数据页还没有刷新到磁盘上。
- ⑥ 当 write pos 追上 check point 时，会先推动 check point 向前移动，空出来位置再记录新的日志。
1.6 crash-safe（系统崩溃恢复）
- innodb 因为有了 redo log 的存在而具备了 crash-safe（系统崩溃恢复）的能力，也就是说，当 MySQL 宕机重启时，MySQL 会自动去检查 redo log file，将还未进行刷脏页的数据从 redo log file 恢复到 MySQL 中。
- 在启动 innodb 时，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。因为 redo log 记录的是数据页的物理变化，所以恢复速度也比逻辑日志（譬如 bin log）要快的多。
- 重启 innodb 时，会检查磁盘上数据页的 LSN，如果数据页的 LSN 小于 redo log file 中的 LSN（write pos 位置），则说明 redo log file 上记录着 数据页上尚未完成的操作（即 刷脏页未完成），那么就会从最近的一个 check point 开始同步数据。
- 举栗子：如果 redo log file 的 LSN 是 600，数据页上的 LSN 是 200，则说明重启前有部分数据没有刷到磁盘上（刷脏页未完成），那么 MySQL 就会把 redo log file 中 LSN 序号从 200 到 600 的记录进行重放刷新到磁盘上。
图7 RedoLog【crash-safe】
1.7 redo log 系统变量及命令
- innodb_log_files_in_group：指定有几个日志组（默认两个），也就是总共会生成几个 redo log file 文件。文件名为 ib_logfile*，其中【*】指的是 0…N，N是日志文件的最大个数。
- innodb_log_buffer_size：指定 redo log buffer 的大小。
- innodb_log_file_size
- 在一个日志组中，每个 redo log file 的大小。根据 innodb_log_buffer_size 设置其大小，几个 redo log file 文件大小加起来差不多和 innodb_log_buffer_size 相等。
- redo log 日志如果满了，在擦除之前，需要确保这些即将被擦除的记录对应在内存中的数据页都已经刷新到磁盘上了（刷脏页要完成）。在擦除旧记录腾出新空间的这段时间，是不能再接收新的更新请求的，此时 MySQL 的性能会下降。所以在并发量大的情况下，合理调整 redo log 的文件大小就显得异常重要了。
- innodb_log_group_home_dir：redo log 日志文件的存放位置。最好还是和
datadir（MySQL数据文件存放位置）分开。innodb_flush_log_at_timeout：刷新 redo log buffer 到 redo log file 的频率（刷新日志的频率），该变量值默认是1秒。与innodb_flush_log_at_trx_commit没有任何关系。
图8 RedoLogFile文件格式
2. undo log（回滚日志）
- undo log 同样属于 MySQL 的 innodb 存储引擎的
事务日志。undo log 记录的是数据的逻辑变化（sql语句），所以属于逻辑日志。- undo log 保证了事务的
原子性（对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况）。- undo log 记录的是【数据
修改前的状态】，在数据修改的过程中，同时会记录一条与当前操作相反的逻辑日志到 undo log 中。当执行 rollback 时，就可以从 undo log 的逻辑日志中读取到对应的内容并进行回滚操作。
- 举栗子
- 表内容如下。假设现在要更新 id = 1的记录（更新name字段），name的原始值为’风中有朵雨做的云’，现在改为’你看你看月亮的脸’。
- 事务在执行 update table_name set name = ‘你看你看月亮的脸’ where id = 1 语句时，会先在 undo log 中记录一条相反的 update table_name set name = ‘风中有朵雨做的云’ where id = 1 记录。这样的话，当出现问题导致事务失败时，就可以借助于 undo log 来把数据 回滚 到事务执行前的状态，以此来保证事务的完整性。
| id | name |
|---|---|
| 1 | ‘哈哈’ |
- Q：同一个事务内一条记录被反复修改（来回揉搓），那么是不是每次都要把修改前的数据状态记录到 undo log 中呢？
- A：不是的。undo log 只记录事务开始前要修改的数据的原始版本。
3. bin log（二进制日志）
3.1 bin log 简介
- bin log 用来记录数据库执行时的
写入操作信息，包含 DDL 和 DML 操作（不包含 select 和 show 等命令，因为这些操作并没有对数据本身做任何的修改）。- bin log 是 MySQL 的**
逻辑日志**，以二进制的形式保存在磁盘上。- bin log 的日志包括两类文件
- 二进制日志索引文件（文件名后缀为.index）：用于记录所有有效的二进制文件。
- 二进制日志文件（文件名后缀为.00000*）：记录数据库的 DDL 和 DML 语句。
- bin log 由 MySQL 的 server 层（服务器层）负责进行记录。所以，任何存储引擎的MySQL数据库都会记录 bin log 日志，也就是说，bin log 日志和存储引擎没有任何的毛关系。
二进制日志索引文件内容如下：
3.2 bin log 刷盘时机
- BinLog日志分为两部分：一部分在缓存中，一部分在磁盘上。所以，需要有刷盘的操作，也就是将缓存的日志数据落入到磁盘上。
- Q：对于InnoDB存储引擎来讲，只有在事务提交时才会记录BinLog日志。而此时的记录还在内存中，那么BinLog是在什么时候刷新到磁盘上的呢？
- A：MySQL通过系统变量
sync_binlog来控制BinLog的刷盘时机，取值范围为 0 - N。
- 0：没有强制要求，纯粹靠操作系统自行判断审核时候该写入磁盘。
- 1：事务在每次 commit 的时候将 BinLog 写入磁盘。
- N：每N个事务，才会将 BinLog 写入磁盘。
MySQL 的系统参数 sync_binlog 设置为1是最安全的。- 案例：在一些实际场景下，为了一点点的性能提升可以适当的把值调大一些，但是会牺牲一定的一致性。比如，使用 mysqldump / mysqlpump 进行数据库备份（备份的结果文件内容就是表数据的 insert into 语句），我们在进行数据库还原时（其实就是对表先执行全表数据的删除操作 [truncate table_name 或 delete from table_name]，再执行备份结果文件中的 insert into 语句），此时，是需要将 MySQL 的全局变量 read_only 设置为 1（只读），在这样的前提下，系统也做不了其他的事情（也就是说，此时此刻就只有数据库还原操作在对数据库表数据进行修改操作），这样的话，我们就可以把 sync_binlog 调大一点。
3.3 bin log 格式
BinLog 日志有 3 中格式
- 日志格式可通过MySQL系统参数
binlog_format来指定。- STATEMENT
- 基于sql语句的复制（statement-based replication， SBR），每一条修改数据的sql语句都会记录到binlog中。
- 优点：不需要记录每一行数据的变化，减少了binlog日志量，节约IO，提高性能。通过 mysqlbinlog 工具可以清晰的看到每条语句的文本。
- 缺点：可能会导致主从数据不一致，比如sql的某些函数now()、sysdate()等。
- ROW
- 基于行的复制（row-based replication，RBR），不记录每条sql语句的上下文信息，只需要记录哪条数据被修改了（记录每一行数据的变更）。
- 举例子：比如一个更新sql语句 updte emp set name = ‘haha’; ① 如果日志格式是“statement”格式，日志中会记录一行sql文本；② 如果是“row”的话，由于这条sql是对全表进行更新，也就是说每一行记录都会发生变更。如果是一个100W行的大表，则日志中会记录100W条记录的变化，这个有点阔怕啊。
- 优点：不会出现某些情况下的存储过程、触发器等无法被正确复制的问题。
- 缺点：会产生大量的日志。
- MIXED
- 基于 statement 和 row 两种模式的混合复制（mixed-based replication，MBR），一般的复制使用statement模式保存binlog，对于statement无法复制的操作使用row模式。
3.4 bin log 系统变量及命令
1. 系统变量
- sql_log_bin：是否记录binlog的开关，默认开启。取值为：ON、OFF。
- log_bin：指定binlog日志的存放位置。默认位置为datadir（MySQL的数据目录）。
- expire_logs_days：二进制日志的有效天数（默认永久保留），还是要设置的，不然日志的体量会非常庞大的。
- max_binlog_size：设置一个 bin log 日志文件的大小（默认最大容量1G）。通常情况下，当单个日志超过最大值的时候会新创建一个文件继续记录。也有例外，比如当二进制文件达到最大值的时候一个事务还没有提交，则日志文件会继续增大，所以说，二进制日志文件大小和该变量设定的最大值不一定相等。
- binlog_do_db：只记录指定数据库的二进制日志，默认全部记录。
- binlog_ignore_db：不记录指定数据库的二进制日志，一般情况下MySQL本身的mysql库就可以不用记录。
- 这两个参数为互斥关系，一般只设置其中一个，只能在启动命令行或配置文件中加入。指定多个数据库时要分行写入，即一行写一个数据库。
2. MySQL命令
- 查询最新的一个binlog日志文件名等信息：show master status;mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000208 | 27516332 | | mysql | | +------------------+----------+--------------+------------------+-------------------+
4. 其他
4.1 redo log 和 bin log 的区别
| 区别点 | redo log | bin log |
|---|---|---|
| 实现方式 | redo log 是 innodb 引擎层实现的，并不是所有引擎都有。 | bin log 是 server 层实现的，所有引擎都可以使用 bin log 日志 |
| 记录方式 | redo log 采用循环写的方式记录，当写到结尾时会回到开头继续写。 | bin log 通过追加的方式记录，通常情况下，当文件大小达到给定值后，后续的日志会记录在新的文件上。 |
| 内容 | redo log 是物理日志，内容基于磁盘的数据页（page） | bin log 的内容是二进制，可以根据MySQL变量 binlog_format 进行设置。 |
| 使用场景 | redo log 适用于崩溃恢复（crash-safe）、数据库备份恢复 | bin log 适用于主从复制和数据恢复。 |
- MySQL还有其他的一些日志，这里简单说一下。
- relay log：中继日志，在主从复制的时候再说。
- slow query log：慢查询，可以用来做粗粒度的慢sql统计，当然也可以使用阿里的druid（德鲁伊）数据库连接池（有界面的）进行分析。
- general query log：一般查询日志，会记录用户的
所有操作，有点阔怕，一般是关闭着的。当我们在使用一些第三方的MySQL辅助工具的时候，可以开启，以用来分析TA们都干了些啥。- error log：错误日志。记录 MySQL 每次启动、停止的时候的一些信息。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
