MySQL Anemometer可视化工具安装-

需要搞清楚pt-query工具

Anemometer基于pt-query-digest将MySQL慢查询可视化

percona-toolkit工具的安装

安装目的：pt-query-digest是percona-toolkit里面一个工具，其作用就是分析慢查询日志，将MySQL慢查询日志进行统计并友好的显示出来

源码安装

下载解压包

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

cdpercona-toolkit-2.2.14/

编译安装工具安装目录在：/usr/local/percona-toolkit/bin

yum installgitperl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5-y
perl Makefile.PLPREFIX=/usr/local/percona-toolkit
make&&makeinstall

配置环境变量

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环境的启动：

systemctlstarthttpd
systemctlstophttpd
systemctlrestarthttpd
systemctl status httpd

查看安装情况

echo'<?php phpinfo();?>'> /var/www/html/phpinfo.php

安装数据库

配置国内清华镜像

cat>/etc/yum.repos.d/mysql-community.repo <<EOF
[mysql57-community]
name=MySQL5.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

启动数据库

systemctlstartmysqld
systemctlrestartmysqld
systemctlstopmysqld
systemctl status mysqld

查看数据库密码

grep'temporary password'/var/log/mysqld.log

使用密码登录

mysql -uroot -p

降低密码强度

setglobalvalidate_password_policy=LOW;
setglobalvalidate_password_length=6;

修改密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
grant all img_height=\”555\” img_width=\”746\” inline=\”0\” src=\”https://img.solves.com.cn/p/2020/03-13/2d6940030a7df2749c3d95cd22e01147.jpg\” />

导入t100w数据

t100w数据库，可以从搜索下载或私信作者索取

下面的sql语句是导入t100w数据表，并执行查询操作

source/root/t100w.sql;
usetest;
selectnum,k1fromt100wwherenum !=0orderbyk1limit10;
selectnum,k1fromt100wwherenum !=10orderbyk1limit100;
selectnum,k1fromt100wwherenum >1000orderbyk1limit100;
selectnum,k1fromt100wwherenum >10000orderbyk1limit10000;
selectnum,k1fromt100wwherenum !=1000orderbyk1limit100;
selectnum,k1fromt100wwherenum !=10orderbyk1limit10000;
select*fromt100wwherenum !=10orderbyk1limit100,300;
select*fromt100wwherenum !=0orderbyk1limit100,3000;
select*fromt100wwherenum >1orderbyk1limit10000,30000;
select*fromt100wwherek1="s"orderbyk1limit10000,30000;
select*fromt100wwherek1="s"orderbyk1limit1000,3000;
selectnum,k1fromt100wwherek1="s"orderbyk1limit1000,3000;
selectnum,k1fromt100wwherek1="s"orderbyk1limit10000,30000;
selectnum,k1fromt100wwherek1="0"orderbyk1limit10000,30000;
selectnum,k1fromt100wwherek1like"%1%"orderbyk1limit10000,30000;
selectnum,k1fromt100wwherek1like"%2%"orderbyk1limit10000,30000;