Mytop工具来监控MySQL资源
下载地址
个人百度网盘地址：
http://pan.baidu.com/s/1i3ePJvf
1. 安装TermReadKey
tar -xvf TermReadKey-2.30.tar.gz
cd TermReadKey-2.30
perl Makefile.pl
make
make test
make install
[root@mysqltest ~]# tar -xvf TermReadKey-2.30.tar.gz
TermReadKey-2.30/
TermReadKey-2.30/genchars.pl
TermReadKey-2.30/Makefile.PL
TermReadKey-2.30/Configure.pm
TermReadKey-2.30/test.pl
TermReadKey-2.30/ReadKey.pm
TermReadKey-2.30/META.yml
TermReadKey-2.30/ReadKey.xs
TermReadKey-2.30/ppport.h
TermReadKey-2.30/MANIFEST
TermReadKey-2.30/README
[root@mysqltest ~]# cd TermReadKey-2.30
[root@mysqltest TermReadKey-2.30]# ll
total 144
-rw-r--r-- 1 501 501 27633 Mar 3 2004 Configure.pm
-rw-r--r-- 1 501 501 10688 Jan 12 2005 genchars.pl
-rw-r--r-- 1 501 501 1222 Mar 3 2004 Makefile.PL
-rw-r--r-- 1 501 501 170 Jan 12 2005 MANIFEST
-rw-r--r-- 1 501 501 245 Jan 12 2005 META.yml
-rw-r--r-- 1 501 501 15782 Jan 12 2005 ppport.h
-rw-r--r-- 1 501 501 16187 Jan 12 2005 ReadKey.pm
-rw-r--r-- 1 501 501 41938 Jan 12 2005 ReadKey.xs
-rw-r--r-- 1 501 501 6357 Jan 12 2005 README
-rw-r--r-- 1 501 501 7696 Jan 12 2005 test.pl
[root@mysqltest TermReadKey-2.30]#
[root@mysqltest TermReadKey-2.30]#
[root@mysqltest TermReadKey-2.30]# perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for Term::ReadKey
[root@mysqltest TermReadKey-2.30]#
[root@mysqltest TermReadKey-2.30]#
[root@mysqltest TermReadKey-2.30]# make
cp ReadKey.pm blib/lib/Term/ReadKey.pm
AutoSplitting blib/lib/Term/ReadKey.pm (blib/lib/auto/Term/ReadKey)
/usr/bin/perl -I/usr/lib/perl5/5.8.8 genchars.pl
Writing termio/termios section of cchars.h... Done.
Checking for sgtty...
Sgtty NOT found.
Writing sgtty section of cchars.h... Done.
/usr/bin/perl /usr/lib/perl5/5.8.8/ExtUtils/xsubpp -noprototypes -typemap /usr/lib/perl5/5.8.8/ExtUtils/typemap ReadKey.xs > ReadKey.xsc && mv ReadKey.xsc ReadKey.c
gcc -c -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -pipe -Wdeclaration-after-statement -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -DVERSION=\"2.30\" -DXS_VERSION=\"2.30\" -fPIC "-I/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/CORE" ReadKey.c
In file included from ReadKey.xs:6:
ppport.h:230:1: warning: "PERL_UNUSED_DECL" redefined
In file included from ReadKey.xs:4:
/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/CORE/perl.h:163:1: warning: this is the location of the previous definition
In file included from ReadKey.xs:375:
cchars.h: In function ‘XS_Term__ReadKey_GetControlChars’:
cchars.h:244: warning: unused variable ‘i’
ReadKey.xs: In function ‘ReadMode’:
ReadKey.xs:770: warning: unused variable ‘Perl___notused’
ReadKey.c: In function ‘XS_Term__ReadKey_SetTerminalSize’:
ReadKey.c:1999: warning: unused variable ‘targ’
Running Mkbootstrap for Term::ReadKey ()
chmod 644 ReadKey.bs
rm -f blib/arch/auto/Term/ReadKey/ReadKey.so
gcc -shared -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic ReadKey.o -o blib/arch/auto/Term/ReadKey/ReadKey.so \
\
chmod 755 blib/arch/auto/Term/ReadKey/ReadKey.so
cp ReadKey.bs blib/arch/auto/Term/ReadKey/ReadKey.bs
chmod 644 blib/arch/auto/Term/ReadKey/ReadKey.bs
Manifying blib/man3/Term::ReadKey.3pm
[root@mysqltest TermReadKey-2.30]# make test
PERL_DL_NONLAZY=1 /usr/bin/perl "-Iblib/lib" "-Iblib/arch" -w test.pl
1 .. 8
ok 1
ok 2
ok 3
ok 4
ok 5
ok 6
ok 7
ok 8
[root@mysqltest TermReadKey-2.30]# make install
Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/Term/ReadKey/ReadKey.so
Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/Term/ReadKey/ReadKey.bs
Files found in blib/arch: installing files in blib/lib into architecture dependent library tree
Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/Term/ReadKey.pm
Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/Term/ReadKey/autosplit.ix
Installing /usr/share/man/man3/Term::ReadKey.3pm
Writing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/Term/ReadKey/.packlist
Appending installation info to /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/perllocal.pod
[root@mysqltest TermReadKey-2.30]#
2. 安装DBI
tar xzvf DBI-1.615.tar.gz
cd DBI-1.615
perl Makefile.pl
make
make test
make install
（安装可能报错）
或者：
[root@mysqltest mytop-1.6]# yum list|grep DBD
Unable to read consumer identity
perl-DBD-MySQL.x86_64 3.0007-2.el5 installed
perl-DBD-Pg.x86_64 1.49-2.el5_3.1 hc360
[root@mysqltest mytop-1.6]# yum -y install perl-DBD-MySQL.x86_64
3. 安装Mytop
tar xzvf mytop-1.6.tar.gz
cd mytop-1.6
perl Makefile.pl
make
make test
make install
[root@mysqltest dudong]# tar -xvf mytop-1.6.tar.gz
mytop-1.6/
mytop-1.6/MANIFEST
mytop-1.6/test.pl
mytop-1.6/mytop
mytop-1.6/Changes
mytop-1.6/INSTALL
mytop-1.6/README
mytop-1.6/Makefile.PL
mytop-1.6/META.yml
[root@mysqltest dudong]# cd mytop-1.6
[root@mysqltest mytop-1.6]# ll
total 84
-rw-r--r-- 1 1000 1000 6647 Feb 17 2007 Changes
-rw-r--r-- 1 1000 1000 20 Apr 28 2002 INSTALL
-rw-r--r-- 1 1000 1000 730 Apr 27 2002 Makefile.PL
-rw-r--r-- 1 1000 1000 137 Feb 17 2007 MANIFEST
-rw-r--r-- 1 1000 1000 405 Feb 17 2007 META.yml
-rwxr-xr-x 1 1000 1000 51906 Feb 17 2007 mytop
-rw-r--r-- 1 1000 1000 1003 Aug 5 2003 README
-rw-r--r-- 1 1000 1000 469 Apr 27 2002 test.pl
[root@mysqltest mytop-1.6]# perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for mytop
[root@mysqltest mytop-1.6]#
[root@mysqltest mytop-1.6]#
[root@mysqltest mytop-1.6]# make
cp mytop blib/script/mytop
/usr/bin/perl -MExtUtils::MY -e ‘MY->fixin(shift)‘ -- blib/script/mytop
Manifying blib/man1/mytop.1
[root@mysqltest mytop-1.6]# cd mytop
-bash: cd: mytop: Not a directory
[root@mysqltest mytop-1.6]# make test
PERL_DL_NONLAZY=1 /usr/bin/perl "-Iblib/lib" "-Iblib/arch" test.pl
1..1
ok 1
[root@mysqltest mytop-1.6]# make install
Installing /usr/local/share/man/man1/mytop.1
Installing /usr/local/bin/mytop
Appending installation info to /usr/lib64/perl5/perllocal.pod
[root@mysqltest mytop-1.6]#
常见错误：
1
安装mytop 出现 Error in option spec: "long|!"
编辑 mytop vim /usr/bin/mytop
找到 "long|!" => \$config{long_nums},
修改成 "long|long_nums|l!" => \$config{long_nums},
# "long|!" => \$config{long_nums},
"long|long_nums|l!" => \$config{long_nums},
2
执行 mytop -uroot -pxxxxx 可能会出现报错：
MySQL on localhost (5.1.43-log) up 345+20:36:31 [09:07:11]
Queries: 3.0 qps: 0 Slow: 0.0 Se/In/Up/De(%): 00/00/00/00
Key Efficiency: 100.0% Bps in/out: 0.0/ 0.0
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
Use of uninitialized value in substitution (s///) at ./mytop line 955.
Use of uninitialized value in substitution (s///) at ./mytop line 955.
...................................................
Use of uninitialized value in substitution (s///) at ./mytop line 955.
解决方法：
vi mytop
跳到955行，找到
$host =~ s/^([^.]+).*/$1/;
$thread->{Host} = $host;
改为：
if ($host)
{
$host =~ s/^([^.]+).*/$1/;
$thread->{Host} = $host;
}
4. 运行Mytop进行监控
配置：
[root@nagiosdb mytop-1.6]# ./mytop -u root -p ‘123456‘ -h 172.25.128.47:3306
Cannot connect to MySQL server. Please check the:
* database you specified "test" (default is "test")
* username you specified "root" (default is "root")
* password you specified "123456" (default is "")
* hostname you specified "172.25.128.47" (default is "localhost")
* port you specified "3306" (default is 3306)
* socket you specified "" (default is "")
The options my be specified on the command-line or in a ~/.mytop
config file. See the manual (perldoc mytop) for details.
Here‘s the exact error from DBI. It might help you debug:
Access denied for user ‘root‘@‘nagiosdb‘ (using password: YES)
【其中，-u参数后面是数据库用户名，-p参数后面是数据库密码，-h参数后面是数据库服务器地址和数据库所用端口号】
运行监控画面如下：
[root@nagiosdb mytop-1.6]# ./mytop
MySQL on localhost (5.6.22) up 0+21:18:24 [15:17:32]
Queries: 3.0 qps: 0 Slow: 0.0 Se/In/Up/De(%): 00/00/00/00
Key Efficiency: 99.9% Bps in/out: 0.0/ 0.2
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
141 centreon sznagios:38676 centreon_s 0 Sleep
147 centreon sznagios:38682 centreon_s 0 Sleep
149 centreon sznagios:38685 centreon_s 0 Sleep
153 centreon sznagios:38692 centreon_s 0 Sleep
158 centreon sznagios:38699 centreon_s 0 Sleep
134920 root localhost test 0 Query show full processlist
83433 centreon sznagios:52368 centreon 1 Sleep
83425 centreon sznagios:52342 centreon 7 Sleep
83405 centreon sznagios:52294 centreon 10 Sleep
83508 centreon sznagios:52734 centreon 10 Sleep
84001 centreon sznagios:55458 centreon 10 Sleep
83479 centreon sznagios:52610 centreon 13 Sleep
84146 centreon sznagios:56369 centreon 13 Sleep
85812 centreon sznagios:38258 centreon 14 Sleep
85775 centreon sznagios:38204 centreon 25 Sleep
83585 centreon sznagios:53207 centreon 39 Sleep
85780 centreon sznagios:38209 centreon 40 Sleep
85794 centreon sznagios:38240 centreon 43 Sleep
85990 centreon sznagios:39089 centreon 58 Sleep
83610 centreon sznagios:53365 centreon 89 Sleep
84305 centreon sznagios:57667 centreon 104 Sleep
86012 centreon sznagios:39180 centreon 179 Sleep
95078 root localhost centreon_s 644 Sleep
119618 root 172.25.1.19 centreon_s 5936 Sleep
122126 root 172.25.1.19 centreon_s 5936 Sleep
119655 root 172.25.1.19 centreon_s 7002 Sleep
119648 root 172.25.1.19 centreon_s 7004 Sleep
119642 root 172.25.1.19 centreon_s 7006 Sleep
119634 root 172.25.1.19 centreon_s 7008 Sleep
119632 root 172.25.1.19 centreon_s 7010 Sleep
119626 root 172.25.1.19 centreon_s 7012 Sleep
119620 root 172.25.1.19 centreon_s 7016 Sleep
117453 root 172.25.1.19 centreon_s 8013 Sleep
117454 root 172.25.1.19 centreon_s 8013 Sleep
96133 cactiuser sznagios:59301 cacti 17736 Sleep
96099 cactiuser sznagios:59230 cacti 17754 Sleep
说明：
第一行显示了主机名称，还有至今 MySQL 的运行时间 (以 days hour:minutes:seconds 为格式)。
第二、三行的 Queries 显示了至今执行的 SQL 查询语句总数，另外还有目前每秒处理的查询数和速度。
第四行的 Key Efficiency 就是传说中的缓存命中率了，如果太低了你可能要调整你的 MySQL 设置，或者调整一下表的结构，后面还有目前的进出速度。
最下方的区域就是目前链接到数据库的各个线程，你可以按 k 杀死一个线程，或者按 f 了解特定线程的信息。
参数：
-u / --user <USERNAME>：指定 username，预设是 root
-p / --pass / --password <PASSWORD>：指定 password，预设是 none
-h / --host <HOSTNAME[:PORT]>：指定 MySQL server 的 hostname，预设是 localhost
-P / --port <PORT>：指定连接 MySQL server 的 port，预设是 3306
-s / --delay <SECONDS>：更新的秒数，预设是 5 秒
-d / --db / --database <DATABASE>：指定连接的资料库，预设是 test
-b / --batch / --batchmode：指定为 batch mode，每次更新不会清除旧的显示结果，会将更新资料显示上最上方，预设是 unset
-S / --socket <PATH_TO_SOCKET>：指定使用 MySQL socket 直接连线，而不使用 TCP/IP 连线，预设是 none (当 mytop 和 MySQL 在同一台时才能使用)
--header or -noheader：是否要显示表头，预设是 header
--color or --nocolor：是否要使用颜色，预设是 color
-i / -idle or -noidle：idle 的 thread 是否要出现在清单上，预设是 idle
mytop 快捷键
s：设定更新时间
p：暂停画面更新
q：离开
u：只看某个使用者的 thread
o：反转排列顺序
Mytop工具来监控MySQL资源
原文：http://blog.itpub.net/29500582/viewspace-1432445/
