一文搞懂 MySQL 日志

前言

MySQL 的日志记录了运行的各种信息,是 MySQL 事务、性能、数据容灾、异常排查等的基础。本文将介绍 MySQL 一些关键日志的作用和原理。


MySQL InnoDB 引擎重要的三个日志:

日志 说明
redo log 重做日志,保证事务的持久性
undo log 回滚日志,来保证事务的原子性
binlog MySQL 的主从复制中同步数据

一、binlog

1. 简介

概述

binlog记录DDL 和 DML语句,但不包括SELECTSHOW 等语句,简单说只要发上了表结构变化或表数据更新,都会产生binlog日志。

特点

undo log是二进制逻辑日志,记录内容是语句的原始逻辑,属于Server层,和引擎无关。只在事务提交时才写入,适用于数据备份和主从复制。

作用

  1. 灾难时的数据恢复;
  2. MySQL 的主从复制。

所在位置

通常默认的MySQL数据目录为/var/lib/mysql

2. 记录格式

日志格式 记录内容
Statement 记录进行数据修改 SQL 语句。
Row 记录每一行的数据变更,占用较多空间。(默认)
Mixed 前两者混合,判断是否可能引起数据不一致:可能则用Row 否则用Statement

3. 写入机制

事务执行过程中,先把日志写到binlog cache
事务提交的时候,再把binlog cache写到binlog文件中。

binlog cache是为了保证一个事务的所有操作能够不被拆开,一次性写入bin log
binlog cache大小受binlog_cache_size参数控制。
binlog cache写入策略受sync_binlog参数控制。

4. 日志操作命令

4.1 查看启动情况

show variables like'%log_bin%';

4.2 日志查看

命令

日志是二进制存储的,无法直接读取,需要通过mysqlbinlog命令查看。

语法

mysqlbinlog [参数选项] logfilename

选项含义

  • -d:指定数据库名称,只列出指定的数据库相关操作。;
  • -o:忽略掉日志中的前n行命令;
  • -v:将行事件(数据变更)重构为SQL语句;
  • -w:将行事件(数据变更)重构为SQL语句,并输出注样信息;

4.3 日志删除

对于比较繁忙的业务系统,每天生成的binlog数据巨大,如果长时间不清除,将会占用大量磁盘空间。可以通过以下几种方式清理日志:

指令 含义
reset master 删除全部日志
purge master logs to 'binlog.xxx' 删除xxx编号之前的日志
purge master logs before 'yyyy-mm-dd hh:mm:ss' 删除引号时间之前产生的日志
show variables like '%binlog_expire_logs_seconds%'; 配置日志过期时间,到期自动删除

二、redo log

1. 简介

概述

redo log,重做日志,记录的是事务提交时数据页的物理修改。

特点

物理日志,InnoDB存储引擎独有的,保证数据的持久性与完整性。记录内容是“在某个数据页上做了什么修改”,在事务过程中是不断写入。
大小是固定的,前面的内容会被覆盖。

2. 写入机制

  1. 当客户端提交数据修改时,会先去Buffer Pool获取数据,若没有则查询出来放入Buffer Pool

  2. 生成redo log放入Redolog Buffer,记录数据页的物理变化,此时redo log的状态是prepare

  3. 事务提交后,将Redolog Buffer中的redo log刷新到磁盘持久化存储,此时redo log的状态是commit

这样即使Buffer Pool中的脏页刷新到磁盘时出错,恢复时也可以通过redo log日志进行重新刷新。

脏页:当内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为“脏页”。

WAL:先写日志,再写磁盘的思想,叫做WAL(Write Ahead Logging)

image.png

3. 对比 binlog

对比维度 redo log bin log
日志类型 物理日志 二进制逻辑日志
写入时机 事务过程中是不断写入 只在事务提交时才写入
位置 InnoDB 磁盘中 MySQL 的 Server 层
空间 固定空间,超出则覆盖 追加写入,可生成多份文件

4. 两阶段提交

了解了上面的binlogredo log以后,你会发现, MySQL在执行更新操作的过程中,一次事务的完成均会记录着两个文件,区别见上面的对比表格。那么问题来了,两个文件到底是哪个先存?以及写入的时机有什么不同?

回答这两个问题之前,需要先考虑另外一个问题,这两个文件能否各存各的,会出问题吗?

答案是:不可以,会出现两个文件中数据不一致的问题,可能导致主从数据库数据不一致

根据redo log的特点,在事务过程中是不断写入,而binlog只在事务提交时才写入,如果我们对某条数据执行了age 更改为 18的操作,此时原 age 为 17,redo log已经写入了数据,而undolog还没写入之前数据库崩溃了。

紧接着数据库重启后进行恢复,主数据库根据redo log恢复数据为age = 18,而从数据库根据binlog日志进行同步age = 17,这时就出现了不一致问题。

接着我们回答一下开始的两个问题,为了避免上述问题的产生,InnoDB存储引擎使用两阶段提交方案:

  1. 生成redo log放入Redolog Buffer,记录数据页的物理变化,此时redo log的状态是prepare

  2. 事务提交后,并且,binlog写入成功后,再将Redolog Buffer中的redo log刷新到磁盘持久化存储,此时redo log的状态commit

  3. 进行数据恢复时,若redo log状态是prepare,则有两种情况:

    1. binlog为空则进行数据回滚;
    2. binlog不为空,代表事务已commit,进行数据恢复,这个一般发生在binlog写入成功,但是redo log更改状态失败时。

三、undo log

1. 简介

概述

undo log,回滚日志,事务执行时,用于记录数据被修改前的信息,在异常发生时,会对已经执行的操作进行回滚。

作用

  1. 异常回滚,保证事务的原子性;
  2. 版本链用于MVCC机制中;

特点

undo log是逻辑日志,可以认为:

  1. delete一条数据时,它会插入一条对应的insert记录;
  2. update一条记录时,它会插入一条对象相反的记录。

当执行回滚时,就可以读取其中的记录进行操作。

分类

  1. 新增时 : 指在insert中产生的日志。这样的记录只对事务本身可见,对其他事务不可见,故可以在事务提交后直接删除。
  2. 修改时:updatedelete中产生的日志。该日志可能要作用于MVCC机制,因此不能在事务提交时就进行删除。提交时放入undo log版本链,使用后删除。

2. 版本链

不同事务或者相同事务对同一条记录进行修改,会使该记录的undo log生成一条记录版本的链表,链表头部是最新的旧记录,链表尾部是最早的旧记录。

隐藏字段 含义
DB_TRX_ID 表示最后一次插入或修改该行的事务 ID
DB_ROLL_PTR 回滚指针,指向该行的 undo log,若该行未被更新,则为空

image.png

上述事务能够看到的版本链上的哪条历史数据,是由MVCCReadView来决定。

四、错误日志

最重要的日志之一,记录了当mysqld.log启动和停止时,以及服务器在运行过程中发生任何严重错误时的相关信息,当数据库出现故障无法使用时,建议先看此日志。

日志默认打开,默认存放目录/var/log/,默认文件名mysqld.log

如果找不到,可执行show variables like '%log_error%'查看。

五、查询日志

该日志记录了客户端所有的操作语句,默认关闭,开启需做以下配置:

  1. 修改/etc/my.cnf文件;
  2. 设置general_log = 1,1 表示开启,0 表示关闭;
  3. 设置日志的文件名,general_log_file = mysql_query.log,未指定默认为host_name.log

六、慢查询日志

该日志记录了所有执行时间超过参数long_query_time,且所记录数不小于min_examined_row_limit的所有 SQL 语句。默认关闭,开启需以下配置(根据所需):

  1. 修改/etc/my.cnf文件;
  2. 设置show_query_log = 1,1 表示开启,0 表示关闭;
  3. 设置long_query_time = 2,未指定默认为 10 秒;
  4. 设置long_show_admin_statements = 1,开启记录执行慢的管理语句;
  5. 设置long_queries_not_using_indexes = 1,开启记录执行较慢且未使用索引的语句;

参考

[1] B 站. 黑马邓老师. MySQL数据库入门到精通.

上一篇:Gin 框架是怎么使用 net http 包的(gin.go)


下一篇:dubbo~javax.validation和jakarta.validation的介绍与排雷