MySQL 使用Anemometer基于pt-query-digest将慢查询可视化

一、PT 安装：

[root ~]$ yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes -y

[root ~]$ rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm

[root ~]$ rpm -ivh percona-toolkit-3.0.2-1.el6.x86_64.rpm

二、下载Anemometer

官网：https://github.com/box/Anemometer

三、安装httpd php,php版本要大于5.3，否则就报错，除此之外还需要：bcmath，php必须支持pdo_mysql、php_mysqli模块，下面我们来安装一下：

[root ~]$ yum install httpd php *bcmath* *mysqli* -y

四、将Anemometer文件包解压，重命名为anemometer，并移动到/var/www/html 下(apache默认路径)

[root ~]$ unzip Anemometer-master.zip

[root ~]$ mv Anemometer-master /var/www/html/anemometer

五、导入anemometer目录下的install.sql，并给该库对应的权限：

[root anemometer]$ pwd
/var/www/html/anemometer

[root anemometer]$  mysql -uroot -p123456  < mysql56-install.sql

mysql> grant all on slow_query_log.* to 'anemometer'@'%' identified by '123456';

mysql> grant all on slow_query_log.* to 'anemometer'@'localhost' identified by '123456';

mysql>  grant select on *.* to  'anemometer'@'%';

mysql> grant all on slow_query_log.* to 'anemometer'@'localhost';

mysql> grant select on *.* to  'anemometer'@'localhost';

六、修改配置文件

[root ~]$ cd /var/www/html/anemometer/conf

[root ~]$ cp sample.config.inc.php config.inc.php

[root ~]$ vim config.inc.php //修改文件中是2处
$conf['datasources']['localhost'] = array(
        'host'  => '127.0.0.1',
        'port'  => 3307,
        'db'    => 'slow_query_log',
        'user'  => 'anemometer',
        'password' => '123456',
        'tables' => array(
                'global_query_review' => 'fact',
                'global_query_review_history' => 'dimension'
        ),
        'source_type' => 'slow_query_log'
);

        $conn['user'] = 'anemometer';
        $conn['password'] = '123456';

七、重启http服务

[root ~]$ /etc/init.d/httpd restart

八、访问界面

http://192.168.1.1/anemometer

观察是否可以看到正常界面，如果看不到，查看http错误日志，我这遇到个错误

[root ~]$ cd /var/log/httpd

[root ~]$ tail -n 200 error_log
Warning: date_default_timezone_get(): It is not safe to rely on the system'stimezo
ne settings. You are*required* to use the date.timezone setting or the date_default_timezone_set()function. In case you used any of those method
s and you are still gettingthis warning, you most likely misspelled the timezone identifier. We selected'Asia/Chongqing' for 'CST/8.0/no DST' in
stead in/var/www/html/anemometer/lib/Anemometer.php on line 48

意思就是php时区的问题，改下php配置文件里的时区:
[root ~]$ vim /etc/php.ini
date.timezone = Asia/Shanghai

重启http服务器：
[root ~]$ /etc/init.d/httpd restart

九、导入慢查询：

pt-query-digest --user=anemometer --password=123456 --port=3307 --review h=192.168.1.1,D=slow_query_log,t=global_query_review --history h=192.168.1.1,D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\"" /u02/mysql3307/mysql-slow-$(date +%Y-%m-%d).log

 

 

十、安装logrotate并配置每日切割mysql慢查询日志

/export/mysql/mysql3307/logs/slow.log
{
create 660 mysql mysql
su root mysql
dateext
daily
missingok
rotate 360
notifempty
copytruncate


postrotate
/usr/bin/pt-query-digest --user=thunder--password=thunder \
                         --review h=127.0.0.1,D=slow_query_log,t=global_query_review \
                         --history h=127.0.0.1,D=slow_query_log,t=global_query_review_history \
                         --no-report --limit=0% \
                         --filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\"" \
                         /export/mysql/mysql3307/logs/slow.log-$(date +%Y-%m-%d)
endscript
}

重复对一个慢日志文件分析，最新的慢日志记录不会插入到数据库中，所以页面中也就没有相关的纪录