How to Monitor MySQL Connections and Queries
This article will explain the installation and usage of mytop, a handy tool for live monitoring of MySQL queries. Also looking into various operations that a user can perform while monitoring the queries on mytop.
Mytop is a commandline tool that resembles the top command but for motoring MySQL. It requires MySQL 3.22 or later, the current MySQL 5.x versions are supported. It works fine with most Linux systems. For this tutorial I will use Ubuntu as basis. It needs Perl, DBI and Term::ReadKey installed on the system. With Term::ANSIColor you can get color in the output displayed if you want. The version 0.7 works even with Windows.
Installation of mytop:
apt-get install mytop
sudo apt-get install mytop
Type in the above commands to extract the data and finish the installation. For CentOS and RedHat users you will have to download rpmforge repository to install it automatically, or else you can go on with the manual method instead.
After the installation, its time to use mytop:
mytop --prompt (this will give you a password prompt, simply hit enter and you will be through)
Above is the default way, you can also type this:
mytop -u root -p (in here you will have to use your mysql password, because you are specifying a user "root" here)
If the above command returns an error like “root : NO PASS” or anything, try this:
mytop -u root -p xxxx ("xxxx" stands for your password)
Please check if your MySQL server is up and running before typing any of the above commands.
There are some arguments that you can pass with mytop such as:
-u or user username: Basically used to definne the username while loginig in to mytop. The default is set to “root”.
-p or pass or password password: Passwords used for the user login. The default password is just empty or none or just say leave the password field blank.
-h or host hostname[:port]: To specify ports if MySQL is not using the default port. The deault is set to 3306.
-s or -delay seconds: To specify the time gap for the refreh. The default is 5.
This are some of the arguments of mytop to find out more you may type:
The man pages hold more than enough information for anyone to understand anything about mytop.
You may create the above file by typing:
You may just check some other options in mytop by typing: ?. It will give a whole page of keyboard shorcuts that you can use in mytop.