mysql日志那些事

一、Mysql常见日志

MySQL有不同类型的日志文件，用来存储不同类型的日志，分为二进制日志、错误日志、通用查询日志、慢查询日志和中继日志，使用这些日志可以查看MySQL内部发生的事情。 ?

二、慢查询日志（slow query log）

1、慢查询日志介绍

慢查询日志（slow query log）：记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化。

?show variables like '%slow%';

查询慢日志时间参数设置

show variables like '%long_query_time%';

如果需要调整可使用set global long_query_time=1;set long_query_time=1;

?注意：在设置long_query_time时间时，既要设置全局的，还要设置当前session的，但是要想永久保留慢查询时间，要把上面涉及到参数写入到配置文件中，并重启mysql服务。

2、慢查询日志分析工具-mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。
查看mysqldumoslow的帮助信息可以使用mysqldumpslow --help

参数（-s和-t）介绍：

-s 这个是排序参数，可选的有：

? al: 平均锁定时间

? ar: 平均返回记录数

? at: 平均查询时间

? c: 访问次数

? l: 锁定时间

? r: 返回记录

? t: 查询时间

-t n 显示头n条记录。

（1）访问次数最多的20个sql语句

mysqldumpslow -s c -t 20 ?***/slow.log

（2）返回记录集最多的20个sql

mysqldumpslow -s r -t 20 ***/slow.log

（3）按照时间排序的前10条SQL

mysqldumpslow -s t -t 10 ***/slow.log

举例说明查出来字段的意思：

[root@gads04919 bin]# ./mysqldumpslow -s t -t 10 /data/mysql/slow.log

Reading mysql slow query log from /data/mysql/slow.log

Count: 3398704 ?Time=0.15s (505325s) ?Lock=0.00s (1661s) ?Rows=1.0 (3398704), gads[gads]@6hosts

??select count(*) from ac_acctdetail a where a.AC_SERIALNO='S'

Count：这类sql语句执行了多少次。

TIME：执行的最大时间，Time=0.15s (505325s) 最大的执行时间是0.15s，总共花费了505325s。

Rows：单次返回的结果数。3398704次总共返回了3398704条记录。

三、通用查询日志（general query log）

该日志记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。开启会占用大量的磁盘空间，默认是关闭的。??

show variables like '%general%';

四、错误日志

记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护。?

SHOW VARIABLES LIKE 'log_err%';

五、二进制日志（Binlog）

binlog即binary log，二进制日志文件，也叫作变更日志（update log）。它记录了数据库所有执行的DDL和DML等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、show等）。二进制日志应用场景有数据复制和数据恢复。

show variables like '%log_bin%';

log_bin_basename?二进制日志文件名 ：二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句select)语句事件。
log_bin_index?二进制的索引文件名：(文件名后缀为.index)用于记录所有的二进制文件?。

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

1、binlog模式

Statement–基于SQL语句的复制(statement-based replication,SBR)，
Row–基于行的复制(row-based replication,RBR)，
Mixed–混合模式复制(mixed-based replication,MBR)。
（1）Statement
每一条会修改数据的sql都会记录在binlog中。
优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。
缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。另外mysql的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题。

（2）Row
它不记录sql语句上下文相关信息，仅保存哪条记录被修改。
优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题.
缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。

（3）Mixed
在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。
（4）查看binlog_format

show variables like 'binlog_format';

2、binlog日志解析

两种方式

（1）、?由于binlog是二进制文件，所以无法直接使用文本打开，需要使用对应的解析工具才可以查看具体内容。不适合使用大日志场景

show binary events

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
参数解释：
a、IN 'log_name':指定要查询的binlog文件名（不指定就是第一个binlog文件）
b、FROM pos:指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）
c、LIMIT【offset】：偏移量(不指定就是0)
d、row_count :查询总条数（不指定就是所有行）

（2）mysqlbinlog工具
mysqlbinlog是mysql原生自带的binlog解析工具，速度快而且可以配合管道命令过滤数据，适合解析大量 binlog文件，建议使用。

mysqlbinlog常见的选项有一下几个：

a、--start-datetime;从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间

b、--stop-datetime;从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样

c、--start-position;从二进制日志中读取指定position 事件位置作为开始。

d、--stop-position;从二进制日志中读取指定position 事件位置作为事件截至

六、中继日志

中继日志只在主从服务器架构的从服务器上存在。从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫中继日志。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步。搭建好主从服务器之后，中继日志默认会保存在从服务器的数据目录下。?