mysqlreport安装执行报错：Authentication plugin ‘caching_sha2_password‘ cannot be loaded

搁潜

已于 2022-06-08 20:04:16 修改

阅读量332

点赞数

分类专栏：性能测试文章标签： perl mysql 数据库

于 2022-06-08 20:00:49 首次发布

性能测试专栏收录该内容

8 篇文章 2 订阅

订阅专栏

mysqlreport安装

mysql命令行中精彩使用下面的指令来获取当前数据库的实时状态，show global variables用来查看配置的参数值：

mysql>show status;

mysql>show innodb status;

mysql>show global variables；#查看配置的参数值

但是他们的显示结果不太友好，我们需要更好的更加人性化的分析结果，而不是堆出来一堆数字。mysqlreport是一个第三方的Mysql状态报告工具，它把mysql的show status 和 show innodb status的结果进行一系列的后期处理，让可读性更强，更友好。下面是mysqlreport的安装过程：

MySQLReport 是用perl语言编写，所以想要运行它首先需要安装perl环境；它还要与MySQL数据库连接，所以还需要安装数据库接口 DBI 和数据库驱动 DBD-MySQL 。

perl -v #如果显示perl版本说明perl环境已经安装

1、安装依赖包

yum -y install perl-DBI   #安装DBI
yum -y install perl-DBD-MySQL   #安装DBD-mysql

安装mysqlreport，链接：http://pkgs.fedoraproject.org/repo/pkgs/mysqlreport/mysqlreport-3.5.tgz/33a345f5e2c89b083a9ff0423f7fd7b4/mysqlreport-3.5.tgz，解压后进入目录

2、mysqlrepoer执行

./mysqlreport --user root --password 123456 --host 127.0.0.1

报错提示：DBI connect(…) failed: Authentication plugin ‘caching_sha2_password’ cannot be loaded

查找的解决方案是修改登录密码加密规则，还原成mysql_native_password，详情参考：https://blog.csdn.net/Hedyqing/article/details/124189438?utm_medium=distribute.pc_relevant.none-task-blog-2_defaultbaidujs_title~default-0-124189438-blog-123681450.pc_relevant_antiscanv4&spm=1001.2101.3001.4242.1&utm_relevant_index=2

修改加密规则后，再次执行mysqlreport命令，执行成功。

Use of uninitialized value $stats{"Qcache_hits"} in numeric ne (!=) at ./mysqlreport line 911.
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 8.0.16             uptime 14 4:37:53      Wed Jun  8 19:52:58 2022

__ Key _________________________________________________________________
Buffer used         0 of   8.00M  %Used:   0.00
  Current       1.46M            %Usage:  18.24
Write hit       0.00%
Read hit        0.00%

__ Questions ___________________________________________________________
Total         679.50k     0.6/s
  DMS         507.46k     0.4/s  %Total:  74.68
  Com_        178.74k     0.1/s           26.30
  -Unknown      7.88k     0.0/s            1.16
  COM_QUIT      1.18k     0.0/s            0.17
Slow 10 s           0       0/s            0.00  %DMS:   0.00  Log:
DMS           507.46k     0.4/s           74.68
  SELECT      436.61k     0.4/s           64.25         86.04
  INSERT       57.13k     0.0/s            8.41         11.26
  UPDATE       12.31k     0.0/s            1.81          2.43
  DELETE        1.42k     0.0/s            0.21          0.28
  REPLACE           0       0/s            0.00          0.00
Com_          178.74k     0.1/s           26.30
  set_option  111.16k     0.1/s           16.36
  commit       53.17k     0.0/s            7.82
  admin_comma   7.54k     0.0/s            1.11

__ SELECT and Sort _____________________________________________________
Scan           82.76k     0.1/s %SELECT:  18.96
Range          17.66k     0.0/s            4.04
Full join       9.91k     0.0/s            2.27
Range check        40     0.0/s            0.01
Full rng join      24     0.0/s            0.01
Sort scan      25.05k     0.0/s
Sort range      1.33k     0.0/s
Sort mrg pass      31     0.0/s

__ Table Locks _________________________________________________________
Waited              0       0/s  %Total:   0.00
Immediate         272     0.0/s

__ Tables ______________________________________________________________
Open             3716 of 4000    %Cache:  92.90
Opened          3.96k     0.0/s

__ Connections _________________________________________________________
Max used           67 of 1000      %Max:   0.67
Total           1.38k     0.0/s

__ Created Temp ________________________________________________________
Disk table          0       0/s
Table          49.43k     0.0/s    Size:  16.0M
File              111     0.0/s

__ Threads _____________________________________________________________
Running             2 of   40
Cached             27 of  100      %Hit:  95.13
Created            67     0.0/s
Slow                0       0/s

__ Aborted _____________________________________________________________
Clients           390     0.0/s
Connects           59     0.0/s

__ Bytes _______________________________________________________________
Sent            1.45G    1.2k/s
Received      221.55M   180.7/s

__ InnoDB Buffer Pool __________________________________________________
Usage         112.00M of 128.00M  %Used:  87.50
Read hit       97.59%
Pages
  Free          1.02k            %Total:  12.50
  Data          6.91k                     84.40 %Drty:   0.00
  Misc            254                      3.10
  Latched                                  0.00
Reads         696.44M   567.9/s
  From file    16.76M    13.7/s            2.41
  Ahead Rnd         0       0/s
  Ahead Sql                 0/s
Writes          1.51M     1.2/s
Flushes       136.59k     0.1/s
Wait Free        5028     0.0/s

__ InnoDB Lock _________________________________________________________
Waits               4     0.0/s
Current             0
Time acquiring
  Total            46 ms
  Average          11 ms
  Max              28 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads        16.97M    13.8/s
  Writes      222.14k     0.2/s
  fsync        55.32k     0.0/s
  Pending
    Reads           0
    Writes          0
    fsync           1

Pages
  Created       2.39k     0.0/s
  Read         16.97M    13.8/s
  Written     136.67k     0.1/s

Rows
  Deleted      56.01k     0.0/s
  Inserted     86.69k     0.1/s
  Read        425.19M   346.7/s
  Updated      17.68k     0.0/s

1.参考链接：https://blog.csdn.net/weixin_42350864/article/details/113218769

2.参考链接：https://blog.csdn.net/Hedyqing/article/details/124189438?utm_medium=distribute.pc_relevant.none-task-blog-2_defaultbaidujs_title~default-0-124189438-blog-123681450.pc_relevant_antiscanv4&spm=1001.2101.3001.4242.1&utm_relevant_index=2