How to install Mtop (MySQL top) in CentOS


mtop (MySQL top) monitors a MySQL database showing the queries which are taking the most amount of time to complete. Features include ‘zooming’ in on a process to show the complete query and ‘explaining’ the query optimizer information.

Step 1: Install RPMForge on CentOS

For Centos 5: How to Install RPMforge yum repository on CentOS 5
For Centos 6: How to Install RPMforge yum repository on CentOS 6

Step 2: install Mtop

yum install mtop

Result:

[root@tutorialspots ~]# yum install mtop
Loaded plugins: fastestmirror
Determining fastest mirrors
epel/metalink                                            |  11 kB     00:00
 * base: repos.dfw.quadranet.com
 * epel: fedora-epel.mirror.lstn.net
 * extras: dallas.tx.mirror.xygenhosting.com
 * rpmforge: mirror.team-cymru.org
 * updates: repo1.dal.innoscale.net
base                                                     | 3.7 kB     00:00
epel                                                     | 4.3 kB     00:00
epel/primary_db                                          | 5.9 MB     00:00
extras                                                   | 3.4 kB     00:00
extras/primary_db                                        |  37 kB     00:00
nginx                                                    | 2.9 kB     00:00
nginx/primary_db                                         | 8.3 kB     00:00
rpmforge                                                 | 1.9 kB     00:00
rpmforge/primary_db                                      | 2.7 MB     00:00
testing-1.1-devtools-6                                   |  951 B     00:00
updates                                                  | 3.4 kB     00:00
updates/primary_db                                       | 4.6 MB     00:00
vesta                                                    | 2.9 kB     00:00
vesta/primary_db                                         |  54 kB     00:00
vz-base                                                  |  951 B     00:00
vz-updates                                               |  951 B     00:00
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package mtop.noarch 0:0.6.6-1.2.el6.rf will be installed
--> Processing Dependency: perl(Curses) for package: mtop-0.6.6-1.2.el6.rf.noarc
h
--> Running transaction check
---> Package perl-Curses.x86_64 0:1.28-1.el6.rf will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package            Arch          Version                 Repository       Size
================================================================================
Installing:
 mtop               noarch        0.6.6-1.2.el6.rf        rpmforge         52 k
Installing for dependencies:
 perl-Curses        x86_64        1.28-1.el6.rf           rpmforge        164 k

Transaction Summary
================================================================================
Install       2 Package(s)

Total download size: 216 k
Installed size: 732 k
Is this ok [y/N]: y
Downloading Packages:
(1/2): mtop-0.6.6-1.2.el6.rf.noarch.rpm                  |  52 kB     00:00
(2/2): perl-Curses-1.28-1.el6.rf.x86_64.rpm              | 164 kB     00:00
--------------------------------------------------------------------------------
Total                                           589 kB/s | 216 kB     00:00
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
  Installing : perl-Curses-1.28-1.el6.rf.x86_64                             1/2
  Installing : mtop-0.6.6-1.2.el6.rf.noarch                                 2/2
  Verifying  : perl-Curses-1.28-1.el6.rf.x86_64                             1/2
  Verifying  : mtop-0.6.6-1.2.el6.rf.noarch                                 2/2

Installed:
  mtop.noarch 0:0.6.6-1.2.el6.rf

Dependency Installed:
  perl-Curses.x86_64 0:1.28-1.el6.rf

Complete!

Step 3: Connect MySQL Server

mysql -u root -p
mysql> grant super, reload, process on *.* to mysqltop;
mysql> grant super, reload, process on *.* to mysqltop@localhost;
mysql> flush privileges;
mysql> quit;

Result:

[root@tutorialspots ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.5.36 MySQL Community Server (GPL) by Remi

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant super, reload, process on *.* to mysqltop;
Query OK, 0 rows affected (0.00 sec)

mysql> grant super, reload, process on *.* to mysqltop@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
Bye

mtop connect mysql

Step 4: use mtop
For local MySQL Server:

mtop

Result:

load average: 0.11, 0.13, 0.15 mysqld 5.5.36 up 0 day(s),  0:04 hrs
5 threads: 1 running, 0 cached. Queries/slow: 13/0 Cache Hit: 98.95%
Opened tables: 0  RRN: 330  TLW: 1  SFJ: 0  SMP: 0  QPS: 0

ID       USER     HOST             DB           TIME   COMMAND STATE        INFO
57       mysqltop localhost                            Query                show

---

mtop

For Remote MySQL Server:

mtop  –host=remotehost –dbuser=username –password=password

Example:

mtop –host=daldb.vpsdime.com –dbuser=toturialspots –password=toturialspots

Related Post

, , ,

No comments yet.

Leave a Reply