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

1、慢查詢主機推送格式

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  --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  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

2、慢查詢主機推動腳本示例

#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####

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

http://$ip/anemometer/ （$ip為Anemometer主機對應(yīng)ip地址）

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

1、對于anemometer的主機上，需要進行慢查詢主機hostname和ip的映射（修改/etc/hosts進行配置），目的在于慢查詢explain執(zhí)行計劃的目標主機解析
#collect mysql slowquery log into lepus database步驟中，$HOSTNAME:$mysql_port
數(shù)據(jù)庫存取的格式，hostname_max類似這種，cnwangdawei:5700
2、中文亂碼的問題，在#collect mysql slowquery log into lepus database步驟中添加 –charset=utf8
3、慢查詢主機數(shù)據(jù)庫是5.7版本的數(shù)據(jù)庫，可能出現(xiàn)界面ts_cnt不顯示，替換percona toolkit為新版本，2.x.x —–> 3.x.x
4、表結(jié)構(gòu)和狀態(tài)字符集顯示亂碼，添加mysqli的字符集設(shè)定，vim /var/www/html/anemometer/lib/QueryExplain.php
新增（194行后增加），$this->mysqli->query("set names utf8");