【MYSQL】工具percona-toolkit-3.0.4-RPM和TAR安裝

RPM安裝
版本查詢下載 https://www.percona.com/downloads/percona-toolkit/

# wget https://www.percona.com/downloads/percona-toolkit/3.0.4/binary/redhat/6/x86_64/percona-toolkit-3.0.4-r2e44c3a-el6-x86_64-bundle.tar

本範例是下載percona-toolkit-3.0.4，解壓後出現兩個檔

# tar -xvf percona-toolkit-3.0.4-r2e44c3a-el6-x86_64-bundle.tar
percona-toolkit-3.0.4-1.el6.x86_64.rpm
percona-toolkit-debuginfo-3.0.4-1.el6.x86_64.rpm

執行rpm安裝主程式出現錯誤訊息

# rpm -ivh percona-toolkit-3.0.4-1.el6.x86_64.rpm
warning: percona-toolkit-3.0.4-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
error: Failed dependencies:
perl(Time::HiRes) is needed by percona-toolkit-3.0.4-1.el6.x86_64
perl(IO::Socket::SSL) is needed by percona-toolkit-3.0.4-1.el6.x86_64
perl(Term::ReadKey) is needed by percona-toolkit-3.0.4-1.el6.x86_64

解決方式安裝依賴包
yum install perl-Time-HiRes
yum install perl-IO-Socket-SSL
yum install perl-TermReadKey.x86_64

另外也有可能缺少的↓
yum install perl-DBI
yum install perl-DBD-MySQL

安裝好缺少的依賴後，再次安裝主程式

# rpm -ivh percona-toolkit-3.0.4-1.el6.x86_64.rpm
warning: percona-toolkit-3.0.4-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing...                ########################################### [100%]

1:percona-toolkit        ########################################### [100%]

然後再下指令pt-query-digest --help只要有出現文字，即表示安裝完成！

# pt-query-digest --help

pt-query-digest analyzes MySQL queries from slow, general, and binary log files.

It can also analyze queries from C<SHOW PROCESSLIST> and MySQL protocol data

from tcpdump.  By default, queries are grouped by fingerprint and reported in

descending order of query time (i.e. the slowest queries first).  If no C<FILES>

are given, the tool reads C<STDIN>.  The optional C<DSN> is used for certain

options like L<"--since"> and L<"--until">.  For more details, please use the

--help option, or try 'perldoc /usr/bin/pt-query-digest' for complete

documentation.

Usage: pt-query-digest [OPTIONS] [FILES] [DSN]

Options:

  --ask-pass                   Prompt for a password when connecting to MySQL

  --attribute-aliases=a        List of attribute|alias,etc (default db|Schema)

  --attribute-value-limit=i    A sanity limit for attribute values (default 0)

  --charset=s              -A  Default character set

  --config=A                   Read this comma-separated list of config files;

                               if specified, this must be the first option on

                               the command line

  --[no]continue-on-error      Continue parsing even if there is an error (

                               default yes)

  --[no]create-history-table   Create the --history table if it does not exist (

                               default yes)

  --[no]create-review-table    Create the --review table if it does not exist (

                               default yes)

  --daemonize                  Fork to the background and detach from the shell

  --database=s             -D  Connect to this database

  --defaults-file=s        -F  Only read mysql options from the given file

  --embedded-attributes=a      Two Perl regex patterns to capture pseudo-

                               attributes embedded in queries

  --expected-range=a           Explain items when there are more or fewer than

                               expected (default 5,10)

  --explain=d                  Run EXPLAIN for the sample query with this DSN

                               and print results

  --filter=s                   Discard events for which this Perl code doesn't

                               return true

  --group-by=A                 Which attribute of the events to group by (

                               default fingerprint)

  --help                       Show help and exit

  --history=d                  Save metrics for each query class in the given

                               table. pt-query-digest saves query metrics (

                               query time, lock time, etc.) to this table so

                               you can see how query classes change over time

  --host=s                 -h  Connect to host

  --ignore-attributes=a        Do not aggregate these attributes (default arg,

                               cmd, insert_id, ip, port, Thread_id, timestamp,

                               exptime, flags, key, res, val, server_id,

                               offset, end_log_pos, Xid)

  --inherit-attributes=a       If missing, inherit these attributes from the

                               last event that had them (default db,ts)

  --interval=f                 How frequently to poll the processlist, in

                               seconds (default .1)

  --iterations=i               How many times to iterate through the collect-

                               and-report cycle (default 1)

  --limit=A                    Limit output to the given percentage or count (

                               default 95%:20)

  --log=s                      Print all output to this file when daemonized

  --order-by=A                 Sort events by this attribute and aggregate

                               function (default Query_time:sum)

  --outliers=a                 Report outliers by attribute:percentile:count (

                               default Query_time:1:10)

  --output=s                   How to format and print the query analysis

                               results (default report)

  --password=s             -p  Password to use when connecting

  --pid=s                      Create the given PID file

  --port=i                 -P  Port number to use for connection

  --preserve-embedded-numbers  Preserve numbers in database/table names when

                               fingerprinting queries

  --processlist=d              Poll this DSN's processlist for queries, with --

                               interval sleep between

  --progress=a                 Print progress reports to STDERR (default time,

                               30)

  --read-timeout=m             Wait this long for an event from the input; 0 to

                               wait forever (default 0).  Optional suffix s=

                               seconds, m=minutes, h=hours, d=days; if no

                               suffix, s is used.

  --[no]report                 Print query analysis reports for each --group-by

                               attribute (default yes)

  --report-all                 Report all queries, even ones that have been

                               reviewed

  --report-format=A            Print these sections of the query analysis

                               report (default rusage,date,hostname,files,

                               header,profile,query_report,prepared)

  --report-histogram=s         Chart the distribution of this attribute's

                               values (default Query_time)

  --resume=s                   If specified, the tool writes the last file

                               offset, if there is one, to the given filename

  --review=d                   Save query classes for later review, and don't

                               report already reviewed classes

  --run-time=m                 How long to run for each --iterations.  Optional

                               suffix s=seconds, m=minutes, h=hours, d=days; if

                               no suffix, s is used.

  --run-time-mode=s            Set what the value of --run-time operates on (

                               default clock)

  --sample=i                   Filter out all but the first N occurrences of

                               each query

  --set-vars=A                 Set the MySQL variables in this comma-separated

                               list of variable=value pairs

  --show-all=H                 Show all values for these attributes

  --since=s                    Parse only queries newer than this value (parse

                               queries since this date)

  --slave-password=s           Sets the password to be used to connect to the

                               slaves

  --slave-user=s               Sets the user to be used to connect to the slaves

  --socket=s               -S  Socket file to use for connection

  --timeline                   Show a timeline of events

  --type=A                     The type of input to parse (default slowlog)

  --until=s                    Parse only queries older than this value (parse

                               queries until this date)

  --user=s                 -u  User for login if not current user

  --variations=A               Report the number of variations in these

                               attributes' values

  --version                    Show version and exit

  --[no]version-check          Check for the latest version of Percona Toolkit,

                               MySQL, and other programs (default yes)

  --[no]vertical-format        Output a trailing "\G" in the reported SQL

                               queries (default yes)

  --watch-server=s             This option tells pt-query-digest which server

                               IP address and port (like "10.0.0.1:3306") to

                               watch when parsing tcpdump (for --type tcpdump);

                               all other servers are ignored

Option types: s=string, i=integer, f=float, h/H/a/A=comma-separated list, d=DSN, z=size, m=time

Rules:

  This tool accepts additional command-line arguments. Refer to the SYNOPSIS and usage information for details.

DSN syntax is key=value[,key=value...]  Allowable DSN keys:

  KEY  COPY  MEANING

  ===  ====  =============================================

  A    yes   Default character set

  D    yes   Default database to use when connecting to MySQL

  F    yes   Only read default options from the given file

  P    yes   Port number to use for connection

  S    yes   Socket file to use for connection

  h    yes   Connect to host

  p    yes   Password to use when connecting

  t    no    The --review or --history table

  u    yes   User for login if not current user

  If the DSN is a bareword, the word is treated as the 'h' key.

Options and values after processing arguments:

  --ask-pass                   FALSE

  --attribute-aliases          db|Schema

  --attribute-value-limit      0

  --charset                    (No value)

  --config                     /etc/percona-toolkit/percona-toolkit.conf,/etc/percona-toolkit/pt-query-digest.conf,/root/.percona-toolkit.conf,/root/.pt-query-digest.conf

  --continue-on-error          TRUE

  --create-history-table       TRUE

  --create-review-table        TRUE

  --daemonize                  FALSE

  --database                   (No value)

  --defaults-file              (No value)

  --embedded-attributes        (No value)

  --expected-range             5,10

  --explain                    (No value)

  --filter                     (No value)

  --group-by                   fingerprint

  --help                       TRUE

  --history                    (No value)

  --host                       (No value)

  --ignore-attributes          arg,cmd,insert_id,ip,port,Thread_id,timestamp,exptime,flags,key,res,val,server_id,offset,end_log_pos,Xid

  --inherit-attributes         db,ts

  --interval                   .1

  --iterations                 1

  --limit                      95%:20

  --log                        (No value)

  --order-by                   Query_time:sum

  --outliers                   Query_time:1:10

  --output                     report

  --password                   (No value)

  --pid                        (No value)

  --port                       (No value)

  --preserve-embedded-numbers  FALSE

  --processlist                (No value)

  --progress                   time,30

  --read-timeout               0

  --report                     TRUE

  --report-all                 FALSE

  --report-format              rusage,date,hostname,files,header,profile,query_report,prepared

  --report-histogram           Query_time

  --resume                     (No value)

  --review                     (No value)

  --run-time                   (No value)

  --run-time-mode              clock

  --sample                     (No value)

  --set-vars                   

  --show-all                   

  --since                      (No value)

  --slave-password             (No value)

  --slave-user                 (No value)

  --socket                     (No value)

  --timeline                   FALSE

  --type                       slowlog

  --until                      (No value)

  --user                       (No value)

  --variations                 

  --version                    FALSE

  --version-check              TRUE

  --vertical-format            TRUE

  --watch-server               (No value)

TAR安裝


解壓

# tar xvf  percona-toolkit-3.0.4.tar

進去資料夾內

# cd   percona-toolkit-3.0.4
# perl Makefile.PL

可能問題一：

Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at Makefile.PL line 1.

BEGIN failed--compilation aborted at Makefile.PL line 1.

解決方式安裝依賴包

# type perl
perl is hashed (/usr/bin/perl)

# yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker

Loaded plugins: fastestmirror

Setting up Install Process

Loading mirror speeds from cached hostfile

* base: free.nchc.org.tw

* extras: free.nchc.org.tw

* updates: free.nchc.org.tw

base                                                                                            | 3.7 kB     00:00     

extras                                                                                          | 3.4 kB     00:00     

updates                                                                                         | 3.4 kB     00:00     

Package perl-ExtUtils-MakeMaker-6.55-144.el6.x86_64 already installed and latest version

Resolving Dependencies

--> Running transaction check

---> Package perl-ExtUtils-CBuilder.x86_64 1:0.27-144.el6 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================================================

Package                               Arch                  Version                         Repository           Size

=======================================================================================================================

Installing:

perl-ExtUtils-CBuilder                x86_64                1:0.27-144.el6                  base                 49 k

Transaction Summary

=======================================================================================================================

Install       1 Package(s)

Total download size: 49 k

Installed size: 59 k

Is this ok [y/N]: y

Downloading Packages:

perl-ExtUtils-CBuilder-0.27-144.el6.x86_64.rpm                                                  |  49 kB     00:00     

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

  Installing : 1:perl-ExtUtils-CBuilder-0.27-144.el6.x86_64                                                        1/1

  Verifying  : 1:perl-ExtUtils-CBuilder-0.27-144.el6.x86_64                                                        1/1

Installed:

  perl-ExtUtils-CBuilder.x86_64 1:0.27-144.el6                                                                         

Complete!

可能問題二：

Checking if your kit is complete...

Warning: the following files are missing in your kit:

    README

Please inform the author.

Writing Makefile for percona-toolkit

解決方式

# yum install perl-DBD-MySQL
# perl Makefile.PL
Writing Makefile for percona-toolkit

# make test

PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t

Files=0, Tests=0,  0 wallclock secs ( 0.01 usr +  0.00 sys =  0.01 CPU)

Result: NOTESTS

# make install

Appending installation info to /usr/lib64/perl5/perllocal.pod

然後再下指令pt-query-digest --help只要有出現文字，即表示安裝完成！

# pt-query-digest --help

pt-query-digest analyzes MySQL queries from slow, general, and binary log files.

It can also analyze queries from C<SHOW PROCESSLIST> and MySQL protocol data

.

.

  --type                       slowlog

  --until                      (No value)

  --user                       (No value)

  --variations                 

  --version                    FALSE

  --version-check              TRUE

  --vertical-format            TRUE

  --watch-server               (No value)

如果執行pt-query-digest --help 報錯

Can't locate Time/HiRes.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/local/bin/pt-query-digest line 3225.

BEGIN failed--compilation aborted at /usr/local/bin/pt-query-digest line 3225.

解決方式

# yum install perl-Time-HiRes

安裝完成後再輸入pt-query-digest --help指令
# pt-query-digest --help

pt-query-digest analyzes MySQL queries from slow, general, and binary log files.

It can also analyze queries from C<SHOW PROCESSLIST> and MySQL protocol data

.

.

  --type                       slowlog

  --until                      (No value)

  --user                       (No value)

  --variations                 

  --version                    FALSE

  --version-check              TRUE

  --vertical-format            TRUE

  --watch-server               (No value)

只要出現訊息就表示成功