0


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的位置都是按照顺序编排的;


本文转载自: https://blog.csdn.net/zhangcongyi420/article/details/127534633
版权归原作者 小码农叔叔 所有, 如有侵权,请联系我们删除。

“mysql 日志分类详解”的评论:

还没有评论