mySQL is the database backbone behind a wide variety of software programs and scripts, but most people don’t know how to code for it properly. As such, mySQL-related overloads and optimization of sub-par mySQL databases can take a lot of time. The easiest way to track and troubleshoot troublesome threads is with mtop – live mySQL query querying.

Prerequisites

mtop requires curses. In CentOS, this is as simple as a few yum commands. There are a few extra steps for cPanel compatibility.

1. Install curses and curses-devel:
   
yum install curses curses-devel
2. Install curses-Perl:

   yum install curses-Perl

   1. If you’re on cPanel, you’ll first need to remove the perl* exclude from yum.conf.

      nano /etc/yum.conf

      In the exclude= line, remove “perl*” – save (CTRL+O) and exit (CTRL+X). Be sure to add it back after installing curses-Perl as per the above command.

Install mtop

Now it’s time to fetch the latest version of mtop and compile it. The homepage is here if you wish to check for the latest version. As of this post, we will be using 0.6.6, the latest version.

1. Grab the mtop source:

   wget http://softlayer.dl.sourceforge.net/project/mtop/mtop/v0.6.6/mtop-0.6.6.tar.gz

2. Configure, make, and install:

   tar -zxf mtop-0.6.6.tar.gz
cd mtop-0.6.6
perl Makefile.PL
make
make install

Configure and run

By default, mtop doesn’t connect as the root user. For most server installations, we want to connect as root. Here’s the quickest and easiest way to run mtop.

1. Find your current mySQL root password:

   cat /root/.my.cnf

2. Copy the root password (and hostname, if different than localhost) and run the command as follows:

   mtop --host=YOURHOSTNAME --dbuser=root --password=YOURPASSWORD --seconds=1