介绍：Anemometer 是一个图形化显示MySQL慢日志的工具。结合pt-query-digest，Anemometer可以很轻松的帮你去分析慢查询日志，让你很容易就能找到哪些SQL需要优化 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
环境概况 以写此文章时 percona-toolkit最新的版本3.0.10为例 mysql数据库对应版本为5.7.21，二进制安装 http和php均是系统CentOS Linux release 7.4.1708 (Core)自带版本
4. 导入慢查询日志 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 ;
