mysql慢查询可视化

mysql实例多了，直接查slowlog文件即便用某些脚本自动处理文件，效率也是极其低下，发现Anemometer很好用。

官方文档：https://github.com/box/Anemometer

一般一台4C4G的虚拟机作为Anemometer足够了，假设IP：1.1.1.1

安装apache 

# cd apr-1.4.5
# ./configure --prefix=/usr/local/apr
# make && make install

# cd apr-util-1.3.12
# ./configure --prefix=/usr/local/apr-util -with-apr=/usr/local/apr/bin/apr-1-config
# make && make install

# cd pcre-8.10
# ./configure --prefix=/usr/local/pcre
# make && make install

# cd httpd-2.4.23
# ./configure --prefix=/usr/local/apache2 --enable-rewrite --enable-so --enable-headers --enable-expires 
--with-mpm=worker --enable-modules=most --enable-deflate 
--with-apr=/usr/local/apr --with-apr-util=/usr/local/apr-util --with-pcre=/usr/local/pcre
# make
# make install
# cp /usr/local/apache2/bin/apachectl /etc/init.d/httpd

# /etc/init.d/httpd start
# netstat -anp | grep 80
tcp        0      0 :::80                       :::*                        LISTEN      17664/httpd

测试：
http://1.1.1.1

安装PHP

# rpm -Uvh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

# yum install gcc bison bison-devel zlib-devel libmcrypt-devel 
mcrypt mhash-devel openssl-devel libxml2-devel libcurl-devel 
bzip2-devel readline-devel libedit-devel sqlite-devel

# rpm -q gcc bison bison-devel zlib-devel libmcrypt-devel 
mcrypt mhash-devel openssl-devel libxml2-devel libcurl-devel 
bzip2-devel readline-devel libedit-devel sqlite-devel

# groupadd www
# useradd -g www -s /sbin/nolog -M www

# cd php-5.6.27
# ./configure 
--prefix=/usr/local/php56 
--with-config-file-path=/usr/local/php56/etc 
--enable-inline-optimization 
--disable-debug 
--disable-rpath 
--enable-shared 
--enable-opcache 
--enable-fpm 
--with-fpm-user=www 
--with-fpm-group=www 
--with-mysql=mysqlnd 
--with-mysqli=mysqlnd 
--with-pdo-mysql=mysqlnd 
--with-gettext 
--enable-mbstring 
--with-iconv 
--with-mcrypt 
--with-mhash 
--with-openssl 
--enable-bcmath 
--enable-soap 
--with-libxml-dir 
--enable-pcntl 
--enable-shmop 
--enable-sysvmsg 
--enable-sysvsem 
--enable-sysvshm 
--enable-sockets 
--with-curl 
--with-zlib 
--enable-zip 
--with-bz2 
--with-readline 
--with-apxs2=/usr/local/apache2/bin/apxs
# make -j8
# make test
# make install

配置：

# cp php.ini-development /usr/local/php56/etc/php.ini
# cp sapi/fpm/init.d.php-fpm /etc/init.d/php-fpm56
# chmod u+x /etc/init.d/php-fpm56 
# cp /usr/local/php56/etc/php-fpm.conf.default /usr/local/php56/etc/php-fpm.conf

# service php-fpm56 start

# php -v
PHP 5.6.27 (cli) (built: Oct 27 2016 14:00:00) 

apache与php集成

# vi /usr/local/apache2/conf/httpd.conf

LoadModule php5_module        modules/libphp5.so
AddType application/x-httpd-php .php

<IfModule dir_module>
    DirectoryIndex index.php index.html
</IfModule>

# vi /usr/local/apache2/htdocs/index.php 
echo "<?php phpinfo();?>"

# /etc/init.d/httpd graceful

访问确认：
http://1.1.1.1

安装anemometer数据库

# cd /usr/local/apache2/htdocs/
# git clone git://github.com/box/Anemometer.git anemometer
# cd anemometer
# mysql -u root -p --host=1.1.1.1 --port=3306 < install.sql
# mysql -u root -p --host=1.1.1.1  --port=3306 -e "grant ALL ON slow_query_log.* to 'anemometer'@'%' IDENTIFIED BY 'superSecurePass';"

# cp conf/sample.config.inc.php conf/config.inc.php 
修改config.inc.phpdatasource_localhost.inc.php中的数据库信息

anemometer客户端脚本

# cat slowlog_upload.sh

#!/bin/bash
LOGFILE=/data/slowlog.log           --慢查询的绝对路径

--自动清理慢查询，保留3W行：
LINENUM=`cat $LOGFILE | wc -l`
if [ $LINENUM -gt 30000 ]; then
    sed -i '1,20000d' $LOGFILE
    /usr/bin/mysql -u root -pmeishd -e 'select @@global.long_query_time into @lqt_save; set global long_query_time=2000; select sleep(2); FLUSH LOGS; select sleep(2); set global long_query_tim
e=@lqt_save;' > /dev/null 2>&1
fi
--每分钟将最新1分钟的慢查询上传anemometer数据库

/usr/bin/pt-query-digest --user=root --password=mypwd --port=3306 
--review h=1.1.1.1 ,D=slow_query_log,t=global_query_review 
--history h=1.1.1.1 ,D=slow_query_log,t=global_query_review_history 
--no-report --limit=0% 
--filter=" $event->{Bytes} = length($event->{arg}) and $event->{hostname}="$HOSTNAME"" 
--since=1m 
$LOGFILE

大功告成，访问：http://1.1.1.1/anemometer

filter by host:默认是mysql的hostname，可以将hostname设置为db的应用名称，或者通过JOB定时修改数据库中的这一列；

点击checksum，查看某条SQL的具体信息：执行次数走势图，SQL文本等

遇到的问题处理

1.时区
# vi php.ini 
date.timezone = Asia/Shanghai;


2.Warning: session_start(): Cannot send session cookie - headers already sent by (output started at /usr/local/apache2/htdocs/anemometer/views/navbar.php:37) 
in /usr/local/apache2/htdocs/anemometer/lib/Anemometer.php on line 608

session.auto_start = 1

最后

以上就是瘦瘦书包为你收集整理的mysql慢查询可视化的全部内容，希望文章能够帮你解决mysql慢查询可视化所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。