anemometer安装

1.背景介绍：
nginx:1.9.3 　　　　安装路径/data/nginx
php:5.5.27 　　　　安装路径 /data/php
mysql:5.7.18 　　　　 安装路径/usr/local/mysql
软件下载目录 /usr/local/src
2.nginx安装
# yum install pcre*
# yum install openssl*
# yum install openssl-devel
# cd /usr/local/src
# wget http://nginx.org/download/nginx-1.9.3.tar.gz
# tar -zxvf nginx-1.9.3.tar.gz
# cd nginx-1.9.3
# ./configure --prefix=/data/nginx \
--with-http_ssl_module --with-http_spdy_module \
--with-http_stub_status_module \
--with-pcre
# make && make install
# ln –s /data/nginx /usr/local/nginx
# mkdir /data/nginx/sites-enabled
# cd /data/nginx/conf
# vi nginx.conf

#在http{}内，server前添加以下内容
include /usr/local/nginx/sites-enabled/*.conf;

# vi /data/nginx/conf/fcgi.conf

fastcgi_param GATEWAY_INTERFACE CGI/1.1;
fastcgi_param SERVER_SOFTWARE nginx;
fastcgi_param QUERY_STRING $query_string;
fastcgi_param REQUEST_METHOD $request_method;
fastcgi_param CONTENT_TYPE $content_type;
fastcgi_param CONTENT_LENGTH $content_length;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
fastcgi_param SCRIPT_NAME $fastcgi_script_name;
fastcgi_param REQUEST_URI $request_uri;
fastcgi_param DOCUMENT_URI $document_uri;
fastcgi_param DOCUMENT_ROOT $document_root;
fastcgi_param SERVER_PROTOCOL $server_protocol;
fastcgi_param REMOTE_ADDR $remote_addr;
fastcgi_param REMOTE_PORT $remote_port;
fastcgi_param SERVER_ADDR $server_addr;
fastcgi_param SERVER_PORT $server_port;
fastcgi_param SERVER_NAME $server_name;
# PHP only, required if PHP was built with --enable-force-cgi-redirect
fastcgi_param REDIRECT_STATUS 200;

# cd /data/nginx/sites-enabled
# vi hdqa.anemometer.com.conf

#for dba-test-hzj01.qa.hengjs.com
log_format anemometer '$http_x_real_ip - $remote_user [$time_local] "$request" '
'$status $body_bytes_sent "$http_referer" '
'"$http_user_agent" $http_x_forwarded_for' '"$request_time"';
access_log /usr/local/nginx/logs/access_anemometer.log anemometer;
server
{
listen 8082;
server_name dba-test-hzj01.qa.hengjs.com;
if ($http_user_agent ~* "(Googlebot|Baiduspider|Sosospider)" ) {
return 403;
}
root /var/www/html/anemometer/;
index index.php;
location ~ "\.(js|ico|gif|jpg|png|css)$" {
expires 1w;
}
location / {
fastcgi_ignore_client_abort on;
fastcgi_pass 127.0.0.1:9000;
fastcgi_index index.php;
include fcgi.conf;
index index.php;
if (!-e $request_filename) {
rewrite . /index.php last;
break;
}
}
}

检查下配置是否有问题：
/usr/local/nginx/sbin/nginx -t

启动nginx
/usr/local/nginx/sbin/nginx
3.php安装
# yum install gcc make gd-devel libjpeg-turbo-devel libpng-devel libxml2-devel bzip2-devel libcurl-devel –y
# cd /etc/yum.repos.d/
# wget http://www.atomicorp.com/installers/atomic
# bash atomic
# yum install php-mcrypt -y
# yum install libmcrypt -y
# yum install libmcrypt-devel -y
# ./configure --prefix=/data/php \
--enable-fpm \
--with-fpm-user=daemon \
--with-fpm-group=daemon \
--with-config-file-path=/usr/local/php/etc \
--with-mysql=mysqlnd \
--with-mysqli=mysqlnd \
--with-pdo-mysql=mysqlnd \
--with-iconv \
--with-iconv-dir \
--with-freetype-dir \
--with-jpeg-dir \
--with-png-dir \
--with-gd \
--with-zlib \
--with-libxml-dir \
--with-curl \
--with-mhash \
--with-xmlrpc \
--with-mcrypt \
--with-openssl \
--enable-xml \
--disable-rpath \
--enable-bcmath \
--enable-shmop \
--enable-sysvsem \
--enable-inline-optimization \
--enable-mbregex \
--enable-mbstring \
--enable-gd-native-ttf \
--enable-ftp \
--enable-pcntl \
--enable-sockets \
--enable-zip \
--enable-soap \
--disable-debug \
--disable-ipv6 \
--with-gettext \
--enable-calendar \
--enable-dom
# make && make install
# ln –s /data/php /usr/local/php
# vi /etc/profile #添加变量

export PHP_HOME=/usr/local/php
export PATH=$PATH:$PHP_HOME/bin

# source /etc/profile
# cd /usr/local/src/php-5.5.27
# cp -rf sapi/fpm/php-fpm.conf     /usr/local/php/etc/
# cp -rf sapi/fpm/init.d.php-fpm    /etc/init.d/php-fpm
# cp -rf php.ini-production         /usr/local/php/etc/php.ini
# echo "/etc/init.d/php-fpm start">>/etc/rc.local
# cd /usr/local/php/etc
# echo ";error_log = /usr/local/php/var/log/php-fpm.log
;log_level = notice
;emergency_restart_threshold = 10
;emergency_restart_interval = 1m
;process_control_timeout = 5
;daemonize = yes
;listen.owner = daemon
;listen.group = daemon
;listen.mode = 0666
;pm = dynamic
;pm.max_children = 256
;pm.min_spare_servers = 128
;pm.max_spare_servers = 256
;pm.max_requests = 1024（*）
;request_terminate_timeout = 600s（*）
;request_slowlog_timeout = 300s
;rlimit_files = 65536
;rlimit_core = 65536">> php-fpm.conf
# vi /usr/local/php/etc/php.ini

max_execution_time = 300    
memory_limit = 128M
post_max_size = 16M
upload_max_filesize = 2M
max_input_time = 300
date.timezone = PRC

vi /etc/php.ini

　[Date]

　date.timezone = Asia/Chongqing

[MySQLi]
extension=php_mysqli.dll
extension=mysqli.so

#启动php

# /etc/init.d/php-fpm start
4.安装mysql server

（略）
5.安装anemometer
wget http://www.percona.com/downloads/percona-toolkit/2.2.12/deb/percona-toolkit_2.2.12.tar.gz

#安装pt工具

tar zxf percona-toolkit_2.2.12.tar.gz
cd percona-toolkit_2.2.12
perl Makefile.PL
make && make install

ln -s /usr/local/bin/pt-visual-explain /usr/bin/pt-visual-explain  

#安装anemometer

unzip Anemometer-master.zip
mv Anemometer-master /var/www/html/anemometer

mysql -uroot -p</var/www/html/anemometer/mysql56-install.sql
mysql -uroot -p </var/www/html/anemometer/install.sql
mysql -uroot -p </var/www/html/anemometer/mysql56-save_history.sql
grant all on slow_query_log.* to 'anemometer'@'%' identified by '123456';

cd /var/www/html/anemometer/conf
mv sample.config.inc.php config.inc.php  （修改此文件，可以实现很多定制化的功能）

$conf['datasources']['localhost'] = array(
'host' => 'localhost',
'port' => 43306,
'db' => 'slow_query_log',
'user' => 'anemometer',
'password' => '123456',
'tables' => array(
'global_query_review' => 'fact',
'global_query_review_history' => 'dimension'
),
'source_type' => 'slow_query_log'
);
...
$conf['plugins'] = array(
'visual_explain' => '/usr/bin/pt-visual-explain',
# percona toolkit has removed query advisor
# 'query_advisor' => '/usr/bin/pt-query-advisor',
'show_create' => true,
'show_status' => true,
'explain' => function ($sample) {
$conn = array();
if (!array_key_exists('hostname_max',$sample) or strlen($sample['hostname_max']) < 5)
{
return;
}
$pos = strpos($sample['hostname_max'], ':');
if ($pos === false)
{
$conn['port'] = 43306;
$conn['host'] = $sample['hostname_max'];
}
else
{
$parts = preg_split("/:/", $sample['hostname_max']);
$conn['host'] = $parts[0];
$conn['port'] = $parts[1];
}
$conn['db'] = 'mysql';
if ($sample['db_max'] != '')
{
$conn['db'] = $sample['db_max'];
}
$conn['user'] = 'anemometer';
$conn['password'] = '123456';
return $conn;
},
);

vi /var/www/html/anemometer/lib/Anemometer.php

$timezone = ini_get('date.timezone','Asia/Shanghai');

6.启动访问

/etc/init.d/php-fpm start
/usr/local/nginx/sbin/nginx -s reload
http://172.16.59.97:8082

7.采集服务器部署

安装pt软件

# yum install perl-Digest-MD5
# wget https://www.percona.com/downloads/percona-toolkit/2.2.15/tarball/percona-toolkit-2.2.15.tar.gz
# tar zxvf percona-toolkit-2.2.15.tar.gz
# cd percona-toolkit-2.2.15
# perl Makefile.PL install
# make && make install

# ln -s /usr/local/bin/pt-visual-explain /usr/bin/pt-visual-explain

创建anemometer用户：
mysql> GRANT SELECT ON *.* TO 'anemometer'@'172.16.59.97' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL PRIVILEGES ON `slow_query_log`.* TO 'anemometer'@'172.16.59.97'；
Query OK, 0 rows affected (0.02 sec)

部署采集脚本：

# vi /home/mysql/bin/collect_slow_log.sh
echo "begin "`date`
/usr/local/bin/pt-query-digest --user=anemometer --password=123456 --socket=/tmp/mysql.sock --port=43306 --since=24h --review h='172.16.59.97',D=slow_query_log,t=global_query
_review --history h='172.16.59.97',D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter=" \$event->{Bytes} = length(\$ev
ent->{arg}) and \$event->{hostname}=\"$HOSTNAME\"" /data/mysql/log/mysql-slow.log
echo "end "`date`

chown -R mysql.mysql /home/mysql/bin/collect_slow_log.sh 

chmod a+x collect_slow_log.sh
# 定时任务
10 2 * * * /home/mysql/bin/collect_slow_log.sh >>/home/mysql/log/collect_slow_log.log 2>&1

 8.碰到的问题 

Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at Makefile.PL line 1.
BEGIN failed--compilation aborted at Makefile.PL line 1.
解决办法：
yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker

/usr/local/bin/pt-query-digest
Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/local/bin/pt-query-digest line 2434.
BEGIN failed--compilation aborted at /usr/local/bin/pt-query-digest line 2434.
解决办法：
yum -y install perl-Digest-MD