Shulou(Shulou.com)06/02 Report--

Anemometer how to visualize Mysql slow query log, in response to this problem, this article details the corresponding analysis and solution, hoping to help more small partners who want to solve this problem find a simpler and easier way.

How it works:

Anemometer: SQL visualization for slow queries

pt-query-digest: extract slow query logs

/etc/my.cnf Open Slow Query

【

#slow_query

log_queries_not_using_indexes=1

long_query_time=1

slow_query_log=1

】

Deployment architecture (standalone deployment):

httpd service [Equivalent to tomcat's use to provide services for Anemometer]

pt-query-digest Slow query log capture import [Extract slow sql from slow query log and write it to Anemometer's own database, which will be written into timed tasks later]

Anemometer visual display [Installation directory: /var/www/htm]

Building the Anemometer Framework

Pre-work:

1. Close selinux

setenforce 0

sed -i 's/enforcing/disabled/g' /etc/sysconfig/selinux

2. Open firewall ports 443, 13306, 80

iptables -I INPUT -p tcp --dport 443 -j ACCEPT

iptables -I INPUT -p tcp --dport 80 -j ACCEPT

iptables -I INPUT -p tcp --dport 13306 -j ACCEPT

service iptables save

3. Ensure accurate timing (optional)

yum install -y ntp ntpdate

cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime #Change the time zone to East 8

cp: overwrite `/etc/localtime'? y

service ntpdate start

[root@Master01 ~]# date -R

Tue, 30 Jan 2018 14:45:34 +0800#+0800 is East 8

chkconfig ntpdate on

deployment

1. Install core pt_query_digest (version 2.2.14)

yum install perl-DBI perl-DBD perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey -y

wget --no-check-certificate https://www.percona.com/downloads/percona-toolkit/2.2.14/RPM/percona-toolkit-2.2.14-1.noarch.rpm

yum install -y percona-toolkit-2.2.14-1.noarch.rpm

2. install PHP

Note that PHP must be above version 5.33, otherwise an error will be reported;

The OS of the installation environment is CentOS 6.8, so the php installed directly by yum is version 5.33.

yum install -y php php-mysql php-gd libjpeg* php-imap php-ldap php-odbc php-pear php-xml php-xmlrpc php-mbstring php-mcrypt php-bcmath php-mhash libmcrypt libmcrypt-devel php-fpm php-dba

3. Install httpd

yum install -y httpd

4. Modify the configuration, start php (if you do not modify the time zone, you will report 500 error when starting httpd)

vim /etc/php.ini

date.timezone = Asia/Shanghai

service php-fpm start

5. Preliminary configuration anemometer:

cp -r anemometer /var/www/html/ #anemometer project program download itself

vim /etc/httpd/conf/httpd.conf

Add ServerName 192.168.214.140:80 (IP of anemometer, if port 80 is not used, you need to match other port numbers here)

6. Execute the library build script:

cd /var/www/html/anemometer

mysql -uroot -p Password

< install.sql #当前主机安装了Anemometer需要使用的mysql 数据库 备注：为了简单直接使用了root 账号进行授权 7.进一步配置anemometer： cd /var/www/html/anemometer/conf/ 修改第一处： cp sample.config.inc.php config.inc.php vim /var/www/html/anemometer/conf/config.inc.php 【$conf['datasources']['mysql56']，$conf['datasources']['localhost_history']，$conf['plugins'] 三处的内容】 $conn['user'] = 'anemometer'; $conn['password'] = '密码'; 修改第二处： vim /var/www/html/anemometer/conf/datasource_localhost.inc.php $conf['datasources']['localhost'] = array( 'host' =>

'192.168.214.140', #根据实际情况进行修改

'port' => 3306,

'db' => 'slow_query_log',

'user' => 'backend',

'password' => 'backend',

'tables' => array(

'global_query_review' => 'fact',

'global_query_review_history' => 'dimension'

),

'source_type' => 'slow_query_log'

);

8.启动apache：

service httpd start

打开刚才发布的网页http://192.168.214.140/anemometer

9.添加定时任务：

定时任务脚本如下：

[root@localhost test]# more fetch-slowlog.sh

#!/bin/sh

# turn on debug

set -x

current_host=`/usr/sbin/ip add show eth0 | grep inet | grep -v "inet6" | awk '{print $2}' | cut -d / -f 1`

mysql_cmd=" mysql -uroot -proot -NB "

project=azure-qa-qyd # 项目名称

hostip=${current_host} #target db IP

#db_name=$3 #数据库名称, this can be ignored

#慢查询文件的绝对路径

slowfile=`$mysql_cmd -e "show variables like 'slow_query_log_file'" 2>/dev/null | cut -f2`

# the db host where review and history of slow log store

db_host=192.168.214.140

db_port=3306

pt-query-digest --user=root --password=root \

--review h=${db_host},P=${db_port},D=slow_query_log,t=global_query_review \

--history h=${db_host},P=${db_port},D=slow_query_log,t=global_query_review_history \

--charset=utf8 \

--no-report --limit=0\% \

--filter="\$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"${project}\" and \$event->{hostip}=\"${hostip}\" " \

"${slowfile}"

#clear slow log

echo > $slowfile

$mysql_cmd -e "flush slow logs"

echo "slow log processed!"

[root@localhost test]# crontab -l

*/5 * * * * /opt/test/fetch-slowlog.sh > /tmp/fetch-slowlog.log 2>&1 #每五分钟运行一次

10. 登录后即可看到慢查询对应的数据库的慢sql

关于Anemometer中怎么可视化Mysql慢查询日志问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注行业资讯频道了解更多相关知识。

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.