前言

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

体验地址：http://www.jnpfsoft.com/?from=infoq