mysql 日志分类详解

前言

对于IT从业人员来说，日志是日常开发和问题排查过程中非常重要的信息，通过日志可以了解到很多有用的信息，很多奇怪的不好定位的问题，往往可以通过分析日志找到答案。

在mysql中，也提供了多种类型的日志，不同类型的日志其作用也不一样，本篇就深入的了解下mysql的各类日志，以及各类日志的使用。

mysql日志分类

按照大类进行划分，mysql的日志主要分为下面几种：

• 二进制日志；
• 错误日志；
• 通用查询日志；
• 慢查询日志；

以上也是大家熟知的4种日志，在mysql8以后，又新增两种日志，中继日志和数据定义语句日志，可以说有这些日志，mysql内部发生的一切事情都可以追溯，几种日志的具体作用如下：

• 慢查询日志：记录了所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化（默认不开启）；
• 通用查询日志：记录了在mysql服务连接的起始时间和终止时间内，以及发送给数据库服务器的所有指令（DML以及DDL），对复原操作场景、定位问题、分析问题以及发现问题，甚至是对数据库操作审计都有很大帮助；
• 错误日志：记录了mysql服务在启动、运行或停止服务过程中出现的问题，便于了解服务器的状态，从而对服务器进行维护；
• 二进制日志：记录了所有更改数据的sql语句，可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的恢复；
• 中继日志：用于主从服务架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作；
• 数据定义语句日志：记录数据定义语句执行的元数据操作；

接下来，将针对主要的几种日志一一做详细的说明

一、错误日志

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

该日志是默认开启的，默认存放目录 /var/log/ ，默认的日志文件名为 mysqld.log，可以通过下面的命令查看mysql的错误日志信息，

show variables like '%log_error%';

 如果需要制定文件名，则需要在my.cnf或者my.ini中做如下配置，修改配置项后，需要重启MySQL服务以生效；

[mysqld]
log-error=[path/[filename]] #path为日志文件所在的目录路径，filename为日志文件名

二、慢查询日志

慢查询日志记录了所有执行的sql中，时间超过参数 long_query_time 设置值并且扫描记录数不小于min_examined_row_limit 的所有的SQL语句的日志，默认不开启，long_query_time 默认为10 秒，最小为 0， 精度可以到微秒。

慢查询日志是很多DBA或开发工程师进行SQL优化和性能分析的重要手段之一，通过慢查询日志可以快速筛选和定位出那些执行比较耗时的SQL；

1、慢查询配置与使用

如果要开启慢查询日志，只需在my.cnf 中配置如下参数：

#开启慢查询日志
slow_query_log=1

#执行时间参数，这个时间可以根据实际需要指定
long_query_time=1

配置完毕上述参数之后，重启mysql服务，使用下面的这条sql进行查询

 由于我们配置的时间是1秒，这里需要3秒才能出结果，可以看到，在目录下生成了慢查询的日志

 我们不妨使用查看下吧，可以发现在日志的末尾，就是慢查询的那条sql语句；

2、慢查询配合索引使用

在日常开发中，为了提升查询性能，通常要对表中的某些高频查询的字段创建索引，默认情况下，慢查询的配置开启之后，是不会记录管理语句的，也不会记录不使用索引进行查找的查询语句，这时候可以使用 log_slow_admin_statements 以及 log_queries_not_using_indexes这两个配置，通过这两个配置，可以输出那些管理相关的sql以及查询未走索引的sql，对于后续给表添加索引也是很好的指导；

可参考如下配置：

#记录执行较慢的管理语句
log_slow_admin_statements =1

#记录执行较慢并且未使用索引的语句
log_queries_not_using_indexes = 1

三、通用查询日志

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

默认情况下，查询日志是不开启的，可以通过下面的语句查看查询日志信息

show variables like '%general%';

如果要开启查询日志，可修改配置文件 /etc/my.cnf ，添加如下内容

#该选项用来开启查询日志 ， 可选值 ： 0 或者 1 ； 0 代表关闭， 1 代表开启
general_log=1

#设置日志的文件名 ， 如果没有指定， 默认的文件名为 host_name.log
general_log_file=zcy.log

开启上面的配置之后，需要重启mysql服务，然后执行一些sql操作，这时可以看到在目录下生成了zcy.log的通用查询日志：

使用tail 命令可以看到操作所产生的sql记录

此后，所有客户端的增删改查操作都会记录在该日志文件之中，长时间运行后，该日志文件将会非常大，因此一般不建议开启这个配置，不然后面可能会影响服务器的性能；

四、二进制日志

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

二进制日志（binlog）可以说是MySQL中及其  重要 的日志了，在日常开发及运维过程中，经常会遇到。 binlog 即 binary log ，二进制日志文件，也叫作变更日志（ update log ）。它记录了数据库所有执行的 DDL 和 DML 等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句 select 、 show 等）

1、二进制日志作用

• 数据恢复；
• 主从复制时使用（数据复制）；

在 MySQL8 版本中，默认二进制日志开启的，可以通过下面的命令查看：

show variables like '%log_bin%';

参数解释：

• log_bin_basename：当前数据库服务器的binlog日志的基础名称(前缀)，具体的binlog文
  件名需要再该basename的基础上加上编号(编号一般从000001开始)；
• log_bin_index：binlog的索引文件，里面记录了当前服务器关联的binlog文件有哪些；

2、日志格式【查看，删除】

默认情况下，二进制格式以行（row）为单位进行保存，如下是默认情况下二进制日志格式的配置

可以通过下面的命令查看日志格式：

show variables like '%binlog_format%';

 

事实上，mysql 提供了多种格式来记录二进制日志，可以根据需要自行调整，不同的格式及特点如下：

日志格式	说明
ROW	基于行的日志记录，记录的是每一行的数据变更（默认）
STATEMENT	基于SQL语句的日志记录，记录的是SQL语句，对数据进行修改的SQL都会记录在日志文件中
MIXED	混合了STATEMENT和ROW两种格式，默认采用STATEMENT，在某些特殊情况下会自动切换为ROW进行记录

如果需要配置二进制日志格式，只需在 /etc/my.cnf 中配置 binlog_format 参数即可

小知识

MySQL 服务 重新启动一次 ，以 “.000001” 为后缀的文件就会增加一个，并且后缀名按 1 递增。即日志文件的 个数与MySQL 服务启动的次数相同；如果日志长度超过了 max_binlog_size 的上限（默认是 1GB ），就 会创建一个新的日志文件；

简单测试

先将目录下的二进制日志删掉，然后执行一条insert和一个update语句，观察下 /var/lib/mysql 目录中日志的变化

然后在该目录下，可以看到重新生成了日志文件

2.1 日志查看

由于日志是以二进制方式存储的，不能直接查看，需要通过二进制日志查询工具 mysqlbinlog 来查
看，具体语法如下：

mysqlbinlog [ 参数选项 ] logfilename

可选项参数说明

1.     -d   指定数据库名称，只列出指定的数据库相关操作；
2.     -o   忽略掉日志中的前n行命令 ；
3.     -v   将行事件(数据变更)重构为SQL语句；
4.     -vv 将行事件(数据变更)重构为SQL语句，并输出注释信息

使用下面的命令查看下最新生成的日志

mysqlbinlog mysql-bin.000001

但是这个看起来比较难受，也很难找到关键的信息，可以使用下面的查看命令快速定位到本次的DML语句

mysqlbinlog -v --base64-output=DECODE-ROWS "/var/lib/mysql/mysql-bin.000001"

注意，使用上面的这种方式查看，其实是展示了mysql内部事件以伪SQL的形式展现了出来，关于mysqlbinlog工具使用技巧还有很多。例如只解析对某个库的操作，或者针对某个时间段内的操作等。下面简单分享几个常用的语句，更多操作可以参考官方文档；

# 可查看参数帮助
mysqlbinlog --no-defaults --help


# 查看最后100行
mysqlbinlog --no-defaults --base64-output=decode-rows -vv mysql-bin.000001 |tail -100


# 根据position查找
mysqlbinlog --no-defaults --base64-output=decode-rows -vv mysql-bin.000001 |grep -A 20 '4939002'

上面这种办法读取出binlog日志的全文内容比较多，不易分辨查看到pos点信息，下面介绍一种更为方便的查询命令

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

参数说明：

• IN 'log_name' ：指定要查询的binlog文件名（不指定就是第一个binlog文件）；
• FROM pos ：指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）；
• LIMIT [offset] ：偏移量(不指定就是0)；
• row_count :查询总条数（不指定就是所有行）；

示例：

show binlog events in 'mysql-bin.000001';

这种查看方式的好处是可以清晰的罗列出完整的操作事件，便于追溯操作人员的行为；

2.2 日志删除

对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果长时间不清除，将会占用大量磁盘空
间以至影响性能。可通过以下几种方式清理日志：

指令	含义
reset master	删除全部 binlog 日志，删除之后，日志编号，将从 binlog.000001重新开始
purge master logs to 'binlog.*'	删除 * 编号之前的所有日志
purge master logs before 'yyyy-mm-dd hh24:mi:ss'	删除日志为 "yyyy-mm-dd hh24:mi:ss" 之前产生的所有日志

当然，也可以在 mysql 的配置文件（my.cnf）中配置二进制日志的过期时间，设置了之后，二进制日志过期会自动删除，具体配置如下：

[mysqld]
binlog_expire_logs_seconds=600

2.3 其他格式日志

上面看到的是row这种格式的日志，除此之外，binlog还有2种格式，分别是Statement和Mixed；

Statement

每一条会修改数据的sql都会记录在binlog中，其优点是不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能；

Mixed

从5.1.8版本之后，mysql 提供了Mixed这种格式，其实就是Statement与Row的结合；

3、日志恢复数据

在某些情况下，比如当发生人力不可控的自然灾难时，而且数据又没有做定时备份的情况下，可以考虑使用二进制日志进行数据恢复，使用 mysqlbinlog 恢复数据的语法如下：

mysqlbinlog [option] filename|mysql –uuser -ppass;

参数说明：

• filename ：是日志文件名；
• option ：可选项，比较重要的两对option参数是--start-date、--stop-date 和 --start-position、--
  stop-position；

option补充说明：

• --start-date 和 --stop-date ：可以指定恢复数据库的起始时间点和结束时间点；
• --start-position和--stop-position ：可以指定恢复数据的开始位置和结束位置；

简单来说就是，使用mysqlbinlog命令来读取filename中的内容，然后使用mysql命令将这些内容
恢复到数据库中；

注意：使用  mysqlbinlog  恢复数据时，必须是编号小的先恢复，例如  mysql-bin.000001必须在mysql-bin.000002之前恢复，这个也很好解释，pos的位置都是按照顺序编排的；