MySQL日志分类及简介

关于

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

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

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

事务日志

重做日志（redo log）

回滚日志（undo log）

二进制日志（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：（同上）