[大数据]MySQL-三大日志详解

三大日志

日志分为二类

逻辑日志：可以简单理解为记录的就是sql语句。

物理日志：因为mysql数据最终是保存在数据页中的，物理日志记录的就是数据页变更。

undo log

undo log 记录数据被修改前的样子

作用

防止丢失数据，用于事务失败后的回滚

Innodb 存储引擎的最大特点就是支持事务，如果本次更新失败，那么该事务中的所有的操作都必须回滚到执行前的样子，也就是说当事务失败的时候，也不会对原始数据有影响

记录时机

在mysql将要更新的数据加载到缓冲区Buffer pool 时，同时往 undo 日志文件中插入一条日志，也就是将 id=1 的这条记录的原来的值记录下来。

更新过程

1. 准备更新一条 SQL 语句
2. MySQL（innodb）会先去缓冲池（BufferPool）中去查找这条数据，没找到就会去磁盘中查找，如果查找到就会将这条数据加载到缓冲池（BufferPool）中
3. 在加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo 日志文件中

到这一步，我们的执行的 SQL 语句已经被加载到 Buffer Pool 中了，然后开始更新这条语句，更新的操作实际是在Buffer Pool中执行的，那问题来了，按照我们平时开发的一套理论缓冲池中的数据和数据库中的数据不一致时候，我们就认为缓存中的数据是脏数据，那此时 Buffer Pool 中的数据岂不是成了脏数据？没错，目前这条数据就是脏数据，Buffer Pool 中的记录是小强 数据库中的记录是旺财 ，这种情况 MySQL是怎么处理的呢，继续往下看

redo log

简介

redo 记录的是数据修改之后的值，不管事务是否提交都会记录下来

• redo 日志文件是 InnoDB 特有的，他是存储引擎级别的，不是 MySQL 级别的

• 服务器宕机，mysql重启时，利用redo log日志内容恢复缓冲区数据

• 一般情况下，redo log Buffer 数据写入磁盘的策略是立即刷入磁盘

为什么需要redo log

• MySQL 为了提高效率，所以先从磁盘读取数据放在内存中去完成，然后直接在内存对数据进行操作，这些操作会在某个时机将其持久化到磁盘中。
• 除了从磁盘中加载文件和将操作前的记录保存到 undo 日志文件中，其他的操作是在内存中完成的
• 内存数据断电丢失：如果此时 MySQL 所在的服务器宕机了，那么 Buffer Pool 中的数据会全部丢失的。这个时候就需要 redo 日志文件来恢复数据

刷入磁盘时机

刷磁盘可以通过 innodb_flush_log_at_trx_commit 参数来设置

• 值为 0 表示不刷入磁盘
• 值为 1 表示立即刷入磁盘(默认情况)
• 值为 2 表示先刷到 os cache

一般情况下，redo log Buffer 数据写入磁盘的策略是立即刷入磁盘

如果 redo log Buffer 刷入磁盘后，数据库服务器宕机了，在下次重启的时候 MySQL 也会将 redo 日志文件内容恢复到 Buffer Pool 中，因为 redo log buffer 中的数据已经被写入到磁盘了，已经被持久化了

（ Redis 的持久化机制类似，在 Redis 启动的时候会检查 rdb 或者是 aof 或者是两者都检查，根据持久化的文件来将数据恢复到内存中）

redo log持久化到磁盘前后区别

mysql服务器宕机，缓冲区的数据会怎么样？

• redo log持久化前，mysql会认为本次事务失败，利用undo log日志恢复到更新前的样子
• redo log持久化后，mysql下次重启的时候会自动将redo log日志文件内容恢复到缓冲区中

更新过程

截至目前，我们应该都熟悉了 MySQL 的执行器调用存储引擎是怎么将一条 SQL 加载到缓冲池和记录哪些日志的，流程如下：

1. 准备更新一条 SQL 语句
2. MySQL（innodb）会先去缓冲池（BufferPool）中去查找这条数据，没找到就会去磁盘中查找，如果查找到就会将这条数据加载到缓冲池（BufferPool）中
3. 在加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo 日志文件中
4. innodb 会在 Buffer Pool 中执行更新操作，更新后的数据会记录在 redo log buffer 中
5. 将 redo log buffer 中的数据写入到 redo log 磁盘文件中(innodb_flush_log_at_trx_commit 参数决定)
6. 提交事务，数据修改永久保存到数据库

全过程

到此为止，从执行器开始调用存储引擎接口做了哪些事情呢？

1. 准备更新一条 SQL 语句
2. MySQL（innodb）会先去缓冲池（BufferPool）中去查找这条数据，没找到就会去磁盘中查找，如果查找到就会将这条数据加载到缓冲池
3. 在加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo 日志文件中
4. innodb 会在 Buffer Pool 中执行更新操作，更新后的数据会记录在 redo log buffer 中
5. MySQL 提交事务的时候，会将 redo log buffer 中的数据写入到 redo 日志文件中

• 刷磁盘可以通过 innodb_flush_log_at_trx_commit 参数来设置
  • 值为 0 表示不刷入磁盘
  • 值为 1 表示立即刷入磁盘
  • 值为 2 表示先刷到 os cache

6. myslq 宕机重启的时候会将 redo 日志恢复到缓冲池中

bin log

简介

binlog用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。

binlog是通过追加的方式进行写入的，可以通过max_binlog_size参数设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。

使用场景

在实际应用中，binlog的主要使用场景有两个，分别是主从复制和数据恢复。

1. 主从复制：在Master端开启binlog，然后将binlog发送到各个Slave端，Slave端重放binlog从而达到主从数据一致。
2. 数据恢复：通过使用mysqlbinlog工具来恢复数据。

bin log刷入磁盘策略

bin log 的刷盘是有相关的策略的，通过sync_bin log来修改

对于InnoDB存储引擎而言，只有在事务提交时才会记录binlog，此时记录还在内存中，那么biglog是什么时候刷到磁盘中的呢？mysql通过sync_binlog参数控制biglog的刷盘时机，取值范围是0-N：

• 0：先写入 os cache，由系统自行判断何时写入磁盘，宕机数据会丢失；(MySQL 5.7.7之前版本的默认值)
• 1：每次commit的时候都要将binlog写入磁盘；（MySQL 5.7.7之后版本的默认值）
• N：每N个事务，才会将binlog写入磁盘。

刷入 bin log 有以下几种模式

• STATMENT

基于 SQL 语句的复制，每一条会修改数据的 SQL 语句会记录到 bin log 中

**【优点】：**不需要记录每一行的变化，减少了 bin log 日志量，节约了 IO , 从而提高了性能

【缺点】：在某些情况下会导致主从数据不一致，比如执行sysdate()、sleep()等

• ROW

基于行的复制(row-based replication, RBR)，不记录每条SQL语句的上下文信息，仅需记录哪条数据被修改了

【优点】：不会出现某些特定情况下的存储过程、或 function、或 trigger 的调用和触发无法被正确复制的问题

【缺点】：会产生大量的日志，尤其是 alter table 的时候会让日志暴涨

• MIXED

  基于 STATMENT 和 ROW 两种模式的混合复制

  • 一般的复制使用 STATEMENT 模式保存 bin log
  • 对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 bin log

bin log是在什么时候记录数据的呢？

其实 MySQL 在提交事务的时候，

1. 不仅仅会将 redo log buffer 中的数据写入到redo log 文件中，同时也会将本次修改的数据记录到 bin log文件中，
2. 也会将本次修改的bin log文件名和修改的内容在bin log中的位置记录到redo log中，
3. 最后还会在redo log最后写入 commit 标记，表示**事务成功提交**了

如果在数据被写入到bin log文件的时候，刚写完，数据库宕机了，数据会丢失吗？

• 只要redo log最后没有 commit 标记，说明本次的事务一定是失败的
• 但是数据没有丢失，因为已经被记录到redo log的磁盘文件中了。在 MySQL 重启的时候，就会将 redo log 中的数据恢复（加载）到Buffer Pool中

玩来玩去数据还是在内存里，我们数据库的数据最后是要被持久化要磁盘里的，什么时候持久化？

其实 MySQL 会有一个后台线程，它会在某个时机将我们Buffer Pool中的脏数据刷到 MySQL 数据库中，这样就将内存和数据库的数据保持统一了

redo日志与bin日志的区别

• redo log是 InnoDB 存储引擎特有的日志文件，而bin log属于是 MySQL 级别的日志

• redo log记录的东西是偏向于物理性质的，如：“对什么数据，做了什么修改”。

• bin log是偏向于逻辑性质的，类似于：“对 students 表中的 id 为 1 的记录做了更新操作” 两者的主要特点总结如下:

本文总结

• Buffer Pool 是 MySQL 的一个非常重要的组件，因为针对数据库的增删改操作都是在 Buffer Pool 中完成的
• Undo log 记录的是数据操作前的样子
• redo log 记录的是数据被操作后的样子（redo log 是 Innodb 存储引擎特有）
• bin log 记录的是整个操作记录（这个对于主从复制具有非常重要的意义）

准备更新到事务提交全过程

从准备更新一条数据到事务的提交的流程描述

1. 首先执行器根据 更新sql，来查询满足条件的数据，先是从缓存池中查询数据，如果没有就会去数据库中查询，如果查询到了就将其放到缓存池中
2. 在数据被缓存到缓存池的同时，将原始数据写入 undo log 日志文件
3. 在 BufferPool 中更新数据，将更新后的数据添加到 redo log buffer 中
4. 完成以后就可以提交事务，在提交事务的前会做以下三件事

• 将redo log buffer中的数据持久化到 redo log 文件中
• 将本次操作记录持久化到 bin log日志文件中
• 将 bin log 记录结束的位置标记记录到redo log中，最后在 redo log 添加 commit 标记(表示事务完成)