MySQL日志详解

概述

1. undo log（回滚日志）：是存储引擎层生成的日志，记录的是事务回滚需要的信息， 其保证了事务的原子性。
2. redo log（重做日志）：是存储引擎层生成的日志，进行数据的故障恢复，实现了事务中的持久性。
3. binlog（二进制日志）：是Service层生成的日志，记录数据库的所有修改操作，用于数据备份和主从复制。
4. error log（错误日志）：记录MySQL启动，运行或停止时出现的问题。
5. slow query log（慢查询日志）：记录执行事件超过long_query_time（可以配置）的所有sql语句。
6. General query log（一般查询日志）：记录所有MySQL服务器的连接信息及所有的SQL语句。

MySQL由Service层和存储引擎层组成。

undo log

undo log（回滚日志）：是存储引擎层生成的日志，记录的是事务回滚需要的信息， 其保证了事务的原子性。

❓问题1：回滚需要哪些信息？

• 在插入一条记录时：将记录的主键值记录即可，后续若要回滚则通过主键值将记录删除。
• 删除一条记录时：将该记录中的内容都记下来，回滚时再将数据插入表中。
• 更新一条记录：将更新的旧值记下来，回滚时将这些列更新为旧值即可。

❓问题2：回滚需要的额外信息

• 产生的undo log都有一个roll_pointer 指针和trx_id。
  1. 通过trx_id可以知道该记录是被哪个事务修改的。
  2. 通过roll_pointer 指针可以将undo log串成一个链表，这个链表被称为版本链。

通过ReadView + undo log 实现MVCC

• MVCC： 多版本并发控制，其维护了数据的多个版本信息。读取当前最新版本—— 当前读；读取历史版本——快照读。通过这种方式，可以实现读写冲突。
• Read View：可以理解成一个数据快照，记录了某时刻的数据。
• ReadView + undo log 如何实现MVCC：undo log保存了一个数据的多个版本，在执行快照读的时候，会顺着undo log的版本链，根据生成的ReadView和undo log的trx_id找到当前快照读可见的版本。

redo log

redo log（重做日志）：是存储引擎层生成的日志，进行数据的故障恢复，实现了事务中的持久性。

❓问题1：MySQL中的数据不是存储在磁盘中吗，为什么还需要故障恢复？

• 回答：因为MySQL会将部分数据放在设计的缓冲池中Buffer Pool。
  1. 没有Buffer Pool： 更新数据时，我们需要从磁盘中读取数据，然后在内存中修改数据，最后将其写回磁盘中。读取数据时，我们同样需要从磁盘中读取数据。非常影响性能。
  2. 加入Buffer Pool： 更新数据时，如果缓冲区中有旧数据，则直接修改缓冲区的数据，然后标记数据所在页为脏页，表明其与磁盘中的数据不一致了，但不会立即将其写入磁盘中。读取数据时，如果内存缓冲区中有数据，则直接读取，没有再从磁盘中读取。
  3. Buffer Pool中信息： Buffer Pool中信息是以页为单位的存储的，其从磁盘中加载数据和往磁盘中写入数据也都以页为单位进行。其中包含数据页，索引页，Undo页（记录Undo log) 等。
  4. Buffer Pool问题： 通过Buffer Pool，我们可以在内存中暂存信息，以提升效率，但这同样会出现数据丢失问题，即上述的脏页丢失。

❓问题2：为什么可以通过redo log解决数据的故障恢复问题，redo log也要写入磁盘，不多余吗？

• 回答：
  1. redo log保存的是追加操作信息，即是物理数据页的修改操作，而不是SQL语句，其写入磁盘是顺序写，而写入数据是随机写，这样性能开销会低很多。
  2. 在对缓冲池的数据修改之后，先将redo log写入磁盘中，这样内存中数据丢失可以通过redo log恢复，保证了事务的持久性。

❓问题3：产生redo log后，是立刻写入磁盘中吗？

• 回答：不是，其会先存入redo log buffer 缓冲区中，写入磁盘时机有以下几种。
  1. 关闭MySQL时。
  2. redo log buffer中的数据大于空间的一半时。
  3. 每隔1秒：最多会丢失1秒数据。
  4. 通过innodb_flush_log_trx_commit参数控制：
     1. 参数为0时：没有这第四种写入磁盘方式。
     2. 参数为1时：每次事务提交时，将缓冲区中的数据写入磁盘中。注：未手动开启事务，将多条命令设置为一个事务时，每条写命令都是一个单独的事务。
     3. 参数为2：每次事务提交时，将缓冲区数据写入内核 Page Cache中，由内核自己控制写入时机。注：写入内核缓冲区，只要不是服务器宕机，即使MySQL进程崩溃，数据也还在。
     4. 三种对比：数据安全层面：1>2>0 性能方面 0>2>1
  5. redo log文件组满的时候（循环往里面添加数据，所以是逻辑上满的时候），即checkpoint追上write pos时。问题4会写。

❓问题4：redo log 是为了保障Buffer Pool中信息安全的，当Buffer Pool中脏页数据写入磁盘后，那么redo log中的数据就没用了，怎么处理这部分信息。

• 回答：InnoDB通过两个大小相同redo log文件组成的redo log group来解决这一问题的。一个文件写满了再写另一个文件，这样循环进行。同时用两个额外信息来进行控制—— write pos表示当前写的位置，checkpoint表示要擦除的位置。
  1. 当脏页数据被写入磁盘中时，cheakpoint往后移动。
  2. 当write pos追上checkpoint时，即两个redo log文件都写满了，这个时候MySQL就暂停所有写操作，等待脏页数据被更新到磁盘中，为redo log文件腾出空间。

binlog

• binlog（归档日志）：是Service层生成的日志，记录数据库的所有修改操作，用于数据备份和主从复制。
• binlog默认关闭，在MySQL配置文件中设置log_bin参数开启：

log_bin = mysql-bin //开启binlog

• binlog的数据格式：
  1. statement（默认）：存储每一条修改命令。命令包含动态函数时，会出现错误。
  2. row：存储修改后的数据。
  3. mixed：上述两个混合，不同的情况使用不同的记录格式。

❓问题1：binlog什么时候写入磁盘中。

• 回答：事务执行过程中，将binlog数据写入binlog cache中，事务提交后，将binlog cache中数据写入文件中（目前数据在内核的page cache中），通过sync_binlog控制写入磁盘时机
  1. 参数为0时（默认），由操作系统觉得什么时候写入磁盘。
  2. 参数为1时，每次提交事务后，马上写入磁盘。
  3. 参数为N时，page cache中存放N个事务binlog信息后，写入磁盘。
  4. 需要同时考虑性能与安全两方面，通常设置为100-1000.

❓问题2：binlog如何实现主从复制。

• 回答：
  1. 保证存储的是数据库全量信息： binlog是追加写，写满一个文件，会再创建一个文件写，存储全量信息。
  2. 将binlog中的数据传输给从库流程： 主库将binlog数据存入从库的中继日志，从库再读取中继日志同步数据。主库不会等待从库读取同步完数据，而是写入中继日志后就返回响应。

❓问题3：主库传输数据是同步还是异步？

• 回答：有多种选择：
  1. 异步复制（默认）：主库上执行完事务后立刻给客户端响应，不会等待binlog复制给全部从库再给客户端响应。
  2. 同步复制：主库等待binlog复制给所有从库后再给客户端响应。
  3. 半同步复制：不等待所有从库响应，只需要部分响应即可。

MySQL为了减少磁盘I/O次数，引入了组提交，当有多个事务提交的时候，将多个binlog刷盘操作合并成一个。

redo log和binlog

两者区别

1. binlog即二进制文件，是service层的日志，对所有存储引擎都可用，用于数据的复制，恢复和备份。redo log是InnoDB引擎层的日志，主要保证事务的持久性。
2. binlog是追加写入的，当文件写满后，会创建新的binlog文件。而redo log是一组文件，使用环形方式写入，旧的日志信息会被覆盖。
3. redo log记录的是物理数据页的修改操作，不是具体的SQL语句，而binlog有多种数据格式，通常记录的是SQL语句或修改后的数据。

两阶段提交问题

• 事务提交后，redo和binlog都要持久化到磁盘，这是两步操作，两者一者发生错误都会造成日志出现不一致问题，从而导致主从复制中主从库数据不一致问题。

  1. 先将redo log写入磁盘中，binlog发生错误：MySQl重启后，通过redo log将记录更新为新值，但从库从binlog获取数据，没有修改，造成主从不一致。
  2. 先将binlog写入磁盘，redo log发生错误：重启后，主库不变，从库被修改，发生主从不一致。

• 为了解决上述问题：使用了两阶段提交：

  1. 提交过程被分为两部分：准备阶段和提交阶段。
  2. 准备阶段：写入redo log，将redo log对应的事务状态修改为prepare。
  3. 提交阶段：写入binlog，将redo log设置为commit。
  4. 当出现上述第二种问题（redo log写入磁盘中，binlog发生错误），从库数据还是因为binlog不变，所以数据不变。而主库会查看redo log对应事务状态
     1. 如果是prepare：则在binlog查看是否有对应记录，有才恢复，没有则不恢复。

优化MySQL磁盘I/O性能

就是让更多的日志一起被写入磁盘。有以下几个可以控制的参数。

1. 控制 redo log缓冲区写入时机，将innodb_flush_log_trx_commit设置为2，即将数据写入内核Page Cache，交由内核控制写入磁盘时机。设置为1时每次事务提交后写入磁盘。
2. 控制 binlog缓冲区写入时机，sync_binlog为1是每次事务提交后写入磁盘，将其设置为较大的数N，表示执行N个事务后将缓冲区数据写入磁盘。
3. 控制组提交参数，延缓binlog写入磁盘时机，让更多的binlog操作合并为一个。

参考资料

https://javabetter.cn/sidebar/sanfene/mysql.html
https://xiaolincoding.com/mysql/log/how_update.html
https://javaguide.cn/database/mysql/mysql-logs.html