mysql慢查询可视化_Anemometer基于pt-query-digest将MySQL慢查询可视化

18688206300

于 2021-02-08 09:31:56 发布

阅读量257

点赞数

文章标签： mysql慢查询可视化

本文链接：https://blog.csdn.net/weixin_28785509/article/details/113951129

版权

本文介绍了如何利用Anemometer和pt-query-digest工具，配合MySQL慢查询日志，实现慢查询的可视化分析。在两台MariaDB服务器上配置Anemometer，设置权限，然后通过pt-query-digest分析日志并导入到数据库。最后，通过Nginx前端展示分析结果，并提供了自动化脚本和日志切分方案。

摘要由CSDN通过智能技术生成

node1:192.168.2.11 MariaDB10.0.17 还部署有nginx的anemometer web前端

node2:192.168.2.12 MariaDB10.0.17

各个节点的my.cnf里面开启慢查询，相关配置如下：[mysqld]

innodb_file_per_table = ON

skip_name_resolve = ON

slow_query_log=ON

slow_query_log_file =localhost-slow.log

long_query_time = 2

1.安装anemometer

node1上安装到nginx的网站目录下# cd /home/wwwroot/

# git clonehttps://github.com/box/Anemometer.git anemometer

# cd anemometer

node2上anemometer的安装目录没什么要求# cd /root

# git clone https://github.com/box/Anemometer.gitanemometer

# cd anemometer

2.创建表和用户名

node1上执行：# mysql -uroot -proot

# mysql -uroot -proot -e"grant ALL ON slow_query_log.* to ‘anemometer‘@‘192.168.2.%‘ IDENTIFIED BY‘123456‘;"

# mysql -uroot -proot -e "grantSELECT ON *.* to ‘anemometer‘@‘192.168.2.%‘ IDENTIFIED BY ‘123456‘;"

# mysql -uroot -proot -e"flush privileges;"

node2上执行：

# mysql -uroot -proot

# mysql -uroot -proot -e"grant ALL ON slow_query_log.* to ‘anemometer‘@‘192.168.2.%‘ IDENTIFIED BY‘123456‘;"

# mysql -uroot -proot -e"grant SELECT ON *.* to ‘anemometer‘@‘192.168.2.%‘ IDENTIFIED BY‘123456‘;"

# mysql -uroot -proot -e"flush privileges;"

3.在两个节点执行pt命令分析慢查询日志，并写入到各自的数据库中

node1上执行：# pt-query-digest --user=anemometer --password=123456--host=192.168.2.11 --review h=192.168.2.11,D=slow_query_log,t=global_query_review--history h=192.168.2.11,D=slow_query_log,t=global_query_review_history--no-report --limit=0% --filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\""localhost-slow.log

node2上执行：# pt-query-digest --user=anemometer --password=123456--host=192.168.2.12 --review h=192.168.2.12,D=slow_query_log,t=global_query_review --history h=192.168.2.12,D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter=" \$event->{Bytes} = length(\$event->{arg}) and\$event->{hostname}=\"$HOSTNAME\"" localhost-slow.log

4.在node1上配置前端# cd /home/wwwroot/anemometer/conf

# cp sample.config.inc.php config.inc.php

# vim config.inc.php 主要修改的地方如下2个【conf项，plugins项】：$conf[‘datasources‘][‘192.168.2.11‘] = array(

‘host‘ => ‘192.168.2.11‘,

‘port‘ => 3306,

‘db‘ => ‘slow_query_log‘,

‘user‘ => ‘anemometer‘,

‘password‘ => ‘123456‘,

‘tables‘ => array(

‘global_query_review‘ =>‘fact‘,

‘global_query_review_history‘=> ‘dimension‘

‘source_type‘ => ‘slow_query_log‘

);

$conf[‘datasources‘][‘192.168.2.12‘] = array(

‘host‘ => ‘192.168.2.12‘,

‘port‘ => 3306,

‘db‘ => ‘slow_query_log‘,

‘user‘ => ‘anemometer‘,

‘password‘ => ‘123456‘,

‘tables‘ => array(

‘global_query_review‘ =>‘fact‘,

‘global_query_review_history‘=> ‘dimension‘

‘source_type‘ => ‘slow_query_log‘

);

$conf[‘plugins‘] = array(

...省略代码...

$conn[‘user‘] = ‘anemometer‘;

$conn[‘password‘] = ‘123456‘;

...省略代码...

# /etc/init.d/nginx restart 重启Nginx

Chrome查看http://192.168.2.11/如下图所示

5.下面是我自己写pt分析慢查询日志的脚本

(anemometer提供的那个个人感觉用不惯，自己照着写了个更简单的)

vim /home/scripts/pt-digest.sh内容如下：#!/bin/bash

# 我这里直接把配置写死了，觉得不太好的话大家可以参考其它文章将数据库的连接配置独立出来

# 慢查询日志存放的目录

SQL_DATADIR="/usr/local/mariadb/var"

# 慢查询日志的文件名(basename)

SLOW_LOG_FILE=$( mysql -uroot -proot -e " show global variables like‘slow_query_log_file‘" -B | tail-n1 | awk ‘{ print $2 }‘ )

# 获取本机IP地址

IP_ADDR=$(/sbin/ifconfig | grep‘inet addr‘ | egrep ‘172.|192.‘ | awk‘{print $2}‘ | awk -F ":" ‘{print $2}‘)

cp $SQL_DATADIR/$SLOW_LOG_FILE/tmp/

# 分析日志并存入slow_query_log这个数据库

/usr/local/bin/pt-query-digest --user=anemometer --password=123456 --host=$IP_ADDR --review h=$IP_ADDR,D=slow_query_log,t=global_query_review --history h=$IP_ADDR,D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter="\$event->{Bytes} = length(\$event->{arg}) and\$event->{hostname}=\"$HOSTNAME\"" /tmp/$SLOW_LOG_FILE

rm -f /tmp/$SLOW_LOG_FILE

调试通过以后,在crontab添加如下命令实现定期采集慢查询日志到数据库存储

59 23 * * * /bin/bash /home/scripts/pt-digest.sh> /dev/null

这样每天就能自动分析采集慢查询日志了。

另外，慢查询日志建议按天切分，这样用pt-query-digest进行SQL慢查询日志统计的时候就避免重复分析了。慢查询按天切分的脚本如下：

Tips下面是慢查询日志切分脚本:

下面是一个轮询切割mySQL慢查询和错误日志的脚本(/home/scripts/mysql_log_rotate)："/usr/local/mariadb/var/localhost-slow.log""/usr/local/mariadb/var/localhost_err" {

create 660 mariadb mariadb # 这个文件权限和属主属组需要根据自己的情况修改

dateext

notifempty

daily

maxage 60