anemometer mysql 500_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

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

$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/slow-query.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 则是读取这里的数据展示。

添加执行计划：需要工具 pt-visual-explain

#下载 pt-visual-explain

cd /usr/bin/

wget percona.com/get/pt-visual-explain

chmod 755 /usr/bin/pt-visual-explain

#配置插件。编辑文件找到 pt-visual-explain 所在插件。

#vim anemometer/conf/config.inc.php

$conf['plugins'] = array(

'visual_explain' => '/usr/bin/pt-visual-explain', # pt-visual-explain 位置

'show_create' => true,

'show_status' => true,

'explain' => function ($sample) {

$conn = array();

$conn['host'] = '10.10.10.9'; #new

$conn['port'] = '3306'; #new

$conn['db'] = 'slow_query_log'; #new

$conn['user'] = 'root'; #new

$conn['password'] = 'mysql'; #new

return $conn; #new

if (!array_key_exists('hostname_max',$sample) or strlen($sample['hostname_max']) < 5)

{

return;

}

$pos = strpos($sample['hostname_max'], ':');

if ($pos === false)

{

$conn['port'] = 3306;

$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'] = 'root';

$conn['password'] = 'mysql';

return $conn;

},

);

如果不指定返回的连接数据，则出错：Error in Query Explain Plugin: Missing field host。这里直接添加。再通过 pt-query-digest 读取慢查询日志到数据库中。查询可以看到执行计划了。