slowlog监控服务器的anemometer配置
slowlog监控平台搭建步骤:
一、安装percona-toolkit-2.2.14
二、准备相关php模块
yum -y install php-common php-bcmath php-mysql php-dba php-cli php-gd php php-pdo
vim /etc/php.ini
-----
date.timezone = Asia/Shanghai
-----
三、下载并配置Anemometer
https://github.com/box/Anemometer
mv Anemometer /var/www/html/slowlog
cd /var/www/html/slowlog/conf
cp sample.config.inc.php config.inc.php
vim config.inc.php
-----
$conf['datasources']['172.16.170.xxx'] = array(
'host' => '172.16.170.xxx',
'port' => 3306,
'db' => 'slow_query_log',
'user' => 'xxx',
'password' => 'xxx',
……………………
$conf['plugins'] = array(
………………
'explain' => function ($sample) {
………………
$conn['user'] = 'xxx';
$conn['password'] = 'xxx';
-----
note:
172.16.170.xxx为本机IP,可以使用域名代替,但是不能使用localhost,否则页面无法访问
四、MySQL授权,以及数据库导入(与上一步骤的config配置相同)
cd /var/www/html/slowlog
mysql -uroot -p -e "grant ALL ON slow_query_log.* to 'xxx'@'%' IDENTIFIED BY 'xxx';"
mysql -uroot -p < install.sql
mysql -uroot -p < mysql56-install.sql
note
数据库需要加上skip-name-resolve选项,否则页面无法访问
五、创建相关目录,并编写脚本创建计划任务
mkdir /home/lzb/logs
mkdir /home/lzb/sh
vim /home/lzb/sh/slowlog.sh
-----
#/bin/bash
cd /home/lzb/logs/
scp root@172.16.170.yyy:/mysqldata/mysql/data/mysql-slow.log ./ #将线上相关的慢日志拷贝到本目录下
/usr/local/bin/pt-query-digest --user=xxx --password=xxx --review h=172.16.170.xxx,D=slow_query_log,t=global_query_review --history h=172.16.170.xxx,D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\"" /home/lzb/logs/mysql-slow.log
##上面语句是percona-toolkit-2.2.14版本语句
-----
crontab -e
-----
30 15 * * * /bin/bash /home/lzb/sh/slowlog.sh >> /home/lzb/result.log 2>&1
-----
note:
慢日志每天进行切割,防止重复
脚本执行后,可以下一步访问地址
五、访问地址:
http://172.16.170.xxx/slowlog
转载于:https://blog.51cto.com/liuzhanbin/1671735