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
For CentOS 7: How to Install RPMforge yum repository on CentOS 7
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
If you see error:
ERROR 1133 (28000): Can't find any matching row in the user table
You can quit mysql then do Step 4 with account root
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 ---
For Remote MySQL Server:
mtop –host=remotehost –dbuser=username –password=password
Example:
mtop –host=daldb.vpsdime.com –dbuser=toturialspots –password=toturialspots
