1：MySQL日志

日志文件中记录着 MySQL 数据库运行期间发生的变化。也就是说用来记录 MySQL 数据库的客户端连接状况、SQL 语句的执行情况和错误信息等。当数据库遭到意外的损坏时，可以通过日志查看文件出错的原因，并且可以通过日志文件进行数据恢复。

MySQL日志文件分为以下几种：

  1. 重做日志（redo log）
  2. 回滚日志（undo log）
  3. 二进制日志（binlog）
  4. 错误日志（errorlog）
  5. 慢查询日志（slow query log）
  6. 一般查询日志（general log）
  7. 中继日志（relay log）

2：binlog

binlog 是 MySQL server 层维护的一种二进制日志，这个文件记录了 MySQL 所有的 DML 操作。通过 binlog 日志我们可以做数据恢复，增量备份，主主复制和主从复制等等。

  • 复制：MySQL 在主库开启 binlog，主库把它的二进制日志传递给从库并回放来达到主从数据一致的目的。
  • 数据恢复：指通过 mysqlbinlog 工具恢复数据。
  • 增量备份：指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或者被修改的文件。

2-1：binlog的录入格式

  • statement(基于SQL语句的复制)：statement模式下记录的是执行的sql语句，也就是主库上执行了什么语句，binlog中就记录什么语句。

  • row(基于行的复制)：row模式下不记录 sql 语句上下文相关信息，仅保存哪条记录被修改。若一条sql语句修改了1000条记录，row格式的日志将会分别记录1000条记录的修改，而statement仅记录一条sql语句。

  • mixed(混合模式复制)：mixed是以上两种 level 的混合使用，MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式，也就是在 statement 和 row 之间选择一种。默认情况下使用statement模式，在以下情况下，Mixed会使用row模式：

    • 当一个函数包含 UUID()。
    • 当AUTO_INCREMENT更新一个或多个带有列的表 并调用触发器或存储函数时。与所有其他不安全语句一样，如果 binlog_format = STATEMENT。
    • 当视图的主体需要基于行的复制时，创建视图的语句也会使用它。例如，当创建视图的语句使用该UUID()函数时，就会发生这种情况 。
    • 当涉及对可加载函数的调用时。
    • 何时使用FOUND_ROWS()或 ROW_COUNT()。
    • 当USER(), CURRENT_USER(), 或 CURRENT_USER被使用时。
    • 当涉及的表之一是mysql数据库中的日志表时 。
    • 使用该LOAD_FILE()功能时。
    • 当一个语句引用一个或多个系统变量时。

mixed模式官网链接->Mixed Binary Logging Format

2-2：不同日志格式的优缺点

2-2-1：statement

  • 优点：在statement模式下首先就是解决了row模式的缺点，不需要记录每一行数据的变化减少了binlog日志量，节省了I/O以及存储资源，提高性能。因为它只需要记录执行的语句的细节以及执行语句的上下文信息。
  • 缺点：在statement模式下，由于它是记录的执行语句，所以为了让这些语句在从库也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在从库被执行的结果和在主库执行时的结果相同。另外就是，由于mysql现在发展比较快，很多的新功能不断的加入，使mysql的复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在statement中，目前已经发现不少情况会造成Mysql的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如：sleep()函数在有些版本中就不能被正确复制，在存储过程中使用了last_insert_id()函数，可能会使主库和从库上得到不一致的id等等。

2-2-2：row

  • 优点：在row 模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条被修改。所以row 模式下的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定的情况下的存储过程，函数或触发器的调用和触发无法被正确复制的问题。
  • 缺点：row 模式下所有的执行的语句记录到日志中的时候，都将记录每一行涉及到的数据的修改，会产生大量的日志内容。

2-3：查看binlog

通过如下语句即可查看binlog是否开启

1
2
-- 查看日志是否开启
show variables like 'log_bin%';

20211111192717

通过show master status;可以查看正在写入的日志，查询结果如下：

20211111192836

之后通过mysqlbinlog工具即可查看日志：

20211111193018

未解码的日志看起来像是乱码，实际上这是通过64位编码转换后的内容，使用mysqlbinlog对应的参数即可查看具体的sql内容：

1
2
3
4
5
6
7

# 查看所有
mysqlbinlog --base64-output=decode-rows -v D:\MySQL\data\log.000001
# 基于pos节点如下
mysqlbinlog --base64-output=decode-rows -v --start-position=1267 --stop-position=1489 D:\MySQL\data\log.000001
# 基于时间节点如下：
mysqlbinlog --base64-output=decode-rows -v --start-datetime="2021-11-11 18:01:09" --stop-datetime="2021-11-11 18:01:11" D:\MySQL\data\log.000001

说明：

  • –base64-ouput=decode-rows：代表解码
  • -v：代表换行显示这些语句，如果没有-v依然看不到具体的语句
  • –start-date 代表你要获取日志的开始时间
  • –stop-date 代表你要获取日志的结束数据

通过show variables like 'binlog_format'语句可以查看当前日期类型，statement显示如下：

20211111193759

执行insert语句的日志记录如下：

1
insert into test(id) VALUES(1),(2),(3);

20211115182812

  • SET TIMESTAMP=1350355892/*!*/;BEGIN：开始事务的时间。
  • #at 1309：事件的起点，是以1309字节开始。
  • #211115 18:27:25：事件发生事件。
  • server id 1：服务id。
  • end_log_pos 1421：事件的终点，是以1421字节结束。
  • CRC32 8x8911e1f6：主从复制事件校验。
  • Query：事件类型
  • execTime 0: 花费的时间。
  • error_code=0:错误码。
  • Xid = 152:表示事务被正确地提交了。

执行update语句的日志如下：

1
update test set `value` =1;

20211115182549

执行delete语句的日志如下：

1
delete from test;

20211115184501

可以看到在statement模式下只会记录对应的sql语句，而不会记录影响到的每一行。

通过set global binlog_format='row';可以将日志类型切换为row模式，之后执行同样的三条sql结果如下：

20211111194641

20211111194813

20211111194836

20211111194852

很明显的可以看出statement模式和row模式的区别，在row模式下会记录影响到的每一行，这样可以保证数据的完整性，但是也会造成日志冗余的问题。

3：redo log

当我们想要修改 DB 上某一行数据的时候，InnoDB 是把数据从磁盘读取到内存的缓冲池上进行修改。这个时候数据在内存中被修改，与磁盘中相比就存在了差异，我们称这种有差异的数据为脏页。
InnoDB 对脏页的处理不是每次生成脏页就将脏页刷新回磁盘，这样会产生海量的 IO 操作，严重影响 InnoDB 的处理性能。既然脏页与磁盘中的数据存在差异，那么如果在这期间 DB 出现故障就会造成数据的丢失。为了解决这个问题，redo log 就应运而生了。

redo log 就是存储了数据被修改后的值。当我们提交一个事务时，InnoDB 会先去把要修改的数据写入日志，然后再去修改缓冲池里面的真正数据页。

redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样。

3-1：binlog 和 redo log 比较

  • redo log 是属于 innoDB 层面，binlog 属于 MySQL Server 层面的，这样在数据库用别的存储引擎时可以达到一致性的要求。
  • redo log 是物理日志，记录该数据页更新的内容；binlog 是逻辑日志，记录的是这个更新语句的原始逻辑。
  • redo log 是循环写，日志空间大小固定；binlog 是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。
  • binlog 可以作为恢复数据使用，主从复制搭建，redo log 作为异常宕机或者介质故障后的数据恢复使用。

3-2：MySQL 如何保证 redo log 和 binlog 的数据是一致的？

通过两阶段提交。

两阶段提交：先在 redo log 提交并将其标记为 prepare 状态，修改后提交 binlog，再把 redo log 改为 commit 状态。保证了数据的一致性。

3-2-1：两阶段提交意义

  1. 如果先写 redo log，后写 binlog，假如写入binlog 失败，但是redo log已经写入成功，事务已经完成。恢复后主库根据 redo log 重做，但是 binlog 不存在，复制到从库到出现主从不一致
  2. 如果先写 binlog，后写 redolog，假如写入redo log 失败，但是binlog已经写入成功。恢复后从库根据主库的 binlog 回放数据，但是主库因为 redo log 不存在回滚事务。也会出现主从不一致。

3-2-2：两阶段提交效果

  1. 如果数据库在记录此事务的 binlog 之前和过程中发生崩溃。数据库在恢复后认为此事务并没有成功提交，则会回滚此事务的操作。与此同时，因为在 binlog 中也没有此事务的记录，所以从库也不会有此事务的数据修改。
  2. 如果数据库在记录此事务的 binlog 之后发生 crash。此时，即使是 redo log 中还没有记录此事务的 commit 标签，数据库在恢复后也会认为此事务提交成功（因为在上述两阶段过程中，binlog 写入成功就认为事务成功提交了）。它会扫描最后一个 binlog 文件，并提取其中的事务 ID（xid），InnoDB 会将那些状态为 Prepare 的事务（redo log 没有记录 commit 标签）的 xid 和 binlog 中提取的 xid 做比较，如果在 binlog 中存在，则提交该事务，否则回滚该事务。这也就是说，binlog 中记录的事务，在恢复时都会被认为是已提交事务，会在 redo log 中重新写入 commit 标志，并完成此事务的重做（主库中有此事务的数据修改）。与此同时，因为在 binlog 中已经有了此事务的记录，所有从库也会有此事务的数据修改。

3-3：如果整个数据库的数据都被删除了，那我可以用 redo log 的记录来恢复吗？

不能，因为功能的不同，redo log 存储的是物理数据的变更，如果我们内存的数据已经刷到了磁盘，那 redo log 的数据就无效了。所以 redo log 不会存储着历史所有数据的变更，文件的内容会被覆盖。

4：undo log

undo log 有两个作用：回滚和多版本并发控制(MVCC)。

  • 回滚：事务的原子性(一个事务内的所有语句要么同时成功，要么同时失败)。
  • 多版本并发控制（MVCC）：通过保存数据在某个时间点的快照来实现并发控制的。也就是说，不管事务执行多长时间，事务内部看到的数据是不受其它事务影响的，根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

当执行 rollback 时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。有时候应用到多版本并发控制的时候，也是通过undo log来实现的：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。

4-1：delete/update操作的内部机制

当事务提交的时候，innodb不会立即删除undo log，因为后续还可能会用到undo log，如隔离级别为repeatable read时，事务读取的都是开启事务时的最新提交行版本，只要该事务不结束，该行版本就不能删除，即undo log不能删除。

但是在事务提交的时候，会将该事务对应的undo log放入到删除列表中，未来通过purge来删除。并且提交事务时，还会判断undo log分配的页是否可以重用，如果可以重用，则会分配给后面来的事务，避免为每个独立的事务分配独立的undo log页而浪费存储空间和性能。

通过undo log记录delete和update操作的结果发现：(insert操作无需分析，就是插入行而已)

  • delete操作实际上不会直接删除，而是将delete对象打上delete flag，标记为删除，最终的删除操作是purge线程完成的。
  • update分为两种情况：update的列是否是主键列。
    • 如果不是主键列，在undo log中直接反向记录是如何update的。即update是直接进行的。
    • 如果是主键列，update分两部执行：先删除该行，再插入一行目标行。