mysql的监控工具mysqlreport安装和说明

最新推荐文章于 2024-03-12 09:59:53 发布

7*24 工作者

最新推荐文章于 2024-03-12 09:59:53 发布

阅读量3.9k

点赞数 1

分类专栏： mysql数据库文章标签： mysql 监控工具 mysqlrepor

本文链接：https://blog.csdn.net/m0_37886429/article/details/72959986

版权

mysql数据库专栏收录该内容

61 篇文章 2 订阅

订阅专栏

mysqlreport以很友好的方式显示 mysql状态变。事实上，它几乎报告了所有的状态。不像 show status 只是在显示了100多个状态值，mysqlreport 则以人性化的方式阐释和格式化了这些状态值，大大增加了其可读性。

mysqlreport 的好处是可以快速的查看各种状态参数组，从而了解服务器的运行状态情况，而无需从 show status的结果中人工计算。例如索引读取比率是个重要的参数，但是 show status 中并没有显示；它是一个推断值(key_reads 和 key_read_requests 的比值)。

mysql命令行中精彩使用下面的指令来获取当前数据库的实时状态

mysql>show status;
mysql>show innodb status;

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

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

一、安装perl环境

[root@linux-node2 ~]# yum -y install perl perl-devel
[root@linux-node2 ~]# perl -v   #如果显示perl版本说明perl环境已经安装

这里写图片描述

二、安装DBI

[root@linux-node2 ~]# cd /usr/local/src/
[root@linux-node2 src]# wget https://cpan.metacpan.org/authors/id/T/TI/TIMB/DBI-1.636.tar.gz
[root@linux-node2 src]# tar zxvf DBI-1.636.tar.gz
[root@linux-node2 src]# cd DBI-1.636
[root@linux-node2 DBI-1.636]# perl Makefile.PL
[root@linux-node2 DBI-1.636]# make
[root@linux-node2 DBI-1.636]# make install

直到出现“Writing Makefile for DBI”

这里写图片描述

三、安装DBD-mysql

[root@linux-node2 ~]# cd /usr/local/src/
[root@linux-node2 src]# wget http://search.cpan.org/CPAN/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.020.tar.gz
[root@linux-node2 src]# tar zxvf DBD-mysql-4.020.tar.gz
[root@linux-node2 src]# cd DBD-mysql-4.020
[root@linux-node2 DBD-mysql-4.020]# perl Makefile.PL --mysql_config=/usr/local/mysql/bin/mysql_config
[root@linux-node2 DBD-mysql-4.020]# make
[root@linux-node2 DBD-mysql-4.020]# make install

这里写图片描述

备注：
1、现在最新版本的DBD-mysql是4.042，下载地址：
http://www.cpan.org/authors/id/M/MI/MICHIELB/DBD-mysql-4.042.tar.gz

2、如果有报错，内容为

PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/Arch')" t/*.t t/00base....................ok 1/6
# Failed test 'use DBD::mysql;' t/00base....................NOK 2
# in t/00base.t at line 21.
# Tried to use 'DBD::mysql'.
# Error: Can't load '/usr/local/src/DBD-mysql-4.020/blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: libmysqlclient.so.16: cannot open shared object file: No such file or directory at /usr/lib64/perl5/5.8.8/x86_64-Linux-thread-multi/DynaLoader.pm line 230.
# at (eval 6) line 2 # Compilation failed in require at (eval 6) line 2.
# BEGIN failed--compilation aborted at t/00base.t line 21.
FAILED--Further testing stopped: Unable to load DBD::mysql make: *** [test_dynamic] Error 9

解决办法

cp /usr/local/mysql/lib/mysql/* /usr/lib
cp /usr/local/mysql/lib/mysql/* /usr/lib64/ (如果64位 否则是lib)

三、安装mysqlreport

建议直接用yum安装

[root@linux-node2 ~]# yum -y install mysqlreport

四、myslreport用法

1、帮助信息和选项

[root@linux-node2 ~]# mysqlreport --help

这里写图片描述

2、输出信息

[root@linux-node2 ~]# mysqlreport --user root --password 123456 -h 192.168.88.134 --no-mycnf --flush-status --outfile ~/123

MySQL 5.6.35             uptime 0 0:58:2        Fri Jun  9 23:19:01 2017

__ Key _________________________________________________________________
Buffer used     1.00k of   8.00M  %Used:   0.01
  Current       1.46M            %Usage:  18.25
Write hit       0.00%
Read hit        0.00%

__ Questions ___________________________________________________________
Total             229     0.1/s
  Com_            153     0.0/s  %Total:  66.81
  DMS              42     0.0/s           18.34
  COM_QUIT         34     0.0/s           14.85
Slow 10 s           0       0/s            0.00  %DMS:   0.00  Log:
DMS                42     0.0/s           18.34
  SELECT           42     0.0/s           18.34        100.00
  REPLACE           0       0/s            0.00          0.00
  DELETE            0       0/s            0.00          0.00
  INSERT            0       0/s            0.00          0.00
  UPDATE            0       0/s            0.00          0.00
Com_              153     0.0/s           66.81
  set_option       98     0.0/s           42.79
  show_status      11     0.0/s            4.80
  show_variab      10     0.0/s            4.37

__ SELECT and Sort _____________________________________________________
Scan               49     0.0/s %SELECT: 116.67
Range               0       0/s            0.00
Full join           2     0.0/s            4.76
Range check         0       0/s            0.00
Full rng join       0       0/s            0.00
Sort scan           7     0.0/s
Sort range          0       0/s
Sort mrg pass       0       0/s

__ Query Cache _________________________________________________________
Memory usage   16.81k 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           0       0/s

__ Tables ______________________________________________________________
Open               68 of 2000    %Cache:   3.40
Opened             75     0.0/s

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

__ Created Temp ________________________________________________________
Disk table          1     0.0/s
Table              60     0.0/s    Size:  16.0M
File                0       0/s

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

__ Aborted _____________________________________________________________
Clients             0       0/s
Connects            0       0/s

__ Bytes _______________________________________________________________
Sent          234.47k    67.3/s
Received       12.34k     3.5/s

__ InnoDB Buffer Pool __________________________________________________
Usage           3.58M of 127.98M  %Used:   2.80
Read hit       77.76%
Pages
  Free          7.96k            %Total:  97.20
  Data            229                      2.80 %Drty:   0.00
  Misc              0                      0.00
  Latched                                  0.00
Reads           1.03k     0.3/s
  From file       230     0.1/s           22.24
  Ahead Rnd         0       0/s
  Ahead Sql                 0/s
Writes              1     0.0/s
Flushes             1     0.0/s
Wait Free           0       0/s

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

__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads           249     0.1/s
  Writes            5     0.0/s
  fsync             5     0.0/s
  Pending
    Reads           0
    Writes          0
    fsync           0

Pages
  Created           0       0/s
  Read            229     0.1/s
  Written           1     0.0/s

Rows
  Deleted           0       0/s
  Inserted          0       0/s
  Read              3     0.0/s
  Updated           0       0/s