MySQL 日志(错误日志、二进制日志、查询日志、慢查询日志)

雨点打透心脏的1/2处 2023-10-18 22:00 161阅读 0赞

##  ##

MySQL 中有 4 中不同的日志，分别是错误日志、二进制日志（binlog 日志）、查询日志和，慢查询日志，应该充分利用这些日志对数据库进行各种维护和调优。

#### 一、错误日志 ####

错误日志时 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。

#### 二、二进制日志 ####

二进制日志（binlog） 记录了所有 DDL 语句和 DML 语句，但是不包括数据查询语句。语句以“事件”的形式保存，它描述了数据的更改过程。此日志对于灾难时的数据恢复起着极其重要的作用。

1. 日志的位置和格式

当用 --log-bin\[=file\_name\] 选项启动时，mysqld 开始将数据变更情况写入日志文件。如果灭有给出 file\_name 值，默认名为主机名后面跟“-bin”。如果给出了文件名，但没有包含路径，则文件默认被写入参数 datadir（数据目录）指定的目录。

二进制日志的格式分为 3 种：statement、row、mixed，可以在启动时通过参数 --binlog\_format 进行设置，这 3 种格式的区别如下：

STATEMENT

mysql 5.1 之前的版本都采用这种方式，日志中记录的都是语句（statement），每一条对数据造成修改的sql语句都会记录到日志中，通过 mysqlbinlog 工具，可以清晰的看到每条语句的文本。主从复制时，从库(slave)会将日志解析为原文本，并在从库中重新执行一次。这种格式的优点的日志记录清晰易读，日志量少，对 I/O 影响较小。缺点是在某些情况下 slave 的日志复制会出错。

ROW

mysql 5.1.11 之后，出现了这种新的日志格式，它将每一行的变更记录到日志中，而不是记录sql语句，比如一个简单的跟新sql：  
update emp set name='abc'  
如果是 statement 格式，日志中会记录一行 sql 文本；  
如果是 row 格式，由于是对全表进行更新，也就是每一行记录都会发生改变，如果是一个 100 万行的大表，则日志中会记录 100万条记录的变化情况。日志量大大增加。

这种格式的优点是会记录每一行数据变化的细节，不会出现某些情况下无法复制的情况，缺点是日志量大，对 I/O 影响较大。

MIXED

这是目前 MySQL 默认的日志格式，即混合了 statement 和 row 两种日志。默认情况下采用 statement，但在一些特殊情况下采用 row 来进行记录，比如  
采用 NDB 存储引擎，此时对表的 DML 语句全部采用 row；  
客户端使用了临时表；  
客户端采用了不确定函数，比如 current\_user() 等；

因为这种不确定函数在主从中得到的值可能不同，导致主从数据产生不一致。mixed 格式能尽量利用两种模式的优点，而避开他们的缺点。

**注意：**可以在 global 和 session 级别对 binlog\_format 进行日志格式设置，但一定要谨慎操作，确保从库的复制能够正常进行。

2. 日志的读取

由于日志以二进制方式存储，不能直接读取，需要用 mysqlbinlog 工具来查看，对于 statement 格式的文件可以用工具直接查看，对于 row 格式的要加 -v 或 -vv 参数进行读取。

3. 日志的删除

对于比较繁忙的系统，每天产生大量日志，这些日志如果长时间不清除，将会对磁盘空间带来极大的浪费，因此，需要定期删除日志。

(1) 删除所有日志

执行 “reset master;” 命令将删除所有 binlog 日志，新日志编号从“000001”开始

(2) 删除指定序号之前的日志文件

执行 “purge master logs to 'mysql-bin.'”命令，将删除编号之前的所有日志(不删除命令中指定的文件)

(3) 删除指定日期前的日志

执行 “purge master log before 'yyyy-mm-dd hh24:mi:ss'” 命令将删除指定日期前的所有日志

(4) 修改配置文件，自动删除

在配置文件的\[mysqld\]中设置参数 `expire_logs_days=#`, 此参数的含义是设置日志的过期天数，过了指定的天数后日志将会被自动删除。

4. 其他选项

二进制日志记录了数据的变化过程，对于数据的完整性和安全性起着非常重要的作用。因此，MySQL 还提供了一些其他参数选项来进行更小粒度的管理

\--binlog-do-db=db\_name

该选项告诉主服务器，如果当前的数据库（即 use 选定的数据库）是 db\_name, 应将更新记录到二进制文件中，其他所有么有显式指定的数据库更新将被忽略，不记录在日志中。

\--binlog-ignore-db=db\_name

该选项告诉主服务器，如果当前的数据库(即 use 选定的数据库) 是 db\_name,不应将更新保存到二进制日志中，其他没有显式忽略的数据库都将进行记录。  
如果想记录或忽略多个数据库，可以对上面两个选项分别使用多次。

\--innodb-safe-binlog

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

#### 三、查询日志 ####

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

1. 日志的位置和格式

查询日志和慢查询日志都可以选择保存在文件或者表中，使用参数 --log-output\[=value\]来进行控制，value 值可以是table、file、none 的一个或者多个组合，中间用逗号进行分割，分别表示日志保存在表、文件、不保存在表和文件中，这里的表指的是 mysql 库中的 general\_log (慢查询日志是 slow\_log) 表。  
其中 none 的优先级最高，比如：  
\--log-output = table, file 表示日志可以同时输出到表和文件中  
\--log-output = table, none 由于 none 的优先级高，表示日志不保存在表和文件中。  
如果不显示设置此参数，则默认输出到文件。

可以通过参数 --general\_log\[=\{0|1\}\] 和 --general\_log\_file=file\_name 来进行控制。  
\--general\_log 设置为1或者不带值都可以启用查询日志；设置为0表示关闭查询日志，如果不指定此参数也不会启用。  
如果没有指定 --general\_log\_file=file\_name 的值，且没有显式设置 --log-output 参数，那么日志将写入参数 datadir(数据目录)指定的路径下，默认文件名为 host\_name.log。

这两个参数都是 global 类型，可以在系统启动时或者系统运行时进行动态修改，如果想在 session 级别控制，则通过在 session 中设置参数 sql\_log\_off 为 on 或者 off。

2. 日志的读取

查询日志记录的格式是纯文本，所以可以直接进行读取。

**注意：** log 日志中记录了所有数据库的操作，对于访问频繁的系统，此日志对系统性能的影响较大，建议一般情况下关闭。

#### 四、 慢查询日志 ####

慢查询日志记录了所有执行时间超过参数 long\_query\_time(单位:秒)设置值并且扫描记录数不小于 min\_examined\_row\_limit 的所有 sql 语句的日志(注意：`获得表锁定的时间不算作执行时间`)。long\_query\_time 默认为 10 秒，最小为 0，精度可以到微秒。

在默认情况下，有两类常见语句不会记录到慢查询日志：`管理语句和不使用索引进行查询的语句`。管理语句包括 alter table、analyze table、check table、create index、drop index、optimize table、repair table。如果要监控这两类 sql语句，可以分别通过设置参数  
\--log-slow-admin-statements 和 log\_queries\_not\_using\_indexes 进行控制。

1. 文件位置和格式

通过以下两个参数开启慢查询日志：

(1) --slow\_query\_log\[=\{0|1\}\]

如果不指定值或者指定值为 1 都会打开慢查询；

(2) --slow\_query\_log\_file\[=file\_name\]

指定慢查询日志的路径

**注意：** 可以使用 --log-output 参数来指定日志的输出方式，默认会输出到文件，当然也可以选择输出到表，需要注意的是，如果选择输出到表，则表中的记录只能精确到秒，而日志文件中可以精确到微秒。

2. 日志的读取

和错误日志、查询日志一样，慢查询日志记录的格式也是纯文本，可以被直接读取。  
如果要设置微秒级的慢查询，可以 `set global long_query_time=0.01`

如果慢查询日志中记录内容很多，可以使用 mysqldumpslow 工具对慢查询日志进行分类汇总。对于 sql 文本完全一致，只是变量不同的语句，mysqldumpslow 将会自动视为同一个语句进行统计，变量值用 N 来代替。这个统计结果将大大增加用户阅读慢查询日志的效率，迅速定位系统的 sql 瓶颈。

**注意：** 慢查询日志对于发现应用中有性能问题的 sql 很有帮助，正常情况下，打开此日志并经常查看分析。