轉(zhuǎn)載自 https://blog.csdn.net/enweitech/article/details/80243722

最近玩MySQL，發(fā)現(xiàn)了一個很不錯的工具，可以把MySQL慢查詢可視化，方便我們?nèi)フ页龊头治雎冋Z句，搭建的步驟不多，但網(wǎng)上詳細(xì)教程比較少，說得也不夠詳細(xì)，一不小心，估計得蛋痛一會，哈哈

Percona Toolkit 是一組高級的命令行工具，用來管理 MySQL 和系統(tǒng)任務(wù)，主要包括：

1、驗證主節(jié)點和復(fù)制數(shù)據(jù)的一致性

2、有效的對記錄行進(jìn)行歸檔

3、找出重復(fù)的索引

4、總結(jié) MySQL 服務(wù)器

5、從日志和 tcpdump 中分析查詢

6、問題發(fā)生時收集重要的系統(tǒng)信息

一、PT安裝：

方法一：rpm包安裝

[root ~]$wgethttp://www.percona.com/downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.12-1.noarch.rpm[root ~]$yuminstallperl-IO-Socket-SSLperl-DBD-MySQLperl-Time-HiRes -y

[root ~]$ rpm -ivh percona-toolkit-2.2.12-1.noarch.rpm? ? ? ? ? ? ? ? ? ?

warning: percona-toolkit-2.2.12-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY

Preparing...? ? ? ? ? ? ? ? ########################################### [100%]

? 1:percona-toolkit? ? ? ? ########################################### [100%]

如果yum 安裝不上依賴包，則檢查下epel源，也可以查看http://www.cnblogs.com/xuanzhi201111/p/4040761.html去解決！

方法二：源碼包安裝

[root ~]$wgethttp://www.percona.com/downloads/percona-toolkit/2.2.12/deb/percona-toolkit_2.2.12.tar.gz[root ~]$tarzxf? percona-toolkit_2.2.12.tar.gz

[root ~]$cd percona-toolkit_2.2.12[root percona-toolkit-2.2.12]$perl Makefile.PL

[root percona-toolkit-2.2.12]$make&&makeinstall

工具安裝目錄在：/usr/local/bin

二、下載Anemometer

官網(wǎng)：https://github.com/box/Anemometer

三、安裝httpd php,php版本要大于5.3，否則就報錯，除此之外還需要：bcmath，php必須支持pdo_mysql、php_mysqli模塊，下面我們來安裝一下：

[root ~]$yuminstallhttpd php *bcmath* *mysqli*? -y

如果epel源像以下的，說明是舊的，該源沒有php_mysqli相關(guān)模塊的

[rootyum.repos.d]$ rpm -q epel-release

epel-release-5-4.noarch

以下版本才有：

[rootyum.repos.d]$ rpm -q epel-release

epel-release-6-8.noarch

我的mysql早已經(jīng)安裝好了的，這里就不多說了

四、將Anemometer文件包解壓，重命名為anemometer，并移動到/var/www/html 下(apache默認(rèn)路徑)

[root ~]$unzipAnemometer-master.zip[root ~]$mvAnemometer-master /var/www/html/anemometer

五、導(dǎo)入anemometer目錄下的install.sql，并給該庫對應(yīng)的權(quán)限：

[root anemometer]$pwd/var/www/html/anemometer

[root anemometer]$? mysql -uroot -p123456 -S /data/mysql-5.5.40/mysql.sock <./mysql56-install.sql

mysql>grantallonslow_query_log.*to'anemometer'@'%'identifiedby'123456';

Query OK, 0rows affected (0.03 sec)

mysql>grantallonslow_query_log.*to'anemometer'@'localhost'identifiedby'123456';?

Query OK, 0rows affected (0.00 sec)

mysql>grantselecton*.*to'anemometer'@'%';

Query OK, 0rows affected (0.00 sec)

mysql>grantallonslow_query_log.*to'anemometer'@'localhost';

Query OK, 0rows affected (0.00 sec)

mysql>grantselecton*.*to'anemometer'@'localhost';

Query OK, 0rows affected (0.00sec)

六、修改可以視化界面的配置信息

修改php配置，vim /etc/php.ini添加以下內(nèi)容，（如果本來就有;extension = mysqli.so,只需要把分號去掉即可，我的是yum安裝的，本來沒有，所以自己加）

修改apache的配置文件，vim /etc/httpd/conf/httpd.conf

[root conf]$cat/etc/httpd/conf/httpd.conf? |grep"ServerName"# ServerName gives the name and port that the server uses to identify itself.

ServerName 192.168.1.128:80

重啟httpd，訪問不了，看httpd的日志報以下錯：

date_default_timezone_set(): Timezone ID'CST'is invalidin/var/www/html/anemometer/lib/Anemometer.php on line47[Fri Nov 2815:47:572014] [error] [client192.168.1.1] PHP Warning:? date_default_timezone_get(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set()function. Incaseyou used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected'Asia/Chongqing'for'CST/8.0/no DST'instead in/var/www/html/anemometer/lib/Anemometer.php on line48

修改下/etc/php.ini

如果重啟httpd，還是訪問不了，日志還是報同時區(qū)的錯誤，則再修改以下的：

vim /var/www/html/anemometer/lib/Anemometer.php +47? 添加下內(nèi)容：

訪問：http://192.168.1.128/anemometer，會提示沒有g(shù)lobal_query_review表，哈哈，別著急哈^.^

七、將慢查詢?nèi)罩就ㄟ^pt-query-digest分析后存入數(shù)據(jù)庫中：

[root ~]$ pt-query-digest --user=anemometer --password=123456--socket=/data/mysql-5.5.40/mysql.sock \> --review h=localhost,D=slow_query_log,t=global_query_review \> --history h=localhost,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-5.5.40/localhost-slow.log

如果你的mysql是通過sock方式連接的話，必須加上--socket指定sock文件，執(zhí)行完以上的命令，數(shù)據(jù)庫里就會多出global_query_review?表和global_query_review_history表了

詳細(xì)導(dǎo)入可以查看官方幫助文檔和表屬性說明：

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

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

回到web端查看：

想了解更多功能，或者怎么使用，這要看大家了^.^

可以參考文章：

https://github.com/box/Anemometer

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

【知識擴展】

MySQL 慢日志簡介

MySQL慢日志（slow query log）想必大家都有聽說，它是用來記錄MySQL中的慢SQL（執(zhí)行耗時超過 long_query_time 預(yù)設(shè)時間的SQL），并且掃描的行數(shù)超過min_examined_row_limit（如果也設(shè)置了這個選項的話），或者需要全表/全索引掃描的SQL（如果設(shè)置了 log_queries_not_using_indexes 選項的話，并不是指所有沒使用索引的SQL），這些SQL效率通常較低，是可能影響MySQL的性能，DBA需要定期優(yōu)化這些SQL。

在MySQL中，如果一個SQL需要長時間等待獲取鎖資源，那么這段獲取鎖的等待時間并不算執(zhí)行時間，當(dāng)SQL執(zhí)行完成，釋放相應(yīng)的鎖，才會記錄到慢日志中，所以MySQL的慢日志中記錄的順序和實際的執(zhí)行順序可能不一樣。

在默認(rèn)情況下，MySQL的慢日志記錄是關(guān)閉的，我們可以通過將設(shè)置slow_query_log=1來打開MySQL的慢查詢?nèi)罩?，通過slow_query_log_file=file_name來設(shè)置慢查詢的文件名，如果文件名沒有設(shè)置，他的默認(rèn)名字為host_name-slow.log。同時，我們也可以設(shè)置 log-output={FILE|TABLE}來指定慢日志是寫到文件還是數(shù)據(jù)庫里面（如果設(shè)置log-output=NONE，將不進(jìn)行慢日志記錄，即使slow_query_log=1）。

默認(rèn)地，MySQL的管理維護命令的慢SQL并不會被記錄到MySQL慢日志中。常見的管理維護命令包括ALTER TABLE,ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, 和REPAIR TABLE。如果希望MySQL的慢日志記錄這類長時間執(zhí)行的命令，可以設(shè)置log_slow_admin_statements = 1。

通過設(shè)置log_queries_not_using_indexes=1，MySQL的慢日志也能記錄那些沒使用索引的SQL（并不需要超過long_query_time，兩者條件滿足一個即可）。但打開該選項的時候，如果你的數(shù)據(jù)庫中存在大量沒有使用索引的SQL，那么MySQL慢日志的記錄量將非常大，所以通常還需要設(shè)置參數(shù)log_throttle_queries_not_using_indexes 。默認(rèn)情況下，該參數(shù)為0，表示不限制，當(dāng)設(shè)置改參數(shù)為大于0的值的時候，表示MySQL在一分鐘內(nèi)記錄的沒使用索引的SQL的數(shù)量，來避免慢日志記錄過多的該類SQL。

在MySQL 5.7.2 之后，如果設(shè)置了慢日志是寫到文件里，需要設(shè)置log_timestamps 來控制寫入到慢日志文件里面的時區(qū)（該參數(shù)同時影響general日志和err日志），不設(shè)置的話，log文件中的記錄采用UTC時間，而非本地時間。如果設(shè)置慢日志是寫入到數(shù)據(jù)庫中，該參數(shù)將不產(chǎn)生作用。

所以，總結(jié)下哪些SQL能被MySQL慢日志記錄：

不會記錄MySQL中的管理維護命令，除非明確設(shè)置log_slow_admin_statements=1;

SQL執(zhí)行時間必須超過long_query_time，（不包括鎖等待時間）

參數(shù)log_queries_not_using_indexes設(shè)置為1，且SQL沒有用到索引，同時沒有超過log_throttle_queries_not_using_indexes 參數(shù)的設(shè)定。

查詢examine的行數(shù)必須超過min_examined_row_limit

注1：如果表沒有數(shù)據(jù)或者只有1條數(shù)據(jù)，優(yōu)化器覺得即便走索引對效率并沒幫助，哪怕 log_queries_not_using_indexes=1 也不會記錄到慢日志中。

注2：如果SQL在QC命中了，也不會記錄到慢日志中。

注3：修改密碼之類的維護操作，密碼部分將會被星號代替，避免明文顯示。

Anemometer 簡介

項目地址：https://github.com/box/Anemometer

演示地址：http://lab.fordba.com/anemometer/

Anemometer 是一個圖形化顯示從MySQL慢日志的工具。結(jié)合pt-query-digest，Anemometer可以很輕松的幫你去分析慢查詢?nèi)罩?，讓你很容易就能找到哪些SQL需要優(yōu)化。

如果你想要使用Anemometer這個工具，那么你需要準(zhǔn)備以下環(huán)境：

一個用來存儲分析數(shù)據(jù)的MySQL數(shù)據(jù)庫

pt-query-digest. (doc: Percona Toolkit )

MySQL數(shù)據(jù)庫的慢查詢?nèi)罩?(doc: The Slow Query Log )

PHP版本為 5.5+，apache或者nginx等web服務(wù)器均可。

安裝

下載Anemometer

git clone git://github.com/box/Anemometer.git anemometer

載入數(shù)據(jù)

首先創(chuàng)建表結(jié)構(gòu)，將global_query_review 以及global_query_review_history 創(chuàng)建出來。由于表定義中存在0000-00-00 00:00:00 的日期默認(rèn)值，需要修改sql_mode，將其zero_date的sql_mode 關(guān)閉，同時關(guān)閉only_full_group_by

cd /www/lab/anemometermysql -f < ./install.sql

現(xiàn)在需要使用pt-query-digest 抓取MySQL的慢查詢?nèi)罩?，然后將?shù)據(jù)插入到slow_query_log 數(shù)據(jù)庫的相應(yīng)表中。

使用如下方式載入數(shù)據(jù)，h表示主機名或者ip地址，D表示database，t表示表名，再最后面跟上慢日志路徑。

如果 pt-query-digest version > 2.2：

$ pt-query-digest --user=anemometer --password=superSecurePass --review h=127.0.0.1,D=slow_query_log,t=global_query_review --review-history h=127.0.0.1,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/slow-query.log

如果 pt-query-digest version <= 2.2

$ pt-query-digest --user=root --password=root --review h=127.0.0.1,D=slow_query_log,t=global_query_review --history h=127.0.0.1,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/slow-query.logPipeline process 11 (aggregate fingerprint) caused an error: Argument "57A" isn't numeric in numeric gt (>) at (eval 40) line 6, <>; line 27.Pipeline process 11 (aggregate fingerprint) caused an error: Argument "57B" isn't numeric in numeric gt (>) at (eval 40) line 6, <> line 28.Pipeline process 11 (aggregate fingerprint) caused an error: Argument "57C" isn't numeric in numeric gt (>) at (eval 40) line 6, <> line 29.

如果你看到一些報錯如上面例子所示，腳本并沒有出現(xiàn)問題，他只是輸出當(dāng)前的操作。

配置Anemometer

修改Anemometer配置文件

$ cd anemometer/conf$ cp sample.config.inc.php config.inc.php

示例的配置文件中，你需要進(jìn)行部分修改，用來連接數(shù)據(jù)庫獲取慢查詢的分析數(shù)據(jù)。

修改 datasource_localhost.inc.php 文件中的配置，主要為主機

$conf['datasources']['localhost'] = array( 'host' => '127.0.0.1', 'port' => 3306, 'db' => 'slow_query_log', 'user' => 'root', 'password' => 'root', 'tables' => array( 'global_query_review' => 'fact', 'global_query_review_history' => 'dimension' ), 'source_type' => 'slow_query_log');

然后訪問127.0.0.1/anemometer 的時候出現(xiàn)

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'slow_query_log.dimension.sample' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (1055)

需要將sql_mode 中only_full_group_by 關(guān)閉。

如果你想利用Anemometer 的explain功能來獲取執(zhí)行計劃，修改配置文件的以下部分。

$conf['plugins'] = array( 'visual_explain' => '/usr/local/bin/pt-visual-explain', --這里需要修改為正確的路徑 ... 'explain' => function ($sample) { $conn['user'] = 'anemometer'; $conn['password'] = 'superSecurePass'; return $conn; },);

結(jié)果展示

在5.7中，默認(rèn)SQL_MODE是啟用ONLY_FULL_GROUP_BY的，需要將其關(guān)閉，否則Anemometer將報錯。

選擇相應(yīng)的列，然后點擊search，就可以顯示結(jié)果

sql執(zhí)行計劃查看以及歷史

當(dāng)我們選擇一個sql的hash值的時候，能看到他的一個具體的執(zhí)行計劃，同時也能看到匹配該sql的歷史sql，消耗，表的統(tǒng)計信息，建表語句等。

同時也能針對sql進(jìn)行評論，為sql優(yōu)化提交建議等。

創(chuàng)建自動收集慢日志腳本

在anemometer下面的文件中有個收集腳本，可以通過crontab進(jìn)行定時收集慢日志，語法如下：

Usage: ./s/anemometer_collect.sh --interval Options: --socket -S The mysql socket to use --defaults-file The defaults file to use for the client --interval -i The collection duration --rate Set log_slow_rate_limit (For Percona MySQL Only) --history-db-host Hostname of anemometer database server --history-db-port Port of anemometer database server --history-db-name Database name of anemometer database server (Default slow_query_log) --history-defaults-file Defaults file to pass to pt-query-digest for connecting to the remote anemometer database

示例腳本：

cd anemometer mkdir etccd etcvi anemometer.local.cnf --這里創(chuàng)建配置文件，添加用戶名密碼[client]user=anemometer_localpassword=superSecurePass./s/anemometer_collect.sh --interval 30 --history-db-host=127.0.0.1

葉師傅補充

我以前的Anemometer玩法是這樣的。

把多個主機的slow log匯聚到一起，調(diào)用pt-query-digest解析并寫入db，方便slow log的統(tǒng)一管理、解析、展示，而不是每個實例都單獨部署一套；

每個主機上可能會跑多實例，其slow log的文件命名，會至少體現(xiàn)IP、端口、業(yè)務(wù)名，比如：slowquery-192.168.0.1:3306-yejr.log；

調(diào)用pt-query-digest解析時，傳遞給 hostname 參數(shù)的值為 "192.168.0.1:3306"，這樣一來，每個實例都可以被單獨處理列出，更加直觀；

通常，每個實例都有統(tǒng)一的監(jiān)控賬號，Anemometer可以利用這個賬號來連接遠(yuǎn)程實例，查看解析SQL執(zhí)行計劃。