一、首先解决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分析决策树: