一文搞懂 MySQL 日志

前言

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

---

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

一、binlog

1. 简介

概述

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

特点

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

作用

1. 灾难时的数据恢复；

2. MySQL 的主从复制。

所在位置

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

2. 记录格式

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数据巨大，如果长时间不清除，将会占用大量磁盘空间。可以通过以下几种方式清理日志：

二、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)。

3. 对比 binlog

4. 两阶段提交

了解了上面的binlog和redo 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，则有两种情况：

   binlog为空则进行数据回滚；

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

三、undo log

1. 简介

概述

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

作用

1. 异常回滚，保证事务的原子性；

2. 版本链用于MVCC机制中；

特点

undo log是逻辑日志，可以认为：

1. 当delete一条数据时，它会插入一条对应的insert记录；

2. 当update一条记录时，它会插入一条对象相反的记录。

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

分类

1. 新增时 ： 指在insert中产生的日志。这样的记录只对事务本身可见，对其他事务不可见，故可以在事务提交后直接删除。

2. 修改时：update或delete中产生的日志。该日志可能要作用于MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log版本链，使用后删除。

2. 版本链

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

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

四、错误日志

最重要的日志之一，记录了当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，开启记录执行较慢且未使用索引的语句；

文章转载自：fuxing．

原文链接：https://www.cnblogs.com/fuxing/p/18222033

体验地址：引迈 - JNPF快速开发平台_低代码开发平台_零代码开发平台_流程设计器_表单引擎_工作流引擎_软件架构