What is the visualization of mysql slow log web based on Query-Digest-UI
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.
