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

2

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 很有帮助,正常情况下,打开此日志并经常查看分析。

你可能感兴趣的

载入中...