Anemometer slow query log monitoring platform

Environment Introduction:

Yum uses a source of 163: Centos6-base-163.repo

Operation of the new system: Red Hat Enterprise Linux Server Release 6.4

Database version: 5.6.27

PHP Version: 5.3.3 #要求php版本大于5.3

percona-toolkit:2.2.17

10.10.203.102 "Web Server"

10.10.203.93 "Monitored side"

You need to do all the work on both machines, there are notes on the steps.

Operation Steps:

1. Installation of the Percona-toolkit tool

10.10.203.93 and 10.10.203.102 are installed

Pt-query-digest is a percona-toolkit inside a tool, its role is to analyze the slow query log, the MySQL slow query log statistics and friendly display.

# wget https://www.percona.com/downloads/percona-toolkit/2.2.17/RPM/percona-toolkit-2.2.17-1.noarch.rpm

# RPM-IVH percona-toolkit-2.2.17-1.noarch.rpm

# which Pt-query-digest

/usr/bin/pt-query-digest

At this point, pt-query-digest installation is complete

2. Building a PHP Web environment

Operation on 10.10.203.102

Yum Install httpd Httpd-devel

Yum install php php-mysql php-common php-gd php-mbstring php-mcrypt php-devel php-xml Php-bcmath

To modify the time zone:

Yum installs PHP, the config file defaults to/etc/php.ini.

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/8B/E6/wKiom1hbxUqiijRHAAAssAmTcmg479.png "title=" Anemo_ 1_3.png "alt=" Wkiom1hbxuqiijrhaaassamtcmg479.png "/>

#对pgm用户授权 "10.10.203.102 and 10.10.206.93"

Mysql> Grant All on * * to ' PGM ' @ ' 10.10.% ' identifiedby ' pgmfetion ';

mysql> flush Privileges;

3. Install Anemometer and configure

: Https://github.com/box/Anemometer

After download, the name is: Anemometer-master.zip

Unzip Anemometer-master.zip

MV Anemometer-master/var/www/html/anemometer

#导入初始化库表 "10.10.203.102 and 10.10.206.93"

Cd/var/www/html/anemometer

Mysql-s/tmp/mysql3307.sock-uroot-psecret <install.sql

Mysql-s/tmp/mysql3307.sock-uroot-psecret <mysql56-install.sql

#修改配置文件

Cd/var/www/html/anemometer/conf

CP sample.config.inc.php config.inc.php

Vim config.inc.php

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/8B/E3/wKioL1hbyquwzCcUAAB1-aU6_AQ544.png "style=" float: none; "title=" Anemo_1_1.png "alt=" Wkiol1hbyquwzccuaab1-au6_aq544.png "/>

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/8B/E7/wKiom1hbyqzRvI8DAAA6cZNhh5o017.png "style=" float: none; "title=" Anemo_1_2.png "alt=" Wkiom1hbyqzrvi8daaa6cznhh5o017.png "/>

Start the HTTP service

/ETC/INIT.D/HTTPD start

Access Address:

http://10.10.203.102/anemometer/

The page can be opened, but there is no data.

4. Import Slow Query log

Check the slow query time, in order to test, I changed very small. The location of the slow query log is Slow.log unspecified and is in the data directory by default.

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/8B/E3/wKioL1hbxq7yJjj1AAAywJHi9Vo673.png "style=" float: none; "title=" 3_1.png "alt=" Wkiol1hbxq7yjjj1aaaywjhi9vo673.png "/>

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/8B/E3/wKioL1hbxq6j0diMAAAs34OrNBE109.png "style=" Float:none, "title=" 3_2.png "alt=" Wkiol1hbxq6j0dimaaas34ornbe109.png "/>

Please refer to readme  to execute the corresponding command: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

My version is greater than 2.2, so execute the command below.

10.10.203.102 "Web Server" machine:

Pt-query-digest--user=pgm--password=pgmfetion--port=3307--review H=10.10.203.102,d=slow_query_lo                       G,t=global_query_review--history H=10.10.203.102,d=slow_query_log,t=global_query_review_history  --no-report--limit=0%--filter= "\ $event->{bytes} = Length (\ $event->{arg}) and \ $event->{hostname}=\ "$HOSTNAME \" "/data/mysql/mysql3307/data/slow.log

10.10.203.93 "Monitored end" machine:

Pt-query-digest--user=pgm--password=pgmfetion--port=7777--review h=10.10.203.93,d=slow_query_log                       , T=global_query_review--history h=10.10.203.93,d=slow_query_log,t=global_query_review_history --no-report--limit=0%--filter= "\ $event->{bytes} = Length (\ $event->{arg}) a nd \ $event->{hostname}=\ "$HOSTNAME \" "/data/mysql/mysql7777/data/slow.log

Re-visit: http://10.10.203.102/anemometer/

Two DB instances, select the db you want to view.

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/8B/E7/wKiom1hby43CxyYuAACgKSK331c129.png "title=" 4_1. PNG "alt=" Wkiom1hby43cxyyuaacgksk331c129.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/8B/E7/wKiom1hbzQ-SdDcgAAFqfvEv_TQ672.png "style=" float: none; "title=" 11.png "alt=" Wkiom1hbzq-sddcgaafqfvev_tq672.png "/>

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/8B/E3/wKioL1hbzRHiTaWMAAKdqf3hAb8661.png "style=" float: none; "title=" 12.png "alt=" Wkiol1hbzrhitawmaakdqf3hab8661.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/8B/E7/wKiom1hbzRGCr_ALAAH7d16c9qE610.png "style=" float: none; "title=" 13.png "alt=" Wkiom1hbzrgcr_alaah7d16c9qe610.png "/>

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/8B/E3/wKioL1hbzROiWPbcAAIn-ETxcCI278.png "style=" float: none; "title=" 14.png "alt=" Wkiol1hbzroiwpbcaain-etxcci278.png "/>

5. Log cutting:

You can use the log cutting tool to logrotate from slow logs and automatically perform analysis commands after cutting, mysql.slow.conf configuration files are as follows:

/data/mysql/mysql3307/data/slow.log{create 660 mysql mysqlsu root  mysqldateextdailymissingokrotate 360notifemptycopytruncatepostrotatept-query-digest --user=pgm  --password=pgmfetion --port=3307                        --review h=10.10.203.102,d=slow_query_ log,t=global_query_review                        --history h=10.10.203.102,d=slow_query_log,t=global _query_review_history                        --no-report --limit=0%                         --filter= " \ $event->{BYTES}&NBSp;= length (\ $event->{arg})  and \ $event->{hostname}=\ "$HOSTNAME \" " /data/mysql/ mysql3307/data/slow.log-$ (date +%y%m%d) Endscript}

You can also place the configuration file in the/ETC/LOGROTATE.D directory by executing the logrotate-f mysql.slow.conf with a timed task.

10.10.206.93 Operation Ibid.

Note:

(1) Log switch, you can also write a script, the method is not limited.

(2) currently does not support 5.7 of the database, the import default initialization table structure will report an exception, the default timestamp in 5.7 has changed.

https://github.com/box/Anemometer/issues/175

The install SQL file does not create the tables properly during first setup. It appears to is due to a change in the default timestamp format in 5.7.

This article is from the "Deep Mountain" blog, please make sure to keep this source http://kenneyzhou.blog.51cto.com/12427643/1885253

Anemometer slow query log monitoring platform