What is Mytop

Mytop  is a tool written in Perl for monitoring MySQL databases. Similar to how top monitors system processes, mytop monitors MySQL threads and the database's overall performance, allowing system administrators or developers to get some insight on how applications are interacting with a database.

Download and Install Mytop Requirements

Mytop will using Perl, DBI, Term::ReadKey, Term::ANSIColor and Time::HiRes installed on the server first.

I believe Perl already installed on the server so we can skip perl.

Install DBI

#cd /root
#wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.611_92.tar.gz
#tar -zxvf DBI-1.611_92.tar.gz
#cd DBI-1.611_92
#perl Makefile.PL
#make && make install

Install Term::ReadKey

#cd /root
#wget http://search.cpan.org/CPAN/authors/id/K/KJ/KJALB/TermReadKey-2.14.tar.gz
#tar -zxvf TermReadKey-2.14.tar.gz
#cd TermReadKey-2.14
#perl Makefile.PL
#make && make install

Install Term::ANSIColor

#cd /root
#wget http://search.cpan.org/CPAN/authors/id/R/RR/RRA/ANSIColor-3.00.tar.gz
#tar -zxvf ANSIColor-3.00.tar.gz
#cd ANSIColor-3.00
#perl Makefile.PL
#make && make install

Install Time::HiRes

#cd /root
#wget http://search.cpan.org/CPAN/authors/id/J/JH/JHI/Time-HiRes-1.9721.tar.gz
#tar -zxvf Time-HiRes-1.9721.tar.gz
#cd Time-HiRes-1.9721
#perl Makefile.PL
#make && make install

Install Mytop

#cd /root
#wget http://jeremy.zawodny.com/mysql/mytop/mytop-1.6.tar.gz
#tar -zxvf mytop-1.6.tar.gz
#cd /root/mytop-1.6
#perl Makefile.PL
#make && make test && make install

Create Mytop Config File

#cd /root
#pico .mytop

user=root
pass=myrootpassword
host=localhost
db=wowtutorial
delay=5
port=3306
socket=/var/lib/mysql/mysql.sock
batchmode=0
header=1
color=1
idle=1

The above defines the defaults mytop will use. Here you can use the pass  option to store the password in the file; if you do, make sure that ~/.mytop is mode 0600 so no one other than you can read it. Also, if connecting to a local MySQL server, using the MySQL socket is better; if that is not an option use the port option to connect to the localhost on that port (default MySQL port is 3306). Provide the socket  option the full path to the mysql.sock socket file (typically this file is where the MySQL databases are stored, such as /var/lib/mysql/).

Mytop Result

#mytop --prompt

You will see a result screen like below

MySQL on localhost (5.1.45)                                                                                                                               up 6+21:18:39 [22:21:05]
 Queries: 19.0   qps:    0 Slow:     0.0         Se/In/Up/De(%):    72077558/00/00/00
             qps now:    0 Slow qps: 0.0  Threads:    2 (   2/   7) 42200/00/00/00
 Key Efficiency: 100.0%  Bps in/out:   0.0/  0.1   Now in/out:   8.4/ 1.1k

      Id      User         Host/IP         DB      Time    Cmd Query or State
      --      ----         -------         --      ----    --- ----------
  323854      root       localhost wowtutoria         0  Query show full processlist
  323890 wowtutori       localhost wowtutoria         0  Query SELECT title FROM menu_router WHERE path = ''


One downside to mytop is that it won't display information on multiple databases at one time, so if you have a server with multiple databases, you can only observe one at a time. The advantage to mytop, however, is that it can help diagnose problems with a slow database, or help you to fine tune your code by showing you slow queries, breaking down individual tasks, and so forth. While it may not be as useful as some other development tools, it is light-weight and easy to install and use on a remote server to keep an eye on key MySQL databases.