需要搞清楚pt-query工具
Anemometer基于pt-query-digest將MySQL慢查詢可視化
percona-toolkit工具的安裝
安裝目的：pt-query-digest是percona-toolkit里面一個工具，其作用就是分析慢查詢日志，將MySQL慢查詢日志進行統計并友好的顯示出來
源碼安裝
下載解壓包
wget https://www.percona.com/downloads/percona-toolkit/2.2.14/tarball/percona-toolkit-2.2.14.tar.gz
tar xf percona-toolkit-2.2.14.tar.gz
cd percona-toolkit-2.2.14/
編譯安裝工具安裝目錄在：/usr/local/percona-toolkit/bin
yum install git perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5 -y
perl Makefile.PL PREFIX=/usr/local/percona-toolkit
make && make install
配置環境變量
echo 'export PATH=/usr/local/percona-toolkit/bin:$PATH' >> /etc/profile
source /etc/profile
查看版本
pt-query-digest --version
php web環境的搭建
安裝目的：Anemometer需要依賴LAMP環境LAMP環境的安裝：
下面的安裝方式一鍵啟動php和httpd服務，兩者自動關聯，不需要其他配置，省不少事。也可以編譯其他web服務器配合php
Apache安裝
yum install httpd httpd-devel -y
安裝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
修改時區
vim /etc/php.ini
修改
;date.timezone =
為
date.timezone = Asia/Shanghai
apache環境的啟動：
systemctl start httpd
systemctl stop httpd
systemctl restart httpd
systemctl status httpd
查看安裝情況
echo '<?php phpinfo();?>' > /var/www/html/phpinfo.php
安裝數據庫
配置國內清華鏡像
cat >/etc/yum.repos.d/mysql-community.repo <<EOF
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql57-community-el7/
enabled=1
gpgcheck=0
EOF
安裝數據庫服務
yum install mysql-community-server -y
啟動數據庫
systemctl start mysqld
systemctl restart mysqld
systemctl stop mysqld
systemctl status mysqld
查看數據庫密碼
grep 'temporary password' /var/log/mysqld.log
使用密碼登錄
mysql -uroot -p
降低密碼強度
set global validate_password_policy=LOW;
set global validate_password_length=6;
修改密碼
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
grant all on *.* to 'root'@'%' identified by '123456';
grant all on *.* to 'anemometer'@'localhost' identified by '123456';
grant all on *.* to 'anemometer'@'%' identified by '123456';
flush privileges;
時間格式設置
這是因為sql_mode中的NO_ZEROR_DATE導致的，在strict mode中不允許'0000-00-00'作為合法日期
vim /etc/my.cnf
[mysqld]
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
開啟慢日志
vim /etc/my.cnf
[mysqld]
slow_query_log=1
設置慢日志閥值
設置超過0.1秒為慢sql語句
vim /etc/my.cnf
[mysqld]
long_query_time=0.1
重啟數據庫
systemctl restart mysqld
查看慢日志配置
select @@long_query_time;
select @@slow_query_log;
select @@slow_query_log_file;
安裝Anemometer
下載安裝：
cd /var/www/html/
git clone https://github.com/box/Anemometer.git anemometer
修改配置文件增加explain讀取用戶密碼信息
cd anemometer/conf
cp sample.config.inc.php config.inc.php
修改配置文件密碼
sed -i '285s#root#anemometer#g' config.inc.php
sed -i "286s#''#'123456'#g" config.inc.php
修改本地數據源密碼
cp datasource_localhost.inc.php datasource_localhost.inc.php.bak
sed -i "3s#localhost#127.0.0.1#g" datasource_localhost.inc.php
sed -i '6s#root#anemometer#g' datasource_localhost.inc.php
sed -i "7s#''#'123456'#g" datasource_localhost.inc.php
導入數據
cd ..
mysql -uanemometer -p123456 -h127.0.0.1 < install.sql
瀏覽器訪問網站，如下圖所示
導入t100w數據
t100w數據庫，可以從搜索下載或私信作者索取
下面的sql語句是導入t100w數據表，并執行查詢操作
source /root/t100w.sql;
use test;
select num,k1 from t100w where num !=0 order by k1 limit 10;
select num,k1 from t100w where num !=10 order by k1 limit 100;
select num,k1 from t100w where num >1000 order by k1 limit 100;
select num,k1 from t100w where num >10000 order by k1 limit 10000;
select num,k1 from t100w where num !=1000 order by k1 limit 100;
select num,k1 from t100w where num !=10 order by k1 limit 10000;
select * from t100w where num !=10 order by k1 limit 100,300;
select * from t100w where num !=0 order by k1 limit 100,3000;
select * from t100w where num >1 order by k1 limit 10000,30000;
select * from t100w where k1="s" order by k1 limit 10000,30000;
select * from t100w where k1="s" order by k1 limit 1000,3000;
select num,k1 from t100w where k1="s" order by k1 limit 1000,3000;
select num,k1 from t100w where k1="s" order by k1 limit 10000,30000;
select num,k1 from t100w where k1="0" order by k1 limit 10000,30000;
select num,k1 from t100w where k1 like "%1%" order by k1 limit 10000,30000;
select num,k1 from t100w where k1 like "%2%" order by k1 limit 10000,30000;
查看慢日志
出于友好提示，密集恐懼者，請不要執行下面的命令！密集恐懼者，請不要執行下面的命令！密集恐懼者，請不要執行下面的命令！
mysqldumpslow -s c -t 1 /var/lib/mysql/db114-slow.log | more
導入慢查詢日志
pt-query-digest --user=anemometer --password=123456 --review h='192.168.255.114',D=slow_query_log,t=global_query_review --history h='192.168.255.114',D=slow_query_log,t=global_query_review_history --no-report --limit=50% --filter=" $event->{Bytes} = length($event->{arg}) and $event->{hostname}="$HOSTNAME"" /var/lib/mysql/db114-slow.log
如下圖所示
以上就是全部的安裝步驟，明天更新anemometer的使用方法
歡迎在評論區一起討論，質疑。文章都是手打原創，每天最淺顯的介紹運維、數據庫相關的技術，喜歡我的文章就關注一波吧，可以看到最新更新和之前的文章。
