MySQL Anemometer可视化工具安装
2020-03-13
加入收藏
需要搞清楚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的使用方法
欢迎在评论区一起讨论，质疑。文章都是手打原创，每天最浅显的介绍运维、数据库相关的技术，喜欢我的文章就关注一波吧，可以看到最新更新和之前的文章。
