Anemometer基于pt-query-digest將MySQL慢查詢可視化

參考文章：

10年的安福網(wǎng)站建設(shè)經(jīng)驗(yàn)，針對設(shè)計(jì)、前端、開發(fā)、售后、文案、推廣等六對一服務(wù)，響應(yīng)快，48小時(shí)及時(shí)工作處理。成都營銷網(wǎng)站建設(shè)的優(yōu)勢是能夠根據(jù)用戶設(shè)備顯示端的尺寸不同，自動(dòng)調(diào)整安福建站的顯示方式，使網(wǎng)站能夠適用不同顯示終端，在瀏覽器中調(diào)整網(wǎng)站的寬度，無論在任何一種瀏覽器上瀏覽網(wǎng)站，都能展現(xiàn)優(yōu)雅布局與設(shè)計(jì)，從而大程度地提升瀏覽體驗(yàn)。成都創(chuàng)新互聯(lián)從事“安福網(wǎng)站設(shè)計(jì)”,“安福網(wǎng)站推廣”以來，每個(gè)客戶項(xiàng)目都認(rèn)真落實(shí)執(zhí)行。

http://ourMySQL.com/archives/1359?utm_source=tuicool&utm_medium=referral

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

單節(jié)點(diǎn)Anemometer監(jiān)控

1 安裝anemometer

# cd /data/www/web3
# git clone https://github.com/box/Anemometer.gitanemometer && cd anemometer

2 創(chuàng)建表和用戶名

# mysql -uroot -proot <install.sql
# 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"flushprivileges;"

我們可以看下表結(jié)構(gòu)如下

3 分析mysql慢日志

# pt版本高于2.2的執(zhí)行下面語句，將慢查詢?nèi)罩痉湃朊麨閟low_query_log數(shù)據(jù)庫中

# pt-query-digest --user=anemometer -h 127.0.0.1 --password=123456 \
--review h=localhost,D=slow_query_log,t=global_query_review\
--history h=localhost,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/mariadb/var/localhost-slow.log

這時(shí)候，數(shù)據(jù)庫的slow_query_log 庫，里面的global_query_review_history和global_query_review這2張表已經(jīng)已經(jīng)有一些數(shù)據(jù)了。

4 修改anemometer配置文件及配置展示日志用的虛擬主機(jī)

# cd /data/www/web3/anemometer/conf
# cp sample.config.inc.php  config.inc.php
# vim config.inc.php  主要修改的地方如下2個(gè)：

配置nginx

# vim /usr/local/nginx/conf/vhost/anemometer.conf  內(nèi)容如下：

server {
       listen   80;
       server_name  192.168.0.88;
       access_log  /home/wwwlogs/anemometer.log  access;
       index index.php index.html;
       root  /data/web3/anemometer;
       include enable-php.conf;
}

# /etc/init.d/nginx reload      重載nginx配置文件

在瀏覽器訪問http://192.168.0.88/ 即可如下圖所示(這幾張圖片是從別人博客摘錄的，他這個(gè)截圖做的特別詳細(xì))

5 自動(dòng)滾動(dòng)日志

# vi /etc/logrotate.d/mysql

postrotate
pt-query-digest --user=anemometer --password=123456 \
--review D=slow_query_log,t=global_query_review \
--review-history 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/mariadb/var/localhost-slow.log
endscript

至此，我們的anemometer算是跑通了。

但是生產(chǎn)環(huán)境的話，我們不可能就一個(gè)節(jié)點(diǎn)的啊，下面就是多節(jié)點(diǎn)部署的問題了。

多節(jié)點(diǎn)mySQL監(jiān)控慢查詢?nèi)罩?/strong>

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

node2:192.168.2.12  MariaDB10.0.17

各個(gè)節(jié)點(diǎn)的my.cnf里面開啟慢查詢，相關(guān)配置如下：

[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的網(wǎng)站目錄下

# 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.  創(chuàng)建表和用戶名

node1上執(zhí)行：

# mysql -uroot -proot <install.sql
# 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上執(zhí)行：

# mysql -uroot -proot <install.sql
# 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.  在兩個(gè)節(jié)點(diǎn)執(zhí)行pt命令分析慢查詢?nèi)罩荆懭氲礁髯缘臄?shù)據(jù)庫中

node1上執(zhí)行：

# 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上執(zhí)行：

# 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個(gè)【conf項(xiàng)，plugins項(xiàng)】：

$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分析慢查詢?nèi)罩镜哪_本

(anemometer提供的那個(gè)個(gè)人感覺用不慣，自己照著寫了個(gè)更簡單的)

vim /home/scripts/pt-digest.sh內(nèi)容如下：

#!/bin/bash
# 我這里直接把配置寫死了，覺得不太好的話大家可以參考其它文章將數(shù)據(jù)庫的連接配置獨(dú)立出來
 
# 慢查詢?nèi)罩敬娣诺哪夸?SQL_DATADIR="/usr/local/mariadb/var"
 
# 慢查詢?nèi)罩镜奈募?basename)
SLOW_LOG_FILE=$( mysql -uroot -proot -e " show global variables like'slow_query_log_file'" | tail-n1 | awk '{ print $2 }' )
 
# 獲取本機(jī)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這個(gè)數(shù)據(jù)庫
/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

調(diào)試通過以后,在crontab添加如下命令實(shí)現(xiàn)定期采集慢查詢?nèi)罩镜綌?shù)據(jù)庫存儲(chǔ)

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

這樣每天就能自動(dòng)分析采集慢查詢?nèi)罩玖恕?/p>

另外，慢查詢?nèi)罩窘ㄗh按天切分，這樣用pt-query-digest進(jìn)行SQL慢查詢?nèi)罩窘y(tǒng)計(jì)的時(shí)候就避免重復(fù)分析了。慢查詢按天切分的腳本如下：

Tips下面是慢查詢?nèi)罩厩蟹帜_本:

下面是一個(gè)輪詢切割mySQL慢查詢和錯(cuò)誤日志的腳本（/home/scripts/mysql_log_rotate）：

"/usr/local/mariadb/var/localhost-slow.log""/usr/local/mariadb/var/localhost_err" { 
    create 660 mariadb mariadb      # 這個(gè)文件權(quán)限和屬主屬組需要根據(jù)自己的情況修改
   dateext
   notifempty
   daily
   maxage 60
   rotate 30
   missingok
   olddir /usr/local/mariadb/var/oldlogs  # 這個(gè)目錄不存在的話，要自己先新建好，并修改屬主為mariadb
 
   postrotate
        if /usr/local/mariadb/bin/mysqladminping -uroot -proot &>/dev/null; then
            /usr/local/mariadb/bin/mysqladminflush-logs -uroot -proot
        fi
   endscript
}

再配置個(gè)CRONTAB：

00 00 * * * (/usr/sbin/logrotate-f /home/scripts/mysql_log_rotate >/dev/null 2>&1)

這樣的話，每天慢查詢?nèi)罩?、錯(cuò)誤日志就自動(dòng)存儲(chǔ)到/usr/local/mariadb/var/oldlogs/這個(gè)目錄下了。