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.
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.
