Anemometer工具将慢查询日志中语句存储到数据库中,并生成报告。
安装PHP相关服务及perl语言相关模块(http://blog.csdn.net/u010587433/article/details/50339377),并开启http服务。
同时需要安装Percona Toolkit工具(http://blog.csdn.net/u010587433/article/details/50337627)。
下载:http://download.csdn.net/detail/u010587433/9363483
解压
unzip Anemometer-master.zip
cd Anemometer-master
导入表并授权
mysql < mysql56-install.sql
mysql < install.sql
grant ALL ON slow_query_log.* to 'anemometer'@'%' IDENTIFIED BY '123456';
配置
mkdir -p /var/www/html/anemometer/
cp -r /root/Anemometer-master/* /var/www/html/anemometer/
cd /var/www/html/anemometer/conf
cp sample.config.inc.php config.inc.php
vi /var/www/html/anemometer/conf/config.inc.php
$conf['datasources']['localhost'] = array(
'host' => '192.168.9.130',
'port' => 3306,
'db' => 'slow_query_log',
'user' => 'anemometer',
'password' => '123456',
'tables' => array(
'global_query_review' => 'fact',
'global_query_review_history' => 'dimension'
)
);
$conf['plugins'] = array(
'visual_explain' => '/usr/bin/pt-visual-explain',
...
'explain' => function ($sample) {
$conn['user'] = 'anemometer';
$conn['password'] = '123456';
return $conn;
},
);
vi /var/www/html/anemometer/scripts/anemometer_collect.sh
添加IP
history_db_host=192.168.9.130
PATH=/home/mysql/product/5.6/mysql-1/bin:$PATH #如果执行脚本时报无法找到mysql,则添加此行
注意:脚本中用到的pt-visual-explain用于生成慢查询的执行计划,且是绝对路径,所以最好
把pt-*命令复制到/usr/bin目录下
cp /usr/local/bin/pt-* /usr/bin/
或创建软连接
ln -s /usr/local/bin/pt-* /usr/bin/
定时收集慢查询日志信息,可将脚本部署到多个MySQL服务器上
crontab -l
*/5 * * * * /var/www/html/anemometer/scripts/anemometer_collect.sh >> /tmp/anemometer.log 2 > 1&
访问即可查看所有慢查询语句
http://192.168.9.130/anemometer
参考:
http://ourmysql.com/archives/1359