mysql慢查询可视化

84 阅读 0 评论 56 点赞

我是靠谱客的博主瘦瘦书包，这篇文章主要介绍mysql慢查询可视化，现在分享给大家，希望可以做个参考。

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

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

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

安装apache

# yum install gcc gcc++ zlib zlib-devel

如果已安装httpd，将其删除
# rpm -qa | grep httpd

下载
http://archive.apache.org/dist/apr/apr-1.4.5.tar.gz
http://archive.apache.org/dist/apr/apr-util-1.3.12.tar.gz
http://jaist.dl.sourceforge.net/project/pcre/pcre/8.10/pcre-8.10.zip

编译安装

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
# 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

配置：

复制代码

1
2
3
4
5
6
7
8
9
# 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集成

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
# 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数据库

在anemometer上安装mysql

复制代码

1
2
3
4
5
6
7
8
# 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客户端脚本

crontab每分钟执行一次slowlog_upload.sh

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 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