Anemometer+percona Toolki implementation of MySQL slow query log visualization function

Recently found a very useful MySQL slow query log visualization tools, online can also find a lot of information, here combined with their own actual situation to reorganize.

1. Experimental environment 1.1 system environment:

操作系统：CentOS 6.5 64位主机地址：10.0.0.26主机名：mysql01mysql版本：mysql-5.6.36

1.2 Visualization Software Environment:

http环境：LAMP可视化软件：Anemometer-master.zip， percona-toolkit-3.0.11-1.el6.x86_64.rpm， perl-TermReadKey-2.30-13.el6.x86_64.rpm

2. Compiling and installing the MySQL service

Install the MySQL service by using the source code compilation installation, and turn on the slow query log function.

Installation dependencies:

To create a user:

useradd -s /sbin/nologin -M mysql

Download MySQL Source package:

cd /usr/local/srcwget -q http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.36.tar.gz

Unzip, compile and install

tar xf mysql-5.6.36.tar.gzcd mysql-5.6.36cmake . -DCMAKE_INSTALL_PREFIX=/opt/mysql-5.6.36 -DMYSQL_DATADIR=/opt/mysql-5.6.36/data -DMYSQL_UNIX_ADDR=/opt/mysql-5.6.36/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITH_ZLIB=bundled -DWITH_SSL=bundled -DENABLED_LOCAL_INFILE=1 -DWITH_EMBEDDED_SERVER=1 -DENABLE_DOWNLOADS=1 -DWITH_DEBUG=0make && make installln -s /opt/mysql-5.6.36/ /opt/mysql

Initialize the database:

\cp support-files/my*.cnf /etc/my.cnf/opt/mysql/scripts/mysql_install_db --basedir=/opt/mysql/ --datadir=/opt/mysql/data --user=mysqlmkdir /opt/mysql/tmpchown -R mysql.mysql /opt/mysql/cp /usr/local/src/mysql-5.6.36/support-files/mysql.server /etc/init.d/mysqldchmod 700 /etc/init.d/mysqldchkconfig mysqld onchkconfig --list mysqld/etc/init.d/mysqld startnetstat -lntup|grep 3306

To modify the default user password:

mysqladmin -uroot password 123456;

To configure the MySQL environment variable:

echo ‘PATH=/opt/mysql/bin/:$PATH‘ >>/etc/profilesource /etc/profile

Modify the configuration file to turn on the slow query log:

[mysqld]slow_query_log=1                #<==开启慢查询日志long_query_time = 1         #<==设置慢查询日志的阈值，超出设定值的SQL被记录到日志文件，缺省10Slog_queries_not_using_indexes        #<==统计不走索引的语句#min_examined_row_limit=1000        #<==返回行少于该值，不被记录到日志，最好不要设置，可能会有冲突slow_query_log_file = /opt/mysql/data/slow.log

After modifying, restart the MySQL service:

/etc/init.d/mysqld restart

3. Installation of the slow Query Visualizer 3.1 Percona tool Introduction

Percona Toolkit is an advanced set of command-line tools for managing MySQL and system tasks, mainly including:

1：验证主节点和复制数据的一致性2：有效的度记录行进行归档3：找出重复的索引4：总结MySQL服务器5：从日志和tcpdump中分析查询6：问题发生时收集重要的系统信息

3.2 Installing the PT tool (RPM mode)

Installation dependencies:

wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-6.repoyum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes -y

Download RPM Package:
The PT tool requires two RPM packages, i.e.:

perl-TermReadKey-2.30-13.el6.x86_64.rpmpercona-toolkit-3.0.11-1.el6.x86_64.rpm

The following were:
https://www.percona.com/downloads/percona-toolkit/3.0.11/binary/redhat/6/x86_64/percona-toolkit-3.0.11-1.el6.x86_64.rpm
http://mirror.centos.org/centos/6/os/x86_64/Packages/perl-TermReadKey-2.30-13.el6.x86_64.rpm
Installing using the RPM command

[[email protected] src]# rpm -ivh perl-TermReadKey-2.30-13.el6.x86_64.rpm Preparing...                ########################################### [100%]   1:perl-TermReadKey       ########################################### [100%][[email protected] src]# rpm -ivh percona-toolkit-3.0.11-1.el6.x86_64.rpm warning: percona-toolkit-3.0.11-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEYPreparing...                ########################################### [100%]   1:percona-toolkit        ########################################### [100%]

3.3 Downloads Anemometer-master.zip

Official website: https://github.com/box/Anemometer

cd /usr/local/src &&wget https://github.com/box/Anemometer/archive/master.zip

4. Install lamp

Before installing, you need to make sure that the Epel-release,php_mysqli module requires version epel-release-6-8.noarch. We have updated the Epel source before installing the PT tool, so we can install it directly from Yum.

yum -y install httpd php *bcmath* *mysqli*

Check the installation results:

[[email protected] ~]# rpm -qa *bcmath* *mysqli*php-bcmath-5.3.3-49.el6.x86_64php-pear-MDB2-Driver-mysqli-1.5.0-0.8.b4.el6.noarch     #<==如果没有这两个，必须先安装

5. Configure Visual Interface 5.1 Configure Anemoeter and database permissions

Move anemometer to the HTTP site directory:

unzip master    #<==下载方式不同，包名也可能不同，因此需注意自己的包名mv Anemometer-master /var/www/html/anemometer

To run the SQL script:
Enter/var/www/html/anemometer to import the MySQL script into the database

mysql -uroot -p123456 < mysql56-install.sql

Give the database the appropriate permissions:

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || slow_query_log     |+--------------------+4 rows in set (0.15 sec)mysql> grant all on slow_query_log.* to ‘anemometer‘@‘%‘ identified by ‘123456‘;Query OK, 0 rows affected (0.01 sec)mysql> grant all on slow_query_log.* to ‘anemometer‘@‘localhost‘ identified by ‘123456‘; Query OK, 0 rows affected (0.00 sec)mysql> grant select on *.* to  ‘anemometer‘@‘%‘;Query OK, 0 rows affected (0.00 sec)mysql> grant all on slow_query_log.* to ‘anemometer‘@‘localhost‘;Query OK, 0 rows affected (0.00 sec)mysql> grant select on *.* to  ‘anemometer‘@‘localhost‘;Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)

5.2 Modifying a configuration file

Anemometer configuration:

vim /var/www/html/anemometer/conf/sample.config.inc.php

PHP Configuration:

vim /etc/php.ini，注意自己的socket文件所在位置

Apache Configuration:

vim /etc/httpd/conf/httpd.conf

6. Service Start-up

Start the HTTP service
Start the HTTPD service and visit the URL: Http://10.0.0.26/anemometer

As prompted, modify the configuration file name:

cp /var/www/html/anemometer/conf/sample.config.inc.php /var/www/html/anemometer/conf/config.inc.php`

Modify the completed configuration file, after entering the system will have the following error:

Don't worry, this is because the default is two tables, run the following command:

pt-query-digest --user=anemometer --password=123456  --socket=/opt/mysql/data/mysql.sock --review h=localhost,D=slow_query_log,t=global_query_review --history h=localhost,D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\"" /opt/mysql/data/slow.log

To this, complete all installation configurations! The results are shown below:

Reference article: https://www.cnblogs.com/xuanzhi201111/p/4128894.html

Anemometer+percona Toolki implementation of MySQL slow query log visualization function