MySQL日志分类及简介

在任何一种数据库中,都会有各种各样的日志,记录着数据库工作的方方面面,以帮助数据库管理员追踪数据库曾经发生过的各种事件。MySQL也不例外,在MySQL中有4种不同的日志,分别是错误日志、二进制日志( BINLOG日志)、查询日志和慢查询日志,这些日志记录着数据库在不同方面的踪迹。

一、错误日志

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

该日志是默认开启的,默认存放目录为mysql的数据目录( var/lib/mysql ) ,默认的日志文件名为hostname.err ( hostname是主机名)。

查看日志位置指令:

show varialbes like 'log_error%';

第一行log_error就是错误日志,这里是采用的是标准输出直接将错误打印在屏幕上。当然也可以配置把错误日志输出到某个文件。

1.1 错误日志配置

为了方便管理,用户可以根据自己的需求来配置错误日志存储位置和日志级别,配置参数如下:

  • log_error = on|文件路径 是否启用错误日志,on表示开启,文件路径表示指定自定义日志路径
  • log_warnings = 1|0 是否记录warnings信息到错误日志中
1.2 错误日志记录信息
  1. 服务器启动和关闭过程中的信息

未必是错误信息,比如mysql是如何去初始化存储引擎的过程记录在错误日志里等等

2.服务器运行过程中的错误信息

比如sock文件找不到,无法加载mysql数据库的数据文件,如果忘记初始化mysql或data dir路径找不到,或权限不正确等 都会记录在此

3.事件调度器运行一个事件时产生的信息

一旦mysql调度启动一个计划任务的时候,它也会将相关信息记录在错误日志中

4.在从服务器上启动从服务器进程时产生的信息

在复制环境下,从服务器进程的信息也会被记录进错误日志

1.3 删除错误日志

在mysql5.5.7之前:数据库管理员可以删除很长时间之前的错误日志,以保证mysql服务器上的硬盘空间。mysql数据库中,可以使用mysqladmin命令开启新的错误日志。mysqladmin命令的语法如下:

mysqladmin –u root –pflush-logs

二、二进制日志


二进制日志( BINLOG )记录了所有的DDL (数据定义语言)语句和DML (数据操纵语言)语句,但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用, MySQL的主从复制,就是通过该binlog实现的。

二进制日志,默认情况下是没有开启的,需要到MySQL的配置文件中开启,并配置MySQL日志的格式。

Linux下同下MySQL的配置文件默认位置:/usr/my.cnf

日志存放位置:配置时,给定了文件名但是没有指定路径,日志默认写入Mysq的数据目录。

#配置开启binlog日志  ,日志文件的前缀名为 mysqlbin
log_bin=mysqlbin

#配置二进制日志的格式,日志格式有STATEMENT、ROW和MIXED三种
binlog_format=STATEMENT
2.1 binlog日志格式

binlog格式分为: STATEMENT、ROW和MIXED三种,详情如下:

  • STATEMENT

STATEMENT格式的binlog记录的是数据库上执行的原生SQL语句。这种方式有好处也有坏处。

好处就是相当简单,简单地记录和执行这些语句,能够让主备保持同步,在主服务器上执行的SQL语句,在从服务器上执行同样的语句。另一个好处是二进制日志里的时间更加紧凑,所以相对而言,基于语句的复制模式不会使用太多带宽,同时也节约磁盘空间。并且通过mysqlbinlog工具容易读懂其中的内容。

坏处就是同一条SQL在主库和从库上执行的时间可能稍微或很大不相同,因此在传输的二进制日志中,除了查询语句,还包括了一些元数据信息,如当前的时间戳。即便如此,还存在着一些无法被正确复制的SQL。另外一个问题就是基于语句的复制必须是串行化的。这要求大量特殊的代码,配置,例如InnoDB的next-key锁等。并不是所有的存储引擎都支持基于语句的复制。

  • ROW

从MySQL5.1开始支持基于行的复制,也就是基于数据的复制,基于行的更改。这种方式会将实际数据记录在二进制日志中,它有其自身的一些优点和缺点,最大的好处是可以正确地复制每一行数据。一些语句可以被更加有效地复制,另外就是几乎没有基于行的复制模式无法处理的场景,对于所有的SQL构造、触发器、存储过程等都能正确执行。主要的缺点就是二进制日志可能会很大,而且不直观,所以,你不能使用mysqlbinlog来查看二进制日志。也无法通过看二进制日志判断当前执行到那一条SQL语句了。

现在对于ROW格式的二进制日志基本是标配了,主要是因为它的优势远远大于缺点。并且由于ROW格式记录行数据,所以可以基于这种模式做一些DBA工具,比如数据恢复,不同数据库之间数据同步等。

  • MIXED

MIXED是MySQL默认使用的二进制日志记录方式,但MIXED格式默认采用基于语句的复制,一旦发现基于语句的无法精确的复制时,就会采用基于行的复制。比如用到UUID()、USER()、CURRENT_USER()、ROW_COUNT()等无法确定的函数。

2.2 binlog的查看方式

binlog使用直接打开是我们看不懂的一堆“乱码”,我们需要借助工具来分析binlog日志,这个工具即使mysql自带的mysqlbinlog
语法:

mysqlbinlog  binlog文件路径

#一般binlog日志文件都比较大,因此我们会在命令的末尾加上 | more
mysqlbinlog  binlog文件路径   | more

示例:

mysqlbinlog  ./binlog.000001 | more

发现虽然不是乱码了,但是日志中的内容把人看的还是云里雾里的。我们还需要添加一个命令参数:--base64-output=decode-rows -v

mysqlbinlog --base64-output=decode-rows -v  binlog.000001 | more
2.3 binlog日志文件的删除

对于比较繁忙的系统,由于每天生成日志量大, 这些日志如果长时间不清楚,将会占用大量的磁盘空间。下面我们将会讲解几种删除日志的常见方法:

方式一

通过Reset Master指令删除全部binlog日志,删除之后,日志编号,将从xx.000001重新开始。

查询之前,先查询下日志文件:

执行删除日志的指令:

Reset master;

执行之后在查看日志文件:

方式二

执行指令purge master logs to 'mysqlbin.xxxxxx',该命令将会删除指定日志文件之前的所有日志文件

方式三

执行指令purge master logs before 'yyyy-mm-dd hh24:mi:ss',该命令将删除日志为“yyyy-mm-dd hh24:mi:ss”之前产生的所有日志文件。

方式四

在MySQL配置文件中设置参数--expire_logs_days=xxxx,此参数的含义就是设置日志的过期天数,过期之后会自动删除日志。比如设置 –expire_logs_days=3;的含义就是日志文件的过期天数是3天。

三、查询日志

查询日志在MySQL中被称为general log(通用日志),查询日志里的内容不要被"查询日志"误导,认为里面只存储select语句,其实不然,查询日志里面记录了数据库执行的所有命令,不管语句是否正确,都会被记录。

默认情况下,查询日志是没有开启的。如果需要开启查询日志,可以在mysql配置文件中做如下配置:

#该选项用来开启查询日志,可选值 0或1。0表示关闭 ,1表示开启
general_log=1

#设置日志的文件名,默认为host_name.log
general_log_file=file_name

或者也可以使用命令设置:

#也可以设置变量那样更改,1开启(0关闭),即时生效,不用重启,首选当然是这样的了
set global general_log=1
set global log_output='table';

四、慢查询日志

慢查询会导致CPU,IOPS,内存消耗过高。当数据库遇到性能瓶颈时,大部分时间都是由于慢查询导致的。 开启慢查询日志,可以让MySQL记录下查询超过指定时间的语句,之后运维人员通过定位分析,能够很好的优化数据库性能。

慢查询日志记录的慢查询不仅仅是执行比较慢的SELECT语句,还有INSERT,DELETE,UPDATE,CALL等DML操作,只要超过了指定时间,都可以称为"慢查询",被记录到慢查询日志中。

默认情况下,慢查询日志是不开启的,只有手动开启了,慢查询才会被记录到慢查询日志中。当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。
慢查询日志支持将日志记录写入文件。

4.1 开启慢查询日志
#查看慢查询日志是否开启
show variables like '%slow_query_log%';

#开启慢查询日志
set global slow_query_log=1;

#查看慢查询时间阈值,单位:秒
show variables like 'long_query_time%'

#设定慢查询阈值,默认是10s,这里设置的时候单位也是秒
set long_query_time=60

如永久生效需要修改配置文件 my.cnf 中[mysqld]下配置

slow_query_log=1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
long_query_time=3
log_output=FILE
4.2 慢查询日志分析工具—mysqldumpslow

先看一下它的帮助

使用示例:

#得到返回记录集最多的 10 个 SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/test-slow.log

#得到访问次数最多的 10 个 SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/test-slow.log

#得到按照时间排序的前 10 条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log

#另外建议在使用这些命令时结合 | 和 more 使用 ,否则有可能出现刷屏的情况
mysqldumpslow -s r -t 10 /var/lib/mysql/test-slow.log | more

五、事务日志

数据库数据存放的文件称为data file;日志文件称为log file;数据库数据是有缓存的,如果没有缓存,每次都写或者读物理disk,那性能就太低下了。数据库数据的缓存称为data buffer,日志(redo)缓存称为log buffer;既然数据库数据有缓存,就很难保证缓存数据(脏数据)与磁盘数据的一致性。

但是万一数据库发生断电,因为缓存的数据没有写入磁盘,导致缓存在内存中的数据丢失而导致数据不一致怎么办?

为了保证事务的ACID特性,就不得不说MySQL InnoDB引擎的事务日志: 重做日志redo和回滚日志undo

innodb通过force log at commit机制实现事务的持久性,即在事务提交的时候,必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化。

注: 在数据库的世界里,数据从来都不重要,日志才是最重要的,有了日志就有了一切!!!

5.1 Redo日志

redo log包括两部分:

  • 一是内存中的日志缓冲(redo log buffer),该部分日志是易失性的;
  • 二是磁盘上的重做日志文件(redo log file),该部分日志是持久的,并且是事务的记录是顺序追加的,性能非常高(磁盘的顺序写性能逼内存的写性能差不了太多)。

InnoDB使用日志来减少提交事务时的开销。因为日志中已经记录了事务,就无须在每个事务提交时把缓冲池的脏块刷新(flush)到磁盘中。事务修改的数据和索引通常会映射到表空间的随机位置,所以刷新这些变更到磁盘需要很多随机IO。InnoDB假设使用常规磁盘,随机IO比顺序IO昂贵得多,因为一个IO请求需要时间把磁头移到正确的位置,然后等待磁盘上读出需要的部分,再转到开始位置。

InnoDB用日志把随机IO变成顺序IO。一旦日志安全写到磁盘,事务就持久化了,即使断电了,InnoDB可以通过redo日志恢复已经提交的事务。

5.2 Undo日志

undo log有两个作用:提供回滚和多版本控制(MVCC)

在数据修改的时候,不仅记录了redo,还记录了相对应的undo,如果因为某些原因导致事务失败或回滚了,可以借助该undo进行回滚。

undo log和redo log记录物理日志不一样,它是逻辑日志。可以认为当delete一条记录时,undo log中会记录一条对应的insert记录,反之亦然,当update一条记录时,它记录一条对应相反的update记录。

当执行rollback时,就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。有时候应用到行版本控制的时候,也是通过undo log来实现的:当读取的某一行被其他事务锁定时,它可以从undo log中分析出该行记录以前的数据是什么,从而提供该行版本信息,让用户实现非锁定一致性读取。

留言区

还能输入500个字符