Anemometer圖形化顯示MySQL慢日志的工具搭建及使用的實(shí)例解析

介紹：Anemometer 是一個(gè)圖形化顯示mysql慢日志的工具。結(jié)合pt-query-digest，Anemometer可以很輕松的幫你去分析慢查詢?nèi)罩?，讓你很容易就能找到哪些SQL需要優(yōu)化

this is the Box Anemometer, the MySQL Slow Query Monitor. This tool is used to analyze slow query logs collected from MySQL instances to identify problematic queries

相關(guān)學(xué)習(xí)推薦：PHP編程從入門到精通

環(huán)境概況

以寫此文章時(shí) percona-toolkit最新的版本3.0.10為例
mysql數(shù)據(jù)庫(kù)對(duì)應(yīng)版本為5.7.21，二進(jìn)制安裝
http和php均是系統(tǒng)centos linux release 7.4.1708 (Core)自帶版本

需要安裝的步驟如下：

1.percona-toolkit工具的安裝

2.php web環(huán)境的搭建安裝

3.Anemometer并配置

4.導(dǎo)入慢查詢?nèi)罩?/p>

5.訪問界面，查看慢查詢

6.其他相關(guān)和問題解決

0. 整體的架構(gòu)

1. percona-toolkit工具的安裝

安裝目的：pt-query-digest是percona-toolkit里面一個(gè)工具，其作用就是分析慢查詢?nèi)罩荆瑢ySQL慢查詢?nèi)罩具M(jìn)行統(tǒng)計(jì)并友好的顯示出來

下載地址：https://www.percona.com/downloads/percona-toolkit/

安裝方式（rpm）：

1、下載包，wget https://www.percona.com/downloads/percona-toolkit/3.0.10/binary/redhat/7/x86_64/percona-toolkit-3.0.10-1.el7.x86_64.rpm

2、安裝依賴，yum install perl-DBI perl-DBD-MySQL perl-IO-Socket-ssl perl-Digest-MD5 -y

3、正式安裝，rpm -ivh percona-toolkit-3.0.10-1.el7.x86_64.rpm

4、安裝完畢驗(yàn)證，pt-query-digest –version pt-query-digest 3.0.10

安裝方式（tar二進(jìn)制）

1、下載包，wget https://www.percona.com/downloads/percona-toolkit/3.0.10/binary/tarball/percona-toolkit-3.0.10_x86_64.tar.gz

2、安裝依賴，yum install perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Digest-MD5 -y

3、解壓包，tar xf percona-toolkit-3.0.10_x86_64.tar.gz

4、直接使用工具， ./percona-toolkit-3.0.10/bin/pt-query-digest –version
pt-query-digest 3.0.10

2. php web環(huán)境的搭建

安裝目的：Anemometer需要依賴
LAMP環(huán)境LAMP環(huán)境的安裝：
1、安裝apache，yum install httpd httpd-devel -y
2、安裝php，yum install php php-mysql php-common php-bcmath php-dba php-cli php-gd php-mbstring php-mcrypt php-devel php-xml php-pdo -y
3、修改時(shí)區(qū)，vim /etc/php.ini，修改為 date.timezone = PRC

LAMP環(huán)境的啟動(dòng)：
1、啟動(dòng)，systemctl start httpd
2、關(guān)閉，systemctl stop httpd
3、重啟，systemctl restart httpd
4、查看，systemctl status httpd

3. 安裝Anemometer并配置

1、下載安裝：
安裝目的：安裝Anemometer應(yīng)用
下載地址：https://github.com/box/Anemometer
下載包：git clone https://github.com/box/Anemometer.git
移動(dòng)到對(duì)應(yīng)路徑：mv Anemometer /var/www/html/anemometer
2、目標(biāo)慢查詢數(shù)據(jù)庫(kù)上需要授予Anemometer主機(jī)對(duì)應(yīng)的權(quán)限
1、目的，用于分析目標(biāo)慢查詢數(shù)據(jù)庫(kù)explain執(zhí)行計(jì)劃
2、授權(quán)，grant select on *.* to ‘anemometer’@’$ip’ identified by ‘123456’;flush privileges;（$ip為Anemometer主機(jī)對(duì)應(yīng)ip地址）
3、修改配置文件增加explain讀取用戶密碼信息 cp conf/sample.config.inc.php conf/config.inc.php vim conf/config.inc.php

4、修改配置文件指向數(shù)據(jù)源文件，vim conf/datasource_localhost.inc.php，當(dāng)然也可以直接vim conf/config.inc.php

5、初始化數(shù)據(jù)源的數(shù)據(jù)庫(kù)表的配置，mysql -uroot -p123456 -h127.0.0.1 -P5700

4. 導(dǎo)入慢查詢?nèi)罩?/strong>

For?pt-query-digest?version?{Bytes}?=?length($event->{arg})?and?$event->{hostname}="$HOSTNAME""?? /var/lib/mysql/db.example.com-slow.log  For?pt-query-digest?version?>=?2.2 $?pt-query-digest?--user=anemometer?--password=superSecurePass? --review?h=db.example.com,D=slow_query_log,t=global_query_review? --history?h=db.example.com,D=slow_query_log,t=global_query_review_history? --no-report?--limit=0%?? --filter="?$event->{Bytes}?=?length($event->{arg})?and?$event->{hostname}="$HOSTNAME""?? /var/lib/mysql/db.example.com-slow.log

#config?anemometer?server,?the?purpose?is?to?push?slow?query?to?the?remote?anemometer?server?and?store?it. anemometer_host="127.0.0.1" anemometer_user="root" anemometer_password="123456" anemometer_port=5700 anemometer_db="slow_query_log"  #config?mysql?server,?the?purpose?is?to?get?the?path?of?the?slow?query?log. mysql_client="/usr/local/mysql-5.7.21/bin/mysql" mysql_user="root" mysql_password="123456" mysql_socket="/tmp/mysql_5700.sock" mysql_port=5700  #config?slowqury?dir?to?cd,?and?then?delete?the?expired?slow?query?file. slowquery_dir="/data/mysql_$mysql_port/"  #get?the?path?of?the?slow?query?log. slowquery_file=`$mysql_client?-u$mysql_user?-p$mysql_password?-S?$mysql_socket?-e?"show?variables?like?'slow_query_log_file'"|grep?log|awk?'{print?$2}'` pt_query_digest="/data/percona-toolkit-3.0.10/bin/pt-query-digest"  #collect?mysql?slowquery?log?into?lepus?database. $pt_query_digest?--user=$anemometer_user?--password=$anemometer_password?--port=$anemometer_port?--review?h=$anemometer_host,D=$anemometer_db,t=global_query_review?--history?h=$anemometer_host,D=$anemometer_db,t=global_query_review_history?--no-report?--limit=0%?--filter="?$event->{Bytes}?=?length($event->{arg})?and?$event->{hostname}="$HOSTNAME:$mysql_port""?$slowquery_file  #generate?a?new?slow?query?log,?the?below?is?generate?a?new?slow?file?per?hour. tmp_log=`$mysql_client?-u$mysql_user?-p$mysql_password?-S?$mysql_socket?-e?"select?concat('$slowquery_dir','slowquery_',date_format(now(),'%Y%m%d%H'),'.log');"|grep?log|sed?-n?-e?'2p'`  #use?new?slow?file?to?config?mysql?slowquery $mysql_client?-u$mysql_user?-p$mysql_password?-S?$mysql_socket?-e?"set?global?slow_query_log?=?0;set?global?slow_query_log_file?=?'$tmp_log';" $mysql_client?-u$mysql_user?-p$mysql_password?-S?$mysql_socket?-e?"set?global?slow_query_log?=?1;?"  #delete?slow?query?file?before?2?days cd?$slowquery_dir /usr/bin/find?./?-name?'slowquery_*.log'?-mtime?+2|xargs?rm?-rf?;  ####END####