MySQL - 几种 log 的深入解析

数据运维 2024-06-30 大白菜程序猿 手机阅读

前言

MySQL - 几种 log 的深入解析-每日运维

MySQL - 几种 log 的深入解析-每日运维

本篇文章已收录到 GitHub 仓库 https://github.com/logerJava/loger‍

MySQL - 几种 log 的深入解析-每日运维

前面在 MySQL基础概念 中我们有提到几种日志, 本篇文章就来详细了解以下 MySQL 的几种 log, 可能大家会困惑, log 应该只是程序运行的记录, 应该没有什么必要学习, 那么 loger 就出几道 MySQL 数据库的面试题, 我们来带着疑问一起看一下 log 到底在 MySQL 中干了什么 ?

  • 了解过 MySQL 的 ACID, 那么你知道 ACID 在 MySQL 中是怎么实现的吗 ?

  • MySQL 在写入数据时, 先写入内存, 再写入磁盘, 那么如果在内存数据还未同步到磁盘时掉电了, MySQL 会怎么处理 ?

  • 在工作中一般怎么确认生产环境的慢 SQL ?

常见的 log

MySQL - 几种 log 的深入解析-每日运维

在 MySQL基础概念 loger 说认为有用的只有四种日志, 但是本篇文章既然是讲解日志, 那么肯定不能只讲四种, 下面是 MySQL 的七种日志, 我会有侧重的逐一分析

  • 重做日志 (redo log)

  • 回滚日志 (undo log)

  • 二进制日志 (binlog)

  • 错误日志 (errorlog)

  • 慢查询日志 (slow log)

  • 一般查询日志 (general log)

  • 中继日志 (relay log)

redo log

MySQL - 几种 log 的深入解析-每日运维

1

redo log 的概念

在了解 redo log 之前我们先要补充一个概念知识 :

  • MySQL 是按页为单位来读取数据的，一个页里面有很多行记录，从内存刷数据到磁盘，也是以页为单位来刷

现在我们考虑一个场景, 在数据库执行如下 SQL 语句 :

    UPDATE student SET name = 'loger' WHERE studentId = 3

    在执行这条 SQL 语句时, MySQL 的操作如下 :

    • 首先判断内存中有没有 studentId = 3 的数据

    • 如果没有就去磁盘找到这条数据所在的页, 将整页数据加载到内存

    • 然后找到 studentId = 3 的行数据, 将内存中的 name 修改为 loger

    此时, 就会发现数据存在不一致的情况, 内存中的数据为正确数据, 修改过后的新的数据, 而磁盘中的数据为未修改的旧的数据, 这时磁盘对应的页的数据称为 脏页

    回到上面提到的第二题, 此时如果掉电了, 怎么办 ? 掉电了数据就没了, 那数据不就不一致了

    基于这种场景, MySQL 的解决方案是, 将你对页的操作, 修改了什么内容, 记录下来, 保存到磁盘, 也就是这里要讲的 redo log 中

    在 redo log 写入成功后, MySQL 就认为事务已经提交成功了, 数据已经持久化, 并会在空闲时间, 将内存数据刷入磁盘

    如果此时掉电, 那么解决起来也简单了, 只需要在重启后将 脏页 数据加载到内存中, 然后利用 redo log 脏页就会修正了

    没错, 我已经猜到了, 伙伴们, 你一定会独立思考然后提出这个问题, 如果我写入内存成功了, 但是 redo log 刷到磁盘失败了呢 ? 这个疑问先留着一会解决

    2

    redo log 如何存储

    我们可以查阅 MySQL 的官方文档 :

    MySQL - 几种 log 的深入解析-每日运维

    我们可以大致分析出如下几个点 :

    • redo log 记录了 SQL 语句以及其他 API 对表产生的变化, 也就是物理变化

    • redo log 存储在磁盘, 用于 crash recovery 后修正数据, 也就是处理宕机, 掉电等问题

    • redo log 默认有两个文件

    • redo log 采取循环写方式 (circular)

    也就是说实际上 redo log, 默认是在 ib_logfile0 和 ib_logfile1 循环来回写的, 那么既然用了默认这个词肯定就有下文了, 冷知识讲解 :

    • innodb_log_file_size：设置每个 redo log 文件的大小，默认是 50331648 byte，也就是 48 MB

    • innodb_log_files_in_group：设置 redo log 文件的数量，默认是 2，最大值是 100

    还有一件事, redo log 在写入磁盘时并不是随机 I/O 的, 而是顺序 I/O 所以写入速度很快, 而 redo log 文件体积又很小, 恢复速度很快

    来, 现在回手掏一下, 事务有 ACID 的四个特性, 那么回顾上面讲的 redo log, InnoDB 就是利用 redo log 来实现持久性的

    binlog

    MySQL - 几种 log 的深入解析-每日运维

    1

    binlog 的概念

    还是上面的例子 :

      UPDATE student SET name = 'loger' WHERE studentId = 3

      在执行这条 SQL 语句时, 其实仅仅生成了 redo log, 还生成了 binlog

      binlog 的概念 : binlog 记录了数据库表结构和表数据变更, 比如 insert, delete, update, create 等, 不会记录查询

      不同于 redo log, binlog 是 MySQL Server 层的功能, 而 redo log 则是 InnoDB 存储引擎的功能, 也就是说, redo log 只要使用了 InnoDB 作为存储引擎就会有, 而 binlog 只要使用的是 MySQL 就会有

      2

      binlog 的作用

      MySQL 既然将 binlog 放在了 Server 层, 那么就代表 binlog 提供了通用的能力, 主要有两个作用数据恢复, 主从复制

      想一想你们 DBA 平时是怎么恢复数据, 还原数据的, 我们回到 binlog, 是不是只需要找到前一时间点的 binlog 重放一下就可以还原了

      在主从复制的场景也是同一个原理, master 将 binlog 发送给 slave, slave 执行 binlog 那么就复制了

      redo log 与 binlog

      MySQL - 几种 log 的深入解析-每日运维

      通过上面的分析, 我们已经了解了这两种 log, 大家会发现, redo log 和 binlog 都可以作为恢复手段, 但其实他们的细节部分还是不一样的, 我们来看一下他们的区别

      1

      存储内容不同

      binlog 记录的是 insert, delete, update, create 等 SQL 语句, 而 redo log 记录的是物理修改内容

      可以理解为, binlog 记录的是逻辑变化, redo log 记录的是物理变化

      2

      功能不同

      • redo log 写入内存, 如果数据库宕了, 我们可以通过 redo log 恢复内存还没有刷盘的数据, 可以恢复宕掉之前的内存数据

      • binlog 可以保持主从一致性, 如果整个数据库都被删除了, binlog 存储着所有数据的变更情况, 可以通过回放 binlog 进行恢复

      需要注意的是, 如果整个数据库都被删除了, redo log 是无法恢复的, 因为 redo log 并不会记录历史的所有数据, 文件内容会被覆盖

      3

      写入细节

      刚才有提到 redo log 和 binlog 都会在执行 update 的时候写入, 那么他应该是怎么写入的呢 ?

      • 写入 : redo log (prepare)

      • 写入 : binlog

      • 写入 : redo log（commit)

      为什么写入 redo log 需要两段式提交, 而不是一次性写入呢 ? 我们可以分为两种情况分析一下

      先写入 redo log, 再写入 binlog

      • 如果 redo log 写入成功, 写入 binlog 失败了, 此时出现宕机情况, 我们根据上面的知识, master 库采用 redo log 恢复数据, 但是因为 binlog 写入失败了, 没有 binlog, 那么从库就没有这些数据, 导致主从不一致现象

      先写入 binlog, 再写入 redo log

      • 与上面的类似, 只不过会反过来, binlog 存在而 redo log 不存在, 会导致从库的数据是最新的, 而主库出现问题

      两段式提交 :

      • 先写入 redo log, 如果失败了, 回滚, 不再继续写入 binlog

      • 如果 redo log 写入成功, binlog 写入失败, 回滚, 删除无效的 binlog

      • 只有当 redo log 和 binlog 都写入成功了, 此次事务才会提交

      也就是说, MySQL 需要保证 redo log 和 binlog 是一致的

      undo log

      MySQL - 几种 log 的深入解析-每日运维

      undo log 主要有两个作用 : 回滚, 多版本控制(MVCC)

      这里简单讲解一下 undo log 的作用, 详细的会放到 MVCC 篇去讲解, 因为多版本控制其实还是挺复杂的, 要讲很多东西

      undo log 实际上存储的也是逻辑日志, 在执行 update 时, 不仅记录上面的两个日志, undo log 也会进行记录, 可以这样理解, 比如你进行了插入操作, 那么 undo log 就会记录一条删除操作, 如果你将 A 修改为了 B, 那么 undo log 里面就会记录一条 B 修改为 A 的记录

      目的就是为了保证回滚, 这些 undo log 存储的记录就相当于之前 SQL 前的一个版本, 回滚时直接返回上一个版本, 就一点毛病没有

      errorlog

      MySQL - 几种 log 的深入解析-每日运维

      从名字就可以看出来, 是错误日志, 记录着 MySQL 启动, 运行期间, 停止时的错误相关信息, 默认情况下这个是关闭的

      我们可以指定 errorlog 的输出路径 :

      • 编辑 my.cnf 写入 log-error = [path]

      • 通过命令参数错误日志 mysqld_safe –user=mysql –log-error=[path] &

      general query log

      MySQL - 几种 log 的深入解析-每日运维

      普通查询日志, 记录了 MySQL 接收到的所有查询或命令操作, 无论是正确还是错误的都会进行记录, 因为记录的比较频繁, 产生开销较大所以默认是关闭的

      slow query log

      MySQL - 几种 log 的深入解析-每日运维

      慢查询日志记录的是执行时间超过 long_query_time 和没有使用索引的查询语句, 只记录成功的语句

      相关参数 :

      • slow_query_log : 1. 开启; 0. 关闭

      • long_query_time : 慢查询阈值

      • log_output : 输出方式

      我们可以通过如下方式配置慢查询 :

        show variables like '%slow_query_log%';
        set global slow_query_log=1;
        show variables like '%slow_query_log%';

        要注意的是, 此处修改只对当前数据库生效, 在 MySQL 重启后会失效, 如果需要配置永久生效需要修改 my.cnf 文件

        参考

        MySQL - 几种 log 的深入解析-每日运维

        • MySQL 是如何实现 ACID 中的 D 的？- 柳树

          https://zhuanlan.zhihu.com/p/98778890

        结尾

        MySQL - 几种 log 的深入解析-每日运维

        相信看了上面的各种日志讲解, 大家已经对前言中提出的问题有了答案, 本篇文章讲解了 MySQL 的 log 问题, 希望大家有所收获

        我是 loger, 扫描下方二维码, 更多程序员知识分享等你来看, 每次打开都有新收获, 不妨来关注一下哦, 兄弟们 👍

        MySQL - 几种 log 的深入解析-每日运维MySQL - 几种 log 的深入解析-每日运维MySQL - 几种 log 的深入解析-每日运维