Monitoring MySQL servers using mytop on OpenSuSe & Others

MySQL is one of the most important programs on a server, unfortunatly it is also pretty resource intensive. One a server it is not uncommon for a single user or even a query to take up the bulk of the servers cpu cycles. Mytop is a very useful program to see what queries a server is currently processing as well as which user is executing them. Think of mytop as top for mysql. If you see a lot from a user that means they are probably the hog. Mytop can also be useful for figuring out exactly which queries are causing the problem in the case of a self-designed website. The following is how to install mytop on the server and run it.

Installation: use "1-click" installer to install mytop
OpenSuSe 11.1 - here
OpenSuSe 11.0 - here

Others: Download the latest version of mytop - here and follow the below instruction to compile and install mytop

tar -zxvf mytop-.tar.gz
  cd mytop-
  perl Makefile.PL
  make
  make test
  make install

Using mytop:
You can directly use the mytop by typing “mytop” on your console screen initially you will get a message that “Unable to connect to Mysql” you will need to provide the host name, database name, username, password

To connect to the MySql database using Mytop you will need to give all your MySql details as a parameter:

-u - username of the database
-p - password for connecting to the database
-h - Host Name
-d - database name

Example: mytop -u root -p password -h localhost -d test

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= xxxx
  host=localhost
  db=mysar
  delay=5
  port=3306
  socket=
  batchmode=0
  header=1
  color=1
  idle=1

Using a config file will help to ensure that your database password isn't visible to users on the command-line. Just make sure that the permissions on ~/.mytop are such that others cannot read it., now go to console and type command: # mytop and you should see something like ...