HowTo: Monitor What MySQL Server Is Doing

Dear nixCraft,

ADVERTISEMENTS

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

🐧 Get the latest tutorials on SysAdmin, Linux/Unix, Open Source & DevOps topics via:
CategoryList of Unix and Linux commands
File Managementcat
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilitiesdig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04

ADVERTISEMENTS
0 comments… add one

Leave a Reply

Your email address will not be published. Required fields are marked *

Use HTML <pre>...</pre>, <code>...</code> and <kbd>...</kbd> for code samples.