Shulou(Shulou.com)05/31 Report--

In this issue, Xiaobian will bring you about how to visualize mysql slow log web based on Query-Digest-UI. The article is rich in content and analyzed and described from a professional perspective. After reading this article, I hope you can gain something.

Query-Digest-UI is a tool based on pt-query-digest in percona-toolkit management tool to monitor MySQL slow query logs and display them graphically with lamp.

The structure diagram is as follows:

I. Preparations:

1. Apache, mod_php, php_pdo

2. Installation: perl-DBD-MySQL-3.0007-2.el5

However, when installing this module, errors may be reported:

libmysqlclient.so.15()(64bit) is needed by perl-DBD-MySQL-3.0007-2.el5.x86_64

libmysqlclient.so.15(libmysqlclient_15)(64bit) is needed by perl-DBD-MySQL-3.0007-2.el5.x86_64

libmysqlclient.so.15 needs to be installed or supplied

A soft link can be made to libmysqlclient.so.16 or libmysqlclient.so.18 under/usr/lib or/usr/lib64: libmysqlclient.so.15

ln -s /usr/lib64/libmysqlclient.so.18 /usr/lib64/libmysqlclient.so.15

II. Installation

1.Query-Digest-UI is dependent on pt-query-digest tool for slow log analysis, so install percona_toolkit

Download percona-toolkit_2.2.17-1.tar.gz

Installation:

#perl Makefile.PL

#make

#make install

2. Install LAMP

3. Download and install Query-Digest-UI

Official website download address: github.com/kormoc/Query-Digest-UI

Download: pan.baidu.com/share/link? shareid=3651139099&uk=2550236109 (provided online, I use this version)

query-digest-ui_last-edition.zip

The requested URL/html/was not found on this server.

4. Create libraries and tables

1.install.sql

Query-Digest-UI does not have this script. To find this sql in Anemomete, download the address: github.com/box/Anemometer Anemometer and Query-Digest-UI are the same. They both provide visualization for mysql slow logs.

2. Create User (Database: 192.168.1.1, lamp+Query-Digest-UI: 192.168.1.2)

grant all on slow_query_log.* to 'slow'@'192.168.1.1' identified by 'test'; On the machine that built the lamp, enable the machine that built the database online to insert data into the slow_query_log library

grant all on slow_query_log.* to 'slow'@'localhost' identified by 'test';

grant select on *.* to 'slow_1'@'192.168.1.2'; online database select on *.* To see the execution plan.

5. Configure Query-Digest-UI

# pwd

/var/www/html/slow

#cp config.php.example config. php If you update this file, you will have this file directly

#vim config.php

.......

$reviewhost = array(

// Replace hostname and database in this setting

// use host=hostname;port=portnum if not the default port

'dsn' => 'mysql:host=192.168.1.2;port=3306;dbname=slow_query_log',

'user' => slow',

'password' => 'test',

// See http://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html#cmdoption-pt-query-digest--review

'review_table' => 'global_query_review',

// This table is optional. You don't need it, but you lose detailed stats

// Set to a blank string to disable

// See http://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html#cmdoption-pt-query-digest--review-history

'history_table' => 'global_query_review_history',

);

Below is the implementation plan configuration

$explainhosts = array(

'label1' => array(

'dsn' => 'mysql:host=192.168.1.1;port=3306',

'user' => 'slow_1',

'password' => 'test',

// By default we scan the host to see what databases we can run a

// explain on. To disable this behavour, set an array here

'databases' => array(

'database1',

'database2',

'........ '

),

),

'label2' => array(

'dsn' => 'mysql:host=;port=3306',

'user' => '',

'password' => '',

// You can also just set the field and keep it empty. We will scan for valid databases

'databases' => array(),

6. start collecting data

Use pt-query-digest tool to push data to offline lamp database online

pt Statements with versions less than 2.2:

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

pt Statements with versions greater than version 2.2:

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

all right

Enter the page

http://192.168.1.2/slow

You can see the slow log...

The above is a small series for everyone to share based on Query-Digest-UI mysql slow log web visualization is how, if there is a similar doubt, may wish to refer to the above analysis to understand. If you want to know more about it, please pay attention to the industry information channel.

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.