Anemometer MySQL 慢查询日志监控平台

Anemometer 是一款开源的(慢查询)日志监控平台,当前主要用于 MySQL 的慢查询日志跟踪。

Anemometer 演示地址:http://lab.fordba.com/anemometer/
Anemometer 项目地址:https://github.com/box/Anemometer


需要工具:

MySQL (用于保存收集的各数据库慢查询日志)

pt-query-digest (日志收集工具,Percona Toolkit)

Apache & PHP 5.5+ (web 服务,浏览器端可视化操作)

Anemometer (必须的)


当前示例属于联网操作,就不一个个下载安装和配置了,系统为 CentOS 7 。

(MySQL 安装略)

Apache 安装:

#安装 Apache  
yum -y install httpd httpd-devel 

#启动 Apache  
service httpd start

#浏览器输地址,此时可打开《Apache 2 Test Page》  
http://10.10.10.9/  
php5.7 安装配置:
#安装 php5.7
rpm -Uvh https://mirror.webtatic.com/yum/el7/epel-release.rpm
rpm -Uvh https://mirror.webtatic.com/yum/el7/webtatic-release.rpm

yum -y install php70w
yum -y install php70w-mysql php70w-common php70w-gd php70w-odbc php70w-pear php70w-xml php70w-bcmath php70w-mbstring

#添加文件info.php
vim /var/www/html/info.php

<?php
phpinfo();
?>

#浏览器输入网址,此时可打开《PHP Version 7.0.30》  
http://10.10.10.9/info.php 


#设置php时区
vim /etc/php.ini

[Date]
date.timezone ="Asia/Shanghai"
anemometer 部署:

cd /var/www/html/
git clone git://github.com/box/Anemometer.git anemometer
mysql -u root -p < install.sql
cp conf/sample.config.inc.php conf/config.inc.php
#本地日志监控:vim conf/datasource_localhost.inc.php
<?php
$conf['datasources']['localhost'] = array(
        'host'  => '127.0.0.1',
        'port'  => 3306,
        'db'    => 'slow_query_log',
        'user'  => 'root',
        'password' => 'mysql',
        'tables' => array(
                'global_query_review' => 'fact',
                'global_query_review_history' => 'dimension'
        ),
        'source_type' => 'slow_query_log'
);
?>

#远程服务器日志监控:vim conf/config.inc.php
$conf['datasources']['10.10.10.9'] = array(
        'host'  => '10.10.10.9',
        'port'  => 3306,
        'db'    => 'slow_query_log',
        'user'  => 'root',
        'password' => 'mysql',
        'tables' => array(
                'global_query_review' => 'fact',
                'global_query_review_history' => 'dimension'
        ),
        'source_type' => 'slow_query_log'
);

(此处省略N行……)
	$conn['user'] = 'root';
	$conn['password'] = 'mysql';
#启动httpd服务
service httpd restart

#浏览器输入网址
http://10.10.10.9/anemometer/index.php

我是将 Anemometer 安装在虚拟机上的(IP:10.10.10.9),虚拟机防火墙已关闭。上图为主机(IP:10.10.10.100)浏览器远程访问。 但是,这里选择数据源跳转的时候会报错!!(查看 php 日志: tail /var/log/httpd/error_log)

[Wed Jun 13 16:10:49.328888 2018] [:error] [pid 30208] [client 10.10.10.100:61565] PHP Warning:  
mysqli::__construct(): (HY000/2003): Can't connect to MySQL server on '127.0.0.1' (13) 
in /var/www/html/anemometer/lib/AnemometerModel.php on line 280, referer: http://10.10.10.9/anemometer/

[Wed Jun 13 16:10:49.328973 2018] [:error] [pid 30208] [client 10.10.10.100:61565] PHP Fatal error:  
Uncaught Exception: Can't connect to MySQL server on '127.0.0.1' (13) 
in /var/www/html/anemometer/lib/AnemometerModel.php:282\nStack trace:\n#0 
/var/www/html/anemometer/lib/Anemometer.php(65): AnemometerModel->connect_to_datasource()
\n#1 /var/www/html/anemometer/index.php(36): Anemometer->__construct(Array)\n#2 {main}\n  
thrown in /var/www/html/anemometer/lib/AnemometerModel.php on line 282, referer: http://10.10.10.9/anemometer/


百思不得其解,百度谷歌了好久,终于找到了解决方法:

SQLSTATE[HY000] [2003] Can’t connect to MySQL server on ‘xxx.xxx.xxx.xxx’ (13)

setsebool -P httpd_can_network_connect=1

pt-query-digest 下载(只该工具):

# 下载 pt-query-digest
cd /var/www/html/
yum -y install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5.x86_64
wget percona.com/get/pt-query-digest
chmod u+x pt-query-digest

MySQL 慢查询日志收集:

#启用慢查询日志,设置后重启MySQL服务
#vim /etc/my.cfg
slow_query_log =1
log_output='File'
long_query_time=5
log_slow_admin_statements=1 
slow_query_log_file=/usr/local/mysql/data/server223-slow.log 
#查看MySQL变量设置情况
mysql> show variables where Variable_name in ('slow_query_log','log_output','long_query_time','log_slow_admin_statements','slow_query_log_file'); 
+---------------------------+--------------------------------------+
| Variable_name             | Value                                |
+---------------------------+--------------------------------------+
| log_output                | FILE                                 |
| log_slow_admin_statements | ON                                   |
| long_query_time           | 5.000000                             |
| slow_query_log            | ON                                   |
| slow_query_log_file       | /usr/local/mysql/data/slow-query.log |
+---------------------------+--------------------------------------+
#收集慢查询日志到数据库 (需要任务定时执行收集)
./pt-query-digest --user=root --password=mysql \
--review h=10.10.10.9,D=slow_query_log,t=global_query_review \
--history h=10.10.10.9,D=slow_query_log,t=global_query_review_history \
--no-report --limit=0% \
--filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\"" \
/usr/local/mysql/data/slow-query.log

每次收集记录都会记录在数据库 slow_query_log 中,Anemometer 则是读取这里的数据展示。



文章来源: Anemometer MySQL 慢查询日志监控平台

人吐槽 人点赞

猜你喜欢

发表评论

用户名: 密码:
验证码: 匿名发表

你可以使用这些语言

查看评论:Anemometer MySQL 慢查询日志监控平台