慢查询日志图形化

前置工作

1.关闭selinux

setenforce 0
sed -i 's/enforcing/disabled/g' /etc/sysconfig/selinux

2.打开防火墙的443,13306,80端口

iptables -I INPUT -p tcp --dport 443 -j ACCEPT
iptables -I INPUT -p tcp --dport 80 -j ACCEPT
iptables -I INPUT -p tcp --dport 13306 -j ACCEPT
service iptables save

3.确保时间准确（非必须）

yum install -y ntp ntpdate
cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime #修改时区为东8区
cp: overwrite `/etc/localtime'? y
service ntpdate start
[root@Master01 ~]# date -R
Tue, 30 Jan 2018 14:45:34 +0800#+0800是东8区
chkconfig ntpdate on

部署工作

1.安装核心组件pt_query_digest（3.2.1版本）

yum install perl-DBI perl-DBD perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey -y
wget --no-check-certificate https://www.percona.com/downloads/percona-toolkit/3.2.1/RPM/percona-toolkit-3.2.1-1.el7.x86_64.rpm
yum install -y percona-toolkit-3.2.1-1.el7.x86_64.rpm

2.安装php

注意php必须是5.33以上的版本，否则报错；

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

3.安装httpd

yum install httpd httpd-devel -y

# 启动httpd
systemctl start httpd

4.修改配置，启动php（不修改时区的话，启动httpd时会报500的错）

vim /etc/php.ini
# 在配置文件最后一行加入
date.timezone = Asia/Shanghai

# 启动php
systemctl start php-fpm

5.初步配置anemometer

# 安装anemometer
git clone https://github.com/box/Anemometer.git anemometer

# 解压
unzip Anemometer-develop.zip 

# 改名 
mv Anemometer-develop anemometer

# 修改目录位置
cp -r anemometer /var/www/html

6.mysql主配置文件设置

 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'
 
 # 开启慢日志
 slow_query_log=1
 
 # 设置慢日志阀值
 long_query_time=0.1
 
 # 重启数据库
 systemctl restart mysqld

7.进一步配置anemometer

# 进入配置文件目录
cd /var/www/html/anemometer/conf

# 修改配置文件来增加explain读取用户密码信息
cp sample.config.inc.php config.inc.php

# 创建数据库用户
mysql> grant all on *.* to 'anemometer'@'localhost' identified by '123456';
mysql> grant all on *.* to 'anemometer'@'%' identified by '123456';
mysql> flush privileges;

# 修改配置文件密码
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

8.导入数据

# 返回上一级
cd ..

# 导入数据
mysql -uanemometer -p123456 -h127.0.0.1 < install.sql

# 重启服务
systemctl restart mysqld httpd

# 访问
10.0.0.54/anemometer