Visualize MySQL slow query using anemometer based on Pt-query-digest

Recently played MySQL, found a very good tool, can be the MySQL slow query can be seen, convenient for us to find and analyze the slow inquiry statement, the steps are not many, but on-line detailed tutorials less, not enough detailed, accidentally, estimated that the egg pain a while, haha

Percona Toolkit is an advanced set of command-line tools for managing MySQL and system tasks, mainly including:
1. Verify the consistency of the master node and replicated data
2. Effective archiving of record lines
3. Find Duplicate Indexes
4. Summarize MySQL Server
5. Analyze queries from logs and tcpdump
6. Collect important System Information when the problem occurs

One, PT installation:

Method One: RPM Package Installation

[Root ~]$wgethttp//www.percona.com/downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.12-1.noarch.rpm[Root ~]$Yum Install Perl-io-socket-sslPerl-dbd-mysqlPerl-time-hires-Y[root~]$ RPM-IVH percona-toolkit-2.2. A-1. noarch.rpm Warning:percona-toolkit-2.2. A-1. Noarch.rpm:Header V4 dsa/SHA1 Signature, key ID cd2efd2a:nokeypreparing ... ########################################### [ -%]   1:p Ercona-toolkit ########################################### [ -%]

If Yum is not installed on a dependency package, check the Epel source, or see http://www.cnblogs.com/xuanzhi201111/p/4040761.html to fix it!

Method Two: Source package installation

 [Root ~]$ wget  http:// www.percona.com/downloads/percona-toolkit/2.2.12/deb/percona-toolkit_2.2.12.tar.gz  [Root ~]$tar  zxf percona-toolkit_2. 2.12 . tar  .gz[root  ~] $CD percona-toolkit_2. 2.12  [root Percona -toolkit-12 ]$perl   Makefile.pl[root Percona -toolkit-2.2 . 12 ] $ make  && install  

Tool installation directory in:/usr/local/bin

Second, download anemometer
Official website: https://github.com/box/Anemometer

Third, install httpd php,php version to be greater than 5.3, or error, in addition to: Bcmath,PHP must support pdo_mysql, Php_mysqli module, let us install:

Yum Install  httpd php *bcmath* *mysqli*-  y

If the Epel source resembles the following, the description is old and the source does not php_mysqli the relevant module

yum. repos.d]$ rpm-q epel-releaseepel-release-5-4. Noarch

The following versions are available:

yum. repos.d]$ rpm-q epel-releaseepel-release-6-8. Noarch

My MySQL has already been installed, there is not much to say

Iv. Unzip the anemometer package, rename it to Anemometer, and move to/var/www/html (Apache default path)

Unzip Anemometer-master. Zip  MV anemometer-master/var/www/html/anemometer

Import the Install.sql in the Anemometer directory and give the library the appropriate permissions:

pwd/var/www/html/anemometer[root anemometer]$  -uroot-p123456-s/data/mysql-5.5.  /mysql.sock <./mysql56-install. sql

Mysql> Grant  All  onSlow_query_log.*  to 'anemometer'@'%'Identified by '123456'; Query OK,0Rows Affected (0.03sec) MySQL> Grant  All  onSlow_query_log.*  to 'anemometer'@'localhost'Identified by '123456'; Query OK,0Rows Affected (0.00sec) MySQL>  Grant Select  on *.*  to  'anemometer'@'%'; Query OK,0Rows Affected (0.00sec) MySQL> Grant  All  onSlow_query_log.*  to 'anemometer'@'localhost'; Query OK,0Rows Affected (0.00sec) MySQL> Grant Select  on *.*  to  'anemometer'@'localhost'; Query OK,0Rows Affected (0.00Sec

Vi. Modify the configuration information of the interface that can be viewed

Modify the PHP configuration, Vim/etc/php.ini Add the following, (if there is already; extension = mysqli.so, just need to remove the semicolon, I was the Yum installed, originally did not, so I added)

Modify the Apache configuration file, vim/etc/httpd/conf/httpd.conf

cat /etc/httpd/conf/httpd.conf  |  grep"ServerName"192.168. 1.128:

Restart httpd, can not access, see HTTPD Log report the following error:

Date_default_timezone_set (): TimeZone ID'CST'is invalidinch/var/www/html/anemometer/lib/anemometer.php on line -[Fri Nov -  the: -: $  the] [ERROR] [Client192.168.1.1] PHP warning:date_default_timezone_get (): It is not safe for rely on the system's timezone settings. you is *required* to use the Date.timezone setting or the DAte_default_timezone_set ()function. Inch CaseYou used an any of those methods and you is still getting this warning, you most likely misspelled the timezone identifier. We selected'asia/chongqing'  for 'cst/8.0/no DST'insteadinch/var/www/html/anemometer/lib/anemometer.php on line -

Modify the next/etc/php.ini

If you restart httpd, or if you cannot access the log or report the error of the same zone, then modify the following:

vim/var/www/html/anemometer/lib/anemometer.php +47 Add the following content:

Visit: Http://192.168.1.128/anemometer, will prompt no global_query_review table, haha, don't worry ha ^.^

Seven, the slow query log through Pt-query-digest analysis after the deposit into the database:

[Root ~]$ pt-query-digest--user=anemometer--password=123456 --socket=/data/mysql-5.5.40/  >--review h=localhost,d=slow_query_log,t=>--history h=localhost,d=slow_query_log,t=  >--no-report--limit=0%--filter="  \ $event->{bytes} = Length (\ $event->{arg}) and \$ Event->{hostname}=\ "$HOSTNAME \"" /data/mysql-5.5.40/localhost-slow.log

If your MySQL is connected by sock way, you must add--socket to specify sock file, execute the above command, the database will be more global_query_review table and global_query _review_history Watch.

Detailed import can view official Help documents and table property descriptions:

http://www.mysqlperformanceblog.com/2012/08/28/hidden-columns-of-query_review_history/

Http://code.google.com/p/maatkit/wiki/EventAttributes

Back to Web-side view:

If you want to know more features or how to use them, it depends on the ^.^

You can refer to the article:

Https://github.com/box/Anemometer

http://blog.itpub.net/26355921/viewspace-1162415/

Visualize MySQL slow query using anemometer based on Pt-query-digest