【MySQL】mytopを入れてみた

GIGAZINEさまの記事 MySQLの状態がリアルタイムでわかるMySQL用topコマンド「mytop」と「innotop」 に触発されて、mytopを入れてみた。GIGAZINEさまの記事ではソースからコンパイルしているが、なんでもrpmで入れたい派の私は、rpmforgeからパッケージを探すことにした。

■■ パッケージを探す

sh-3.00# yum --enablerepo=rpmforge search mytop
Loading "fastestmirror" plugin
Searching Packages:
Setting up repositories
Loading mirror speeds from cached hostfile
Reading repository metadata in from local files
Excluding Packages in global exclude list
Finished

mytop.noarch 1.4-1.el4.rf rpmforge
Matched from:
mytop
mytop is a console-based (non-gui) tool for monitoring the threads and
overall performance of a MySQL 3.22.x, 3.23.x, and 4.x server. It runs
on most Unix systems (including Mac OS X) which have Perl, DBI, and
Term::ReadKey installed. And with Term::ANSIColor installed you even
get color. If you install Time::HiRes, you'll get good real-time
queries/second stats. As of version 0.7, it even runs on Windows
(somewhat).
http://jeremy.zawodny.com/mysql/mytop/

いつものように、rpmforgeをenableにしてインストールする。

■■ インストール

sh-3.00# yum --enablerepo=rpmforge install mytop
Loading "fastestmirror" plugin
Setting up Install Process
Setting up repositories
Loading mirror speeds from cached hostfile
Reading repository metadata in from local files
Excluding Packages in global exclude list
Finished
Parsing package install arguments
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for mytop to pack into transaction set.
mytop-1.4-1.el4.rf.noarch 100% |=========================| 2.9 kB 00:00
---> Package mytop.noarch 0:1.4-1.el4.rf set to be updated
--> Running transaction check

Dependencies Resolved

=============================================================================
Package Arch Version Repository Size
=============================================================================
Installing:
mytop noarch 1.4-1.el4.rf rpmforge 31 k

Transaction Summary
=============================================================================
Install      1 Package(s)
Update       0 Package(s)
Remove       0 Package(s)
Total download size: 31 k
Is this ok [y/N]: y
Downloading Packages:
(1/1): mytop-1.4-1.el4.rf 100% |=========================| 31 kB    00:00
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing: mytop                        ######################### [1/1]

Installed: mytop.noarch 0:1.4-1.el4.rf
Complete!

■■ mytopパッケージに含まれるファイルを調べる

sh-3.00$ rpm -ql mytop
/usr/bin/mytop
/usr/share/doc/mytop-1.4
/usr/share/doc/mytop-1.4/Changes
/usr/share/doc/mytop-1.4/INSTALL
/usr/share/doc/mytop-1.4/MANIFEST
/usr/share/doc/mytop-1.4/README
/usr/share/man/man1/mytop.1.gz

■■ まずは使ってみる

sh-3.00$ mytop
Can't locate Term/ReadKey.pm in @INC (@INC contains: /usr/lib/perl5/5.8.5/i386-linux-thread-multi /usr/lib/perl5/5.8.5 /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.4/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.3/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.2/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.1/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.5 /usr/lib/perl5/site_perl/5.8.4 /usr/lib/perl5/site_perl/5.8.3 /usr/lib/perl5/site_perl/5.8.2 /usr/lib/perl5/site_perl/5.8.1 /usr/lib/perl5/site_perl/5.8.0 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.4/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.3/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.2/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.1/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.5 /usr/lib/perl5/vendor_perl/5.8.4 /usr/lib/perl5/vendor_perl/5.8.3 /usr/lib/perl5/vendor_perl/5.8.2 /usr/lib/perl5/vendor_perl/5.8.1 /usr/lib/perl5/vendor_perl/5.8.0 /usr/lib/perl5/vendor_perl .) at /usr/bin/mytop line 165.

おやおや、Term/ReadKey.pm がないって怒られてしまった。というわけで、こいつもrpmforgeから探してインストールする。

sh-3.00# yum --enablerepo=rpmforge search ReadKey
sh-3.00# yum --enablerepo=rpmforge install perl-Term-ReadKey
Loading "fastestmirror" plugin
Setting up Install Process
Setting up repositories
Loading mirror speeds from cached hostfile
Reading repository metadata in from local files
Excluding Packages in global exclude list
Finished
Parsing package install arguments
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for perl-Term-ReadKey to pack into transaction set.
perl-Term-ReadKey-2.30-2. 100% |=========================| 3.9 kB 00:00
---> Package perl-Term-ReadKey.i386 0:2.30-2.el4.rf set to be updated
--> Running transaction check

Dependencies Resolved

Transaction Summary
=============================================================================
Install      1 Package(s)
Update       0 Package(s)
Remove       0 Package(s)
Total download size: 31 k
Is this ok [y/N]: y
Downloading Packages:
(1/1): perl-Term-ReadKey- 100% |=========================| 31 kB    00:01
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing: perl-Term-ReadKey            ######################### [1/1]

Installed: perl-Term-ReadKey.i386 0:2.30-2.el4.rf
Complete!

■■ あらためて使ってみる

sh-3.00$ mytop
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 "" (default is "")
* hostname you specified "localhost" (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'@'localhost' (using password: NO)

なるほど、データベース名とユーザ、パスワードを入れなきゃだめなのか。
詳しい使い方を見てみよう

sh-3.00$ perldoc mytop

とりあえず最低限の使い方は

Arguments

mytop handles long and short command-line arguments. Not all options have both long and short formats, however. The
long arguments can start with one or two dashes ‘-’ or ‘--’. They are shown here with just one.

       -u or -user username
    &n
bsp;      Username to use when logging in to the MySQL server. Default: ‘‘root’’.

-p or -pass or -password password
Password to use when logging in to the MySQL server. Default: none.

       -h or -host hostname[:port]
           Hostname of the MySQL server. The hostname may be followed by an option port number. Note that the port is
           specified separate from the host when using a config file. Default: ‘‘localhost’’.

-port or -P port
If you’re running MySQL on a non-standard port, use this to specify the port number. Default: 3306.

-s or -delay seconds
How long between display refreshes. Default: 5

-d or -db or -database database
Use if you’d like mytop to connect to a specific database by default. Default: ‘‘test’’.

このくらいか。

例えば、MySQLのrootユーザになってpostfixの認証データベースを見てみたい場合には

sh-3.00$ mytop -uroot -p<パスワード> -dpostfix -s1

として実行してやれば、postfixのデータベースを1秒おきに見てくれる。
実行結果は以下の通り。下記はあくまで実行時の例である。

====================================================================================================
MySQL on localhost (4.1.20)                                up 15+12:42:15 [12:46:08]
Queries: 113.8k qps:    0 Slow:     0.0         Se/In/Up/De(%):    91/00/00/00
             qps now:    2 Slow qps: 0.0 Threads:    2 (   1/   0) 00/00/00/00
Key Efficiency: 100.0% Bps in/out: 16.3/121.6   Now in/out: 42.0/ 4.8k

      Id      User         Host/IP         DB      Time    Cmd Query or State
      --      ----         -------         --      ----    --- ----------
    4457      root       localhost    postfix         0 Query show full processlist
    4451   postfix       localhost    postfix       376 Sleep
====================================================================================================

しかし、いちいちコマンドラインに直接パスワード入れるってのどうなの？と思うのが人の常。自分さえ忘れなきゃいいとかそういう問題じゃなくて、historyから見られてしまったりするのが嫌だ。(ほかのユーザがrootになって、.bash_historyを直接見られるのは仕方ないにしても)せめて画面にパスワードが出るのだけは避けたい。というわけで、何か方法はないかと探してみる。

sh-3.00$ perldoc mytop

Config File

       Instead of always using bulky command-line parameters, you can also use a config file in your home directory
       ("~/.mytop"). If present, mytop will read it automatically. It is read before any of your command-line arguments
       are processed, so your command-line arguments will override directives in the config file.

Here is a sample config file "~/.mytop" which implements the defaults described above.

         user=root
         pass=
         host=localhost
         db=test
         delay=5
         port=3306
         socket=
         batchmode=0
         header=1
         color=1
         idle=1

あったよ！というわけで、ホームディレクトリの下に「.mytop」ファイルを作って、そこに書いてやる。

sh-3.00$ vi .mytop
user=root
pass=(パスワードを記述)
host=localhost
db=
delay=1
port=3306
socket=
batchmode=0
header=1
color=1
idle=1

sh-3.00$ chmod 600 .mytop
これで自分とroot以外は読み書きできない。

なお、複数のDBを見たいので、dbの欄は空欄にしておいて、mytopコマンド実行時に、都度引数を与えてあげることにする。

sh-3.00$ mytop -d postfix

これで、さっきの画面が出てくるという寸法。ちなみに、どうせ見るなら最初から入っているDB「mysql」を引数に与えてやって見たほうがよいかも知れない。また、mytop実行時に、ThunderbirdとFFFTPを立ち上げて、それぞれ認証をやらせてみると

sh-3.00$ mytop -d mysql

====================================================================================================
MySQL on localhost (4.1.20)                                 up 0+00:01:52 [13:08:52]
Queries: 217.0 qps:    2 Slow:     0.0         Se/In/Up/De(%):    05/00/00/00
             qps now:    2 Slow qps: 0.0 Threads:    3 (   1/   0) 00/00/00/00
Key Efficiency: 76.9% Bps in/out: 55.9/ 4.3k   Now in/out: 42.0/ 4.6k

      Id      User         Host/IP         DB      Time    Cmd Query or State
      --      ----         -------         --      ----    --- ----------
       3      root       localhost      mysql         0
Query show full processlist
       5   proftpd       localhost    proftpd         9 Sleep
       4   postfix       localhost    postfix        24 Sleep
====================================================================================================

このように表示される。その他、運用上の注意としては以下の通り。