mysql日志与日志分析

mysql日志与日志分析

MySQL有几种不同的日志文件，通常包括错误日志文件，二进制日志，通用日志，慢查询日志，等等。这些日志可以帮助我们定位mysqld内部发生的事件，数据库性能故障，记录数据的变更历史，用户恢复数据库等等

• a、错误日志：记录启动、运行或停止mysqld时出现的问题。
• b、通用日志：记录建立的客户端连接和执行的语句。
  一般设置为关闭状态，
  show global variables like ‘%general%’;
  set global general_log = on; // 打开
  set global general_log = off; // 关闭
• c、更新日志：记录更改数据的语句。该日志在MySQL 5.1中已不再使用。
• d、二进制日志：记录所有更改数据的语句。还用于复制。
• e、慢查询日志：记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。
• f、Innodb日志：innodb redo log

pt-query-digest

pt-query-digest是用于分析mysql慢查询的一个工具，它可以分析binlog、General log、slowlog，也可以通过SHOWPROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析。可以把分析结果输出到文件中，分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间、次数、占比等，可以借助分析结果找出问题进行优化。

用法介绍： pt-query-digest [OPTION…] [FILE] 解析和分析mysql日志文件
* 使用示例：

范例1：分析本地的慢查询文件 pt-query-digest --user=root --password=zhang@123 /data/dbdata/localhost-slow.log 

• 范例2：重新回顾满查询日志，并将结果保存到query_review中，注意query_review表的表结构必须先建好，
  

  
  表结构如下： CREATE TABLE query_review
  ( checksum BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  fingerprint TEXT NOT NULL,
  sample TEXT NOT NULL,
  first_seen DATETIME,
  last_seen DATETIME,
  reviewed_by VARCHAR(20),
  reviewed_on DATETIME,
  comments TEXT );

命令如下： pt-query-digest --user=root --password=zhang@123 --review h=localhost,D=test,t=query_review /data/dbdata/localhost-slow.log

如果slow log够大的话，会消耗相当多的cpu和内存，所以最好把slow log和pt-query-digest放到其它的server上面运行。[root@test]# ./pt-query-digest slow.log >digest.log常见的用法举例：- (1)直接分析慢查询文件:pt-query-digest slow.log > slow_report.log

• (2)分析最近12小时内的查询：
  pt-query-digest –since=12h slow.log > slow_report2.log

• (3)分析指定时间范围内的查询：
  pt-query-digest slow.log –since ‘2014-04-17 09:30:00’ –until ‘2014-04-17 10:00:00’> > slow_report3.log

• (4)分析指含有select语句的慢查询
  pt-query-digest–filter ‘$event->{fingerprint} =~ m/^select/i’ slow.log> slow_report4.log

• (5) 针对某个用户的慢查询
  pt-query-digest–filter ‘($event->{user} || “”) =~ m/^root/i’ slow.log> slow_report5.log

• (6) 查询所有所有的全表扫描或full join的慢查询
  pt-query-digest–filter ‘((event−>Fullscan||"")eq"yes")||((event->{Full_join} || “”) eq “yes”)’ slow.log> slow_report6.log

• (7)把查询保存到query_review表
  pt-query-digest –user=root –password=abc123 –review h=localhost,D=test,t=query_review–create-review-table slow.log

• (8)把查询保存到query_history表
  pt-query-digest –user=root –password=abc123 –review h=localhost,D=test,t=query_ history–create-review-table slow.log_20140401
  pt-query-digest –user=root –password=abc123–review h=localhost,D=test,t=query_history–create-review-table slow.log_20140402

• (9)通过tcpdump抓取mysql的tcp协议数据，然后再分析
  tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
  pt-query-digest –type tcpdump mysql.tcp.txt> slow_report9.log

• (10)分析binlog
  mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
  pt-query-digest –type=binlog mysql-bin000093.sql > slow_report10.log

• (11)分析general log
  pt-query-digest –type=genlog localhost.log > slow_report11.log