MySQL日志文件

 

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

添加微信免费咨询高性价比云主机信息
微信号:kinnah333

未经允许不得转载:云技术 » MySQL日志文件

赞 (0)