CentOS6上安装MySQL效能监控mysqlreport

一、首先解决yum无法使用

CentOS 6已经随着2020年11月的结束进入了EOL(Reaches End of Life),不过有一些老设备依然需要支持,CentOS官方也给这些还不想把CentOS 6扔进垃圾堆的用户保留了最后一个版本的镜像,只是这个镜像不会再有更新了

官方便在12月2日正式将CentOS 6相关的软件源移出了官方源,随之而来逐级镜像也会陆续将其删除。

不过有一些老设备依然需要维持在当前系统,CentOS官方也给这些还不想把CentOS 6扔进垃圾堆的用户保留了各个版本软件源的镜像,只是这个软件源不会再有更新了。

在CeintOS6使用yum会报错,如:

[root@c8-20 ~]# yum makecache
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
YumRepo Error: All mirror URLs are not using ftp, http[s] or file.
 Eg. Invalid release/repo/arch combination/
removing mirrorlist with no valid mirrors: /var/cache/yum/x86_64/6/base/mirrorlist.txt
Error: Cannot find a valid baseurl for repo: base

修复方式:

sed -i "s|enabled=1|enabled=0|g" /etc/yum/pluginconf.d/fastestmirror.conf
mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup
curl -o /etc/yum.repos.d/CentOS-Base.repo https://www.xmpan.com/Centos-6-Vault-Aliyun.repo 
#如果是海外用户,替换 curl -o /etc/yum.repos.d/CentOS-Base.repo https://www.xmpan.com/Centos-6-Vault-Official.repo
yum clean all
yum makecache

二、安装Perl

如果系统自带有就忽略这步

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

三、安装GCC

如果已安装就忽略这步

yum -y install gcc gcc-c++ libstdc++-devel

四、安装DBI

yum -y install perl-DBI

或者

wget https://cpan.metacpan.org/authors/id/T/TI/TIMB/DBI-1.636.tar.gz
tar -zxvf DBI-1.636.tar.gz
cd DBI-1.636
perl Makefile.PL
make
make test
make install

五、安装DBD-mysql

yum -y install perl-DBD-MySQL

或者

wget http://search.cpan.org/CPAN/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.038.tar.gz
tar -zxvf DBD-mysql-4.038.tar.gz

cd DBD-mysql-4.038
perl Makefile.PL --mysql_config=/usr/bin/mysql_config
make
make test
make install
# /usr/bin/mysql_config 这个路径需要通过 ps -ef|grep mysql 确认一下mysql的安装和运行路径

如果 make test报错,就可能是lib64下找不到mysql lib文件,比如报错如下:

[root@localhost DBD-mysql-4.038]# make test
PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
t/00base.t .............................. 1/6 
#   Failed test 'use DBD::mysql;'
#   at t/00base.t line 15.
#     Tried to use 'DBD::mysql'.
#     Error:  Can't load '/usr/local/src/DBD-mysql-4.038/blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: libmysqlclient.so.20: 无法打开共享对象文件: 没有那个文件或目录 at /usr/lib64/perl5/DynaLoader.pm line 200.
#  at (eval 8) line 2
# Compilation failed in require at (eval 8) line 2.
# BEGIN failed--compilation aborted at (eval 8) line 2.
Bailout called.  Further testing stopped:  Unable to load DBD::mysql
FAILED--Further testing stopped: Unable to load DBD::mysql
make: *** [test_dynamic] 错误 255

可以用以下方式解决:

cp /usr/local/mysql5.7/lib/* /usr/lib64/ # (如果64位 否则是lib)
make
make install
# /usr/local/mysql/lib的路径需要按mysql具体安装路径确定

六、安装mysqlreport

yum -y install mysqlreport

或者

wget https://github.com/daniel-nichter/hackmysql.com/blob/master/mysqlreport/mysqlreport
chmod 777 mysqlreport

七、使用mysqlreport

 ./mysqlreport --help

./mysqlreport --user root --password 123456 --socket /tmp/mysql.sock
# /tmp/mysql.sock 按实际情况
# 支持运程监控
# ./mysqlreport --user root --password 123456 -h 172.16.2.134 --no-mycnf --flush-status --outfile ~/123

输出结果:

MySQL 5.7.12             uptime 220 19:55:35    Mon Jan 11 09:45:20 2021

__ Key _________________________________________________________________
Buffer used     7.00k of   8.00M  %Used:   0.09
  Current       1.46M            %Usage:  18.26
Write hit      25.00%
Read hit       99.29%

__ Questions ___________________________________________________________
Total          79.68M     4.2/s
  DMS          59.69M     3.1/s  %Total:  74.91
  Com_         17.16M     0.9/s           21.54
  COM_QUIT      3.08M     0.2/s            3.87
  -Unknown    252.65k     0.0/s            0.32
Slow 10 s          12     0.0/s            0.00  %DMS:   0.00  Log:
DMS            59.69M     3.1/s           74.91
  SELECT       41.43M     2.2/s           51.99         69.41
  INSERT        8.87M     0.5/s           11.13         14.86
  UPDATE        8.31M     0.4/s           10.42         13.92
  DELETE        1.08M     0.1/s            1.36          1.82
  REPLACE           0       0/s            0.00          0.00
Com_           17.16M     0.9/s           21.54
  set_option   10.65M     0.6/s           13.36
  commit        3.93M     0.2/s            4.93
  show_status   1.91M     0.1/s            2.40

__ SELECT and Sort _____________________________________________________
Scan           12.33M     0.6/s %SELECT:  29.77
Range             150     0.0/s            0.00
Full join     510.31k     0.0/s            1.23
Range check         0       0/s            0.00
Full rng join       0       0/s            0.00
Sort scan       1.00M     0.1/s
Sort range    232.62k     0.0/s
Sort mrg pass 315.35k     0.0/s

__ Query Cache _________________________________________________________
Memory usage   16.35k of   1.00M  %Used:   1.60
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.91M     0.1/s

__ Tables ______________________________________________________________
Open             2000 of 2000    %Cache: 100.00
Opened         17.69k     0.0/s

__ Connections _________________________________________________________
Max used          142 of  151      %Max:  94.04
Total           3.08M     0.2/s

__ Created Temp ________________________________________________________
Disk table     36.51k     0.0/s
Table         870.93k     0.0/s    Size:  32.0M
File          322.97k     0.0/s

__ Threads _____________________________________________________________
Running             1 of    8
Cached              5 of    9      %Hit:  99.85
Created         4.68k     0.0/s
Slow                0       0/s

__ Aborted _____________________________________________________________
Clients           269     0.0/s
Connects           64     0.0/s

__ Bytes _______________________________________________________________
Sent           32.46G    1.7k/s
Received       10.54G   552.4/s

__ InnoDB Buffer Pool __________________________________________________
Usage         111.98M of 127.98M  %Used:  87.50
Read hit       99.99%
Pages
  Free          1.02k            %Total:  12.50
  Data          7.15k                     87.27 %Drty:   0.00
  Misc             19                      0.23
  Latched                                  0.00
Reads          38.51G    2.0k/s
  From file     4.13M     0.2/s            0.01
  Ahead Rnd         0       0/s
  Ahead Sql                 0/s
Writes        226.80M    11.9/s
Flushes        22.62M     1.2/s
Wait Free       23796     0.0/s

__ InnoDB Lock _________________________________________________________
Waits           10686     0.0/s
Current             0
Time acquiring
  Total        327209 ms
  Average          30 ms
  Max             435 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads         4.77M     0.3/s
  Writes       38.39M     2.0/s
  fsync        18.45M     1.0/s
  Pending
    Reads           0
    Writes          0
    fsync           0

Pages
  Created       3.61M     0.2/s
  Read          4.77M     0.3/s
  Written      22.64M     1.2/s

Rows
  Deleted       6.23M     0.3/s
  Inserted    973.26M    51.0/s
  Read         50.93G    2.7k/s

关于mysql分析决策树:

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 酷酷鲨 设计师:CSDN官方博客 返回首页