01

概述

    日志是记录了MySQL数据库的各种类型活动的数据。DBA可以利用这些日志文件定位故障，优化性能等。

02

分类

    在MySQL中，有4种不同的日志，分别是：错误日志(errorlog)、二进制日志(binlog)、查询日志(log)和慢查询日志(slow query log)。

    2.1  错误日志

    1、概述

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

    2、设置

    查看：

    SHOW VARIABLES LIKE ‘log_error’\G;

    路径设置：

    可以使用—log-error=[file_name]选项来指定mysqld(MySQL服务器)保存错误日志文件的位置。如果没有给定file_name值，mysqld使用错误日志名host_name.err并默认在参数DATADIR(数据目录)指定的目录中写入日志文件。

    在实际应用中，如果数据库启动报错或者crash，或者出现告警信息等，可以通过查看错误日志获取必要的信息。

2.2 二进制日志

    1、概述

    二进制文件记录了对MySQL数据库的所有更新操作(其中还包括执行更新操作的时间等额外信息)，不包括查询和SHOW这类操作(binlog作用就是备份恢复使用的，所以只需要记录修改操作即可)。

    二进制文件默认关闭，需要手动指定参数启动。根据MySQL官方手册的测试数据，开启二进制日志会使性能下降1%，但是考虑到可以使用复制(replication)和point-in-time的恢复，这些性能的损失绝对是可以接受的。

    查看：

    SHOW BINLOG EVENT IN ‘mysqld.00001’\G;

    配置：

    通过参数log-bin[=name]可以开启二进制日志，如果不指定name，默认二进制日志文件名为主机名，后缀名为二进制日志的序列号，所在路径为数据库所在目录(datadir)。

    查看datadir：show variables like ‘datadir’;

    2、参数

    max_binlog_size：指定了单个二进制日志文件最大值，如果超过该值，则产生新的二进制日志文件后缀名+1，并记录到.index文件。

    binlog_cache_size：控制缓冲大小，默认大小32K，基于会话的，因此每开启一个事务就分配一个binlog_cache_size大小的缓存，所以不能设置过大。当一个事务的记录大于binlog_cache_size时，MySQL会把缓冲中的日志写入一个临时文件中，因此该值又不能设置太小。

    sync_binlog：表示每写缓冲多少次就要同步到磁盘。如果设置为1，表示采用同步写磁盘的方式来写二进制日志，这时候写操作不使用操作系统的缓冲来写二进制日志。sync_binlog的默认值为0，如果使用InnoDB存储引擎进行复制，并且想得到最大的可用性，建议将该值设置为ON(对数据库IO系统带来一定影响)。

    binlog-do-db：表示需要写入哪些库的日志，默认为空，表示需要同步所有库的日志到二进制日志。

    binlog-ignore-db：表示需要忽略写入哪些库的日志，默认为空，表示需要同步所有库的日志到二进制日志。

    log-slave-update：如果当前数据库是复制中的slave节点，则它不会将从master取得并执行的二进制日志写入自己的二进制文件中。

    binlog_format：记录二进制日志的格式。在MySQL5.1之前，没有这个参数，所有二进制文件的格式都是基于SQL语句(statement)级别的，因此基于这个格式的二进制日志文件的复制(Replication)和Oracle的逻辑Standby有点类似。

    该值可以设置为STATEMENT、ROW和MIXED。

    1、STATEMENT格式下，记录的是逻辑SQL语句。

    2、ROW格式下，记录表的行更改情况。

    3、MIXED格式下，MySQL默认采用STATEMENT格式进行二进制文件记录，但是在一些情况下会使用ROW格式，这些情况包括：

    1)表的存储引擎为NDB，这时对表的DML操作都会以ROW格式记录；

    2)使用了UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT()等不确定函数；

    3)使用了INSERT DELAY语句；

    4)使用了用户定义函数(UDF)；

    5)使用了临时表(temporary table)。

    此外，binlog_format参数还有对于存储引擎的限制。

    在通常情况下，我们将参数binlog_format设置为ROW，这可以为数据库的恢复和复制带来更好的可靠性，但是这会带来二进制文件大小的增加

    要查看二进制日志文件内容，必须通过MySQL提供的工具mysqlbinlog。对于STATEMENT格式的二进制日志文件，在使用mysqlbinlog后，看到的就是执行的逻辑SQL语句。但是，如果使用ROW格式记录，会发现mysqlbinlog的结果变得“不可读”，其实只要加上参数-v或-vv就能清楚地看到执行的具体信息了(-vv会比-v显示更新的类型)。

    3、作用

    二进制日志的主要作用如下：

    1)恢复(recovery)：某些数据的恢复需要二进制日志，例如，如果需要恢复数据库全量备份的文件，可以通过二进制日志进行point-in-time恢复。

    2)复制(replication)：通过复制和执行二进制日志slave与master进行实时同步。

    3)审计(audit)：用户通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。

    2.3 查询日志

    1、概述

    查询日志记录了所有对MySQL数据库的请求信息，不论这些请求是否得到正确的响应。

    默认文件名为：主机名.log。

    2、区别

     二进制日志与查询日志区别：

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

    2.4 慢查询日志

    1、概述

    慢查询日志记录了所有执行时间超过参数long_query_time(单位：秒，运行时间等于long_query_time的情况不会被记录)设置值并且扫描记录数不小于min_examinied_row_limit的所有SQL语句的日志(注意，获得表锁定的时间不算做指定时间)。

    慢查询日志默认关闭，需要手动开启。

    MySQL5.1开始，支持将慢查询日志记录到表中，这样用户查询就更加方便直观了。慢查询表在MySQL架构下，名称为slow_log。

    2、参数

    set slow_query_log：是否开启慢查询日志，通过set slow_query_log=on;开启。

    long_query_time：阈值。long_query_time默认为10秒，最小为0，从MySQL5.1开始，精度可以到微秒。

    min_examinied_row_limit：允许扫描的最小行数。

    long_queries_not_using_indexes：如果运行SQL未命中索引，则MySQL数据库同样会将这条SQL语句记录到慢查询日志中(这样方便后续优化)。

    MySQL5.6新增一个参数log_throttle_queries_not_using_indexes，用来表示每分钟允许记录到slow log且未命中索引的SQL语句次数。

    long_query_io：将超过指定逻辑IO次数的SQL语句记录到slow_log中，该值默认为100。

    为了兼容原MySQL数据库的运行方式，还添加了参数slow_query_type，用来表示启用slow log的方式。可选的值为：

    0：表示不把SQL语句记录到slow log

    1：表示根据运行时间将SQL语句记录到slow log

    2：表示根据逻辑IO次数将SQL语句记录到slow log

    3：表示根据运行时间及逻辑IO次数将SQL语句记录到slow log

    3、分析

    如果在慢查询日志中搜索，则会非常不方便，分析起来很麻烦，MySQL提供了mysqldumpslow工具帮助分析慢查询日志。

    指令：mysqldumpslow ***-slow.log

    如果希望得到执行时间最长的5条SQL语句，操作如下：

    mysqldumpslow -s -al -n 5 ***-slow.log