Fork me on GitHub

浅谈MySQL日志文件系统

MySQL日志文件系统

日志是mysql数据库的重要组成部分。日志文件中记录着mysql数据库运行期间发生的变化;也就是说用来记录mysql数据库的客户端连接状况、SQL语句的执行情况和错误信息等。当数据库遭到意外的损坏时,可以通过日志查看文件出错的原因,并且可以通过日志文件进行数据恢复。

MySQL日志分类

MySQL日志大致可以分为一下几种类型:

日志类型 日志作用
错误日志 记录启动、运行或停止mysqld时出现的问题。
通用查询日志 记录建立的客户端连接和执行的语句。
二进制日志 记录所有更改数据的语句。还用于复制。
中继日志 只有主从复制的slave服务器才会有,记录的是来自主服务器的数据变化;
慢查询日志 记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。
DDL日志 也叫元数据日志,记录的是通过DDL进行的元数据操作;

错误日志

MySQL的错误日志,默认是开启并无法关闭,一般存放在在数据目录中,以主机名.err格式命名,也可以用 可以用–log-error[=file_name]选项来开启mysql错误日志,该选项指定mysqld保存错误日志文件的位置。错误日志一般是文本文件,可以直接tail打开进行分析查看。

错误日志不仅仅记录着错误信息,它记录的事件有:

  • 服务器启动和关闭过程中的信息
  • 服务器运行过程中的错误信息
  • 事件调度器运行一个事件时产生的信息
  • (如果被配置为从服务器)启动从服务器进程时产生的信息

查看错误日志存放路径:

mysql> SHOW VARIABLES LIKE 'log_error%';
+---------------+---------------------------------+
| Variable_name | Value                           |
+---------------+---------------------------------+
| log_error     | /var/lib/mysql/iZ2319y43bmZ.err |
+---------------+---------------------------------+
1 row in set (0.00 sec)

mysql> 

通用查询日志

通用查询日志记录了数据库执行的命令。而不仅仅只是select语句,当开启查询日志之后,数据库可能需要不停地写入查询日志,会增加很多磁盘I/O,所以除非是出于调试目的,一般不建议开启查询日志。

如果需要可以手动开启。如果开启了查询日志,可以通过如下3种方式输出在查询日志

  • 将查询日志存放于指定的日志文件中。
  • 将查询日志存放于mysql.general_log表中。
  • 将查询日志同时存放于指定的日志文件与mysql库的general_log表中。

查看通用查询日志相关信息:

mysql> show variables where variable_name like '%general_log%' or variable_name='log_output';
+------------------+---------------------------------+
| Variable_name    | Value                           |
+------------------+---------------------------------+
| general_log      | OFF                             |
| general_log_file | /var/lib/mysql/iZ2319y43bmZ.log |
| log_output       | FILE                            |
+------------------+---------------------------------+
3 rows in set (0.00 sec)

log_oupt可以log_output:表示当查询日志开启以后,以哪种方式存放,log_output可以设置为4种值,FILE、TABLE 、FILE,TABLE、NONE。

此值为NONE时表示不记录查询日志,即使general_log设置为ON,如果log_output设置为NONE,也不会记录查询日志,其实,log_output不止控制用户查询日志的输出,慢查询日志的输出也是由此参数进行控制,也就是说,log_output设置为file,就表示查询日志和慢查询日志都存放到对应的文件中,设置为table,查询日志和慢查询日志就都存放在对应的数据库表中。

慢查询日志

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

long_query_time 默认值是 10 ,单位是 s,即默认是 10秒 。默认情况下,MySQL数据库并不会开启慢查询日志,需要手动设置这个参数。

慢查询日志开销比较小,通过对慢查询的分析可以到系统的性能问题,找到相关的sql语句并进一步优化,因为或多或少都有一些性能开销,所以如果不是为了调优,一般建议不用开启。

mysql> SHOW VARIABLES LIKE 'slow_query_log%';
+---------------------+--------------------------------------------------+
| Variable_name       | Value                                            |
+---------------------+--------------------------------------------------+
| slow_query_log      | OFF                                              |
| slow_query_log_file | /usr/local/var/mysql/upstreamdeMac-mini-slow.log |
+---------------------+--------------------------------------------------+

慢查询相关配置

# slow logging
slow_query_log=1    //是否启用慢查询日志,[1 | 0] 或者 [ON | OFF], MySQL 5.6以前此参数应该为log_slow_queries
log-output=FILE   // 指定用文件存储或者用数据表存储,与通用查询日志共用这个参数,日志记录到系统的专用日志表中,要比记录到文件耗费更多的系统资源,因此对于需要启用慢查询日志,又需要能够获得更高的系统性能,那么建议优先记录到文件。

slow_query_log_file  //指定文件存储路径和文件名,一般在数据目录下,默认是/var/lib/mysql/hostname-slow.log。

long_query_time=10    //慢查的时长单位为秒,可以精确到小数点后6位(微秒)。
log_queries_not_using_indexes  //没有使用到索引的语句,是否被记录到慢查询日志中。
log_timestamps      //主要是控制 error log、genera log,等等记录日志的显示时间参数,默认时间戳是utc时间,与北京时间相差8h,一般我们会设置成SYSTEMd为操作系统时间。

慢查询日志分析

慢查询一般用下面两种工具来进行分析:

mysqldumpslow

mysqldumpslow是官方自带的慢查询日志分析工具,用perl编写,可以用mysqldumpslow -h来看看具体用法。

[root@iZ2319y43bmZ ~]# mysqldumpslow  -h
Option h requires an argument
ERROR: bad option

Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
​                al: average lock time
​                ar: average rows sent
​                at: average query time
​                 c: count
​                 l: lock time
​                 r: rows sent
​                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
​               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

[root@iZ2319y43bmZ ~]# 


主要参数:

  • -v、–verbose : 在详细模式下运行,打印有关该程序的更多信息。
  • -d、–debug : 在调试模式下运行。
  • -s [sort_type] : sort_type 是信息排序的依据
    • al:average lock time,按平均等待锁的时间排序
    • ar:average rows sent,按平均发给客户端的行总数排序
    • at:average query time,按平均查询时间排序
    • c:count,按出现总次数排序
    • l:lock time,按等待锁的时间排序
    • r:rows sent,按扫描的行总数排序
    • t:query time,按累计总耗费时间排序
  • -r :倒序信息排序
  • -t NUM :只显示 top n 条查询
  • -g PATTERN : 根据字符串筛选慢查询日志,可写正则匹配,大小写不敏感。
  • -h HOSTNAME : 根据服务器名称选择慢查询日志
  • -i NAME : 根据服务器 MySQL 实例名称选择慢查询日志
  • -l : 不要将总时间减去锁定时间

[root@iZ2319y43bmZ mysql]# mysqldumpslow slowquery_2018052511.log

Reading mysql slow query log from slowquery_2018052511.log
Count: 1  Time=1.23s (3s)  Lock=0.00s (0s)  Rows=143.0 (143), doyd[doyd]@[119.145.8.228]
  show full tables from `kpzs_v2` where table_type = 'S'

[root@iZ2319y43bmZ mysql]# 
 

它主要统计慢查询语句的如下信息:

  • 出现次数(count)
  • 执行最长时间,耗费总时间
  • 等待锁的时间
  • 发送给客户端的行数
  • 执行该语句的客户端地址
  • 执行的原语句

percona-toolkit

percona-toolkit 是一组高级命令行工具的集合,用prel语言编写,需要自行手工安装,安装可以参考官网文档,用来执行各种通过手工执行非常复杂和麻烦的 mysql 和系统任务。percona-toolkit中关于慢查询,主要是pt-query-digest 这个工具,这个工具可以把分析结果输出到表中,做慢查询监控时,可以考虑写脚本去分析慢查询并将结果输出到监控库,设置脚本定期执行即可。如果想直接定位分析,一般pt-query-digest slowquery_yyyymmddhh.log即可

用法:

pt-query-digest [OPTIONS] [FILES] [DSN]

参数说明:

  • –create-review-table : 当使用–review参数把分析结果输出到表中时,如果没有表就自动创建。
  • –create-history-table : 当使用–history参数把分析结果输出到表中时,如果没有表就自动创建。
  • –filter : 对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
  • –limit : 限制输出结果百分比或数量,默认值是20,即将最慢的20条语句输出,如果是50%则按总响应时间占比从大到小排序,输出到总和达到50%位置截止。
  • –host : mysql服务器地址
  • –user : mysql用户名
  • -password : mysql用户密码
  • –history : 将分析结果保存到表中,分析结果比较详细,下次再使用–history时,如果存在相同的语句,且查询所在的时间区间和历史表中的不同,则会记录到数据表中,可以通过查询同一CHECKSUM来比较某类型查询的历史变化。
  • –review : 将分析结果保存到表中,这个分析只是对查询条件进行参数化,一个类型的查询一条记录,比较简单。当下次使用–review时,如果存在相同的语句分析,就不会记录到数据表中。
  • –output : 分析结果输出类型,值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon,一般使用report,以便于阅读。
  • –since : 从什么时间开始分析,值为字符串,可以是指定的某个 “yyyy-mm-dd [hh:mm:ss]” 格式的时间点,也可以是简单的一个时间值:s(秒)、h(小时)、m(分钟)、d(天),如12h就表示从12小时前开始统计。
    –until : 截止时间,配合—since可以分析一段时间内的慢查询。

[root@iZ2319y43bmZ mysql]# pt-query-digest  slowquery_2018060110.log

# 240ms user time, 20ms system time, 24.45M rss, 204.81M vsz
# Current date: Mon Jun  4 10:08:33 2018
# Hostname: iZ2319y43bmZ
# Files: slowquery_2018060110.log
# Overall: 1 total, 1 unique, 0 QPS, 0x concurrency ______________________
# Time range: all events occurred at 2018-06-01 10:49:59
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time             1s      1s      1s      1s      1s       0      1s
# Lock time           75us    75us    75us    75us    75us       0    75us
# Rows sent            143     143     143     143     143       0     143
# Rows examine         143     143     143     143     143       0     143
# Query size            42      42      42      42      42       0      42


# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===========
#    1 0xFBE0E38DA0F69470 1.0037 100.0%     1 1.0037  0.00 SHOW TABLES

# Query 1: 0 QPS, 0x concurrency, ID 0xFBE0E38DA0F69470 at byte 0 ________
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2018-06-01 10:49:59
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count        100       1
# Exec time    100      1s      1s      1s      1s      1s       0      1s
# Lock time    100    75us    75us    75us    75us    75us       0    75us
# Rows sent    100     143     143     143     143     143       0     143
# Rows examine 100     143     143     143     143     143       0     143
# Query size   100      42      42      42      42      42       0      42
# String:
# Databases    kpzs
# Hosts        localhost
# Users        doyd
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `kpzs` LIKE 'kpzs_v2'\G
#    SHOW CREATE TABLE `kpzs`.`kpzs_v2`\G
SHOW /*!50002 FULL*/ TABLES FROM `kpzs_v2`\G
[root@iZ2319y43bmZ mysql]# 





二进制日志文件

二进制日志(binlog)记录 MySQL 数据库中所有与更新相关的操作,即二进制日志记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言)语句,但是不包括数据查询语句。常用于恢复数据库和主从复制。

二进制日志作用

假如备份策略是每天0:00备份一次,那么在本次备份后,下面备份前的这个时间段内某一时刻,数据库崩溃,如果只是依靠备份文件来恢复,中间那段时间的数据就丢失了,所以还需要依靠二进制日志文件来对0:00之后的所有更新相关操作进行replay一次,这样就可以将数据恢复到崩溃时的状态,这与oracle中的归档日志是类似的。

二进制日志类型

  • statement模式:记录对数据库实例的更新语句,例如对语句update A set user=’test’,这种模式只记录该语句,这种模式的优点是日志量小,IO压力小,性能较高,缺点是记录的不够精确,例如,某些sql语句带了一些函数如user(),不同的数据库用户执行sql可能会操作不同的数据,这种情况下,只是记录语句是不够的,在恢复的时候,并不知道该语句当初是哪个用户执行的,所以并不能保证恢复与操作完全一致。

  • row模式:记录对语句所修改到的行,以及对行的具体修改,例如,update A set user=’test’,这条语句所影响到的行,以及修改,都会记录到二进制文件中,如果A表中有1000行数据,那么这一千行数据都会

  • mixed模式:在如下情况,系统会自动从statement模式切换刀片

查看二进制相关配置

mysql> SHOW VARIABLES LIKE 'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
+---------------------------------+-------+
5 rows in set (0.04 sec)

mysql> 

0%