HowTo: Monitor What MySQL Server Is Doing

Dear nixCraft,

How can I monitor what my MySQL server is doing?

Sincerely,

Confused About MySQL Server

Dear Confused,

To solve MySQL problems or to optimize MySQL server you need to use special tool called mytop. It is a UNIX top command clone for MySQL server. It updates every few seconds, so you can get a reasonable look at your SQL performance. It is capable of displaying a huge amount of information.

Debian / Ubuntu Linux: Install mytop

Use the apt-get command to install the same or download it from official web site
# apt-get install mytop

CentOS / RHEL Linux: Install mytop

First, turn on EPEL repo and type:
# yum install mytop
Sample outputs:

Loaded plugins: auto-update-debuginfo, product-id, protectbase, rhnplugin, subscription-manager
Updating certificate-based repositories.
Unable to read consumer identity
0 packages excluded due to repository protections
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package mytop.noarch 0:1.7-3.b737f60.el6 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
====================================================================================================
 Package             Arch                 Version                          Repository          Size
====================================================================================================
Installing:
 mytop               noarch               1.7-3.b737f60.el6                epel                32 k
Transaction Summary
====================================================================================================
Install       1 Package(s)
Total download size: 32 k
Installed size: 68 k
Is this ok [y/N]: y
Downloading Packages:
mytop-1.7-3.b737f60.el6.noarch.rpm                                           |  32 kB     00:00
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : mytop-1.7-3.b737f60.el6.noarch                                                   1/1
Installed products updated.
  Verifying  : mytop-1.7-3.b737f60.el6.noarch                                                   1/1
Installed:
  mytop.noarch 0:1.7-3.b737f60.el6
Complete!

How do I use mytop command?

The syntax is as follows:

 
mytop -u DB-USERNAME -p DB-PASSWORD-HERE

In this example, connect using admin username and password as 123456:
$ mytop -u admin -p 123456
Sample outputs:

You can also specify a database name such as foobar to monitor:
$ mytop -u admin -p 123456 -d foobar

Finally, you can store this information in a config file called ~/.mytop file:
$ cat ~/.mytop
Sample outputs:

user=admin
pass=123456
host=mysql0.hosting.some.com
db=imail
delay=10
port=3306
socket=
batchmode=0
header=1
color=1
idle=1

mytop is capable of displaying a large amount of information and it supports lots of keyboard shortcut too, so be sure to read the man page and documentation of mtop.

Sincerely,
nixCfraft