Anemometer graphical display of MySQL slow log tool build use

Description: Anemometer is a graphical display of MySQL slow log tool. Combining Pt-query-digest,anemometer can easily help you analyze slow query logs, so you can easily find which SQL needs to be optimized
This is the Box anemometer, the MySQL Slow Query Monitor. This tool was used to analyze slow query logs collected slave MySQL instances to identify problematic queries
?
Official website: https://github.com/box/Anemometer

Environment overview
To write this article, percona-toolkit the latest version of 3.0.10 as an example
MySQL database corresponds to version 5.7.21, binary installation
Both HTTP and PHP are system CentOS Linux release 7.4.1708 (Core) comes with version

The steps to install are as follows:

1. Installation of the Percona-toolkit tool
2. Building a PHP Web environment
3. Install Anemometer and configure
4. Import Slow Query log
5. Access the interface to view slow queries
6. Other related and problem solving
   ?

1. Installation of the Percona-toolkit tool

安装目的：pt-query-digest是percona-toolkit里面一个工具，其作用就是分析慢查询日志，将MySQL慢查询日志进行统计并友好的显示出来：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、安装完毕验证，pt-query-digest --version    pt-query-digest 3.0.10安装方式（tar二进制）    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. Building a PHP Web environment

安装目的：Anemometer需要依赖LAMP环境LAMP环境的安装：    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、修改时区，vim /etc/php.ini，修改为 date.timezone = PRCLAMP环境的启动：    1、启动，systemctl start httpd    2、关闭，systemctl stop httpd    3、重启，systemctl restart httpd    4、查看，systemctl status httpd

?

3. Install Anemometer and configure

1、下载安装：    安装目的：安装Anemometer应用    ：https://github.com/box/Anemometer    下载包：git clone https://github.com/box/Anemometer.git    移动到对应路径：mv Anemometer /var/www/html/anemometer2、目标慢查询数据库上需要授予Anemometer主机对应的权限    1、目的，用于分析目标慢查询数据库explain执行计划    2、授权，grant select on *.* to ‘anemometer‘@‘$ip‘ identified by  ‘123456‘;flush privileges;    （$ip为Anemometer主机对应ip地址）3、修改配置文件增加explain读取用户密码信息    cp conf/sample.config.inc.php conf/config.inc.php    vim conf/config.inc.php

4、修改配置文件指向数据源文件，vim conf/datasource_localhost.inc.php，当然也可以直接vim conf/config.inc.php

5、初始化数据源的数据库表的配置，mysql -uroot -p123456 -h127.0.0.1 -P5700 < install.sql，每个datasource源头可以对应不同的数据库database（修改install.sql的内容）

?

4. Import Slow Query log

1. Slow query host push format 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= "\ $even T->{bytes} = Length (\ $event->{arg}) and \ $event->{hostname}=\ "$HOSTNAME \" "\/var/lib/mysql/db.exam Ple.com-slow.log for Pt-query-digest version >= 2.2 $ pt-query-digest--user=anemometer--password=supersecur EPass--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.examp LE.COM-SLOW.LOG2, slow query host driveScript Example 

#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=5700anemometer_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/mysq L "mysql_user=" root "mysql_password=" 123456 "mysql_socket="/tmp/mysql_5700.sock "Mysql_port=5700#config Slowqury dir To CDs, and then delete the expired slow query file.slowquery_dir= "/data/mysql_$mysql_port/" #get the path of the slow Quer Y 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 $} ' 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 $mys QL_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 dayscd $slowquery _dir/usr/bin/find./-name ' Slowquery_*.log '-mtime +2|xargs rm-rf; # # # #END # # #

?

5. Access the interface to view slow query

/HTTP $ip/anemometer/($ip IP address for anemometer host)




?

6. Other related and problem solving

1、对于anemometer的主机上，需要进行慢查询主机hostname和ip的映射（修改/etc/hosts进行配置），目的在于慢查询explain执行计划的目标主机解析    #collect mysql slowquery log into lepus database步骤中，$HOSTNAME:$mysql_port    数据库存取的格式，hostname_max类似这种，cnwangdawei:57002、中文乱码的问题，在#collect mysql slowquery log into lepus database步骤中添加 --charset=utf83、慢查询主机数据库是5.7版本的数据库，可能出现界面ts_cnt不显示，替换percona toolkit为新版本，2.x.x -----> 3.x.x4、表结构和状态字符集显示乱码，添加mysqli的字符集设定，vim /var/www/html/anemometer/lib/QueryExplain.php    新增（194行后增加），$this->mysqli->query("set names utf8");

?

Anemometer graphical display of MySQL slow log tool build use