MySQL日志分类及简介

关于

MySQL中有六种日志文件，分别是：

事务日志：
1. 重做日志（redo log）
2. 回滚日志（undo log）
二进制日志（binlog）
错误日志（errorlog）
慢查询日志（slow query log）
一般查询日志（general log）
中继日志（relay log）

其中，查询日志中的“查询”，是广义的查询（涉及到数据库查找的诸多操作），而非特指“SELECT”语句。

事务日志

但是万一数据库发生断电，因为缓存的数据没有写入磁盘，导致缓存在内存中的数据丢失而导致数据不一致怎么办？

为了保证事务的 ACID 特性，就不得不说MySQL InnoDB引擎的事务日志: 重做日志（redo log）和回滚日志（undo log）。

InnoDB 通过“force log at commit”机制实现事务的持久性，即：在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的“redo log file”和“undo log file”中进行持久化。

“redo log”与“undo log”
重做日志（redo log）	与回滚日志（undo log）
物理日志（存储更新的数据）	逻辑日志（存储SQL操作语句）
用于：事务持久化（存储脏块数据记录）	用于：提供回滚；（即对事务原子性提供支持）实现 MVCC 的数据行快照。
磁盘中：（不属于任何表空间，而是独立的物理文件）文件名为：“ib_logfileN”（如：ib_logfile0，iblogfile1... iblogfilen）默认情况下，在磁盘上由名为“ib_logfile0”和“ib_logfile1”的两个文件物理表示（以循环方式写入）	磁盘中：位于“系统表空间”（System Tablespace）、“撤消表空间”（Undo Tablespace）、“临时表空间”（temporary tablespace）中文件名为：“.ibd” 撤消日志存在于撤消日志段中，包含在 rollback segments 中。
都在内存中具有缓存（“Log Buffer”中），都在磁盘具有日志文件（但位置不同），都是将日志缓存通过“OS Cache”写入到磁盘日志文件中。见：“InnoDB：InnoDB 体系结构”

在数据修改的时候，不仅记录了 redo log，还记录了相对应的 undo log。
另一说 redo log 属于“Physiological Logging”，见：“逻辑日志与物理日志”。

示例：【】

假设有x、y两个数据，值分别为1,2.

A.事务开始.

B.记录x=1到undolog.

C.修改x=3.

D.记录x=3到redolog.

E.记录y=2到undolog.

F.修改y=4.

G.记录y=4到redolog.

H.将redolog写入磁盘。

I.事务提交

如上：

redo log：用于事务持久化
1. 为了保证持久性，必须在事务提交前将 redo log 持久化。
2. 数据不需要在事务提交前写入磁盘，而是缓存在内存中。
undo log：提供事务回滚
1. I/O 影响：其中“B”、“D”、“E”、“G”均是内存中 log buffer 的操作，只有“H”进行了I/O操作。

关于“事务日志缓存”与“事务日志文件”

数据库数据存放的日志文件称为data file；日志文件称为log file；数据库数据是有缓存的，如果没有缓存，每次都写或者读物理disk，那性能就太低下了。数据库数据的缓存称为data buffer，日志（redo）缓存称为log buffer；既然数据库数据有缓存，就很难保证缓存数据（脏数据）与磁盘数据的一致性。

注意：一般所说的 log file 并不是磁盘上的物理日志文件，而是操作系统缓存中的 log file，官方手册上的意思也是如此（例如：With a value of 2, the contents of the InnoDB log buffer are written to the log file after each transaction commit and the log file is flushed to disk approximately once per second）。

如何将“事务日志缓存写入磁盘”？

为了确保每次日志都能写入到事务日志文件中，在每次将 log buffer 中的日志写入日志文件的过程中都会调用一次操作系统的fsync操作【即“fsync()”系统调用：系统I/O同步函数】。

因为 MariaDB/MySQL 是工作在用户空间的，MariaDB/MySQL 的 log buffer 处于用户空间的内存中。要写入到磁盘上的 log file 中，中间还要经过操作系统内核空间的 os buffer，调用“fsync()”的作用就是将 OS buffer 中的日志刷到磁盘上的 log file 中。

从 log buffer 写日志到磁盘的 log file 中，过程如下：

之所以要经过一层 os buffer，是因为 open 日志文件的时候，open 没有使用 O_DIRECT 标志位，该标志位意味着绕过操作系统层的 os buffer，I/O直写到底层存储设备。不使用该标志位意味着将日志进行缓冲，缓冲到了一定容量，或者显式“fsync()”才会将缓冲中的刷到存储设备。使用该标志位意味着每次都要发起系统调用。【？？？】
比如写abcde，不使用 o_direct 将只发起一次系统调用，使用 o_object 将发起5次系统调用。

何时将“事务日志缓存写入磁盘”？

MySQL 支持用户自定义在 commit 时如何将 log buffer 中的日志刷 log file 中。这种控制通过变量 innodb_flush_log_at_trx_commit 的值来决定。该变量有 3 种值：

这个变量只是控制 commit 动作是否刷新 log buffer 到磁盘。

“innodb_flush_log_at_trx_commit=0”：事务过程中日志一直缓存在 log buffer 中，但事务提交时不会产生“log写操作”，而是 MySQL 内部每秒进行“log写操作”。
- 如果发生 crash，即丢失 1s 内的事务修改操作。
“innodb_flush_log_at_trx_commit=1”：事务每次提交都会产生“log写操作”。【默认】
- 这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，I/O的性能较差。
“innodb_flush_log_at_trx_commit=2”，事务每次提交都仅写入到 os buffer，然后由系统内部每秒调用“fsync()”将 os buffer 中的日志写入到磁盘日志文件中。
- 如果数据库实例 crash，不会丢失 log。【因为内存、系统中缓存还在】
- 但是如果服务器 crash，由于 os buffer 还来不及 fsync 到磁盘文件，所以会丢失这一部分的数据。（因为是服务器宕机，所以内存、系统中缓存也会丢失）

“log写操作”：即“将 log buffer 中的日志写入 os buffer 并调用“fsync()”刷到磁盘日志文件中”。

在主从复制结构中，要保证事务的持久性和一致性，需要对日志相关变量设置为如下：

如果启用了二进制日志，则设置“sync_binlog=1”，即每提交一次事务同步写到磁盘中。
总是设置“innodb_flush_log_at_trx_commit=1”，即每提交一次事务都写到磁盘中。

上述两项变量的设置保证了：每次提交事务都写入二进制日志和事务日志，并在提交时将它们刷新到磁盘中。

重做日志（redo log）

redo log 包括两部分：

内存中的日志缓冲（redo log buffer），该部分日志是易失性的；
磁盘上的日志文件（redo log file），该部分日志是持久的，并且是事务的记录是顺序追加的，性能非常高。（磁盘的顺序写性能逼内存的写性能差不了太多）

作用

redo log 有两个作用：

事务持久化：数据不需要在事务提交前写入磁盘，而是缓存在内存中。一旦日志安全写到磁盘，事务就持久化了。
因为日志中已经记录了事务，如果发生异常，InnoDB 可以重放日志并且恢复已经提交的事务。
减少磁盘I/O：【？？？】

记录形式

redo log 实际上记录数据页的变更，而这种变更记录是没必要全部保存，因此 redo log 实现上采用了大小固定，循环写入的方式，当写到结尾时，会回到开头循环写日志。如下图：

默认情况下，在磁盘上由名为“ib_logfile0”和“ib_logfile1”的两个文件物理表示（以循环方式写入）.

如上图：（顺时针）

“write pos”表示 redo log 当前记录的 LSN (逻辑序列号)位置；【从“write pos”开始写 redo log】
“check point”表示脏块更改记录刷盘后对应 redo log 所处的 LSN (逻辑序列号)位置。【从“check point”开始刷到数据表】
“write pos”到“check point”之间的部分是 redo log 空着的部分，用于记录新的记录；
“check point”到“write pos”之间是 redo log 待刷新的脏块的更改记录。

当“write pos”追上“check point”时，会先推动“check point”向前移动，空出位置再记录新的日志。

NOTE：

因为 redo log 记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志（如 binlog）要快很多。
启动 InnoDB 的时候，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。
重启 InnoDB 时，首先会检查磁盘中数据页的 LSN ，如果数据页的 LSN 小于日志中的 LSN ，则会从“check point”开始恢复。
若在宕机前正处于“check point”的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度，此时会出现数据页中记录的 LSN 大于日志中的 LSN，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。

参数

innodb_log_files_in_group：redo log 文件的个数，默认 2 个，最大 100 个。
- 命名方式如：ib_logfile0，iblogfile1... iblogfilen。
innodb_log_file_size：设置文件大小，默认值为 48M，最大值为 512G。
- 注意最大值指的是整个 redo log 系列文件之和，即（innodb_log_files_in_group * innodb_log_file_size ）不能大于最大值512G。
innodb_log_group_home_dir：文件存放路径。
innodb_log_buffer_size：Redo Log 缓存区，默认 8M，可设置1-8M。
延迟事务日志写入磁盘，把 redo log 放到该缓冲区，然后根据 innodb_flush_log_at_trx_commit 参数的设置，再把日志从 buffer 中刷到磁盘中。
innodb_flush_log_at_trx_commit：【见上面：“何时将‘事务日志缓存写入磁盘’？”】

回滚日志（undo log）

undo log 和 redo log 记录物理日志不一样，它是逻辑日志。可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录。

作用

undo log 有两个作用：

提供回滚；（对事务原子性提供支持）
当执行 rollback 时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚。
多个行版本控制（MVCC）。
当读取的某一行被其他事务锁定时，它可以从 undo log 中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。

存储方式

InnoDB 存储引擎对 undo 的管理采用段的方式。“rollback segment”称为回滚段，每个回滚段中有 1024 个“undo log segment”。

在以前老版本，只支持 1 个 rollback segment，这样就只能记录 1024 个 undo log segment。后来 MySQL 5.5 可以支持 128 个 rollback segment，即支持 128*1024 个 undo log segment。
还可以通过变量“innodb_undo_logs”（5.6 版本以前该变量是 innodb_rollback_segments ）自定义多少个“rollback segment”，默认值为 128。

undo log 默认存放在共享表空间中。

如果开启了“innodb_file_per_table”（独占表空间），将放在每个表的“.ibd”文件中。
在 MySQL 5.6 中，undo 的存放位置还可以通过变量“innodb_undo_directory”来自定义存放目录，默认值为“.”表示datadir。

默认 rollback segment 全部写在一个文件中，但可以通过设置变量“innodb_undo_tablespaces”平均分配到多个文件中。

该变量默认值为 0，即全部写入一个表空间文件。

该变量为静态变量，只能在数据库示例停止状态下修改，如写入配置文件或启动时带上对应参数。但是 InnoDB 存储引擎在启动过程中提示，不建议修改为非 0 的值，如下：

2017-03-31 13:16:00 7f665bfab720 InnoDB: Expected to open 3 undo tablespaces but was able
2017-03-31 13:16:00 7f665bfab720 InnoDB: to find only 0 undo tablespaces.
2017-03-31 13:16:00 7f665bfab720 InnoDB: Set the innodb_undo_tablespaces parameter to the
2017-03-31 13:16:00 7f665bfab720 InnoDB: correct value and retry. Suggested value is 0

二进制日志（binlog）

binlog 用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。

写入性操作：包括，数据库表结构变更（CREATE、ALTER、DROP等）及表数据修改（INSERT、UPDATE、DELETE等），但不包括“SELECT”、“SHOW”等操作。
binlog 是 mysql 的逻辑日志，并且由 Server 层进行记录，使用任何存储引擎的 mysql 数据库都会记录 binlog 日志。

作用

恢复（recovery）：某些数据的恢复需要二进制日志。例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行point-in-time的恢复。【备份】
复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库（一般称为slave或者standby）与一台MySQL数据库（一般称为master或者primary）进行实时同步。【主从复制】
审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击。【？】
事务：【分布式事务】
在开启 binlog 的情况下，为了保证 binlog 与 redo 的一致性，MySQL将采用事务的两阶段提交协议。当MySQL系统发生崩溃时，事务在存储引擎内部的状态可能为“prepared”和“commit”两种。对于“prepared”状态的事务，是进行提交操作还是进行回滚操作，这时需要参考 binlog：
1. 如果事务在 binlog 中存在，那么将其提交；
2. 如果不在 binlog 中存在，那么将其回滚，这样就保证了数据在主库和从库之间的一致性。

binlog 索引文件

为了管理所有的 binlog 文件，MySQL额外创建了一个“base-name.index”文件，它按顺序记录了 MySQL 使用的所有 binlog 文件。如果你想自定义 index 文件的名称，可以设置“log_bin_index=file”参数。

千万不要在 mysqld 运行的时候手动修改 index 文件的内容，这样会使 mysqld 产生混乱。

binlog 开启

如果想开启 binlog（默认关闭），可以在 MySQL 配置文件中通过配置参数“log-bin = [base-name]”启动二进制日志。如果不指定“base-name”，则默认二进制日志文件名为主机名，并以自增的数字作为后缀，例如“mysql-bin.000001”，所在目录为数据库所在目录（datadir）。

当满足下面三种情况时，会创建新的二进制文件（文件后缀会自增）：

文件大小达到“max_binlog_size”参数设置值时。
执行“flush logs”命令。
重启 mysqld 进程。

当文件后缀从 000001 增长到 999999 时，又会回到 000001。

binlog 格式

binlog格式分为三种：

STATEMENT：
基于语句的复制，记录的是数据库上执行的原生SQL语句。
- 优点：
  1. 简单直观地记录和执行这些语句，能够让主备保持同步，在主服务器上执行的SQL语句，在从服务器上执行同样的语句。（通过 mysqlbinlog 工具容易读懂其中的内容）
  2. 二进制日志里的时间更加紧凑，所以相对而言，基于语句的复制模式不会使用太多带宽，同时也节约磁盘空间。
- 缺点：
  1. 同一条SQL在主库和从库上执行的时间可能稍微或很大不相同，因此在传输的二进制日志中，除了查询语句，还包括了一些元数据信息，如当前的时间戳。即便如此，还存在着一些无法被正确复制的SQL。
    例如，使用“INSERT INTO TB1 VALUE(CUURENT_DATE())”这一条使用函数的语句插入的数据复制到当前从服务器上来就会发生变化。
    
    存储过程和触发器在使用基于语句的复制模式时也可能存在问题。
  2. 基于语句的复制必须是串行化的。这要求大量特殊的代码，配置，例如 InnoDB 的 next-key 锁等。
ROW：
基于行的复制。将实际数据记录在二进制日志中，也就是基于数据的复制，基于行的更改。（从 MySQL5.1 开始支持）
- 优点：可以正确地复制每一行数据，几乎没有基于行的复制模式无法处理的场景，对于所有的SQL构造、触发器、存储过程等都能正确执行。
- 缺点：二进制日志可能会很大，而且不直观。（不能使用 mysqlbinlog 来查看二进制日志）
- 但由于其优点远大于缺点，对于ROW格式的二进制日志基本是标配了，在数据恢复、不同数据库之间数据同步等方面十分重要。
MIXED：【MySQ L默认使用的二进制日志记录方式】
（STATEMENT + ROW）默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

比如用到“UUID()”、“USER()”、“CURRENT_USER()”、“ROW_COUNT()”等无法确定的函数。

binlog 相关参数

max_binlog_size：限定单个 binlog 文件的大小（默认1 G）
如果当前 binlog 文件的大小达到了参数指定的阈值，会创建一个新的 binlog 文件作为当前活跃的 binlog 文件，后续所有对数据库的修改都会记录到新的 binlog 文件中。
- 注意：binlog 文件可能会大于 max_binlog_size 参数设定的阈值。【因为一个事务所产生的所有事件必须记录在同一个 binlog 文件中，所以可能导致文件大小大于阈值】
binlog_cache_size：二进制日志缓冲大小（默认32 K）
当使用事务的表存储引擎（如InnoDB存储引擎）时，所有未提交（uncommitted）的二进制日志会被记录到一个缓存中去，等该事务提交（committed）时直接将缓冲中的二进制日志写入二进制日志文件。
- 此外，binlog_cache_size 是基于会话（session）的，也就是说，当一个线程开始一个事务时，MySQL 会自动分配一个大小为 binlog_cache_size 的缓存，因此该值的设置需要相当小心，不能设置过大。当一个事务的记录大于设定的binlog_cache_size时，MySQL会把缓冲中的日志写入一个临时文件中，因此该值又不能设得太小。
通过“SHOW GLOBAL STATUS”命令查看“binlog_cache_use”（使用缓冲写二进制日志的次数）、“binlog_cache_disk_use”（使用临时文件写二进制日志的次数）的状态，可以判断当前binlog_cache_size的设置是否合适。
sync_binlog：“sync_binlog=[N]”中的 N 表示每提交多少个事务就进行 binlog 刷新到磁盘。
1. “sync_binlog=0”，表示刷新 binlog 时间点由操作系统自身来决定，操作系统自身会每隔一段时间就会刷新缓存数据到磁盘；
2. “sync_binlog=1”，表示采用同步写磁盘的方式来写二进制日志，每次事务提交时就会刷新 binlog 到磁盘；
- 如果使用 Innodb 存储引擎进行复制，并且想得到最大的高可用性，需要将此值设置为 1。不过该值为 1 时，确时会对数据库I/O系统带来一定的开销。
  但是，即使将sync_binlog设为1，还是会有一种情况导致问题的发生：当使用 InnoDB 存储引擎时，在一个事务发出 COMMIT 动作之前，由于 sync_binlog 为 1，因此会将二进制日志立即写入磁盘。如果这时已经写入了二进制日志，但是提交还没有发生，并且此时发生了宕机，那么在 MySQL 数据库下次启动时，由于 COMMIT 操作并没有发生，这个事务会被回滚掉。但是二进制日志已经记录了该事务信息，不能被回滚。
  
  对于这个问题，MySQL 使用了两阶段提交来解决的，简单说就是对于已经写入到 binlog 文件的事务一定会提交成功，而没有写入到 binlog 文件的事务就会进行回滚，从而保证二进制日志和 InnoDB 存储引擎数据文件的一致性，保证主从复制的安全。
binlog-do-db & binlog-ignore-db：表示需要写入或者忽略写入哪些库的二进制日志。
默认为空，表示需要同步所有库的日志到二进制日志。
log-slave-update：【主从复制中，从服务器的二进制文件是否更新】
如果当前数据库是复制中的 slave 角色，则它不会将 master 取得并执行的二进制日志写入自己的二进制日志文件中去。如果需要写入，要设置 log-slave-update。
- 如果需要搭建 master–>slave–>slave 架构的复制，则必须设置该参数。
binlog-format：设置二进制日志的记录格式。
log_bin_trust_function_creators：
默认为 OFF，这个参数开启会限制存储过程、Function、触发器的创建。

错误日志（errorlog）

默认情况下，错误日志是开启的，且无法被禁止。

默认情况下，错误日志是存储在数据库的数据文件目录中，名称为“hostname.err”（hostname为服务器主机名）。

错误日志配置

为了方便管理，用户可以根据自己的需求来配置错误日志存储位置和日志级别，配置参数如下:

log_error = on|文件路径：是否启用错误日志，on表示开启，文件路径表示指定自定义日志路径。
log_warnings = 1|0：是否记录 warnings 信息到错误日志中。

错误日志记录信息

错误日志记录了以下几种信息：

服务器启动和关闭过程中的信息。
未必是错误信息，比如 mysql 是如何去初始化存储引擎的过程记录在错误日志里等等
服务器运行过程中的错误信息。
比如 sock 文件找不到，无法加载 mysql 数据库的数据文件，如果忘记初始化 mysql 或 data dir 路径找不到，或权限不正确等都会记录在此
事件调度器运行一个事件时产生的信息。
一旦 mysql 调度启动一个计划任务的时候，它也会将相关信息记录在错误日志中
在从服务器上启动从服务器进程时产生的信息。
在复制环境下，从服务器进程的信息也会被记录进错误日志

错误日志示例

[root@stu18 data]#  tail -20 stu18.magedu.com.err
130813  15:30:50  InnoDB: Starting shutdown...
130813  15:30:51  InnoDB: Shutdown completed;  log sequence number 1630920
130813 15:30:51  [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
130813 15:30:52  mysqld_safe mysqld from pid file /mydata/data/stu18.magedu.com.pid ended
130813 15:30:53  mysqld_safe Starting mysqld daemon with databases from /mydata/data
130813 15:30:54  InnoDB: The InnoDB memory heap is disabled     #禁用了InnoDB memory的堆功能。
130813 15:30:54  InnoDB: Mutexes and rw_locks use GCC atomic builtins #Mutexes（互斥量）和rw_locks（行级锁）是GCC编译的是InnoDB内置的。
130813 15:30:54  InnoDB: Compressed tables use zlib 1.2.3     #默认压缩工具是zlib
130813 15:30:55  InnoDB: Initializing buffer pool, size = 128.0M    #InnoDB引擎的缓冲池（buffer pool）的值大小
130813 15:30:55  InnoDB: Completed initialization of buffer pool
130813 15:30:55  InnoDB: highest supported file format is Barracuda.
130813  15:30:57  InnoDB: Waiting for the  background threads to start
130813 15:30:58  InnoDB: 5.5.33 started; log sequence number 1630920
130813 15:30:58  [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
130813 15:30:58  [Note]   - '0.0.0.0' resolves to  '0.0.0.0';  #0.0.0.0会反解主机名，这里反解失败
130813 15:30:58  [Note] Server socket created on IP: '0.0.0.0'.
130813 15:30:58  [Note] Event Scheduler: Loaded 0 events    #事件调度器没有任何事件，因为没有装载。
130813 15:30:58  [Note] /usr/local/mysql/bin/mysqld: ready for connections. #mysql启动完成等待客户端的请求。
Version:  '5.5.33-log'  socket:  '/tmp/mysql.sock'  port: 3306  Source distribution  #创建一个本地sock用于本地连接。

删除错误日志

在 MySQL 5.5.7 之前：数据库管理员可以删除很长时间之前的错误日志，以保证mysql服务器上的硬盘空间。
mysql数据库中，可以使用mysqladmin命令开启新的错误日志。语法如下：
```
mysqladmin –u root –pflush-logs
```
- 也可以使用登录 mysql 数据库中使用“FLUSH LOGS”语句来开启新的错误日志。

在 MySQL 5.5.7 之后：服务器将关闭错误日志删除功能。只能使用重命名原来的错误日志文件，手动冲洗日志创建一个新的。方式如下：【？】

[root@stu18 data]# mv stu18.magedu.com.err  stu18.magedu.com.err.old
[root@stu18 data]# mysqladmin flush-logs
[root@stu18 data]# ls
hellodb  myclass  mysql-bin.000003  mysql-bin.index           stu18.magedu.com.pid     ibdata1      mysql     mysql-bin.000004  performance_schema    ib_logfile0  mysql-bin.000001  stu18.magedu.com.err           test   ib_logfile1  mysql-bin.000002  stu18.magedu.com.err.old

查询日志（general log）

查询日志在 MySQL中被称为 general log（通用日志）。

查询日志“查询”并不局限于“SELECT”语句，而是在命令操作过程中涉及到数据库的“查询、查找”过程。

所以，查询日志记录了数据库执行的所有命令，不管语句是否正确，都会被记录。比如：

insert 查询为了避免数据冲突，如果此前插入过数据，当前插入的数据如果跟主键或唯一键的数据重复那肯定会报错；
update 时也会查询因为更新的时候很可能会更新某一块数据；
delete 查询，只删除符合条件的数据；

这些操作都会有“查询”过程，因此都会产生查询日志。

在并发操作非常多的场景下，查询信息会非常多，那么如果都记录下来会导致I/O非常大，影响MySQL性能，因此如果不是在调试环境下，不建议开启查询日志功能。

而调试环境下，查询日志的开启有助于帮助我们分析哪些语句执行密集，执行密集的select语句对应的数据是否能够被缓存等。

查询日志配置

“general_log”：控制开启、关闭 MySQL 查询日志。
“general_log_file”：控制查询日志的位置。

判断 MySQL 数据库是否开启了查询日志：

mysql> show variables like '%general_log%';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | OFF                          |
| general_log_file | /var/lib/mysql/DB-Server.log |
+------------------+------------------------------+
2 rows in set (0.00 sec)
 
mysql>

查询日志位置

如果开启了查询日志，参数 log_output 控制着查询日志的存储方式。【“log_output”不仅控制查询日志的输出，也控制着慢查询日志的输出】

“log_output”可以设置为以下 4 种值：

FILE ：表示日志存储在文件中。
TABLE：表示日志存储在 mysql 库中的“general_log”表中。
FILE, TABLE：表示将日志同时存储在文件和“general_log”表中。
- 该值会徒增很多I/O压力，一般不会这样设置。
NONE ：表示不记录日志。
- 即使“general_log”设置为“ON”，如果“log_output”设置为“NONE”，也不会记录查询日志。

查看“log_output”设置：

mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec) 
mysql>

开启/关闭查询日志

开启/关闭查询日志的方法有如下几种：

在配置文件中设置：【不推荐】

# 可以在 my.cnf 里添加，1开启（0关闭），重启服务器生效
# 1、开启
general-log = 1
log_output='table'
# 2、关闭
general-log = 0

重启服务器生效。

通过命令设置：

# 设置变量更改，1开启（0关闭），即时生效
# 1、开启
set global general_log=1
set global log_output='table';
# 2、关闭
set global general_log=0

MySQL 实例重启后，相关配置又恢复到默认值。

修改查询日志名称或位置

先关闭查询日志，修改名称或位置，再重新打开查询日志。如下：

mysql> show variables like 'general_log%';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | ON                           |
| general_log_file | /var/lib/mysql/DB-Server.log |
+------------------+------------------------------+
2 rows in set (0.00 sec)
 
mysql> set global general_log='OFF';
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global general_log_file='/u02/mysql_log.log';
Query OK, 0 rows affected (0.00 sec)
mysql> set global general_log='ON';
Query OK, 0 rows affected (0.02 sec)

如果遇到下面类似问题，这个是因为权限问题导致。

mysql> set global general_log_file='/data/mysql_log.log';ERROR 1231 (42000): Variable 'general_log_file' can't be set to the value of '/data/mysql_log.log'

将对应目录的 owner 修改为 mysql 即可解决问题。如下所示：

[root@DB-Server u02]# chown -R mysql:mysql /data

查看查询日志信息

查看存储在“general_log”表中的查询日志：

mysql> select * from mysql.general_log;
+---------------------+---------------------------+-----------+-----------+--------------+----------------------------------+
| event_time          | user_host                 | thread_id | server_id | command_type | argument                         |
+---------------------+---------------------------+-----------+-----------+--------------+----------------------------------+
| 2017-07-06 12:32:05 | root[root] @ localhost [] |         1 |         1 | Query        | show variables like 'general%'   |
| 2017-07-06 12:32:28 | root[root] @ localhost [] |         1 |         1 | Query        | show variables like 'log_output' |
| 2017-07-06 12:32:41 | root[root] @ localhost [] |         1 |         1 | Query        | select * from MyDB.test          |
| 2017-07-06 12:34:36 | [root] @ localhost []     |         3 |         1 | Connect      | root@localhost on                |
| 2017-07-06 12:34:36 | root[root] @ localhost [] |         3 |         1 | Query        | KILL QUERY 1                     |
| 2017-07-06 12:34:36 | root[root] @ localhost [] |         3 |         1 | Quit         |                                  |
| 2017-07-06 12:34:51 | root[root] @ localhost [] |         1 |         1 | Query        | select * from mysql.general_log  |
+---------------------+---------------------------+-----------+-----------+--------------+----------------------------------+
7 rows in set (0.02 sec)
 
mysql>

查看存储在文件中的查询日志：查找、打开对应的文件即可。

查询日志归档

查询日志归档有以下两种方式：

在 mysql 中操作：

mysql> system mv /var/lib/mysql/DB-Server.log  /var/lib/mysql/DB-Server.log.20170706

mysql> system mysqladmin flush-logs -p

Enter password:

在 shell 中操作：

[root@DB-Server mysql]# mv /var/lib/mysql/DB-Server.log  /var/lib/mysql/DB-Server.log.20170706

[root@DB-Server mysql]# mysqladmin flush-logs -p

Enter password:

慢查询日志（slow query log）

慢查询会导致CPU，IOPS，内存消耗过高。当数据库遇到性能瓶颈时，大部分时间都是由于慢查询导致的。开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，之后运维人员通过定位分析，能够很好的优化数据库性能。

慢查询：（同“查询日志”的“查询”），不仅是执行比较慢的“SELECT”语句，还有“INSERT”，“DELETE”，“UPDATE”，“CALL”等 DML 操作，只要超过了指定时间，都可以称为“慢查询”，被记录到慢查询日志中。

分析工具 pt-query-digest【？？？】

pt-query-digest 是分析MySQL查询日志最有力的工具，该工具功能强大，它可以分析 binlog，Generallog，slowlog，也可以通过“show processlist”或者通过 tcpdump 抓取的 MySQL 协议数据来进行分析，比 mysqldumpslow 更具体，更完善。

以下是使用 pt-query-digest 的示例：

//直接分析慢查询文件
pt-query-digest  slow.log > slow_report.log

该工具可以将查询的剖析报告打印出来，可以分析结果输出到文件中，分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间，次数，占比等，可以借助分析结果找出问题进行优化。

中继日志（relay log）

relay log 是复制过程中产生的日志，很多方面都跟 binary log 差不多，区别是: relay log 是从库服务器I/O线程将主库服务器的二进制日志读取过来记录到从库服务器本地文件，然后从库的 SQL 线程会读取 relay-log 日志的内容并应用到从库服务器上。

relay log相关参数

max_relay_log_size：标记 relay log 允许的最大值。
如果该值为0，则默认值为 max_binlog_size（1G）；如果不为0，则 max_relay_log_size 则为最大的 relay_log 文件大小；
relay_log：定义relay_log的位置和名称。
如果值为空，则默认位置在数据文件的目录，文件名为“host_name-relay-bin.nnnnnn”。
relay_log_index：（同“relay_log”，定义 relay_log 的位置和名称；）
relay_log_info_file：设置“relay-log.info”的位置和名称（“relay-log.info”记录 MASTER 的 binary_log 的恢复位置和 relay_log 的位置）
relay_log_recovery：
当 slave 从库宕机后，假如 relay-log 损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的 relay-log，并且重新从 master 上获取日志，这样就保证了 relay-log 的完整性。

默认情况下该功能是关闭的，将 relay_log_recovery 的值设置为 1 时，可在 slave 从库上开启该功能。
- 建议开启。
relay_log_space_limit：设置中继日志最大限额。
中继日志最大限额用于防止中继日志写满磁盘。
- 此设置存在主库崩溃，从库中继日志不全的情况，不到万不得已，不推荐使用。
sync_relay_log：（与“sync_binlog”作用一样）

1. “sync_binlog=0”，slave 的I/O线程接收到 master 发送过来的 binlog 日志，并不是马上就刷入中继日志里，而是由操作系统决定何时来写入。虽然安全性降低了，但减少了大量的磁盘I/O操作。
2. “sync_binlog=1”，slave 的I/O线程每次接收到 master 发送过来的 binlog 日志都要写入系统缓冲区，然后刷入 relay log 中继日志里。这是最安全的，在崩溃的时候最多会丢失一个事务，但会造成磁盘的大量I/O。

- 默认是 0。
sync_relay_log_info：（同上）

重做日志（redo log）	与回滚日志（undo log）
物理日志（存储更新的数据）	逻辑日志（存储SQL操作语句）
用于：事务持久化（存储脏块数据记录）	用于：提供回滚；（即对事务原子性提供支持）实现 MVCC 的数据行快照。
磁盘中：（不属于任何表空间，而是独立的物理文件）文件名为：“ib_logfileN”（如：ib_logfile0，iblogfile1... iblogfilen）默认情况下，在磁盘上由名为“ib_logfile0”和“ib_logfile1”的两个文件物理表示（以循环方式写入）	磁盘中：位于“系统表空间”（System Tablespace）、“撤消表空间”（Undo Tablespace）、“临时表空间”（temporary tablespace）中文件名为：“.ibd” 撤消日志存在于撤消日志段中，包含在 rollback segments 中。
都在内存中具有缓存（“Log Buffer”中），都在磁盘具有日志文件（但位置不同），都是将日志缓存通过“OS Cache”写入到磁盘日志文件中。见：“InnoDB：InnoDB 体系结构”