Mytop – A Useful Tool for Monitoring MySQL/MariaDB

Mytop – A Useful Tool for Monitoring MySQL/MariaDB

Mytop is an open source and free monitoring program for MySQL and MariaDB databases was written by Jeremy Zawodny using Perl language. It is much similar in look and feel of the most famous linux system monitoring tool called top.

Mytop program provides a command-line shell interface to monitor real time MySQL/MariaDB threads, queries per second, process list and performance of databases and gives a idea for the database administrator to better optimize the server to handle heavy load.

By default Mytop tool is included in the Fedora and Debian/Ubuntu repositories, so you just have to install it using your default package manager.

If you are using RHEL/CentOS distributions, then you need to enable third party EPEL repository to install it.

CentOS 7 64 Bit

# wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
# rpm -ivh epel-release-latest-7.noarch.rpm

CentOS 6 32-64 Bit

## RHEL/CentOS 6 32-Bit ##
# wget http://dl.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm
# rpm -ivh epel-release-6-8.noarch.rpm

## RHEL/CentOS 6 64-Bit ##
# wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
# rpm -ivh epel-release-6-8.noarch.rpm

For other Linux distributions you can get mytop source package and compile it from source as shown.

# tar -zxvf mytop-<version>.tar.gz
# cd mytop-
# perl Makefile.PL
# make
# make test
# make install

In this MySQL monitoring tutorial, we will show you how to install, configure and use mytop on various Linux distributions.

Please note you must have running MySQL/MariaDB Server on the system to install and use Mytop.

Installing Mytop

To install Mytop, run the appropriate command below for your Linux distribution to install it.

$ sudo apt install mytop	#Debian/Ubuntu
# yum install mytop	        #RHEL/CentOS
# dnf install mytop	        #Fedora 22+
# pacman -S mytop	        #Arch Linux
# zypper in mytop	        #openSUSE

Output :

[root@server ~]# yum install mytop
Loaded plugins: fastestmirror
base                                                                                                           | 3.6 kB  00:00:00
centosplus                                                                                                     | 3.4 kB  00:00:00
epel/x86_64/metalink                                                                                           | 3.4 kB  00:00:00
epel                                                                                                           | 4.7 kB  00:00:00
extras                                                                                                         | 3.4 kB  00:00:00
updates                                                                                                        | 3.4 kB  00:00:00
Determining fastest mirrors
 * base: centos.excellmedia.net
 * centosplus: centos.excellmedia.net
 * epel: repos.del.extreme-ix.org
 * extras: centos.excellmedia.net
 * updates: centos.excellmedia.net
Resolving Dependencies
--> Running transaction check
---> Package mytop.noarch 0:1.7-10.b737f60.el7 will be installed
--> Processing Dependency: perl(DBD::mysql) >= 1 for package: mytop-1.7-10.b737f60.el7.noarch
--> Processing Dependency: perl(Term::ReadKey) >= 2.1 for package: mytop-1.7-10.b737f60.el7.noarch
--> Processing Dependency: perl(DBI) for package: mytop-1.7-10.b737f60.el7.noarch
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:4.023-6.el7 will be installed
---> Package perl-DBI.x86_64 0:1.627-4.el7 will be installed
--> Processing Dependency: perl(RPC::PlServer) >= 0.2001 for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(RPC::PlClient) >= 0.2000 for package: perl-DBI-1.627-4.el7.x86_64
---> Package perl-TermReadKey.x86_64 0:2.30-20.el7 will be installed
--> Running transaction check
---> Package perl-PlRPC.noarch 0:0.2020-14.el7 will be installed
--> Processing Dependency: perl(Net::Daemon) >= 0.13 for package: perl-PlRPC-0.2020-14.el7.noarch
--> Processing Dependency: perl(Net::Daemon::Test) for package: perl-PlRPC-0.2020-14.el7.noarch
--> Processing Dependency: perl(Net::Daemon::Log) for package: perl-PlRPC-0.2020-14.el7.noarch
--> Processing Dependency: perl(Compress::Zlib) for package: perl-PlRPC-0.2020-14.el7.noarch
--> Running transaction check
---> Package perl-IO-Compress.noarch 0:2.061-2.el7 will be installed
--> Processing Dependency: perl(Compress::Raw::Zlib) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch
--> Processing Dependency: perl(Compress::Raw::Bzip2) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch
---> Package perl-Net-Daemon.noarch 0:0.48-5.el7 will be installed
--> Running transaction check
---> Package perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 will be installed
---> Package perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==================================================================================================
 Package                       Arch         Version               Repository              Size
==================================================================================================
 mytop                        noarch       1.7-10.b737f60.el7     epel                    33 k
Installing for dependencies:
 perl-Compress-Raw-Bzip2      x86_64       2.061-3.el7            base                    32 k
 perl-Compress-Raw-Zlib       x86_64       1:2.061-4.el7          base                    57 k
 perl-DBD-MySQL               x86_64       4.023-6.el7            base                   140 k
 perl-DBI                     x86_64       1.627-4.el7            base                   802 k
 perl-IO-Compress             noarch       2.061-2.el7            base                   260 k
 perl-Net-Daemon              noarch       0.48-5.el7             base                    51 k
 perl-PlRPC                   noarch       0.2020-14.el7          base                    36 k
 perl-TermReadKey             x86_64       2.30-20.el7            base                    31 k

Transaction Summary
===================================================================================================
Install  1 Package (+8 Dependent packages)

Total download size: 1.4 M
Installed size: 3.5 M
Is this ok [y/d/N]:y

Using Mytop to Monitor MySQL

Mytop needs MySQL/MariaDB login credentials to monitor databases and connects to the server with the root username by default. You can specify the necessary options for connecting to the database server on the command-line as you run it or in the file ~/.mytop .

Edit .mytop file as follows

#nano /root/.mytop

Now, add the below lines in mytop config file

host=localhost
db=mysql
delay=3
port=3306
socket=
batchmode=0
color=1
idle=1

Just run the following command to start the mytop and provide your MySQL/MariaDB root user password, when prompted. This will connect to the test database by default.

# mytop --prompt
Password:

You will get output as follows.

Mytop – A Useful Tool for Monitoring MySQL

MySQL on localhost (5.5.60-MariaDB)                                                                           up 0+00:06:42 [09:58:01]
 Queries: 144.0  qps:    0 Slow:     0.0         Se/In/Up/De(%):    00/00/00/00
             qps now:    1 Slow qps: 0.0  Threads:    1 (   1/   0) 00/00/00/00
 Key Efficiency: 100.0%  Bps in/out:   9.6/ 2.0k   Now in/out:  16.2/ 3.8k

      Id      User         Host/IP         DB      Time    Cmd Query or State
       --      ----         -------         --      ----    --- ----------
        4      root       localhost      mysql         0  Query show full processlist

If you would like to monitor specific database, then use the -d option as shown below. For example the below command will monitor database

# mytop --prompt -d test
Password:
MySQL on localhost (5.5.60-MariaDB)                                                                          
 Queries: 353.0  qps:    0 Slow:     0.0         Se/In/Up/De(%):    00/00/00/00

 Key Efficiency: 100.0%  Bps in/out:  11.6/ 2.5k

      Id      User         Host/IP         DB      Time    Cmd Query or State
       --      ----         -------         --      ----    --- ----------
      4657      root       localhost      test         0  Query show full processlist
      4665      root       localhost      test         0  sleep


[root@server ~]#

Hope, this will help you in finding your needs, please share your valuable comments to improve us.

 

You may also like...

Leave a Reply

Your email address will not be published.