前言

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 ，则有两种情况：

   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. 修改时： 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 ，开启记录执行较慢且未使用索引的语句；

参考

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