【MYSQL】記錄一次install_driver(mysql) failed: Attempt to reload DBD/mysql.pm aborted.報錯-版本8.0

#使用percona-toolkit工具內的pt-duplicate-key-checker功能時報錯

[root@rosalie ~]# pt-duplicate-key-checker -uroot -p1234 --socket=/tmp/mysql.sock  --database=tttt

install_driver(mysql) failed: Attempt to reload DBD/mysql.pm aborted.

Compilation failed in require at (eval 20) line 3.

at /usr/bin/pt-duplicate-key-checker line 894

#查看一下/usr/bin/pt-duplicate-key-checker的894行是些什麼

#顯然是跟DBD有關，可能那麼說明DBD::MYSQL未安裝成功。

   894          $dbh = eval { DBI->connect($cxn_string, $user, $pass, $defaults) };

   895    

   896          if ( !$dbh && $EVAL_ERROR ) {

   897             if ( $EVAL_ERROR =~ m/locate DBD\/mysql/i ) {

   898             die "Cannot connect to MySQL because the Perl DBD::mysql module is "

   899                  . "not installed or not found.  Run 'perl -MDBD::mysql' to see "

   900                  . "the directories that Perl searches for DBD::mysql.  If "

   901                  . "DBD::mysql is not installed, try:\n"

   902                  . "  Debian/Ubuntu  apt-get install libdbd-mysql-perl\n"

   903                  . "  RHEL/CentOS    yum install perl-DBD-MySQL\n"

---------------------------------------------------------------

#問題是因為目標機器缺少mysql驅動程序庫：libmysqlclient.so

#這是構建“DBD-mysql”時的一個重要庫。所以要修復它

#查詢一下mysql.so路徑

[root@rosalie ~]# find / -name "mysql.so"

/usr/lib64/perl5/auto/DBD/mysql/mysql.so

#檢查一下mysql.so與libmysqlclient.so.16的依賴關係，顯然無法連接到動態庫

[root@rosalie ~]# ldd /usr/lib64/perl5/auto/DBD/mysql/mysql.so

    linux-vdso.so.1 =>  (0x00007ffcfb9ef000)

    libmysqlclient.so.16 => not found

    libz.so.1 => /lib64/libz.so.1 (0x00007f28b3b3f000)

    libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f28b3908000)

    libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f28b36ef000)

    libm.so.6 => /lib64/libm.so.6 (0x00007f28b346a000)

    libssl.so.10 => /usr/lib64/libssl.so.10 (0x00007f28b31fe000)

    libcrypto.so.10 => /usr/lib64/libcrypto.so.10 (0x00007f28b2e19000)

    libc.so.6 => /lib64/libc.so.6 (0x00007f28b2a84000)

    libfreebl3.so => /lib64/libfreebl3.so (0x00007f28b2881000)

    libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f28b263d000)

    libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f28b2355000)

    libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f28b2151000)

    libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f28b1f25000)

    libdl.so.2 => /lib64/libdl.so.2 (0x00007f28b1d20000)

    /lib64/ld-linux-x86-64.so.2 (0x00007f28b3f7c000)

    libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f28b1b15000)

    libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f28b1912000)

    libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f28b16f7000)

    libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f28b14da000)

    libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f28b12ba000)

 

解決libmysqlclient.so.16 => not found的問題

下載 libmysqlclient.so.16， http://files.directadmin.com/services/debian_5.0_64/libmysqlclient.so.16

#下載後拷貝到  /lib64/ 下

[root@rosalie-mysql]#  cp libmysqlclient.so.16 /lib64/

# 拷貝完成後再次查看 依賴關係

[root@rosalie-mysql]#  ldd /usr/lib64/perl5/auto/DBD/mysql/mysql.so

    linux-vdso.so.1 =>  (0x00007fffcf49f000)

    libmysqlclient.so.16 => /lib64/libmysqlclient.so.16 (0x00007f84f0d20000)

    libz.so.1 => /lib64/libz.so.1 (0x00007f84f0b0a000)

    libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f84f08d2000)

    libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f84f06b9000)

    libm.so.6 => /lib64/libm.so.6 (0x00007f84f0435000)

    libssl.so.10 => /usr/lib64/libssl.so.10 (0x00007f84f01c8000)

    libcrypto.so.10 => /usr/lib64/libcrypto.so.10 (0x00007f84efde3000)

    libc.so.6 => /lib64/libc.so.6 (0x00007f84efa4f000)

    libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f84ef831000)

    libfreebl3.so => /lib64/libfreebl3.so (0x00007f84ef62e000)

    libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f84ef3ea000)

    libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f84ef102000)

    libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f84eeefe000)

    libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f84eecd2000)

    libdl.so.2 => /lib64/libdl.so.2 (0x00007f84eeacd000)

    /lib64/ld-linux-x86-64.so.2 (0x00007f84f125c000)

    libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f84ee8c2000)

    libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f84ee6bf000)

    libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f84ee4a4000)

    libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f84ee285000)

#接著再次執行pt-duplicate-key-checker

#出現的錯誤問題變了一點

[root@rosalie-mysql]# pt-duplicate-key-checker -u root -p1234 --database=tttt

DBI connect(';;mysql_read_default_group=client','root',...) failed: Client does not support authentication protocol requested by server; consider upgrading MySQL client at /usr/bin/pt-duplicate-key-checker line 894

mysqli_real_connect(): The server requested authentication method unknown to the client [sha256_password]

#因8.0.11版本起，不再像mysql5.7及以前版本那樣，設定使用者密碼時預設的驗證方式為caching_sha2_password，如果發現升級mysql8.0.11後原有的程式不能連線mysql，可迅速在mysql command line client客戶端用下面的命令設定成mysql5.7及以前版本的密碼驗證方式，同時MYSQL8.0.11下修改密碼的方式與原先也不大一樣，原先的部分修改密碼的命令在mysql8.0.11下不能使用。

#進入MYSQL內查詢使用者帳號及HOST

mysql> select user,host from mysql.user;

+------------------+-----------+

| user             | host      |

+------------------+-----------+

| root             | 127.0.0.1 |

| rosalie          | 127.0.0.1 |

| mysql.infoschema | localhost |

| mysql.session    | localhost |

| mysql.sys        | localhost |

| root             | localhost |

| rosalie          | localhost |

+------------------+-----------+

7 rows in set (0.00 sec)

#似乎是密碼加密方法不同，由原本mysql_native_password 改成8.0的 caching_sha2_password

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '1234';

Query OK, 0 rows affected (0.00 sec)

#之後再次執行就沒有報錯了

[root@rosalie-mysql]# pt-duplicate-key-checker -u root -p1234 --database=tttt

【MYSQL】percona-toolkit工具-利用pt-duplicate-key-checker找出重複索引與冗餘索引

因實際環境上，可能開發因為需求不斷增加，但卻沒有仔細分析檢測過是否重複，此時可以利用percona-toolkit工具內的pt-duplicate-key-checker功能，來找出重複索引與冗餘索引。

安裝方法可參考↓

https://rosalie1211.blogspot.com/2019/03/mysqlpercona-toolkit-304-rpmtar.html

pt-duplicate-key-checker的使用方法

pt-duplicate-key-checker [OPTION...] [DSN]

使用說明：檢查重複或多餘(冗餘)的索引和外鍵，並打印出信息。

參數說明：加粗的為常用的。

--all-structs：

結構（B樹，哈希等）的索引，默認情況下是禁止的。因為BTREE索引可能會覆蓋作為FULLTEXT索引的列，其實他們不是真正的重複，因為是不同的索引結構。

--ask-pass 

提示輸入密碼。

--charset

縮寫-A，字符集設置：utf8，gbk，latin1 …

--[no] clustered

主鍵和第二索引一起是重複的，默認開啟。檢測時的非主鍵索引的多列索引的後綴是一個主鍵最左邊的前綴，則作為一個重複鍵。如：

PRIMARY KEY (`a`)

KEY `b` (`b`,`a`)

SELECT ... WHERE b=1 ORDER BY a;

如果按照這個工具去掉b索引，則會出現filesort，他給的意見是留下b（b）單列索引，把order by a去掉，因為他們的結果是一樣的，a是主鍵。

--config 

 讀取該配置文件的列表。

--databases

只檢查該列表中的數據庫。

--defaults-file

縮寫-F，讀取Mysql的配置文件，需要絕對路徑。

--engines

縮寫-e，只檢查該列表中指定的表的存儲引擎。

--tables

縮寫-t，只檢查列表中指定的表。

--help 

顯示幫助

--host

縮寫-h，連接到主機地址。

--ignore-databases

跳過檢查的某些數據庫。

--ignore-engines

跳過檢查的某些存儲引擎。

--ignore-tables

 跳過檢查某些表。

--ignore-order

加了這個參數會報： KEY(a,b)和KEY(b,a)是重複索引，默認關閉。

--key-types

檢查索引的類型：f=foreignkeys, k=keys or fk=both，默認是fk。

--password

縮寫-p，連接mysql時候的密碼。

--pid 

連接的PID

--port

縮寫-P，連接時候的端口。

--socket

縮寫-S，連接時候的套接字。

--[no]sql

打印出sql，如果有重複會答應出刪除重複索引的sql。默認開啟。

--[no]summary

打印出索引的統計信息。默認開啟。

--verbose 

縮寫-v，打印出所有的索引信息，包括重複索引。

--version 

打印版本信息。

#創建一個test_index表，並加入多個索引

mysql >

CREATE TABLE test_index(

    ID INT NOT NULL PRIMARY KEY,

    A INT NOT NULL,

    B INT NOT NULL,

    C INT NOT NULL,

    D INT NOT NULL,

    UNIQUE(ID),

    INDEX(ID),

    KEY B (`B`),

    KEY A (`A`),

    KEY ABC (`A`,`B`,`C`),

    KEY BCD (`B`,`C`,`D`),

    KEY ID_C_D (`ID`,`C`,`D`)

) ENGINE=InnoDB;

#可以看一下表的結構

mysql> DESC test_index;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| ID    | int(11) | NO   | PRI | NULL    |       |

| A     | int(11) | NO   | MUL | NULL    |       |

| B     | int(11) | NO   | MUL | NULL    |       |

| C     | int(11) | NO   |     | NULL    |       |

| D     | int(11) | NO   |     | NULL    |       |

+-------+---------+------+-----+---------+-------+

5 rows in set (0.00 sec)

#開始分析剛創建的那張test_index的索引是否重覆

[root@rosalie-mysql02 mysql]# pt-duplicate-key-checker -u root -p1234 --database=tttt

# A software update is available:

#   * The current version for Percona::Toolkit is 3.0.5

# ########################################################################

# tttt.test_index                                                         

# ########################################################################

# ID_2 is a duplicate of PRIMARY

# Key definitions:

#   KEY `ID_2` (`ID`),

#   PRIMARY KEY (`ID`),

# Column types:

#      `id` int(11) not null

# To remove this duplicate index, execute:

ALTER TABLE `tttt`.`test_index` DROP INDEX `ID_2`;

##紫色的是工具分析出,可以直接在DB執行

#索引ID_2是多餘的,因為ID原本就是主鍵了,又多設一個INDEX變ID_2

# Uniqueness of ID ignored because PRIMARY is a duplicate constraint

# ID is a duplicate of PRIMARY

# Key definitions:

#   UNIQUE KEY `ID` (`ID`),

#   PRIMARY KEY (`ID`),

# Column types:

#      `id` int(11) not null

# To remove this duplicate index, execute:

ALTER TABLE `tttt`.`test_index` DROP INDEX `ID`;

#索引ID是多餘的,因為ID原本就是主鍵了,又多設一個唯一索引ID這欄索引就再重複

# B is a left-prefix of BCD

# Key definitions:

#   KEY `B` (`B`),

#   KEY `BCD` (`B`,`C`,`D`),

# Column types:

#      `b` int(11) not null

#      `c` int(11) not null

#      `d` int(11) not null

# To remove this duplicate index, execute:

ALTER TABLE `tttt`.`test_index` DROP INDEX `B`;

#索引B是多餘的,因為已經有一個組合索引，同單左前綴索引效用

# A is a left-prefix of ABC

# Key definitions:

#   KEY `A` (`A`),

#   KEY `ABC` (`A`,`B`,`C`),

# Column types:

#      `a` int(11) not null

#      `b` int(11) not null

#      `c` int(11) not null

# To remove this duplicate index, execute:

ALTER TABLE `tttt`.`test_index` DROP INDEX `A`;

#索引A是多餘的,因為已經有一個組合索引，同單左前綴索引效用

# ########################################################################

# Summary of indexes                                                      

# ########################################################################

# Size Duplicate Indexes   16  #Size重複索引16

# Total Duplicate Indexes  4   #重複索引總數

# Total Indexes            9

#這是在未刪除多餘索引前查看表創建的內容

mysql> show create table tttt.test_index;

| test_index | CREATE TABLE `test_index` (

  `ID` int(11) NOT NULL,

  `A` int(11) NOT NULL,

  `B` int(11) NOT NULL,

  `C` int(11) NOT NULL,

  `D` int(11) NOT NULL,

  PRIMARY KEY (`ID`),

  UNIQUE KEY `ID` (`ID`),

  KEY `ID_2` (`ID`),

  KEY `B` (`B`),

  KEY `A` (`A`),

  KEY `ABC` (`A`,`B`,`C`),

  KEY `BCD` (`B`,`C`,`D`),

  KEY `ID_C_D` (`ID`,`C`,`D`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 

1 row in set (0.00 sec)

#依上面工具分析的結果，將多餘的INDEX刪除後，再重新查看

mysql> show create table tttt.test_index;

| test_index | CREATE TABLE `test_index` (

  `ID` int(11) NOT NULL,

  `A` int(11) NOT NULL,

  `B` int(11) NOT NULL,

  `C` int(11) NOT NULL,

  `D` int(11) NOT NULL,

  PRIMARY KEY (`ID`),

  KEY `ABC` (`A`,`B`,`C`),

  KEY `BCD` (`B`,`C`,`D`),

  KEY `ID_C_D` (`ID`,`C`,`D`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

【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)

只要出現訊息就表示成功