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