Mtop is yet another console based mysql monitoring tool which helps the administrator to monitor the mysql server both locally and remotely. The tool functions are very similar like the mytop and other monitoring tool but it has the added feature called zooming‘ in on a process to show the complete query, ‘explaining‘ the query optimizer information for a query and ‘killing‘ queries. In addition, server performance statistics, configuration information, and tuning tips are also provided alone with this tool.
Mtop packages include another tool called mkill which monitors a MySQL server for long-running queries and kills them after a
specified time interval. Queries can be selected based on regexes on the user, host, command, database, state, and query.
Installing Mtop
To install the tool either by source or rpm, you need to met the following pre-requested to install and run the tool successfully.
- nCurses
DBI
DBD::mysql
Getopt::Long
Net::Domain
You can install the nCurses, DBI & DBD::mysql packages via yum
- # yum install ncurses ncurses-devel
- #yum install perl-DBI perl-DBD-MySQL
The other two (Getopt::Long & Net::Domain) need to be downloaded and install from perl.org or you can use the perl -Mcpan to install from the shell and once you installed the pre-requested you can go ahead with the mtop installation.
Mtop rpm can be downloaded from https://linux.die.net/man/1/mtop
Mtop source can be downloaded from http://sourceforge.net/projects/mtop/
- tar zxvf mtop-0.6.6.tar.gz cd mtop-0.6.6/# perl Makefile.PL
# make
# make install
Running Mtop
For more mtop help you can check the man mtop page. You need to create a user on mysql called mysqltop and this user should have all privileges set to N except Process_priv which must be set to Y.
- mysql> grant super, reload, process on *.* to mysqltop;
mysql> grant super, reload, process on *.* to mysqltop@localhost;
mysql> flush privileges;
And run the command ‘mtop’ to start monitoring your server. Similarly to monitor the remote mysql server.
- mtop –host=<hostname>–dbuser=<username> –password=<password> –seconds=1
Make sure you have open the port 3306 on the remote server to allow connection from the monitoring host, you also need to grant access to the monitoring server ip address for the user db to connect with the server.
mtop
Useful links :