MyTop, mysql monitoring tools

MySQL, is one of important databases today. Most web server has it.
And you will want to monitor MySQL activity, to see what queries a server is currently processing as well as which user is executing them

MyTop is ‘top’ clone for mysql, a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL.

Installation (centOS/RHEL)
1. Look for gcc and gcc-c++ software packages in your system, this is essential for mytop.

# rpm -qa gcc
# rpm -qa gcc-c++

If�the packages found, go to step number 2. If the packages not found, please run this

# yum install gcc
# yum install gcc-c++

2. Install TermReadKey
go to usr/local/src directory

# cd /usr/local/src
# wget http://search.cpan.org/CPAN/authors/id/J/JS/JSTOWE/TermReadKey-2.30.tar.gz
# tar -zxvf TermReadKey-2.30.tar.gz
# cd TermReadKey-2.30
# perl Makefile.PL
# make test
# make
# make install

3. Install MyTop
After TermReadKey installation, you can go back to usr/local/src directory

# wget http://freshmeat.net/redir/mytop/6933/url_tgz/mytop-1.6.tar.gz
# tar -zxvf mytop-1.6.tar.gz
# cd mytop-1.6
# perl Makefile.PL
# make test
# make
# make install

4 Configure MyTop
go to root (~) directory

# cd /root

make file .mytop, this is where MyTop Configuration saved

# vi .mytop

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

Fill user, pass, host, db with your setting

5. Run MyTop

# mytop

More about MyTop
This explanation taken from MyTop Documentation.
You can read more there

The mytop display screen is really broken into two parts. The top 4 lines (header) contain summary information about your MySQL server.

MySQL on localhost (3.22.32) up 3+23:14:20 [23:54:52]
Queries Total: 617 Avg/Sec: 0.00 Now/Sec: 0.05 Slow: 0
Threads Total: 1 Active: 1 Cached: 0
Key Efficiency: 88.38% Bytes in: 0 Bytes out: 0

The first line identified the hostname of the server (localhost) and the version of MySQL it is running. The right had side shows the uptime of the MySQL server process in days+hours:minutes:seconds format (much like FreeBSD’s top) as well as the current time.

The second line displays the total number of queries the server has processed, the average number of queries per second, the real-time number of queries per second, and the number of slow queries.

The third line deals with threads. And the fourth line displays key buffer efficiency (how often keys are read from the buffer rather than disk) and the number of bytes that MySQL has sent and received.

The second part of the display lists as many threads as can fit on screen. By default they are sorted according to their idle time (least idle first). The display looks like:

Id User Host Dbase Time Cmd Query or State
– —- —- —– —- — ————–
61 jzawodn localhost music 0 Query show processlist

The thread id, username, host from which the user is connecting, database to which the user is connected, number of seconds of idle time, the command the thread is executing, and the query info are all displayed.