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

How does Anemometer graphically display the construction and use of MySQL slow log tools? I believe that many inexperienced people are at a loss about this, so this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Introduction: Anemometer is a tool for graphically displaying MySQL slow logs. Combined with pt-query-digest,Anemometer, you can easily analyze slow query logs, so that you can easily find out which SQL needs to be optimized.

This is the Box Anemometer, the MySQL Slow Query Monitor. This tool is used to analyze slow query logs collected from MySQL instances to identify problematic queries

Environmental profile

Take the latest version 3.0.10 of percona-toolkit at the time of writing as an example.

The corresponding version of mysql database is 5.7.21, binary installation

Both http and php are built-in versions of system CentOS Linux release 7.4.1708 (Core)

The steps to install are as follows:

Installation of 1.percona-toolkit tools

Construction and installation of 2.php web environment

3.Anemometer and configure

4. Import slow query log

5. Visit the interface to view slow queries

6. Other related and problem solving

0. The overall architecture

1. Installation of percona-toolkit tools

Installation purpose: pt-query-digest is a tool in percona-toolkit. Its function is to analyze the slow query log, count the MySQL slow query log and display it amicably.

Download address: https://www.percona.com/downloads/percona-toolkit/

Installation method (rpm):

1. Download the package, wget https://www.percona.com/downloads/percona-toolkit/3.0.10/binary/redhat/7/x86_64/percona-toolkit-3.0.10-1.el7.x86_64.rpm

2. Installation dependency, yum install perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Digest-MD5-y

3. Official installation, rpm-ivh percona-toolkit-3.0.10-1.el7.x86_64.rpm

4. Verify after installation, pt-query-digest-- version pt-query-digest 3.0.10

Installation method (tar binary)

1. Download the package, wget https://www.percona.com/downloads/percona-toolkit/3.0.10/binary/tarball/percona-toolkit-3.0.10_x86_64.tar.gz

2. Installation dependency, yum install perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Digest-MD5-y

3. Decompress package, tar xf percona-toolkit-3.0.10_x86_64.tar.gz

4. Use the tool directly,. / percona-toolkit-3.0.10/bin/pt-query-digest-- version

Pt-query-digest 3.0.10

2. The construction of php web environment

Purpose of installation: Anemometer needs to rely on

Installation of the LAMP environment LAMP environment:

1. Install apache,yum install httpd httpd-devel-y

2. Install php,yum install php php-mysql php-common php-bcmath php-dba php-cli php-gd php-mbstring php-mcrypt php-devel php-xml php-pdo-y

3. Modify the time zone, vim / etc/php.ini, to date.timezone = PRC

Startup of the LAMP environment:

1. Start, systemctl start httpd

2. Close, systemctl stop httpd

3. Restart, systemctl restart httpd

4. View, systemctl status httpd

3. Install Anemometer and configure

1. Download and install:

Installation purpose: install Anemometer application

Download address: https://github.com/box/Anemometer

Download package: git clone https://github.com/box/Anemometer.git

Move to the corresponding path: mv Anemometer / var/www/html/anemometer

2. The corresponding permissions of the Anemometer host need to be granted on the target slow query database.

1. Purpose: to analyze the explain execution plan of the target slow query database

2. Authorization, grant select on *. * to 'anemometer'@'$ip' identified by' 123456flush privileges; ($ip is the ip address corresponding to the Anemometer host)

3. Modify the configuration file to add explain to read user password information cp conf/sample.config.inc.php conf/config.inc.php vim conf/config.inc.php\\

4. Modify the configuration file to point to the data source file, vim conf/datasource_localhost.inc.php, or you can vim conf/config.inc.php directly.

5. Configuration of database tables that initialize the data source, mysql-uroot-p123456-h227.0.0.1-P5700

< install.sql，每个datasource源头可以对应不同的数据库database（修改install.sql的内容） 4. 导入慢查询日志 1、慢查询主机推送格式 For pt-query-digest version < 2.2$ pt-query-digest --user=anemometer --password=superSecurePass \--review h=db.example.com,D=slow_query_log,t=global_query_review \--review-history h=db.example.com,D=slow_query_log,t=global_query_review_history \--no-report --limit=0% \ --filter=" \$event->

{Bytes} = length (\ $event- > {arg}) and\ $event- > {hostname} =\ "$HOSTNAME\"\ / var/lib/mysql/db.example.com-slow.logFor pt-query-digest version > = 2.2$ pt-query-digest-- user=anemometer-- password=superSecurePass\-- review examples. T=global_query_review_history\-no-report-limit=0%\-filter= "\ $event- > {Bytes} = length (\ $event- > {arg}) and\ $event- > {hostname} =\" $HOSTNAME\ "\ / var/lib/mysql/db.example.com-slow.log

2. Example of slow query host push script

# config anemometer server, the purpose is to push slow query to the remote anemometer server and store it.anemometer_host= "127.0.0.1" anemometer_user= "root" anemometer_password= "123456" anemometer_port=5700anemometer_db= "slow_query_log" # config mysql server, the purpose is to get the path of the slow query log.mysql_client= "/ usr/local/mysql-5.7.21/bin/mysql" mysql_user= "root" mysql_password= "123456" mysql_socket= "/ tmp/mysql_5700.sock" mysql_port=5700#config slowqury dir to cd And then delete the expired slowquery file.slowquery_dir= "/ data/mysql_$mysql_port/" # get the path of the slowquery log.slowquery_file= `$ mysql_client-u$mysql_user-p$mysql_password-S $mysql_socket-e "show variables like 'slow_query_log_file'" | grep log | awk' {print $2} '`pt_query_digest= "/ data/percona-toolkit-3.0.10/bin/pt-query-digest" # collect mysql slowquery log into lepus database.$pt_query_digest-- user=$anemometer_ User-password=$anemometer_password-port=$anemometer_port-review h=$anemometer_host No-report-- limit=0%-filter= "\ $event- > {Bytes} = length (\ $event- > {arg}) and\ $event- > {hostname} =\" $HOSTNAME:$mysql_port\ "$slowquery_file#generate a new slowquery log, the below is generate a new slow file per hour.tmp_log= `$mysql_client-u$mysql_user-p$mysql_password-S $mysql_socket-e" select concat ('$slowquery_dir' 'slowquery_',date_format (now (),'% Y% m% d% H'), '.log') | | grep log | sed-n-e '2p' `# use new slow file to config mysql slowquery$mysql_client-u$mysql_user-p$mysql_password-S $mysql_socket-e "set global slow_query_log = 0 mysql_client set global slow_query_log_file =' $tmp_log';" $mysql_client-u$mysql_user-p$mysql_password-S $mysql_socket-e "set global slow_query_log = 1 "# delete slowquery file before 2 dayscd $slowquery_dir/usr/bin/find. /-name 'slowquery_*.log'-mtime + 2 | xargs rm-rf; # END####

5. Visit the interface to view slow queries

Http://$ip/anemometer/ ($ip is the ip address of the Anemometer host)

6. Other related and problem solving

1. For anemometer hosts, the mapping between hostname and ip is required (modify / etc/hosts for configuration) to slow query the target host resolution of the explain execution plan

# in the collect mysql slowquery log into lepus database step, $HOSTNAME:$mysql_port

Database access format, hostname_max is similar to this, cnwangdawei:5700

2. For the problem of garbled codes in Chinese, add-- charset=utf8 in # collect mysql slowquery log into lepus database step

3. The slow query host database is version 5.7. it may appear that the interface ts_cnt does not display, replace percona toolkit with the new version, 2.x.x-> 3.x.x

4. Table structure and status character set display garbled, add mysqli character set setting, vim / var/www/html/anemometer/lib/QueryExplain.php

Add (add after 194lines), $this- > mysqli- > query ("set names utf8")

After reading the above, have you mastered the tool construction and use of Anemometer graphical how to display MySQL slow logs? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.