mysql慢查询可视化_十分钟部署Anemometer作为Mysql慢查询可视化系统

前言

采用Anemometer将Mysql慢查询日志可视化，可以更便捷的查询慢查询日志，并根据时间戳进行历史查询。如下是单机版Anemometer部署的演示，实际应用中，为安全起见，建议把anemometer 分开到另外的机器上。

工作原理

Anemometer： 实现日志可视化

pt-query-digest ：抽取慢查询日志

环境信息

Ip

功能

软件信息

安装路径

操作系统

192.168.9.11

http服务

httpd-2.2.15-54

yum缺省路径

centos6.9

慢查询日志抓取导入

pt-query-digest

rpm缺省路径

慢查询日志切割

logrotate

yum缺省路径

可视化展示

Anemometer

/var/www/html

安装步骤

1.安装apache

yum install httpd -y

2.安装pt-query-digest并设置抓取脚本

yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL -y

#需要安装perl-TermReadKey否则会报错

wget http://pkgs.repoforge.org/perl-TermReadKey/perl-TermReadKey-2.30-1.el3.rf.x86_64.rpm

rpm -ivh perl-TermReadKey-2.30-1.el3.rf.x86_64.rpm

wget https://www.percona.com/downloads/percona-toolkit/2.2.20/RPM/percona-toolkit-2.2.20-1.noarch.rpm

rpm -ivh percona-toolkit-2.2.20-1.noarch.rpm

vim/home/shell_script/slow_db_export.sh

#!/bin/bash/usr/bin/pt-query-digest --user=anemometer --password=123456 --review h=192.168.9.11,D=slow_query_log,t=global_query_review --history h=192.168.9.11,

D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter="\$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\"" /usr/local/mysql/data/mysql.slow

chmod-R 755 /home/shell_script/slow_db_export.sh

3.安装logrotate并配置每日切割mysql慢查询日志

yum install logrotate -y

vim/etc/logrotate.d/mysql/usr/local/mysql/data/mysql.slow {

create660mysql mysql

dateext

notifempty

daily

maxage60rotate30missingok

compress

olddir/usr/local/mysql/old_log

postrotate

##########runif mysqld isrunningif /usr/local/mysql/bin/mysqladmin ping -h172.27.127.12 -uroot -p'Hs!hs8989' -S /usr/local/mysql/mysql.sock &>/dev/null; then/usr/local/mysql/bin/mysqladmin flush-logs -h172.27.127.12 -uroot -p'Hs!hs8989' -S /usr/local/mysql/mysql.sock

fi

endscript

}

chmod-R 755 /etc/logrotate.d/mysql

4.将pt-query-digest脚本和logrotate切割脚本放入crontab中

#############23点55分-分割导入当天的慢查询数据#####################55 23 * * * /home/shell_script/slow_db_export.sh

###############23点59分-切割慢查询日志#############59 23 * * * root ( /usr/sbin/logrotate -f /etc/logrotate.d/mysql)

5.安装Anemometer

cd /var/www/htmlgit clone https://github.com/box/Anemometer.git anemometer&&cd anemometer

#创建表和用户名

# mysql-uroot -proot

# mysql-uroot -proot -e "grant ALL ON slow_query_log.* to 'anemometer'@'localhost' IDENTIFIED BY '123456';"# mysql-uroot -proot -e "grant SELECT ON *.* to 'anemometer'@'localhost' IDENTIFIED BY '123456';"# mysql-uroot -proot -e "flush privileges;"

6.导入日志并打开页面测试(http://192.168.9.11/amemometer

#执行刚刚编辑的slow_db_export.sh脚本

./home/shell_script/slow_db_export.sh

#重启apache

service httpd restart

至此，已经通过Anemometer实现了mysql慢查询日志可视化功能。