docker mysql:5.6镜像安装mysqlreport、pt-query-digest

更新debian源

echo "deb http://ftp.cn.debian.org/debian/ stretch main" > /etc/apt/sources.list
echo "deb http://ftp.cn.debian.org/debian/ stretch-updates main" >> /etc/apt/sources.list 
echo "deb http://ftp.cn.debian.org/debian-security stretch/updates main" >> /etc/apt/sources.list

mysqlreport安装

安装DBI、DBD::mysql

rm -f /root/.cpan/sources/modules/02packages.details.txt.gz
rm -f /root/.cpan/sources/modules/03modlist.data.gz
apt install make gcc g++ wget libmysqlclient-dev
perl -MCPAN -e shell
install DBI
install DBD::mysql

安装&运行mysqlreport

#下载mysqlreport tgz
wget http://pkgs.fedoraproject.org/repo/pkgs/mysqlreport/mysqlreport-3.5.tgz/33a345f5e2c89b083a9ff0423f7fd7b4/mysqlreport-3.5.tgz
#解压到目录mysqlreport
tar -zxvf mysqlreport-3.5.tgz 
#使用mysqlreport收集信息
mv mysqlreport-3.5 mysqlreport
cd mysqlreport && mkdir report
./mysqlreport --user=root --port 3306 --outfile=../mysqlreport/report/mysqlreport20201217.txt

# ./mysqlreport --user=root --port 3306 --outfile=../mysqlreport/report/mysqlreport20201217.txt
Use of uninitialized value in formline at ./mysqlreport line 1099.
Use of uninitialized value $is in multiplication (*) at ./mysqlreport line 829.
Use of uninitialized value in formline at ./mysqlreport line 1227.
Use of uninitialized value in formline at ./mysqlreport line 1235.
MySQL 5.6.42-log         uptime 0 1:45:48       Wed Mar  9 04:43:05 2022

__ Key _________________________________________________________________
Buffer used   104.00k of   8.00M  %Used:   1.27
  Current       1.56M            %Usage:  19.51
Write hit      92.75%
Read hit      100.00%

__ Questions ___________________________________________________________
Total           2.35k     0.4/s
  Com_          1.51k     0.2/s  %Total:  64.52
  DMS           1.33k     0.2/s           56.52
  -Unknown        591     0.1/s           25.17
  COM_QUIT         97     0.0/s            4.13
Slow 10 s           0       0/s            0.00  %DMS:   0.00  Log:
DMS             1.33k     0.2/s           56.52
  INSERT          884     0.1/s           37.65         66.62
  SELECT          421     0.1/s           17.93         31.73
  UPDATE           17     0.0/s            0.72          1.28
  DELETE            5     0.0/s            0.21          0.38
  REPLACE           0       0/s            0.00          0.00
Com_            1.51k     0.2/s           64.52
  show_warnin     527     0.1/s           22.44
  stmt_execut     254     0.0/s           10.82
  stmt_prepar     156     0.0/s            6.64

__ SELECT and Sort _____________________________________________________
Scan              245     0.0/s %SELECT:  58.19
Range               0       0/s            0.00
Full join           3     0.0/s            0.71
Range check         0       0/s            0.00
Full rng join       0       0/s            0.00
Sort scan           4     0.0/s
Sort range          0       0/s
Sort mrg pass       0       0/s

__ Query Cache _________________________________________________________
Memory usage   16.82k of   1.00M  %Used:   1.64
Block Fragmnt 100.00%
Hits                0       0/s
Inserts             1     0.0/s
Insrt:Prune       1:1       0/s
Hit:Insert     0.00:1

__ Table Locks _________________________________________________________
Waited              0       0/s  %Total:   0.00
Immediate       1.39k     0.2/s

__ Tables ______________________________________________________________
Open               81 of 2000    %Cache:   4.05
Opened            216     0.0/s

__ Connections _________________________________________________________
Max used            2 of  151      %Max:   1.32
Total             101     0.0/s

__ Created Temp ________________________________________________________
Disk table         99     0.0/s
Table             335     0.1/s    Size:   1.0M
File                9     0.0/s

__ Threads _____________________________________________________________
Running             1 of    1
Cached              1 of    9      %Hit:  98.02
Created             2     0.0/s
Slow                0       0/s

__ Aborted _____________________________________________________________
Clients             4     0.0/s
Connects            5     0.0/s

__ Bytes _______________________________________________________________
Sent            3.12M   490.8/s
Received      516.43M   81.4k/s

__ InnoDB Buffer Pool __________________________________________________
Usage           4.02M of   5.00M  %Used:  80.31
Read hit       97.05%
Pages
  Free             63            %Total:  19.69
  Data            256                     80.00 %Drty:   0.00
  Misc              1                      0.31
  Latched                                  0.00
Reads          35.49k     5.6/s
  From file     1.05k     0.2/s            2.95
  Ahead Rnd         0       0/s
  Ahead Sql                 0/s
Writes         15.71k     2.5/s
Flushes         1.38k     0.2/s
Wait Free           0       0/s

__ InnoDB Lock _________________________________________________________
Waits               1     0.0/s
Current             0
Time acquiring
  Total          2000 ms
  Average        2000 ms
  Max            2000 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads         1.06k     0.2/s
  Writes        2.62k     0.4/s
  fsync         1.22k     0.2/s
  Pending
    Reads           0
    Writes          0
    fsync           0

Pages
  Created         747     0.1/s
  Read          1.05k     0.2/s
  Written       1.38k     0.2/s

Rows
  Deleted          10     0.0/s
  Inserted        331     0.1/s
  Read            846     0.1/s
  Updated          96     0.0/s

pt-query-digest

安装Digest::MD5

perl -MCPAN -e shell
install Digest::MD5

pt-query-digest安装与使用

官方：https://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html

wget percona.com/get/pt-query-digest
chmod u+x pt-query-digest
mv pt-query-digest /usr/bin/
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
...