浅析MySQL日志体系

一、事务日志

MySQL InnoDB 引擎中，事务日志主要由两个部分组成，分别是Redolog和Undolog。其中Redolog也称为重做日志，用于保证事务的原子性和持久性，在崩溃恢复期间纠正不完整事务写入的数据。而Undolog主要是记录数据改动的前映像信息，用来实现事务回滚和MVCC功能。

1.1、Redo log 的基本概念

Redo日志主要包含了两部分：

1)Redo Log Buffer(日志缓冲区)，是位于内存中的一块区域，主要用于缓存要写入磁盘日志文件中的数据。

2)Redo Log File，位于磁盘上，持久化Log Buffer中的日志数据。建库后，默认会创建名为ib_logfile0和ib_logfile1的两个文件。

为了保证日志的持久性，InnoDB存储引擎通过ForceLog at commit机制实现，即当事务提交时，必须先将该事务的所有日志写入到重做日志中。在InnoDB的默认设置下(innodb_flush_method)，每次将RedoLog Buffer中日志更新到磁盘中的日志文件时，先将重做日志缓冲写入到文件系统缓存，然后调用一次操作系统的fsync操作写入Redolog file，其过程如下图所示。

图 1 log buffer到log file写入过程

针对为了保证严格遵守ACID特性和磁盘IO性能取舍的问题，在MySQLInnoDB引擎中还有一个参数innodb_flush_log_at_trx_commit，用来控制Logbuffer中的日志刷新到磁盘的策略，其策略如下：

1)当设置为1时(default)，事务每次提交时都会将Log buffer中的日志写入操作系统缓存并调用fsync写入到Redo log file中。这样可以保证每一个事务日志不丢失。

2)当设置为0时，并不会在事务提交时一并将Log buffer中的日志 fsync到Redo log file中,而是根据innodb_flush_log_at_timeout(default=1) 参数设置的固定时间频率(s)，定期将log buffer日志持久化到Redo log file。这样就有可能会在异常宕机时，意外丢失一部分redo 日志，造成了部分事务日志丢失。

3)当设置为2时，会在事务提交时将Log buffer中的日志写入到Redo log file，但仅写入文件系统的缓存中，不进行fsync操作。这样也不能完全保证一个事务中所有日志的完整性，如果当系统宕机，也可能会造成没有fsync到磁盘中的日志丢失。

1.2 Undo log 的基本概念

Undo log是与单个读写事务关联的undo日志记录的集合，undolog记录包含关于如何撤消事务对聚簇索引记录的最新更改的信息。Undolog存放在数据库内部的一个特殊段(segment)中，称为undosegment，位于共享表空间内。

Undolog 在数据库中主要实现了两个功能分别是事务的回滚和MVCC。

二、Binlog

Binlog(二进制日志)是MySQLServer层维护的一种二进制日志，主要记录了数据库中所有的DDL操作和DML操作，但Binlog中不会记录SELECT和SHOW这类查询语句，Binlog语句是以”事务”的形式保存在磁盘中。

2.1主要作用

1)复制：在MySQL源端开启Binlog，并向从端发送源端生成的binlog，再由从端回放日志实现与源端数据一致。

2)数据恢复：通过Binlog日志实现指定时间点的数据恢复。

2.2 Binlog日志的格式

在my.cnf文件中设置log_bin=/DIRECTORY_NAME/file_name参数，开启。

Binlog日志主要有三种格式，可以在my.cnf文件中通过–binlog_format参数进行设置。

1)STATEMENT

基于SQL语句模式，日志中记录是执行语句和上下文环境。该模式下生成的日志量较少所以减少了对磁盘IO的影响，提高了性能。

2)ROW

基于行的模式，会将每一行的变化都记录到日志中，相较于statement格式，其日志内容要大很多，对磁盘IO影响较大，但是由于记录了每行数据修改的细节，因此不会发生某些特定情况下从库无法复制的情况发生。

3)MIXED

混合模式，即混合了Statement和Row两种日志格式，是MySQL默认的日志格式。在混合模式下，MySQL会默认使用基于SQL语句的日志记录模式(Statement)，但是在一些特殊的场景下会自动切换到基于行的日志记录模式。

三、Relay log

Relay log(中继日志)是在MySQL主从复制时产生的日志，在MySQL的主从复制主要涉及到三个线程:

1) Log dump线程：向从库的IO线程传输主库的Binlog日志

2) IO线程：向主库请求Binlog日志，并将Binlog日志写入到本地的relay log中。

3) SQL线程：读取Relay log日志，将其解析为SQL语句并逐一执行。

图2

从图2中可以看出，从库的IO线程接收到主库的logdump线程传递的Binlog日志后，会将其写入到本地的一个日志中，这个日志就是Relaylog。在文件目录中，一般由多个host_name-relay-bin.nnnnnn 的日志文件和host_name-relay-bin.index索引文件组成，其中日志文件记录的是事务中修改数据的信息，索引文件记录的是使用过的日志文件信息。

Relaylog日志的格式与Binlog的一致，但是相较于Binlog多了master.info和relay-log.info两个日志(默认存储于数据文件目录中)。master.info主要记录上一次读取到master同步过来的binlog的位置，从节点的连接信息和主节点信息，以及连接master和启动复制必须的所有信息。relay-log.info主要记录了从节点文件复制的进度，下一个事件从什么位置开始，由sql线程负责更新。

四、General Query Log

General QueryLog(通用查询日志)记录了用户的所有操作，包括启动和关闭MySQL服务、增删改语句和查询语句，需要注意查询日志在binlog中是不记录的。默认情况下，该日志是关闭状态。

相关参数如下:

general_log = [0|1] --0关闭，1开启
log-output = [TABLE|FILE|NONE] --设置日志输出对象
general_log_file = [FILENAME] --设置输出日志名称

五、Slow Query Log

Slow QueryLog(慢速查询日志)由SQL语句组成，记录了所有执行时间超过参数log_query_time(秒)且扫描的记录数不小于min_examined_row_limit的所有SQL语句的日志，慢查询日志主要用于查找执行时间较长的语句。

默认情况下，管理语句和不使用索引的查询语句是不会记录在slowquery log中，不过可以通过设置参数log_slow_admin_statements和log_queries_not_using_indexes对这两类语句进行监控。在MySQL中，管理语句被定义为ALTER TABLE，ANALYZETABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE和REPAIRTABLE。

如果从文本日志中搜索一些慢查询的语句，会非常消耗时间，MySQL推荐使用mysqldumpslow工具对慢查询日志进行分类汇总。

相关参数如下:

long_query_time = 10 --默认是10s,最小值是0

slow_query_log[={0|1}] --也可以是布尔值(ON|OFF),默认是0，日志关闭。

slow_query_log_file=file_name --指定慢查询日志文件名称，默认host_name-slow.log

log-output = [TABLE|FILE|NONE] –设置日志输出对象

六、Error log

ErrorLog(错误日志)包含mysqld启动和关闭时间的记录，以及诊断消息，如服务器启动和关闭期间以及服务器运行时发生的错误、警告和注意事项。当数据库出现故障导致无法运行时，可以通过查看该日志进行问题分析。

相关参数如下:

log-error [=file_name] --设置日志名称

总结

MySQL数据库的日志系统是一个较为庞大的体系，本文中仅仅是针对一些常见的日志做了简要的介绍，在使用和参数设置中还有非常多的细节，大家也可以参考官方文档进行详细的学习。