MySQL之日志配置全解

错误日志

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

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

相关配置

  • log-error:错误日志存放路径
  • log-warnings:是否将警告信息也输入错误日志,1代表是(5.7后废弃)
  • log_error_verbosity:(代替log-warnings)错误日志级别
    • 1:errors only
    • 2:errors and warnings
    • 3:errors,warnings and notes

二进制日志

二进制日志包含了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言)语句的事件信息,但是不包括数据查询语句。语句以"事件"的形式保存,所以包含了时间、事件开始和结束位置等信息。此日志对于灾难时的数据恢复起着极其重要的作用,MySQL的主从复制, 就是通过该binlog实现的。

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

mysqld还创建一个二进制日志索引文件,当二进制日志文件滚动的时候会向该文件中写入对应的信息。所以该文件包含所有使用的二进制日志文件的文件名。默认情况下该文件与二进制日志文件的文件名相同,扩展名为'.index'。

相关配置

  • log_bin=[on|off|file_name]:开启binlog,如果没有给定file_name,则默认为datadir下的主机名加"-bin",并在后面跟上一串数字表示日志序列号。
  • binlog_format=[STATEMENT|ROW|MIXED]:指定二进制日志的格式。
    • STATEMENT:该日志格式在日志文件中记录的都是SQL语句(statement),每一条对数据进行修改的SQL都会记录在日志文件中,通过Mysql提供的mysqlbinlog工具,可以清晰的查看到每条语句的文本。主从复制的时候,从库(slave)会将日志解析为原文本,并在从库重新执行一次。
    • ROW:该日志格式在日志文件中记录的是每一行的数据变更,而不是记录SQL语句。比如,执行SQL语句 : update tb_book set status='1' , 如果是STATEMENT 日志格式,在日志中会记录一行SQL文件; 如果是ROW,由于是对全表进行更新,也就是每一行记录都会发生变更,ROW 格式的日志中会记录每一行的数据变更。
    • MIXED:这是目前MySQL默认的日志格式,即混合了STATEMENT 和 ROW两种格式。默认情况下采用STATEMENT,但是在一些特殊情况下采用ROW来进行记录。MIXED 格式能尽量利用两种模式的优点,而避开他们的缺点。
  • log_bin_index = file_name:配置索引文件的名字
  • expire_logs_days:指定自动删除二进制日志的时间,即日志过期时间
  • binlog_do_db :明确指定要记录日志的数据库
  • binlog_ignore_db :指定不记录二进制日志的数据库
  • binlog_cache_size = 32768 : 基于事务类型的日志会先记录在缓冲区,当达到该缓冲大小时这些日志会写入磁盘
  • max_binlog_size :指定二进制日志文件最大值,超出指定值将自动滚动。但由于事务不会跨文件,所以并不一定总是精确。
  • sync_binlog = { 0 | n } :日志何时刷到磁盘
    • 0:不同步,日志何时刷到磁盘由FileSystem决定,这个性能最好。
    • n:每写n次二进制日志事件(不是事务),MySQL将执行一次磁盘同步指令fdatasync()将缓存日志刷新到磁盘日志文件中。Mysql中默认的设置是sync_binlog=0,即不同步,这时性能最好,但风险最大。一旦系统奔溃,缓存中的日志都会丢失。
    • 在Innodb的主从复制结构中,如果启用了二进制日志(几乎都会启用),要保证事务的一致性和持久性的时候,必须将sync_binlog的值设置为1,因为每次事务提交都会写入二进制日志,设置为1就保证了每次事务提交时二进制日志都会写入到磁盘中,从而立即被从服务器复制过去。

日志查看

由于日志以二进制方式存储,不能直接读取,需要用mysqlbinlog工具来查看,语法如下 :

mysqlbinlog log-file

日志删除

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

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

2) 执行指令 purge master logs to 'mysqlbin.******' ,该命令将删除 ****** 编号之前的所有日志。

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

4) 设置参数 --expire_logs_days=# ,此参数的含义是设置日志的过期天数, 过了指定的天数后日志将会被自动删除,这样将有利于减少DBA 管理日志的工作量。

查询日志

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

默认情况下, 查询日志是未开启的。如果需要开启查询日志,需要手动配置。通常不需要开启此日志。

相关配置

  • general_log = {0 | 1} : 是否开启查询日志
  • general_log_file : 查询日志存放路径,不给定路径时默认的文件名以 hostname.log 命名。

慢查询日志

MySQL 的慢查询日志是 MySQL 提供的一种日志记录,它用来记录在 MySQL 中响应时间超过阈值的语句,具体指运行时间超过 long_query_time 值的 SQL,则会被记录到慢查询日志中。

long_query_time 的默认值为 10,意思是运行 10s 以上的语句。默认情况下,Mysql 数据库并不启动慢查询日志,需要我们手动来设置这个参数,当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。

格式大致如下:

# Time: 180118 14:58:37
# User@Host: root[root] @ localhost []  Id:   150
# Query_time: 0.000270  Lock_time: 0.000109 Rows_sent: 0  Rows_examined: 6
SET timestamp=1516258717;
delete from user where User='app';

是哪条语句导致慢查询(sql_text),该慢查询语句的查询时间(query_time),锁表时间(Lock_time),以及扫描过的行数(rows_examined)

相关配置

  • slow_query_log :是否开启慢查询日志,1 表示开启,0 表示关闭。
  • slow-query-log-file:新版(5.6 及以上版本):MySQL 数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件 host_name-slow.log
  • long_query_time :慢查询阈值,当查询时间多于设定的阈值时,记录日志。
  • log_queries_not_using_indexes:未使用索引的查询也被记录到慢查询日志中(可选项)。
  • log_output:日志存储方式。默认值是'FILE',表示将日志存入文件。'TABLE'表示将日志存入数据库,这样日志信息就会被写入到 mysql.slow_log 表中,支持同时记录到文件和数据库,配置的时候以逗号隔开即可。

慢日志分析工具 mysqldumpslow

在实际生产环境中,如果要手工分析日志,查找、分析 SQL,显然是个体力活,MySQL 提供了日志分析工具 mysqldumpslow。

参数:

  • -s 按照那种方式排序
    • c:访问计数
    • l:锁定时间
    • r:返回记录
    • al:平均锁定时间
    • ar:平均访问记录数
    • at:平均查询时间
  • -t 是top n的意思,返回多少条数据。
  • -g 可以跟上正则匹配模式,大小写不敏感。

例:

得到返回记录集最多的 10 个 SQL。

mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

得到访问次数最多的 10 个 SQL

mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

得到按照时间排序的前 10 条里面含有左连接的查询语句。

mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

另外建议在使用这些命令时结合 | 和 more 使用 ,否则有可能出现刷屏的情况。

mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

原创文章,作者:彭晨涛,如若转载,请注明出处:https://www.codetool.top/article/mysql%e4%b9%8b%e6%97%a5%e5%bf%97%e9%85%8d%e7%bd%ae%e5%85%a8%e8%a7%a3/