Skip to content

31. MySQL 日志

Log Type Information Written to Log
Error log 在mysqld启动、运行、停止时发生的错误
General query log 建立的客户端连接和从客户端接收到的语句
Binary log 更改数据的语句(也用于复制)
Relay log 从复制主服务器接收的数据更改
Slow query log 执行时间超过 long_query_time 秒的查询语句
DDL log (metadata log) DDL 语句执行的元数据操作

下面介绍四种日志的作用和使用方法，可以充分利用市面上的mysql分析工具根据这些日志对数据库进行各种维护和调优 。

1. 错误日志⚓

它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。

可以用--log-error[=file_name]选项来指定 mysqld（MySQL 服务器）保存错误日志文件的位置（不指定该参数时输出到控制台）。如果没有给定 file_name 值，mysqld 使用错误日志名 host_name.err（host_name 为主机名）并默认在参数 DATADIR（数据目录/var/lib/mysql）指定的目录中写入日志文件。

2. 二进制日志⚓

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。

此日志对于灾难时的数据恢复起着极其重要的作用。

2.1 日志的存放⚓

默认写到/var/lib/mysql目录中的binlog.xxxxxx文件中。也可以使用--log-bin[=file_name]指定。

可通过mysqlbinlog读取日志。

2.2 日志的格式⚓

--binlog-format=name，name有以下三种：

  • ROW：基于行的二进制日志。将每一行的变更记录到日志中，而不是sql语句。即只要某行发生了变化，就将此行记录进去。优点是会记录每一行的数据的变化细节，不会出现某些情况下无法复制的情况。缺点是日志量大，对IO影响较大。
  • STATEMENT：基于sql语句的二进制日志。优点是日志记录清晰易读，日志量少，对IO影响小。缺点是在某些情况下slave的日志复制会出错。
  • MIXED：混合了前两种的日志形式。默认日志格式。默认情况下采用STATEMENT，特殊情况下采用ROW。比如采用NDB引擎时，对表的DML语句全部采用ROW；客户端使用了临时表；客户端使用了不确定函数（CURRENT_USER()等）。该日志格式尽量利用前两者的优点，而避开缺点。

2.3 日志的删除⚓

对于比较繁忙的 OLTP（在线事务处理）系统，由于每天生成日志量大，这些日志如果长时间不清除，将会对磁盘空间带来很大的浪费。

2.3.1 方法 1⚓

利用MySQL的RESET MASTER命令，该命令将删除所有 BINLOG 日志，新日志编号从000001开始。

2.3.2 方法 2⚓

PURGE { BINARY | MASTER } LOGS
    { TO 'log_name' | BEFORE datetime_expr }

该命令将删除xxxxxx编号之前或datetime_expr之前的所有日志。

PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

2.3.3 方法 3⚓

设置参数--expire_logs_days=#，此参数的含义是设置日志的过期天数，过了指定的天数后日志将会被自动删除 。

还有一个相似的参数：--binlog-expire-logs-seconds，默认值是30天，两者同时设置时，该参数具有更高的优先级。

2.4 其他选项⚓

MySQL 还提供了一些其他参数选项来进行更小粒度的管理 。

  • --binlog-do-db=db_name

作用是告知主服务器，如果当前使用的数据库（即USE database语句）是db_name，那么记录更新。

  • --binlog-ignore-db=db_name

与上面的选项相反。两者可同时、多次使用。

  • --innodb-safe-binlog

此选项经常和--sync-binlog＝N（每写 N 次日志就同步磁盘）一起配合使用，使得事务在日志中的记录更加安全。

  • SET SQL_LOG_BIN=0

具有 SUPER 权限的客户端可以通过此语句禁止将自己的语句记入二进制记录。

3. 查询日志⚓

查询日志记录了客户端的所有语句，而二进制日志不包含只查询数据的语句。

关闭和开启日志记录：--general-log[={0|1}]。默认关闭。如果同时使用了选项--log-output=TABLE，那么默认输出到mysql.general_log。

指定日志文件：--general-log-file=file_name。默认值是host_name.log

指定查询日志和慢查询日志输出目的：使用log-output系统变量。

日志的读取：日志是纯文本，可直接读取。

4. 慢查询日志⚓

慢查询日志记录了包含所有执行时间超过参数long_query_time（单位：秒）所设置值的 SQL 语句的日志。**获得表锁定的时间不算作执行时间。 **

关闭和开启日志记录：--slow-query-log[={0|1}]。默认关闭。其他选项可用的前提。

指定日志文件：--slow-query-log-file=name。默认值是hostname-slow.log。其他选项可用的前提。

4.1 日志分析⚓

如果慢查询日志中记录内容很多，可以使用 mysqldumpslow工具来对慢查询日志进行统计。 

mysqldumpslow bj37-slow.log

对于 SQL 文本完全一致，只是变量不同的语句，mysqldumpslow 将会自动视为同一个语句进行统计，变量值用 N 来代替。

通过--log-slow-admin-statements 服务器选项，可以请求将慢管理语句，例如 OPTIMIZE TABLE、ANALYZE TABLE 和 ALTER TABLE 写入慢查询日志。

设置微秒级别：

set global long_query_time=0.01;